your table schema, data and requirements seem to keep changing with every post.
You are inserting a string value into [Quant] and a numeric value into [JobNumber].
SiteId and SiteNames are gone. Please repost clean dat and clean requirements.
So odd it worked on my end.
Works on my computer
I'm using temp tables, so I don't muddy up my database
Drop table #BarCodesBetweenDates
create table #BarCodesBetweenDates(
StaffName varchar(50),
WBName varchar(50),
SiteName varchar(250),
PN Varchar(20),
Description varchar(250),
Quant int,
[JobNumber] int );
create table #Site(SiteID int , SiteName varchar(50));
INSERT INTO #BarCodesBetweenDates
([StaffName]
,[Description]
,[SiteName]
,[WBName]
,[PN]
,[Quant]
,[JobNumber])
Values
('Victor P','Drum Return','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6420',3,136),
('Victor P','LIQUID CHLORINE 15 LTR ','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6221',2,136),
('Victor P','Drum Return','Site 47 for Client 33','Water Body 48 For Site 47','0506CH6420',1,135),
('Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',4,139),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',4,139),
('Victor P','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',1,133),
('Victor P','STABILISER 2.0KG','Site 22 for Client 21','Water Body 39 For Site 22','0514CH4626',1,132),
('Victor P','LIQUID CHLORINE 15 LTR','Site 43 for Client 57','Water Body 44 For Site 43','0506CH6221',2,138),
('Victor P','Drum Return','Site 43 for Client 57','Water Body 44 For Site 43','0506CH6420',2,138),
('Victor P','LIQUID CHLORINE 15 LTR ','Site 44 for Client 55','Water Body 67 For Site 44','0506CH6221',2,127),
('Victor P','NON FUMING ACID 15 LTR ','Site 44 for Client 55','Water Body 67 For Site 44','0508CH6010 ',1,127),
('Victor P','Drum Return','Site 44 for Client 55','Water Body 67 For Site 44','0506CH6420',3,127),
('Victor P','SPA SHOCK 500GMS ','Site 44 for Client 55','Water Body 67 For Site 44','0560CHS1099 ',1,127),
('Victor P','BUFFER 2KG ','Site 44 for Client 55','Water Body 67 For Site 44','0509CH67626 ',1,127),
('Victor P','LIQUID CHLORINE 15 LTR ','Site 58 for Client 21','Water Body 5 For Site 58','0506CH6221',1,139),
('Victor P','LIQUID CHLORINE 15 LTR ','Site 58 for Client 21','Water Body 4 For Site 58','0506CH6221',1,139),
('Victor P','NON FUMING ACID 15 LTR ','Site 58 for Client 21','Water Body 4 For Site 58','0508CH6010 ',1,139),
('Victor P','NON FUMING ACID 15 LTR ','Site 22 for Client 21','Water Body 49 For Site 22','0508CH6010 ',8,132),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 4 For Site 58','0506CH6420',3,139),
('Victor P','Drum Return','Site 22 for Client 21','Water Body 49 For Site 22','0506CH6420',8,132),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',2,139),
('Victor P','Drum Return','Site 47 for Client 33','Water Body 48 For Site 47','0506CH6420',1,135),
('Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',35,139),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',35,139),
('Victor P','LIQUID CHLORINE 15 LTR','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',2,133),
('Victor P','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',2,133),
('Victor P','CALCIUM HARDNESS PLUS 10KG ','Site 61 for Client 65','Water Body 53 For Site 61','0529CHPK4897 ',1,137),
('Victor P','CALCIUM PLUS HARDNESS 4 KG ','Site 61 for Client 65','Water Body 53 For Site 61','052967414 ',1,137),
('Victor P','1l Clarifier','Site 61 for Client 65','Water Body 53 For Site 61','SEAD2036CPC',1,137),
('Victor P','1l Non Copper Algaecide ','Site 61 for Client 65','Water Body 53 For Site 61','SEAD2037CPA',1,137),
('Victor P','LIQUID CHLORINE 15 LTR ','Site 61 for Client 65','Water Body 53 For Site 61','0506CH6221',3,137),
('Victor P','Drum Return','Site 61 for Client 65','Water Body 53 For Site 61','0506CH6420',3,137),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',1,139),
('Victor P','LIQUID CHLORINE 15 LTR ','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',4,139),
('Victor P','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 79 For Site 58','SEAD2037CPA',1,139),
('Victor P','CALCIUM HARDNESS PLUS 10KG ','Site 59 for Client 102','Water Body 17 For Site 59','0529CHPK4897 ',2,131),
('Victor P','1l Non Copper Algaecide ','Site 59 for Client 102','Water Body 17 For Site 59','SEAD2037CPA',1,131),
('Victor P','1l Clarifier','Site 22 for Client 21','Water Body 34 For Site 22','SEAD2036CPC',1,132),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',2,139),
('Garry R','1000 Litre Bulk Sodium Hypochlorite','Site 22 for Client 21','Water Body 49 For Site 22','CPSpecial',1,132),
('Garry R','Drum Return','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6420',3,136),
('Garry R','LIQUID CHLORINE 15 LTR ','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6221',2,136),
('Garry R','NON FUMING ACID 15 LTR ','Site 58 for Client 21','Water Body 79 For Site 58','0508CH6010 ',2,139),
('Garry R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',1,139),
('Garry R','LIQUID CHLORINE 15 LTR ','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',25,139),
('Garry R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',25,139),
('Garry R','NON FUMING ACID 15 LTR ','Site 22 for Client 21','Water Body 49 For Site 22','0508CH6010 ',4,132),
('Garry R','Drum Return','Site 22 for Client 21','Water Body 49 For Site 22','0506CH6420',4,132),
('Garry R','NON COPPER ALGAECIDE 20 LITRES ','Site 58 for Client 21','Water Body 7 For Site 58','0515CHPK-0099 ',1,139),
('Garry R','1l Clarifier','Site 58 for Client 21','Water Body 7 For Site 58','SEAD2036CPC',20,139),
('Garry R','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 14 For Site 58','SEAD2037CPA',2,139),
('Garry R','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 7 For Site 58','0508CH6020',2,139),
('Garry R','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 16 For Site 58','CPSpecial2',1,139),
('Garry R','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 16 For Site 58','CPSpecial4',0.24,139),
('Garry R','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 15 For Site 58','CPSpecial4',0.23,139),
('Garry R','Chemical-Cyanuric Acid Per Kg','Site 22 for Client 21','Water Body 39 For Site 22','CPSpecial4',0.23,132),
('Garry R','Drum Return','Site 58 for Client 21','Water Body 7 For Site 58','0506CH6420',2,139),
('Garry R','LIQUID CHLORINE 15 LTR ','Site 74 for Client 114','Water Body 50 For Site 74','0506CH6221',1,125),
('Garry R','Drum Return','Site 74 for Client 114','Water Body 50 For Site 74','0506CH6420',1,125),
('Garry R','NON FUMING ACID 15 LTR ','Site 47 for Client 33','Water Body 48 For Site 47','0508CH6010 ',4,135),
('Garry R','BROMINE 10 KG ','Site 22 for Client 21','Water Body 33 For Site 22','0560CH1005 ',1,132),
('Garry R','BROMINE 10 KG ','Site 22 for Client 21','Water Body 38 For Site 22','0560CH1005 ',1,132),
('Garry R','Drum Return','Site 47 for Client 33','Water Body 48 For Site 47','0506CH6420',1,135),
('Garry R','LIQUID CHLORINE 15 LTR ','Site 22 for Client 21','Water Body 49 For Site 22','0506CH6221',3,132),
('Garry R','Drum Return','Site 22 for Client 21','Water Body 49 For Site 22','0506CH6420',2,132),
('Garry R','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 10 For Site 58','0508CH6020',0.015,139),
('Garry R','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 23 For Site 58','CPSpecial2',1.5,139),
('Garry R','Chemical - Sodium Bicarbonate Per Kg','Site 58 for Client 21','Water Body 23 For Site 58','CPSpecial3',0.54,139),
('Garry R','1l Clarifier','Site 58 for Client 21','Water Body 23 For Site 58','SEAD2036CPC',0.2,139),
('Garry R','Chemical - Sodium Bicarbonate Per Kg','Site 58 for Client 21','Water Body 2 For Site 58','CPSpecial3',1,139),
('Victor P','Chemical - Sodium Bicarbonate Per Kg','Site 58 for Client 21','Water Body 2 For Site 58','CPSpecial3',1,139),
('Victor P','1l Clarifier','Site 58 for Client 21','Water Body 2 For Site 58','SEAD2036CPC',0.5,139),
('Victor P','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 2 For Site 58','SEAD2037CPA',1,139),
('Victor P','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',2,133),
('Victor P','NON FUMING ACID 15 LTR ','Site 20 for Client 19','Water Body 29 For Site 20','0508CH6010 ',1,133),
('Victor P','LIQUID CHLORINE 15 LTR ','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',4,133),
('Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 8 For Site 58','0506CH6221',1,139),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 8 For Site 58','0506CH6420',1,139),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',4,139),
('Victor P','LIQUID CHLORINE 15 LTR ','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',25,139),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',25,139),
('Victor P','NON FUMING ACID 15 LTR ','Site 20 for Client 19','Water Body 29 For Site 20','0508CH6010 ',1,133),
('Victor P','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',3,133),
('Victor P','LIQUID CHLORINE 15 LTR ','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6221',2,136),
('Victor P','Drum Return','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6420',2,136),
('Victor P','CALCIUM PLUS HARDNESS 4 KG ','Site 20 for Client 19','Water Body 29 For Site 20','052967414 ',2,133),
('Victor P','STABILISER 2.0KG ','Site 20 for Client 19','Water Body 29 For Site 20','0514CH4626 ',1,133),
('Victor P','PHOTOMETER PH RED TABS 250 PK ','Site 58 for Client 21','Water Body 79 For Site 58','0584511771BT ',2,139),
('Victor P','PHOTO DPD-1 HR TABS MD100/2 250 PK','Site 58 for Client 21','Water Body 79 For Site 58','0585511501BT ',2,139),
('Victor P','PHOTOMETER DPD NO3 250 PK ','Site 58 for Client 21','Water Body 79 For Site 58','0584511591BT ',2,139),
('Victor P','LIQUID CHLORINE 15 LTR','Site 43 for Client 57','Water Body 44 For Site 43','0506CH6221',2,138),
('Victor P','NON FUMING ACID 15 LTR ','Site 43 for Client 57','Water Body 44 For Site 43','0508CH6010 ',1,138),
('Victor P','Drum Return','Site 43 for Client 57','Water Body 44 For Site 43','0506CH6420',2,138),
('Victor P','NON FUMING ACID 15 LTR ','Site 58 for Client 21','Water Body 4 For Site 58','0508CH6010 ',1,139),
('Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 4 For Site 58','0506CH6221',1,139),
('Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 5 For Site 58','0506CH6221',1,139),
('Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 22 for Client 21','Water Body 31 For Site 22','0515CHPK-0099 ',1,132),
('Victor P','CLARIFIER 20 LITRES ','Site 22 for Client 21','Water Body 31 For Site 22','0518CHPK-01800 ',1,132),
('Victor P','HYDROCHLORIC ACID 20 LTR','Site 22 for Client 21','Water Body 31 For Site 22','0508CH6020',1,132),
('Victor P','Drum Return','Site 22 for Client 21','Water Body 31 For Site 22','0506CH6420',1,132),
('Victor P','Chemical - Sodium Bicarbonate Per Kg','Site 22 for Client 21','Water Body 36 For Site 22','CPSpecial3',6,132),
('Victor P','Chemical-Cyanuric Acid Per Kg','Site 22 for Client 21','Water Body 36 For Site 22','CPSpecial4',5,132),
('Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 22 for Client 21','Water Body 36 For Site 22','0515CHPK-0099 ',0.05,132),
('Victor P','CLARIFIER 20 LITRES ','Site 22 for Client 21','Water Body 36 For Site 22','0518CHPK-01800 ',0.05,132),
('Victor P','Chemical - Sodium Bicarbonate Per Kg','Site 22 for Client 21','Water Body 32 For Site 22','CPSpecial3',1,132),
('Victor P','Chemical-Cyanuric Acid Per Kg','Site 22 for Client 21','Water Body 32 For Site 22','CPSpecial4',2,132),
('Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 22 for Client 21','Water Body 32 For Site 22','0515CHPK-0099 ',0.025,132),
('Victor P','CLARIFIER 20 LITRES ','Site 22 for Client 21','Water Body 32 For Site 22','0518CHPK-01800 ',0.025,132),
('Victor P','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 6 For Site 58','CPSpecial2',5,139),
('Victor P','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 6 For Site 58','CPSpecial4',2,139),
('Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 58 for Client 21','Water Body 6 For Site 58','0515CHPK-0099 ',0.025,139),
('Victor P','CLARIFIER 20 LITRES ','Site 58 for Client 21','Water Body 6 For Site 58','0518CHPK-01800 ',0.025,139),
('Victor P','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 3 For Site 58','CPSpecial2',5,139),
('Victor P','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 3 For Site 58','CPSpecial4',1,139),
('Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 58 for Client 21','Water Body 3 For Site 58','0515CHPK-0099 ',0.025,139),
('Victor P','CLARIFIER 20 LITRES ','Site 58 for Client 21','Water Body 3 For Site 58','0518CHPK-01800 ',0.025,139),
('Victor P','Chemical - Calcium Chloride Per Kg','Site 22 for Client 21','Water Body 34 For Site 22','CPSpecial2',5,132),
('Victor P','Chemical-Cyanuric Acid Per Kg','Site 22 for Client 21','Water Body 34 For Site 22','CPSpecial4',1,132),
('Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 22 for Client 21','Water Body 34 For Site 22','0515CHPK-0099 ',0.025,132),
('Victor P','CLARIFIER 20 LITRES ','Site 22 for Client 21','Water Body 34 For Site 22','0518CHPK-01800 ',0.025,132),
('Victor P','BUFFER 2KG ','Site 82 for Client 115','Water Body 71 For Site 82','0509CH67626 ',3,136),
('Victor P','BUFFER 2KG ','Site 38 for Client 45','Water Body 66 For Site 38','0509CH67626 ',2,130),
('Victor P','1000 Litre Bulk Sodium Hypochlorite','Site 22 for Client 21','Water Body 49 For Site 22','CPSpecial',1,132),
('Victor P','SALT 20 KG','Site 58 for Client 21','Water Body 16 For Site 58','0565CHPS25',2,139),
('Victor P','SALT 20 KG','Site 58 for Client 21','Water Body 15 For Site 58','0565CHPS25',2,139),
('Victor P','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 4 For Site 58','0508CH6020',0.1,139),
('Victor P','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 8 For Site 58','0508CH6020',0.02,139),
('Victor P','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 11 For Site 58','0508CH6020',0.025,139),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',1,139),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 4 For Site 58','0506CH6420',1,139),
('Garry R','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 4 For Site 58','CPSpecial4',1,139),
('Garry R','1l Clarifier','Site 58 for Client 21','Water Body 4 For Site 58','SEAD2036CPC',0.5,139),
('Garry R','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 4 For Site 58','SEAD2037CPA',0.5,139),
('Garry R','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 5 For Site 58','SEAD2037CPA',0.5,139),
('Garry R','1l Clarifier','Site 58 for Client 21','Water Body 5 For Site 58','SEAD2036CPC',0.5,139),
('Garry R','1l Clarifier','Site 58 for Client 21','Water Body 11 For Site 58','SEAD2036CPC',0.2,139),
('Garry R','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',6,139),
('Garry R','Drum Return','Site 47 for Client 33','Water Body 48 For Site 47','0506CH6420',2,135),
('Garry R','Drum Return','Site 47 for Client 33','Water Body 26 For Site 47','0506CH6420',2,135),
('Garry R','NON FUMING ACID 15 LTR ','Site 47 for Client 33','Water Body 26 For Site 47','0508CH6010 ',2,135),
('Garry R','HYDROCHLORIC ACID 20 LTR','Site 83 for Client 121','Water Body 76 For Site 83','0508CH6020',1,128),
('Garry R','Drum Return','Site 83 for Client 121','Water Body 76 For Site 83','0506CH6420',3,128),
('Garry R','SPA SHOCK 500GMS ','Site 58 for Client 21','Water Body 3 For Site 58','0560CHS1099 ',1,139),
('Garry R','SPA SHOCK 500GMS ','Site 58 for Client 21','Water Body 7 For Site 58','0560CHS1099 ',1,139),
('Garry R','LIQUID CHLORINE 15 LTR','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',2,133),
('Garry R','LIQUID CHLORINE 15 LTR ','Site 83 for Client 121','Water Body 76 For Site 83','0506CH6221',2,128),
('Garry R','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',2,133),
('Garry R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',3,139),
('Garry R','SPA SHOCK 500GMS ','Site 66 for Client 76','Water Body 52 For Site 66','0560CHS1099 ',1,129),
('Garry R','Chemical - Calcium Chloride Per Kg','Site 66 for Client 76','Water Body 52 For Site 66','CPSpecial2',10,129),
('Garry R','1l Clarifier','Site 66 for Client 76','Water Body 52 For Site 66','SEAD2036CPC',1,129),
('Garry R','LIQUID CHLORINE 15 LTR','Site 66 for Client 76','Water Body 52 For Site 66','0506CH6221',4,129),
('Garry R','Drum Return','Site 66 for Client 76','Water Body 52 For Site 66','0506CH6420',4,129),
('Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',25,139),
('Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',25,139),
('Tony R','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 15 For Site 58','CPSpecial2',1,139),
('Tony R','Chemical-Cyanuric Acid Per Kg','Site 22 for Client 21','Water Body 39 For Site 22','CPSpecial4',0.14,132),
('Tony R','LIQUID CHLORINE 15 LTR ','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6221',3,136),
('Tony R','HYDROCHLORIC ACID 20 LTR','Site 82 for Client 115','Water Body 71 For Site 82','0508CH6020',1,136),
('Tony R','Drum Return','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6420',3,136),
('Tony R','NON FUMING ACID 15 LTR ','Site 22 for Client 21','Water Body 49 For Site 22','0508CH6010 ',4,132),
('Tony R','NON COPPER ALGAECIDE 20 LITRES ','Site 58 for Client 21','Water Body 7 For Site 58','0515CHPK-0099 ',1,139),
('Tony R','1l Clarifier','Site 58 for Client 21','Water Body 7 For Site 58','SEAD2036CPC',20,139),
('Tony R','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 7 For Site 58','CPSpecial2',175,139),
('Tony R','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 7 For Site 58','CPSpecial4',14,139),
('Tony R','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 14 For Site 58','SEAD2037CPA',2,139),
('Tony R','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 7 For Site 58','0508CH6020',2,139),
('Tony R','Drum Return','Site 58 for Client 21','Water Body 7 For Site 58','0506CH6420',2,139),
('Tony R','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0508CH6020',1,139),
('Tony R','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0508CH6020',1,139),
('Tony R','LIQUID CHLORINE 15 LTR','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',2,133),
('Tony R','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',3,139),
('Tony R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',3,139),
('Tony R','Drum Return','Site 22 for Client 21','Water Body 49 For Site 22','0506CH6420',4,132),
('Tony R','Chemical - Sodium Bicarbonate Per Kg','Site 47 for Client 33','Water Body 26 For Site 47','CPSpecial3',25,135),
('Tony R','Chemical - Calcium Chloride Per Kg','Site 47 for Client 33','Water Body 48 For Site 47','CPSpecial2',100,135),
('Tony R','NON FUMING ACID 15 LTR ','Site 47 for Client 33','Water Body 48 For Site 47','0508CH6010 ',3,135),
('Tony R','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',4,133),
('Tony R','Chemical - Calcium Chloride Per Kg','Site 47 for Client 33','Water Body 48 For Site 47','CPSpecial2',100,135),
('Tony R','LIQUID CHLORINE 15 LTR','Site 81 for Client 114','Water Body 69 For Site 81','0506CH6221',1,134),
('Tony R','Drum Return','Site 81 for Client 114','Water Body 69 For Site 81','0506CH6420',1,134),
('Tony R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',2,139),
('Tony R','LIQUID CHLORINE 15 LTR','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',4,133),
('Tony R','SPA SHOCK 500GMS ','Site 58 for Client 21','Water Body 10 For Site 58','0560CHS1099 ',1,139),
('Tony R','1000 Litre Bulk Sodium Hypochlorite','Site 22 for Client 21','Water Body 49 For Site 22','CPSpecial',1,132),
('Tony R','HYDROCHLORIC ACID 20 LTR','Site 82 for Client 115','Water Body 71 For Site 82','0508CH6020',1,136),
('Tony R','Drum Return','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6420',3,136),
('Tony R','LIQUID CHLORINE 15 LTR','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6221',2,136),
('Tony R','BUFFER TA INCREASER 10KG','Site 82 for Client 115','Water Body 71 For Site 82','0509CH67134',1,136),
('Tony R','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',20,139),
('Tony R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',20,139),
('Tony R','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',4,133),
('Tony R','STABILISER 2.0KG ','Site 81 for Client 114','Water Body 69 For Site 81','0514CH4626 ',1,134),
('Tony R','NON FUMING ACID 15 LTR ','Site 20 for Client 19','Water Body 29 For Site 20','0508CH6010 ',2,133),
('Tony R','LIQUID CHLORINE 15 LTR','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',2,133),
('Tony R','1l Non Copper Algaecide ','Site 20 for Client 19','Water Body 29 For Site 20','SEAD2037CPA',1,133),
('Tony R','1l Clarifier','Site 20 for Client 19','Water Body 29 For Site 20','SEAD2036CPC',1,133),
('Tony R','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',4,133);
--Add dummy records to Site table
insert into #Site
Select substring(SiteName, 6, 2), SiteName from #BarCodesBetweenDates
group by SiteName
DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(WBName)
FROM
(SELECT DISTINCT WBName
FROM #BarCodesBetweenDates
) AS B
DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 'SELECT SiteID, ' + @Columns + '
FROM
(SELECT S.siteID, b.SiteName, B.WBName as LocationName, B.PN, B.Description,
SUM(B.Quant) AS Expr1
FROM #Site S INNER JOIN
#BarCodesBetweenDates B ON s.SiteName = B.SiteName
GROUP BY B.SiteName, B.WBName, B.PN, B.Description, s.SiteID
) AS PivotData
PIVOT
(
COUNT(Expr1)
FOR LocationName IN (' + @Columns + ')
) AS PivotResult
ORDER BY Description'
select @SQL
exec (@SQL)