Add Deposit For Some Items in View

DavidCreative · February 8, 2020, 11:05pm

I really appreciate all the help this forum has been. At times I am sure I would have been tempted to give up without all of your help. Of course my nature is such I wouldnt give up I would have found a work around, but nonetheless thanks very much.

I am afraid I need help again.

I have a view I am playing with and I really need to get to work.

The view is;

INSERT INTO [dbo].[BarCodesBetweenDates]
(StaffName,Description,SiteName,WBName,PN,Quant,JobNumber)
Values(
Scott T,Drum Return,Site 43 for Client 57,Water Body 44 For Site 43,0506CH6420,1,138
Scott T,Drum Return,Site 20 for Client 19,Water Body 29 For Site 20,0506CH6420,2,133
Scott T,Drum Return,Site 43 for Client 57,Water Body 44 For Site 43,0506CH6420,2,138
Scott T,NON COPPER ALGAECIDE 20 LITRES     ,Site 22 for Client 21,Water Body 34 For Site 22,0515CHPK-0099            ,0.05,132
Scott T,CLARIFIER 20 LITRES                ,Site 22 for Client 21,Water Body 34 For Site 22,0518CHPK-01800           ,0.05,132
Scott T,LIQUID CHLORINE 15 LTR,Site 43 for Client 57,Water Body 44 For Site 43,0506CH6221,3,138
Scott T,NON FUMING ACID 15 LTR             ,Site 38 for Client 45,Water Body 66 For Site 38,0508CH6010               ,1,130
Scott T,Drum Return,Site 38 for Client 45,Water Body 66 For Site 38,0506CH6420,1,130
Scott T,NON FUMING ACID 15 LTR             ,Site 20 for Client 19,Water Body 29 For Site 20,0508CH6010               ,2,133
Scott T,LIQUID CHLORINE 15 LTR,Site 58 for Client 21,Water Body 79 For Site 58,0506CH6221,5,139
Scott T,Drum Return,Site 58 for Client 21,Water Body 79 For Site 58,0506CH6420,2,139
Tony R,Drum Return,Site 66 for Client 76,Water Body 52 For Site 66,0506CH6420,4,129
Tony R,1l Non Copper Algaecide ,Site 58 for Client 21,Water Body 14 For Site 58,SEAD2037CPA,2,139
Chris R,1000 Litre Bulk Sodium Hypochlorite,Site 22 for Client 21,Water Body 49 For Site 22,CPSpecial,1,132
Chris R,LIQUID CHLORINE 15 LTR,Site 58 for Client 21,Water Body 7 For Site 58,0506CH6221,14,139
Chris R,Chemical - Calcium Chloride Per Kg,Site 58 for Client 21,Water Body 2 For Site 58,CPSpecial2,25,139
Chris R,LIQUID CHLORINE 15 LTR,Site 82 for Client 115,Water Body 71 For Site 82,0506CH6221,2,136
Chris R,Drum Return,Site 82 for Client 115,Water Body 71 For Site 82,0506CH6420,2,136
Chris R,Drum Return,Site 58 for Client 21,Water Body 79 For Site 58,0506CH6420,4,139
Chris R,LIQUID CHLORINE 15 LTR,Site 58 for Client 21,Water Body 79 For Site 58,0506CH6221,20,139
Chris R,Chemical - Sodium Bicarbonate Per Kg,Site 58 for Client 21,Water Body 3 For Site 58,CPSpecial3,1,139
Chris R,Chemical-Cyanuric Acid Per Kg,Site 58 for Client 21,Water Body 3 For Site 58,CPSpecial4,2,139
Chris R,Chemical - Sodium Bicarbonate Per Kg,Site 58 for Client 21,Water Body 6 For Site 58,CPSpecial3,2,139
Chris R,Chemical-Cyanuric Acid Per Kg,Site 58 for Client 21,Water Body 6 For Site 58,CPSpecial4,2,139
Chris R,Chemical-Cyanuric Acid Per Kg,Site 58 for Client 21,Water Body 12 For Site 58,CPSpecial4,0.6,139
Chris R,Drum Return,Site 58 for Client 21,Water Body 79 For Site 58,0506CH6420,20,139
Chris R,NON COPPER ALGAECIDE 20 LITRES     ,Site 58 for Client 21,Water Body 3 For Site 58,0515CHPK-0099            ,0.02,139
Chris R,1l Clarifier,Site 58 for Client 21,Water Body 3 For Site 58,SEAD2036CPC,0.25,139
Chris R,1l Clarifier,Site 58 for Client 21,Water Body 6 For Site 58,SEAD2036CPC,0.25,139
Scott T,PHOTO DPD-1 HR TABS MD100/2  250 PK,Site 58 for Client 21,Water Body 79 For Site 58,0585511501BT             ,2,139
Scott T,PHOTOMETER PH RED TABS 250 PK      ,Site 58 for Client 21,Water Body 79 For Site 58,0584511771BT             ,1,139
Scott T,PHOTOMETER DPD NO3  250 PK         ,Site 58 for Client 21,Water Body 79 For Site 58,0584511591BT             ,1,139
Chris R,1l Non Copper Algaecide ,Site 58 for Client 21,Water Body 6 For Site 58,SEAD2037CPA,0.2,139
Chris R,NON FUMING ACID 15 LTR             ,Site 47 for Client 33,Water Body 48 For Site 47,0508CH6010               ,3,135
Chris R,Drum Return,Site 47 for Client 33,Water Body 48 For Site 47,0506CH6420,5,135
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 44 for Client 55,Water Body 67 For Site 44,0506CH6221,3,127
Tony R,Drum Return,Site 44 for Client 55,Water Body 67 For Site 44,0506CH6420,3,127
Tony R,LIQUID CHLORINE 15 LTR             ,Site 58 for Client 21,Water Body 79 For Site 58,0506CH6221,25,139
Tony R,Drum Return,Site 58 for Client 21,Water Body 79 For Site 58,0506CH6420,25,139
Tony R,Chemical - Sodium Bicarbonate Per Kg,Site 22 for Client 21,Water Body 34 For Site 22,CPSpecial3,2,132
Tony R,Chemical-Cyanuric Acid Per Kg,Site 22 for Client 21,Water Body 34 For Site 22,CPSpecial4,1,132
Tony R,1l Clarifier,Site 22 for Client 21,Water Body 34 For Site 22,SEAD2036CPC,0.5,132
Tony R,1l Non Copper Algaecide ,Site 22 for Client 21,Water Body 34 For Site 22,SEAD2037CPA,0.25,132
Tony R,Chemical - Sodium Bicarbonate Per Kg,Site 22 for Client 21,Water Body 36 For Site 22,CPSpecial3,18,132
Tony R,Chemical-Cyanuric Acid Per Kg,Site 22 for Client 21,Water Body 36 For Site 22,CPSpecial4,4,132
Tony R,1l Non Copper Algaecide ,Site 22 for Client 21,Water Body 36 For Site 22,SEAD2037CPA,1,132
Tony R,NON FUMING ACID 15 LTR             ,Site 22 for Client 21,Water Body 49 For Site 22,0508CH6010               ,4,132
Tony R,Drum Return,Site 22 for Client 21,Water Body 49 For Site 22,0506CH6420,4,132
Tony R,SPA SHOCK 500GMS                   ,Site 59 for Client 102,Water Body 17 For Site 59,0560CHS1099              ,2,131
Tony R,Drum Return,Site 20 for Client 19,Water Body 29 For Site 20,0506CH6420,2,133
Tony R,LIQUID CHLORINE 15 LTR             ,Site 74 for Client 114,Water Body 50 For Site 74,0506CH6221,1,125
Tony R,Drum Return,Site 74 for Client 114,Water Body 50 For Site 74,0506CH6420,1,125
Tony R,LIQUID CHLORINE 15 LTR,Site 58 for Client 21,Water Body 7 For Site 58,0506CH6221,14,139
Tony R,Drum Return,Site 47 for Client 33,Water Body 48 For Site 47,0506CH6420,2,135
Tony R,LIQUID CHLORINE 15 LTR,Site 58 for Client 21,Water Body 6 For Site 58,0506CH6221,4,139
Robert G,1l Non Copper Algaecide ,Site 59 for Client 102,Water Body 17 For Site 59,SEAD2037CPA,2,131
Robert G,1l Clarifier,Site 59 for Client 102,Water Body 17 For Site 59,SEAD2036CPC,2,131
Robert G,1l Clarifier,Site 20 for Client 19,Water Body 29 For Site 20,SEAD2036CPC,1,133
Robert G,1l Non Copper Algaecide ,Site 20 for Client 19,Water Body 29 For Site 20,SEAD2037CPA,1,133
Robert G,LIQUID CHLORINE 15 LTR,Site 58 for Client 21,Water Body 11 For Site 58,0506CH6221,1,139
Robert G,LIQUID CHLORINE 15 LTR,Site 58 for Client 21,Water Body 8 For Site 58,0506CH6221,1,139
Chris R,1l Clarifier,Site 58 for Client 21,Water Body 8 For Site 58,SEAD2036CPC,0.2,139
Chris R,LIQUID CHLORINE 15 LTR             ,Site 58 for Client 21,Water Body 79 For Site 58,0506CH6221,2,139
Chris R,NON FUMING ACID 15 LTR             ,Site 58 for Client 21,Water Body 79 For Site 58,0508CH6010               ,2,139
Chris R,Drum Return,Site 58 for Client 21,Water Body 79 For Site 58,0506CH6420,3,139
Chris R,LIQUID CHLORINE 15 LTR,Site 20 for Client 19,Water Body 29 For Site 20,0506CH6221,2,133
Chris R,Drum Return,Site 20 for Client 19,Water Body 29 For Site 20,0506CH6420,2,133
Robert G,BUFFER  2KG                        ,Site 38 for Client 45,Water Body 66 For Site 38,0509CH67626              ,3,130
Robert G,LIQUID CHLORINE 15 LTR,Site 66 for Client 76,Water Body 52 For Site 66,0506CH6221,5,129
Robert G,Drum Return,Site 66 for Client 76,Water Body 52 For Site 66,0506CH6420,4,129
Robert G,Drum Return,Site 66 for Client 76,Water Body 52 For Site 66,0506CH6420,1,129
Robert G,NON FUMING ACID 15 LTR             ,Site 22 for Client 21,Water Body 49 For Site 22,0508CH6010               ,8,132
Robert G,Drum Return,Site 22 for Client 21,Water Body 49 For Site 22,0506CH6420,8,132
Victor P,Chemical - Calcium Chloride Per Kg,Site 58 for Client 21,Water Body 12 For Site 58,CPSpecial2,1.5,139
Victor P,PHOTOMETER DPD NO3  250 PK         ,Site 22 for Client 21,Water Body 49 For Site 22,0584511591BT             ,2,132
Victor P,PHOTO DPD-1 HR TABS MD100/2  250 PK,Site 22 for Client 21,Water Body 49 For Site 22,0585511501BT             ,1,132
Victor P,PHOTOMETER PH RED TABS 250 PK      ,Site 22 for Client 21,Water Body 49 For Site 22,0584511771BT             ,2,132
Victor P,HYDROCHLORIC ACID 20 LTR,Site 82 for Client 115,Water Body 71 For Site 82,0508CH6020,1,136
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
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)

I am happy with the view.

The challenge is adding the drum deposits.

In practical terms we scan the bar code for the product being delivered. Some of the products have a drum deposit. Rather than scan a drum deposit we implicitly pick up the drum deposit by comparing to the drum deposit table:
INSERT INTO [dbo].[ShtStockDrumDeposit]
(StockDepositID,ShtPartNumber)
Values(
1,0507CH6220
2,0508CH6020
3,0506CH6221
4,0508CH6010)

As shtpartNumber isnt included in the view as loaded above, please use a text match on the name to the stock table.

INSERT INTO [dbo].[ShtStock]
           (
    ShtStockId,BarCode,PartNumber,Description)
 VALUES(
448,9317370908074,0506CH6221,LIQUID CHLORINE 15 LTR             
457,9319737142619,0508CH6010               ,NON FUMING ACID 15 LTR             
460,9319737123403,0509CH67626              ,BUFFER  2KG                        
465,9319737080102,0512CH67686              ,AQUACHLOR PH UP 2KG                
467,9319737432208,0514CH4626               ,STABILISER 2.0KG                   
481,SEAQ0515CHAG1935,0515CHAG1935             ,SODIUM THIOSULPHATE PER KG         
485,SEAQ0515CHMET,0515CHMETRID             ,METAL RID STAIN REMOVER 2KG        
487,SEAQ0515CHPK0099,0515CHPK-0099            ,NON COPPER ALGAECIDE 20 LITRES     
499,9319737410121,0518CH66166              ,POOL WATER CLARIFIER 1 LTR         
500,SEAQ0518CHK01800,0518CHPK-01800           ,CLARIFIER 20 LITRES                
507,SEAQ520CH91020,0520CH91020              ,CARTRIDGE CLEANER                  
510,9319737489943,052967414,CALCIUM PLUS HARDNESS 4 KG         
512,9319737489738,0529CHPK4897             ,CALCIUM HARDNESS PLUS 10KG         
525,9334024002346,0540CH1006               ,POOL MAGIC 10 KG FINE GRADE CHLORIN
529,9319737602557,0560CH1005               ,BROMINE  10 KG                     
539,9319737046191,0560CHS1099              ,SPA SHOCK 500GMS                   
572,4049991090214,0584511051BT             ,PHOTOMETER DPD NO 1 TABLETS 250 PK 
574,4049991057064,0584511082BT             ,PHOTOMETER DPD NO3 TABLETS PK 500  
576,4049991091471,0584511371BT             ,PHOTOMETER CYNURIC STAB TABS  250PK
577,4049991084046,0584511591BT             ,PHOTOMETER DPD NO3  250 PK         
578,4049991059075,0584511770BT             ,PHOTOMETER PHENOL RED TABS 100PK   
579,4049991059143,0584511771BT             ,PHOTOMETER PH RED TABS 250 PK      
580,4049991059181,0584511772BT             ,PHOTOMETER PHENOL RED TAB PK 500   
581,4049991091846,0584513211BT             ,PHOTOMETER T/A TABS 250 PK         
582,4049991110172,0584513241BT             ,PHOTOMETER ALKA M TABS PKT 250 HR  
584,4049991093512,0584517761BT             ,CALCIO COMBO 1 & 2 TABS 100 PACK   
585,4049991093536,0584517762,CALCIO  COMBO PK1& 2   250ea PHOTO 
592,4049991084015,0585511501BT             ,PHOTO DPD-1 HR TABS MD100/2  250 PK
593,4049991084039,0585511590BT             ,PHOTO DPD-3 HR TABS MD100/2 -100PK 
1993,SEAD1993CPS,CPSpecial,1000 Litre Bulk Sodium Hypochlorite
1994,SEAD1994CPS2  ,CPSpecial2,Chemical - Calcium Chloride Per Kg
1995,SEAD1995CPS3,CPSpecial3,Chemical - Sodium Bicarbonate Per Kg
1996,SEAD1996CPS4,CPSpecial4,Chemical-Cyanuric Acid Per Kg
1997,656055000016,0515CH4621,POOL BLOCKS (5) 1.5KG
1998,65605500002,0515CH4622,POOL DROPS PLUS H/D ALGICIDE
1999,656055000061,0560CHS1101,OXYCLEAR SPA/POOL SHOCK 1.5kg
2000,656055000122,0515SSR100,SERIOUS STAIN REMOVER
2001,9312113001159,0515CHAMS210,MULTI STAIN REMOVER LO/CHLO
2002,9312113002392,0534AM7075,TILE CLEANER 750MM SPRAY
2003,9312113004419,0520CHACM200,MAXI FLOC PLUS
2004,9312113010052,0520BAF500,ANTIFOAM
2005,9312113020204,0515AMV001,STARVER  X REMOVER PHOS 1.0 LTR
2006,9315325000750,0565CHPS25,SALT      20  KG
2007,9319737001008,0540CH1003,POOL/K 10 KG L/RESIDUE CHLORINE
2008,9319737028531,0560CHS1009,BOOST & BALANCE 1.0 KG
2009,9319737086357,0520CH5003301C,SAND CLEANER
2010,9319737090224,0508CH6020,HYDROCHLORIC ACID 20 LTR
2011,9319737180024,0514CH4626,STABILISER 2.0KG
2012,9319737200104,0507CH67093,1 KG CHLORINE TABLETS
2013,9319737210103,0515AQ-331010,AVERT DE-SCALER 1 LTR
2014,9319737250055,0514CH317,STABILISER 25 KG
2015,9319737270022,0509CH67626,BUFFER  2KG
2016,9319737320024,0529CHPK4898,CALCIUM PLUS 2 KG
2017,9319737330047,052967414,CALCIUM PLUS HARDNESS 4 KG
2019,9319737500501,0508CH6005,HYDROCHLORIC ACID   5 LTR
2020,9319737534018,0520CH4628,1 LITRE CELL CLEANER
2021,9319737601505,0506CH6221,LIQUID CHLORINE 15 LTR
2023,9319737800205,0540CH1001,POOL/K 2KG L/RESIDUE CHLORINE
2024,9319737800403,0509CH67685,BUFFER TA INCREASER 4KG
2025,9319737801509,0508CH6010,NON FUMING ACID 15 LTR
2026,9319737900400,0540CH1002,POOL/K 4KG L/ RESIDUE CHLORINE
2027,9319737123205,0509CH67134,BUFFER TA INCREASER 10KG
2029,9339941005005,0560CHS1001,SPA BROMINE 1KG
2030,9342344000461,0560CHS1002,SPA BROMINE 2KG
2031,SEAQ0506CHSell,0506CH6420,Drum Deposit
2032,SEAQ0506CHPlus,0506CH6420,Drum Return
2033,SEAD2033CPS,CPSpecial5,C5 Chlorine Dioxide Pre-Cursor Tablet
2035,9319737090507,0506CH6221,LIQUID CHLORINE 15 LTR
2038,SEAD2036CPC,SEAD2036CPC,1l Clarifier
2039,SEAD2037CPA,SEAD2037CPA,1l Non Copper Algaecide 
2041,SEAD2041CPA,SEAD2041CPA,1 Tablet Tri Chloro Tab)

If the PartNumber is in the ShtPartNumber then what I previously did was ran a recordset query to step through and add a drum deposit for the items delivered by that staff.

So:
Victor P,LIQUID CHLORINE 15 LTR ,Site 20 for Client 19,Water Body 29 For Site 20,0506CH6221,4,133

Would get:
Victor P,Drum Deposit ,Site 20 for Client 19,Water Body 29 For Site 20,0506CH6420,4,133
added to BarCodesBetweenDates output

Hope that is clear enough to follow. The drum deposit is always 0506CH6420

yosiasz · February 9, 2020, 12:09am

Great job on providing sample data. Did you try running it locally to see if the script you provided works without errors? Just be sure to provide it with temporary table #BarCodesBetweenDates, otherwise you could be messing up the real dbo.BarCodesBetweenDates table

mike01 · February 10, 2020, 7:50pm

We don't mind helping, but please provide data that we can readily consume. Your insert statements all fail for both tables. Also, not really clear on your question

yosiasz · February 10, 2020, 8:21pm

He keeps posting the same thing in different posts. Under 2 different accounts dave_creative and new_creative. I cannot keep track

mike01 · February 10, 2020, 9:36pm

Dave,

you need to provide some easily consumable DDL and Data as well as what you expect. This is what it should look like

--Table Creates:

IF OBJECT_ID('tempdb..#Site') IS NOT NULL 
    DROP TABLE #Site
IF OBJECT_ID('tempdb..#BarCodesBetweenDates') IS NOT NULL 
    DROP TABLE #BarCodesBetweenDates

IF OBJECT_ID('tempdb..#ShtStock') IS NOT NULL 
    DROP TABLE #ShtStock
IF OBJECT_ID('tempdb..#ShtStockDrumDeposit') IS NOT NULL 
    DROP TABLE #ShtStockDrumDeposit



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));

create table #ShtStock
(ShtStockId int,
   BarCode varchar(20),
   PartNumber varchar(20),
   Description varchar(50))


create table #ShtStockDrumDeposit
(StockDepositID int,
 ShtPartNumber varchar(20))

--SampleData

INSERT INTO #ShtStockDrumDeposit
(StockDepositID,ShtPartNumber)
Values(1,'0507CH6220'),
(2,'0508CH6020'),
(3,'0506CH6221'),
(4,'0508CH6010')

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

create table #ShtStockDrumDeposit
(StockDepositID int,
 ShtPartNumber varchar(20))

INSERT INTO #ShtStock
           (
    ShtStockId,BarCode,PartNumber,Description)
 VALUES
 (448,'9317370908074','0506CH6221','LIQUID CHLORINE 15 LTR'),
(457,'9319737142619','0508CH6010','NON FUMING ACID 15 LTR'),
(460,'9319737123403','0509CH67626','BUFFER 2KG'),
(465,'9319737080102','0512CH67686','AQUACHLOR PH UP 2KG'),
(467,'9319737432208','0514CH4626','STABILISER 2.0KG'),
(481,'SEAQ0515CHAG1935','0515CHAG1935','SODIUM THIOSULPHATE PER KG'),
(485,'SEAQ0515CHMET','0515CHMETRID','METAL RID STAIN REMOVER 2KG'),
(487,'SEAQ0515CHPK0099','0515CHPK-0099','NON COPPER ALGAECIDE 20 LITRES'),
(499,'9319737410121','0518CH66166','POOL WATER CLARIFIER 1 LTR'),
(500,'SEAQ0518CHK01800','0518CHPK-01800','CLARIFIER 20 LITRES'),
(507,'SEAQ520CH91020','0520CH91020','CARTRIDGE CLEANER'),
(510,'9319737489943','052967414','CALCIUM PLUS HARDNESS 4 KG'),
(512,'9319737489738','0529CHPK4897','CALCIUM HARDNESS PLUS 10KG'),
(525,'9334024002346','0540CH1006','POOL MAGIC 10 KG FINE GRADE CHLORIN'),
(529,'9319737602557','0560CH1005','BROMINE 10 KG'),
(539,'9319737046191','0560CHS1099','SPA SHOCK 500GMS'),
(572,'4049991090214','0584511051BT','PHOTOMETER DPD NO 1 TABLETS 250 PK'),
(574,'4049991057064','0584511082BT','PHOTOMETER DPD NO3 TABLETS PK 500'),
(576,'4049991091471','0584511371BT','PHOTOMETER CYNURIC STAB TABS 250PK'),
(577,'4049991084046','0584511591BT','PHOTOMETER DPD NO3 250 PK'),
(578,'4049991059075','0584511770BT','PHOTOMETER PHENOL RED TABS 100PK'),
(579,'4049991059143','0584511771BT','PHOTOMETER PH RED TABS 250 PK'),
(580,'4049991059181','0584511772BT','PHOTOMETER PHENOL RED TAB PK 500'),
(581,'4049991091846','0584513211BT','PHOTOMETER T/A TABS 250 PK'),
(582,'4049991110172','0584513241BT','PHOTOMETER ALKA M TABS PKT 250 HR'),
(584,'4049991093512','0584517761BT','CALCIO COMBO 1 & 2 TABS 100 PACK'),
(585,'4049991093536','0584517762','CALCIO COMBO PK1& 2 250ea PHOTO'),
(592,'4049991084015','0585511501BT','PHOTO DPD-1 HR TABS MD100/2 250 PK'),
(593,'4049991084039','0585511590BT','PHOTO DPD-3 HR TABS MD100/2 -100PK'),
(1993,'SEAD1993CPS','CPSpecial','1000 Litre Bulk Sodium Hypochlorite'),
(1994,'SEAD1994CPS2','CPSpecial2','Chemical - Calcium Chloride Per Kg'),
(1995,'SEAD1995CPS3','CPSpecial3','Chemical - Sodium Bicarbonate Per Kg'),
(1996,'SEAD1996CPS4','CPSpecial4','Chemical-Cyanuric Acid Per Kg'),
(1997,'656055000016','0515CH4621','POOL BLOCKS (5) 1.5KG'),
(1998,'65605500002','0515CH4622','POOL DROPS PLUS H/D ALGICIDE'),
(1999,'656055000061','0560CHS1101','OXYCLEAR SPA/POOL SHOCK 1.5kg'),
(2000,'656055000122','0515SSR100','SERIOUS STAIN REMOVER'),
(2001,'9312113001159','0515CHAMS210','MULTI STAIN REMOVER LO/CHLO'),
(2002,'9312113002392','0534AM7075','TILE CLEANER 750MM SPRAY'),
(2003,'9312113004419','0520CHACM200','MAXI FLOC PLUS'),
(2004,'9312113010052','0520BAF500','ANTIFOAM'),
(2005,'9312113020204','0515AMV001','STARVER X REMOVER PHOS 1.0 LTR'),
(2006,'9315325000750','0565CHPS25','SALT 20 KG'),
(2007,'9319737001008','0540CH1003','POOL/K 10 KG L/RESIDUE CHLORINE'),
(2008,'9319737028531','0560CHS1009','BOOST & BALANCE 1.0 KG'),
(2009,'9319737086357','0520CH5003301C','SAND CLEANER'),
(2010,'9319737090224','0508CH6020','HYDROCHLORIC ACID 20 LTR'),
(2011,'9319737180024','0514CH4626','STABILISER 2.0KG'),
(2012,'9319737200104','0507CH67093','1 KG CHLORINE TABLETS'),
(2013,'9319737210103','0515AQ-331010','AVERT DE-SCALER 1 LTR'),
(2014,'9319737250055','0514CH317','STABILISER 25 KG'),
(2015,'9319737270022','0509CH67626','BUFFER 2KG'),
(2016,'9319737320024','0529CHPK4898','CALCIUM PLUS 2 KG'),
(2017,'9319737330047','052967414','CALCIUM PLUS HARDNESS 4 KG'),
(2019,'9319737500501','0508CH6005','HYDROCHLORIC ACID 5 LTR'),
(2020,'9319737534018','0520CH4628','1 LITRE CELL CLEANER'),
(2021,'9319737601505','0506CH6221','LIQUID CHLORINE 15 LTR'),
(2023,'9319737800205','0540CH1001','POOL/K 2KG L/RESIDUE CHLORINE'),
(2024,'9319737800403','0509CH67685','BUFFER TA INCREASER 4KG'),
(2025,'9319737801509','0508CH6010','NON FUMING ACID 15 LTR'),
(2026,'9319737900400','0540CH1002','POOL/K 4KG L/ RESIDUE CHLORINE'),
(2027,'9319737123205','0509CH67134','BUFFER TA INCREASER 10KG'),
(2029,'9339941005005','0560CHS1001','SPA BROMINE 1KG'),
(2030,'9342344000461','0560CHS1002','SPA BROMINE 2KG'),
(2031,'SEAQ0506CHSell','0506CH6420','Drum Deposit'),
(2032,'SEAQ0506CHPlus','0506CH6420','Drum Return'),
(2033,'SEAD2033CPS','CPSpecial5','C5 Chlorine Dioxide Pre-Cursor Tablet'),
(2035,'9319737090507','0506CH6221','LIQUID CHLORINE 15 LTR'),
(2038,'SEAD2036CPC','SEAD2036CPC','1l Clarifier'),
(2039,'SEAD2037CPA','SEAD2037CPA','1l Non Copper Algaecide'),
(2041,'SEAD2041CPA','SEAD2041CPA','1 Tablet Tri Chloro Tab')

Query

Select b.StaffName, b.SiteName, b.WBName, ss.PartNumber, Quant, JobNumber
from #ShtStock ss
join #BarCodesBetweenDates b
on ss.partNumber = b.PN
join #ShtStockDrumDeposit dd
on ss.partNumber = dd.shtPartNumber
join #Site s
on s.SiteName = b.SiteName

New_Creative · February 10, 2020, 9:55pm

Thanks Mike

That's very helpful. Perhaps It will be less frustrating for all now I know exactly what is needed. I will use this as a pattern.