Inserting values

Transact-SQL
1

This query checks if there are values listed are available, I would like to insert another value,

create table #goldList (

name VARCHAR(50),
taskactive varchar (1)
)

---** INSERT INTO @goldlist
--VALUES('drugname','FormName','RelevantInfoPolicy','AvailabilityPolicy',
--'DurationInDays','CodedFreq','ClassType','ClassValue')

create index gl_name on #goldlist (name)

--- Checks for all Pharmacy Catalog items March 2009
--- Added and ocmi.name not like 'Saline Lock%' to not look for this item.

INSERT INTO #goldlist
select distinct ocmi.name [name] , cit.active [taskactive]
FROM cv3ordercatalogmasteritem ocmi
left join cv3catalogitemtask cit
on ocmi.guid=cit.ordercatalogmasteritemguid
LEFT JOIN CV3CatalogTaskLocationXRef tasklocxref
on cit.guid=tasklocxref.CatalogItemTaskGUID
join cv3location l
on l.guid=tasklocxref.LocationGroupGUID
WHERE ocmi.active=1
and ocmi.name not like 'zz%'
and ocmi.name not like 'qq%'
and ocmi.name <> 'refill'
and ocmi.name not like 'qa%'
and ocmi.name not like 'Saline Lock%'

/*
select ocmi.name

from cv3ordercatalogmasteritem ocmi

left JOIN

CV3OrganizationalUnit ou

ON ocmi.orgunitguid = ou.guid

where ou.name = 'Pharmacy'
and ou.active=1
and ocmi.name not like 'zz%'
and ocmi.name not like 'qq%'
and ocmi.name <> 'SK RX UOMTEST'
and ocmi.name <> 'SKStylenol'
and ocmi.name <> 'refill'
and ocmi.name not like 'qa%'
and ocmi.active=1*/

--------DO NOT REMOVE 2 INSERTED ITEMS BELOW----------------------------------
/*
INSERT INTO @goldlist VALUES('Transfusion Medicine Infusion Order' )

INSERT INTO @goldlist VALUES('Transfusion Medicine Injection Order')
---Saline Lock also checked??--- */

---We get this TaskLocations from Andrea----
Declare @TaskLocations TABLE (
Location VARCHAR(40)
)

INSERT INTO @tasklocations VALUES('FMC-100')
INSERT INTO @tasklocations VALUES('FMC-101')
INSERT INTO @tasklocations VALUES('FMC-102')
INSERT INTO @tasklocations VALUES('FMC-103A')
INSERT INTO @tasklocations VALUES('FMC-103B')
INSERT INTO @tasklocations VALUES('FMC-111')
INSERT INTO @tasklocations VALUES('FMC-112')
INSERT INTO @tasklocations VALUES('FMC-21')
INSERT INTO @tasklocations VALUES('FMC-22')
INSERT INTO @tasklocations VALUES('FMC-23')
INSERT INTO @tasklocations VALUES('FMC-26')
INSERT INTO @tasklocations VALUES('FMC-27 Hemodialysis')
INSERT INTO @tasklocations VALUES('FMC-30')
INSERT INTO @tasklocations VALUES('FMC-31')
INSERT INTO @tasklocations VALUES('FMC-32')
INSERT INTO @tasklocations VALUES('FMC-36')
INSERT INTO @tasklocations VALUES('FMC-37A')
INSERT INTO @tasklocations VALUES('FMC-37B')
INSERT INTO @tasklocations VALUES('FMC-42')
INSERT INTO @tasklocations VALUES('FMC-46')
INSERT INTO @tasklocations VALUES('FMC-47')
INSERT INTO @tasklocations VALUES('FMC-47D')
INSERT INTO @tasklocations VALUES('FMC-51')
INSERT INTO @tasklocations VALUES('FMC-52')
INSERT INTO @tasklocations VALUES('FMC-55')
INSERT INTO @tasklocations VALUES('FMC-57A')
INSERT INTO @tasklocations VALUES('FMC-57B')
INSERT INTO @tasklocations VALUES('FMC-58')
INSERT INTO @tasklocations VALUES('FMC-61')
INSERT INTO @tasklocations VALUES('FMC-62')
INSERT INTO @tasklocations VALUES('FMC-71')
INSERT INTO @tasklocations VALUES('FMC-72')
INSERT INTO @tasklocations VALUES('FMC-81')
INSERT INTO @tasklocations VALUES('FMC-82')
INSERT INTO @tasklocations VALUES('FMC-91')
INSERT INTO @tasklocations VALUES('FMC-92')
INSERT INTO @tasklocations VALUES('FMC-94')
INSERT INTO @tasklocations VALUES('FMC-ECT')
INSERT INTO @tasklocations VALUES('FMC-GOR')
INSERT INTO @tasklocations VALUES('FMC-OBS')
INSERT INTO @tasklocations VALUES('FMC-OR1')
INSERT INTO @tasklocations VALUES('FMC-OR7')
INSERT INTO @tasklocations VALUES('FMC-OR9')
INSERT INTO @tasklocations VALUES('FMC-PACU Womens')
INSERT INTO @tasklocations VALUES('FMC-PACU1')
INSERT INTO @tasklocations VALUES('FMC-PACU1FLR')
INSERT INTO @tasklocations VALUES('FMC-PACU7')
INSERT INTO @tasklocations VALUES('FMC-PACU7FLR')
INSERT INTO @tasklocations VALUES('FMC-TEST')
INSERT INTO @tasklocations VALUES('PLC-22')
INSERT INTO @tasklocations VALUES('PLC-23B')
INSERT INTO @tasklocations VALUES('PLC-23F')
INSERT INTO @tasklocations VALUES('PLC-25')
INSERT INTO @tasklocations VALUES('PLC-27')
INSERT INTO @tasklocations VALUES('PLC-31')
INSERT INTO @tasklocations VALUES('PLC-32')
INSERT INTO @tasklocations VALUES('PLC-33')
INSERT INTO @tasklocations VALUES('PLC-34')
INSERT INTO @tasklocations VALUES('PLC-35A')
INSERT INTO @tasklocations VALUES('PLC-35B')
INSERT INTO @tasklocations VALUES('PLC-38')
INSERT INTO @tasklocations VALUES('PLC-41')
INSERT INTO @tasklocations VALUES('PLC-42')
INSERT INTO @tasklocations VALUES('PLC-43')
INSERT INTO @tasklocations VALUES('PLC-44')
INSERT INTO @tasklocations VALUES('PLC-51')
INSERT INTO @tasklocations VALUES('PLC-52')
INSERT INTO @tasklocations VALUES('PLC-53')
INSERT INTO @tasklocations VALUES('PLC-53V')
INSERT INTO @tasklocations VALUES('PLC-54')
INSERT INTO @tasklocations VALUES('PLC-Day Surgery')
INSERT INTO @tasklocations VALUES('PLC-OBS')
INSERT INTO @tasklocations VALUES('PLC-OR')
INSERT INTO @tasklocations VALUES('PLC-PACU')
INSERT INTO @tasklocations VALUES('PLC-PACU-Temp')
INSERT INTO @tasklocations VALUES('PLC-Pediatric Day Surgery')
INSERT INTO @tasklocations VALUES('PLC-TEST')
INSERT INTO @tasklocations VALUES('RGH-24HR')
INSERT INTO @tasklocations VALUES('RGH-45')
INSERT INTO @tasklocations VALUES('RGH-46')
INSERT INTO @tasklocations VALUES('RGH-47')
INSERT INTO @tasklocations VALUES('RGH-48')
INSERT INTO @tasklocations VALUES('RGH-49')
INSERT INTO @tasklocations VALUES('RGH-56')
INSERT INTO @tasklocations VALUES('RGH-57')
INSERT INTO @tasklocations VALUES('RGH-58')
INSERT INTO @tasklocations VALUES('RGH-59')
INSERT INTO @tasklocations VALUES('RGH-61')
INSERT INTO @tasklocations VALUES('RGH-61B')
INSERT INTO @tasklocations VALUES('RGH-62A')
INSERT INTO @tasklocations VALUES('RGH-62B')
INSERT INTO @tasklocations VALUES('RGH-63')
INSERT INTO @tasklocations VALUES('RGH-64')
INSERT INTO @tasklocations VALUES('RGH-71')
INSERT INTO @tasklocations VALUES('RGH-72')
INSERT INTO @tasklocations VALUES('RGH-73')
INSERT INTO @tasklocations VALUES('RGH-74')
INSERT INTO @tasklocations VALUES('RGH-81')
INSERT INTO @tasklocations VALUES('RGH-82')
INSERT INTO @tasklocations VALUES('RGH-ADS')
INSERT INTO @tasklocations VALUES('RGH-CCU')
INSERT INTO @tasklocations VALUES('RGH-CYG3')
INSERT INTO @tasklocations VALUES('RGH-Day Surgery')
INSERT INTO @tasklocations VALUES('RGH-ECT')
INSERT INTO @tasklocations VALUES('RGH-ICU')
INSERT INTO @tasklocations VALUES('RGH-OBS')
INSERT INTO @tasklocations VALUES('RGH-OR4')
INSERT INTO @tasklocations VALUES('RGH-PACU')
INSERT INTO @tasklocations VALUES('RGH-PACU 3rd Floor')
INSERT INTO @tasklocations VALUES('RGH-PACU-Temp')
INSERT INTO @tasklocations VALUES('RGH-TEST')
INSERT INTO @tasklocations VALUES('SAFPC-SAFPC1')
INSERT INTO @tasklocations VALUES('SAFPC-SAFPC2')
INSERT INTO @tasklocations VALUES('FMC-Day Medicine Temp')
INSERT INTO @tasklocations VALUES('RGH-83')
INSERT INTO @tasklocations VALUES('RGH-84')
INSERT INTO @tasklocations VALUES('RGH-93')
INSERT INTO @tasklocations VALUES('RGH-94')
INSERT INTO @tasklocations VALUES('RGH-Day Medicine Temp')
INSERT INTO @tasklocations VALUES('ACH-1')
INSERT INTO @tasklocations VALUES('ACH-2')
INSERT INTO @tasklocations VALUES('ACH-24H')
INSERT INTO @tasklocations VALUES('ACH-3')
INSERT INTO @tasklocations VALUES('ACH-4')
INSERT INTO @tasklocations VALUES('ACH-ACHADOP')
INSERT INTO @tasklocations VALUES('ACH-ACHPACU')
INSERT INTO @tasklocations VALUES('ACH-Day Surgery')
INSERT INTO @tasklocations VALUES('ACH-Hemodialysis')
INSERT INTO @tasklocations VALUES('ACH-Hemodialysis Temp')
INSERT INTO @tasklocations VALUES('ACH-MH')
INSERT INTO @tasklocations VALUES('ACH-OR')
INSERT INTO @tasklocations VALUES('ACH-PACU Temp')
INSERT INTO @tasklocations VALUES('ACH-PICU')
INSERT INTO @tasklocations VALUES('ACH-TEST')
INSERT INTO @tasklocations VALUES('PLC-37')
INSERT INTO @tasklocations VALUES('PLC-28')
INSERT INTO @tasklocations VALUES('PLC-29')
INSERT INTO @tasklocations VALUES('PLC-39')
INSERT INTO @tasklocations VALUES('PLC-48')
INSERT INTO @tasklocations VALUES('PLC-49')
INSERT INTO @tasklocations VALUES('ACH-SSSU')
INSERT INTO @tasklocations VALUES('ACH-Pediatric Surgery OR')
INSERT INTO @tasklocations VALUES('S M Chumir-Urgent Care')
INSERT INTO @tasklocations VALUES('SCHC-Urgent Care')
INSERT INTO @tasklocations VALUES('RGH-Med Assess Unit')
INSERT INTO @tasklocations VALUES('ACH-*Emergency')
INSERT INTO @tasklocations VALUES('RGH-**Emergency')
INSERT INTO @tasklocations VALUES('FMC-*Emergency')
INSERT INTO @tasklocations VALUES('PLC-*Emergency')
INSERT INTO @tasklocations VALUES('FMC-ED TEST ')
INSERT INTO @tasklocations VALUES('S M Chumir-UC TEST ')
INSERT INTO @tasklocations VALUES('FMC-Bronchoscopy Suite')
INSERT INTO @tasklocations VALUES('RGH-Day Medicine Inpatient')
INSERT INTO @tasklocations VALUES('FMC-Day Medicine Inpatient')
INSERT INTO @tasklocations VALUES('SCHC-UC TEST')
INSERT INTO @tasklocations VALUES('FMC-Hematology/Oncology Clinic')
INSERT INTO @tasklocations VALUES('FMC-54')
INSERT INTO @tasklocations VALUES('FMC-64')
INSERT INTO @tasklocations VALUES('FMC-Day Surgery McCaig Tower')
INSERT INTO @tasklocations VALUES('FMC-PACU3')
INSERT INTO @tasklocations VALUES('FMC-SSU')
INSERT INTO @tasklocations VALUES('FMC-44')
INSERT INTO @tasklocations VALUES('FMC-OR3')
INSERT INTO @tasklocations VALUES('FMC-PACU3FLR')
INSERT INTO @tasklocations VALUES('FMC-ICUA')
INSERT INTO @tasklocations VALUES('FMC-ICUB')
INSERT INTO @tasklocations VALUES('FMC-ICUC')
INSERT INTO @tasklocations VALUES('FMC-41A')
INSERT INTO @tasklocations VALUES('FMC-41B')
INSERT INTO @tasklocations VALUES('ACH-NICU')
INSERT INTO @tasklocations VALUES('RGH-IP Flex Unit')
INSERT INTO @tasklocations VALUES('FMC-ED TST TEMP')
INSERT INTO @tasklocations VALUES('RFH-1')
INSERT INTO @tasklocations VALUES('FMC-ECT Clinic')
INSERT INTO @tasklocations VALUES('SHC-Cardiac Function Clinic')
INSERT INTO @tasklocations VALUES('SHC-Atrial Fibrillation Clinic')
INSERT INTO @tasklocations VALUES('SHC-Cardiac Arrhythmia Service')
INSERT INTO @tasklocations VALUES('SHC-Pacemaker Clinic')
INSERT INTO @tasklocations VALUES('SHC-Cardiology Clinic')
INSERT INTO @tasklocations VALUES('SHC-ECG Labs')
INSERT INTO @tasklocations VALUES('SHC-ECHO Labs')
INSERT INTO @tasklocations VALUES('SHC-Nuclear Cardiology')
INSERT INTO @tasklocations VALUES('FMC-Rare Blood/Bleed Disorders')
INSERT INTO @tasklocations VALUES('PLC-38 Hematology')
INSERT INTO @tasklocations VALUES('SHC-PACU Temp')
INSERT INTO @tasklocations VALUES('SHC-Surg Suites Temp')
INSERT INTO @tasklocations VALUES('SHC-SSU')
INSERT INTO @tasklocations VALUES('SHC-Rapid Access')
INSERT INTO @tasklocations VALUES('SHC-ICUA')
INSERT INTO @tasklocations VALUES('SHC-ICUB')
INSERT INTO @tasklocations VALUES('SHC-ICUC')
INSERT INTO @tasklocations VALUES('SHC-ICUD')
INSERT INTO @tasklocations VALUES('SHC-580')
INSERT INTO @tasklocations VALUES('SHC-ADOP')
INSERT INTO @tasklocations VALUES('SHC-PACU1')
INSERT INTO @tasklocations VALUES('PLC-Hematology Clinic')
INSERT INTO @tasklocations VALUES('PLC-Treatment Room')
INSERT INTO @tasklocations VALUES('SHC Day Surgery')
INSERT INTO @tasklocations VALUES('SHC-680')
INSERT INTO @tasklocations VALUES('SHC-660')
INSERT INTO @tasklocations VALUES('SHC-560A')
INSERT INTO @tasklocations VALUES('SHC-560B')
INSERT INTO @tasklocations VALUES('SHC-560C')
INSERT INTO @tasklocations VALUES('SHC-780')
INSERT INTO @tasklocations VALUES('SHC-Day Medicine')
INSERT INTO @tasklocations VALUES('FMC-DI OP Recovery')
INSERT INTO @tasklocations VALUES('ACH-NICU OP Medical')
INSERT INTO @tasklocations VALUES('SHC-TEST')
INSERT INTO @tasklocations VALUES('SHC-*Emergency')
INSERT INTO @tasklocations VALUES('SHC-ED TST')
INSERT INTO @tasklocations VALUES('SHC-Endoscopy Unit')
INSERT INTO @tasklocations VALUES('SHC-HPTP Clinic')
INSERT INTO @tasklocations VALUES('ACH-ED-Fast Track')
INSERT INTO @tasklocations VALUES('ACH-ED-Holding Area')
INSERT INTO @tasklocations VALUES('ACH-ED-Intake')
INSERT INTO @tasklocations VALUES('ACH-ED-MH/Main')
INSERT INTO @tasklocations VALUES('ACH-ED-Middle')
INSERT INTO @tasklocations VALUES('ACH-ED-Registration Area')
INSERT INTO @tasklocations VALUES('ACH-ED-SST')
INSERT INTO @tasklocations VALUES('ACH-ED-Trauma/Procedure')
INSERT INTO @tasklocations VALUES('ACH-ED-Triage')
INSERT INTO @tasklocations VALUES('FMC-ED-40s')
INSERT INTO @tasklocations VALUES('FMC-ED-50s')
INSERT INTO @tasklocations VALUES('FMC-ED-60s')
INSERT INTO @tasklocations VALUES('FMC-ED-70s')
INSERT INTO @tasklocations VALUES('FMC-ED-Holding Area')
INSERT INTO @tasklocations VALUES('FMC-ED-Mon 1')
INSERT INTO @tasklocations VALUES('FMC-ED-Mon 2')
INSERT INTO @tasklocations VALUES('FMC-ED-Mon 3')
INSERT INTO @tasklocations VALUES('FMC-ED-Mon 4')
INSERT INTO @tasklocations VALUES('FMC-ED-Registration Area')
INSERT INTO @tasklocations VALUES('FMC-ED-Trauma')
INSERT INTO @tasklocations VALUES('FMC-ED-Triage')
INSERT INTO @tasklocations VALUES('PLC-ED-Area A')
INSERT INTO @tasklocations VALUES('PLC-ED-Area B')
INSERT INTO @tasklocations VALUES('PLC-ED-Area C')
INSERT INTO @tasklocations VALUES('PLC-ED-Area D')
INSERT INTO @tasklocations VALUES('PLC-ED-Area E')
INSERT INTO @tasklocations VALUES('PLC-ED-Gyne/Psych')
INSERT INTO @tasklocations VALUES('PLC-ED-Holding Area')
INSERT INTO @tasklocations VALUES('PLC-ED-Intake A')
INSERT INTO @tasklocations VALUES('PLC-ED-Intake B')
INSERT INTO @tasklocations VALUES('PLC-ED-Main WR')
INSERT INTO @tasklocations VALUES('PLC-ED-Registration Area')
INSERT INTO @tasklocations VALUES('PLC-ED-Resuscitation')
INSERT INTO @tasklocations VALUES('PLC-ED-Treatment')
INSERT INTO @tasklocations VALUES('RGH-ED-Area A')
INSERT INTO @tasklocations VALUES('RGH-ED-Area B1')
INSERT INTO @tasklocations VALUES('RGH-ED-Area B2')
INSERT INTO @tasklocations VALUES('RGH-ED-Area C')
INSERT INTO @tasklocations VALUES('RGH-ED-Area E')
INSERT INTO @tasklocations VALUES('RGH-ED-Area F')
INSERT INTO @tasklocations VALUES('RGH-ED-Hold Area')
INSERT INTO @tasklocations VALUES('RGH-ED-Registration Area')
INSERT INTO @tasklocations VALUES('RGH-ED-Resus 1-2')
INSERT INTO @tasklocations VALUES('RGH-ED-Triage')
INSERT INTO @tasklocations VALUES('SMCHC-UC-Area A')
INSERT INTO @tasklocations VALUES('SMCHC-UC-Area B')
INSERT INTO @tasklocations VALUES('SMCHC-UC-Area C')
INSERT INTO @tasklocations VALUES('SMCHC-UC-Mental Health')
INSERT INTO @tasklocations VALUES('SMCHC-UC-Registration Area')
INSERT INTO @tasklocations VALUES('SMCHC-UC-Resus Room')
INSERT INTO @tasklocations VALUES('SMCHC-UC-Triage')
INSERT INTO @tasklocations VALUES('SCHC-UC-Fast Track')
INSERT INTO @tasklocations VALUES('SCHC-UC-Green')
INSERT INTO @tasklocations VALUES('SCHC-UC-Holding Area')
INSERT INTO @tasklocations VALUES('SCHC-UC-Monitors')
INSERT INTO @tasklocations VALUES('SCHC-UC-Registration Area')
INSERT INTO @tasklocations VALUES('SCHC-UC-Triage')
INSERT INTO @tasklocations VALUES('SCHC-UC-Yellow')
INSERT INTO @tasklocations VALUES('SHC-ED-Area A')
INSERT INTO @tasklocations VALUES('SHC-ED-Area D')
INSERT INTO @tasklocations VALUES('SHC-ED-Area E')
INSERT INTO @tasklocations VALUES('SHC-ED-Area F')
INSERT INTO @tasklocations VALUES('SHC-ED-Hold Room')
INSERT INTO @tasklocations VALUES('SHC-ED-Intake')
INSERT INTO @tasklocations VALUES('SHC-ED-Registration Area')
INSERT INTO @tasklocations VALUES('SHC-ED-Resuscitation')
INSERT INTO @tasklocations VALUES('SHC-ED-Triage Room')
INSERT INTO @tasklocations VALUES('ACH-ED-MH/Main2')
INSERT INTO @tasklocations VALUES('ACH-ED-Middle2')
INSERT INTO @tasklocations VALUES('ACH-ED-Procedure')
INSERT INTO @tasklocations VALUES('RGH-ED-Resus 3')
INSERT INTO @tasklocations VALUES('SHC-NICU')
INSERT INTO @tasklocations VALUES('SHC-760')
INSERT INTO @tasklocations VALUES('PLC-ED-Resuscitation2')
INSERT INTO @tasklocations VALUES('FMC-ED-Trauma 2')
INSERT INTO @tasklocations VALUES('SHC-Early Gestation Assessment')
INSERT INTO @tasklocations VALUES('SHC-Maternity Triage')
INSERT INTO @tasklocations VALUES('SHC-Obstetrics/Gynecology Cl')
INSERT INTO @tasklocations VALUES('FMC-TEST OP')
INSERT INTO @tasklocations VALUES('SHC-Bronchoscopy')
INSERT INTO @tasklocations VALUES('ACH-IP Preadmission')
INSERT INTO @tasklocations VALUES('FMC-IP Preadmission')
INSERT INTO @tasklocations VALUES('PLC-IP Preadmission')
INSERT INTO @tasklocations VALUES('RGH-IP Preadmission')
INSERT INTO @tasklocations VALUES('SHC-IP Preadmission')
INSERT INTO @tasklocations VALUES('SHC-Womens Procedure Clinic')
INSERT INTO @tasklocations VALUES('PLC-Pediatric Day Medicine')
INSERT INTO @tasklocations VALUES('SHC-Bronch Temp')
INSERT INTO @tasklocations VALUES('RGH-Area D')
INSERT INTO @tasklocations VALUES('FMC-Antenatal Consultation Cl')
INSERT INTO @tasklocations VALUES('PLC-Limb Salvage Clinic')
INSERT INTO @tasklocations VALUES('FMC-104')
INSERT INTO @tasklocations VALUES('PLC-Procedure Recovery')
INSERT INTO @tasklocations VALUES('PLC-Hemodialysis PCU 12')
INSERT INTO @tasklocations VALUES('FMC-Day Cardiology')
INSERT INTO @tasklocations VALUES('PLC-12 Hemodialysis')
INSERT INTO @tasklocations VALUES('SHC-Cardio-Oncology Clinic')
INSERT INTO @tasklocations VALUES('SHC-Structural Heart Clinic')
INSERT INTO @tasklocations VALUES('SHC-CCU')
INSERT INTO @tasklocations VALUES('PLC-Day Hospital')
INSERT INTO @tasklocations VALUES('FMC-Cardiology Unit 92 Temp')
INSERT INTO @tasklocations VALUES('FMC-Cardiology Cath Lab Temp')
INSERT INTO @tasklocations VALUES('FMC-Cardiology Unit 103a Temp')
INSERT INTO @tasklocations VALUES('FMC-Cardiology Unit 103b Temp')
INSERT INTO @tasklocations VALUES('S M Chumir-PoliceAndCrisisTeam')
INSERT INTO @tasklocations VALUES('RGH-Cardiac Function Cl')
INSERT INTO @tasklocations VALUES('PLC-Day Medicine Temp')
INSERT INTO @tasklocations VALUES('PLC-Day Medicine')
INSERT INTO @tasklocations VALUES('FMC-Short Stay Cardiology')
INSERT INTO @tasklocations VALUES('RGH-ED-Area D')
INSERT INTO @tasklocations VALUES('SHC-Wound Services')
INSERT INTO @tasklocations VALUES('PLC-Pulmonary Procedure Room')
INSERT INTO @tasklocations VALUES('ACH-HOT After Hours Care')
INSERT INTO @tasklocations VALUES('RGH-CCU-Temp')
INSERT INTO @tasklocations VALUES('FMC-Critical Care Follow Up Cl')
INSERT INTO @tasklocations VALUES('FMC-Day Surgery-TBCC')
INSERT INTO @tasklocations VALUES('FMC-TBCC ADOP')
INSERT INTO @tasklocations VALUES('FMC-TBCC OR')
INSERT INTO @tasklocations VALUES('FMC-TBCC PACU')
INSERT INTO @tasklocations VALUES('FMC-TBCC PACU Temp')
INSERT INTO @tasklocations VALUES('PLC-Advanced Venous Access Ser')
INSERT INTO @tasklocations VALUES('FMC-ED-80s')
INSERT INTO @tasklocations VALUES('FMC-Maternal Fetal Medicine')
INSERT INTO @tasklocations VALUES('PLC-58')
INSERT INTO @tasklocations VALUES('PLC-59')
INSERT INTO @tasklocations VALUES('PLC-CCU Temp')
INSERT INTO @tasklocations VALUES('PLC-Maternal Fetal Medicine')
INSERT INTO @tasklocations VALUES('RGH-Maternal Fetal Medicine')
INSERT INTO @tasklocations VALUES('SCHC-Test UC-Blue')
INSERT INTO @tasklocations VALUES('SCHC-Test UC-WR')
INSERT INTO @tasklocations VALUES('SHC-ED-Green Area')
INSERT INTO @tasklocations VALUES('SHC-Maternal Fetal Medicine')

Create Table #Match (
itemname VARCHAR(50),
location VARCHAR(50)
)

CREATE INDEX match_name ON #Match(ITEMNAME)
insert into #match
Select gl.name as [itemname], l.location as [location]
from #goldlist gl, @tasklocations l
where gl.taskactive=1

--select itemname [MATCHITEM], location [MATCHLocation] from #Match
--order by 1, 2
Create Table #Compare (
itemname VARCHAR(50),
location VARCHAR(50),
taskactive varchar (1)
)

CREATE INDEX cp_name ON #Compare(ITEMNAME)
insert into #compare
select distinct ocmi.name [itemname], l.name [location] , cit.active [taskactive]
FROM cv3ordercatalogmasteritem ocmi
left join cv3catalogitemtask cit
on ocmi.guid=cit.ordercatalogmasteritemguid
LEFT JOIN CV3CatalogTaskLocationXRef tasklocxref
on cit.guid=tasklocxref.CatalogItemTaskGUID
join cv3location l
on l.guid=tasklocxref.LocationGroupGUID
join #match m
on ocmi.name=m.itemname
---WHERE ocmi.codedtimegroupcode = 'RX_General'
WHERE ocmi.active=1
and ocmi.name not like 'zz%'
and ocmi.name not like 'qq%'
and ocmi.name <> 'refill'
and cit.active=1

--select itemname [CompreITEM], location [compareLocation], taskactive
--[active] from #compare
--cheb touch

---I. This is for the Missing locations---
select distinct 'RX_UNIT_108' as [CASENUM], m.itemname [ListedItems],
M.location [Detailed Locations Missing], c.itemname [CompareItem],
c.location [comparelocation]
from #match m
left join #compare c
on m.itemname = c.itemname and m.location = c.location and
c.taskactive=1
where (c.itemname is NULL and c.location is NULL)
order by m.itemname, m.location

----II. This one is for Extra locations ---
select distinct 'RX_UNIT_109' as [CASENUM], m.itemname [ListedItems],
M.location [ListedLocation], c.itemname [CompareItem], c.location
[Extra Locations Attached]
from #match m
right join #compare c
on m.itemname = c.itemname and m.location = c.location
where m.itemname is null and m.location is null and c.taskactive=1
order by c.itemname, c.location

---III. Those items without attached locations or have inactive tasks
select distinct 'RX_UNIT_110' as [CASENUM], ocmi.name as 'No Locations
or Inactive', cit.touchedwhen [Touched_When], cit.touchedby
[Touched_By],
case
when cit.active=0 then 'InactiveLocation'
when l.name is null then 'No Location' else 'ok'
end as Result,
case when ocmi.orgunitguid=9000001002083001 then 'Yes'
else 'No'
end as 'Pharmacy?'
from cv3ordercatalogmasteritem ocmi
JOIN #match m
on ocmi.name=m.itemname
join CV3OrganizationalUnit ou
ON ocmi.orgunitguid = ou.guid
left join #goldlist gl
on ocmi.name=gl.name
left join cv3catalogitemtask cit
on ocmi.guid=cit.ordercatalogmasteritemguid
left join cv3catalogtasklocationxref locxrf
on locxrf.CatalogItemTaskGUID=cit.guid
left join cv3location l
on locxrf.LocationGroupGUID=l.guid
where ocmi.orgunitguid=9000001002083001
and (ocmi.name not like 'zz%'
and ocmi.name not like 'qq%'
and l.name is null) or

(ocmi.orgunitguid=9000001002083001
and ocmi.name not like 'zz%'
and ocmi.name not like 'qq%' and
cit.active=0)

or (ocmi.name not like 'zz%'
and ocmi.name not like 'qq%' and
cit.active=0)
order by 5 desc , 1, 2

------IV: Should Fail if item is set to ALL Location or External Task--

select * from
(
select distinct ocmi.name, cit.touchedby [Last Touched],
--cit.isforalllocations, cit.isexternallygenerated,
case when cit.isforalllocations=1 then 'FAIL Due to AllLocations'
when cit.isexternallygenerated=1 then 'FAIL Due to External Task'
else 'PASS'
end as [TASKResult]
from cv3ordercatalogmasteritem ocmi
JOIN #match m
on ocmi.name=m.itemname
--join #goldlist gl
--on ocmi.name=gl.name
left join cv3catalogitemtask cit
on ocmi.guid=cit.ordercatalogmasteritemguid
left join cv3catalogtasklocationxref locxrf
on locxrf.CatalogItemTaskGUID=cit.guid
left join cv3location l
on locxrf.LocationGroupGUID=l.guid
where
ocmi.name not like 'zz%'
and ocmi.name not like 'qq%' and cit.active=1
) X

where [TASKResult] != 'Pass'

drop table #goldlist

drop table #Match

drop table #Compare

2

Not sure what you're asking. But you should be creating CLUSTERED indexes on all the temp tables, not nonclustered indexes. For example:

create clustered index gl_name on #goldlist (name)

1 Like
3

the query shows null as all the values exists in 2439 rows .. If I want to insert one row looks like thats not possible

4

Thank you for trying to post DDL, but you failed! Please follow basic Netiquette AND post the DDL we need to answer this. Follow industry AND ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys AND constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

SQL is based on a tiered architecture. The database tier handles all of the database retrieval AND data integrity. But nothing else. The data display AND formatting is done in presentation layers that get data from the database layer.

Look at your code. Do you really have a magic generic “name”?? Not in RDBMS! Is “task_active” really a 1950's assembly language flag?? and think about how silly “VARCHAR(1)” is, just think. We do not use GUID inside a schema; the “G” is for “global” and not for local identifiers.

CREATE TABLE GoldList
(name VARCHAR(50), –- no key, very long column!
task_active VARCHAR(1) --- assembly flag!
);

The non-table is two columns, but the insertion is eight columns. Think about it! Why are you putting meta data in the table in the “name” column?

Your posting is garbage and we need to start over, after we fire the moron who did this non-schema to you. Yes, it is that bad. :frowning:

5

Thanks for your reply,
The sql which I have posted here may be garbage and
unfortunately I have to deal with this. I know each
point of yours is very correct, but I have to make this
work,

The query written over there is working. This is
to check whether the locations has been loaded fully.

If I have to explain this again, I am doing a bulk load, instead of an insert,
and I am just wondering, whether I can convert this .blk extn to an SQL

Thanks