Understanding Errors and Dynamic Pivot Columns

New_Creative · February 6, 2020, 9:07pm

Okay, I gave up trying to communicate on the last topic as I simply don't know how to convey the data. I sincerely hope I don't have to this time as it is very frustrating.

I am attempting to lean how to make my pivot tables dynamic. I have been following along a tutorial I found on line. As I have built the query I have been testing it and it tested fine where I could...until the end.

The query I have built is:
DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME('MyQ')
FROM
(SELECT DISTINCT WBName
FROM dbo.BarCodesBetweenDates) AS B
DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 'dbo.Site.SiteID, ' + @Columns + '
FROM
(SELECT TOP (100) PERCENT dbo.Site.SiteID, dbo.BarCodesBetweenDates.SiteName, dbo.BarCodesBetweenDates.WBName, dbo.BarCodesBetweenDates.PN, dbo.BarCodesBetweenDates.Description,
SUM(dbo.BarCodesBetweenDates.Quant) AS Expr1
FROM dbo.Site INNER JOIN
dbo.BarCodesBetweenDates ON dbo.Site.SiteName = dbo.BarCodesBetweenDates.SiteName
GROUP BY dbo.BarCodesBetweenDates.SiteName, dbo.BarCodesBetweenDates.WBName, dbo.BarCodesBetweenDates.PN, dbo.BarCodesBetweenDates.Description, dbo.Site.SiteID
HAVING (dbo.Site.SiteID = 58)
) AS PivotData
PIVOT
(
COUNT(Expr1)
FOR LocationName IN (' + @Columns + ')
) AS PivotResult
ORDER BY dbo.BarCodesBetweenDates.Description'
EXEC(@SQL);

This query parses fine. However when I run execute I am getting the following errors:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.

My questions are:

First these error messages I note they always contain the line number, is this literally the line number on screen? Assuming that is true, why doesn't the design window include the line numbers, or is this something I can turn on?

Equally as confusing the first error claims the error is near ',' on line 1...Uhmm...Line 1 doesn't even contain a ' ,.' Am I being too literal?

Why can something Parse ok and throw errors on running?

Oh and can anyone show me where the actual error is in this statement? As I am unclear on what or where to look.

mike01 · February 6, 2020, 9:12pm

you need brackets around the column names

change this
COALESCE(@Columns + ', ','') + QUOTENAME('MyQ')

to
COALESCE(@Columns + ',[', '[') + QUOTENAME('MyQ')+ ']'

Can you run select @SQL before the Exec (@SQL) and supply here? That will help identify what is happening

New_Creative · February 6, 2020, 9:24pm

SiteID, ][MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], [[MyQ], 

(1 row affected)

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.

Completion time: 2020-02-07T05:18:23.7696350+08:00

That's interesting.

The tutorial definitely didn't show the square brackets and the results of teh SQL shouldn't be MyQ it should be WBNames like Outdoor pool, Indoor Pool etc.

I kind of got it half working by Changing the SET @SQL = 'SiteID, ' + @Columns + ' to SET @SQL = 'WBName, ' + @Columns + ' However this is outputting the Site Names not the WBNames?

And why is it outputting [[ with only ] on the other end?

Thanks

yosiasz · February 6, 2020, 9:25pm

All kinds of Issues you will need to fix up.

DECLARE @Columns as VARCHAR(MAX)


create table BarCodesBetweenDates(WBName varchar(50), 
SiteName varchar(250), PN int, Description varchar(250), Quant int );
create table Site(SiteID int , SiteName varchar(50));

insert into Site
select 58, 'Tatooine'

insert into BarCodesBetweenDates
select 'Da Forrest', 'Tatooine', 88989898, 'Desert planet and childhood home of Anakin Skywalker and Luke Skywalker. Location of Jabba the Hutt''s palace', 550 

SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(WBName)
FROM
(SELECT DISTINCT WBName
FROM dbo.BarCodesBetweenDates
) AS B

DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 'SELECT dbo.Site.SiteID, ' + @Columns + '
FROM
(SELECT TOP (100) PERCENT dbo.Site.SiteID, dbo.BarCodesBetweenDates.SiteName, dbo.BarCodesBetweenDates.WBName, dbo.BarCodesBetweenDates.PN, dbo.BarCodesBetweenDates.Description,
SUM(dbo.BarCodesBetweenDates.Quant) AS Expr1
FROM dbo.Site INNER JOIN
dbo.BarCodesBetweenDates ON dbo.Site.SiteName = dbo.BarCodesBetweenDates.SiteName
GROUP BY dbo.BarCodesBetweenDates.SiteName, dbo.BarCodesBetweenDates.WBName, dbo.BarCodesBetweenDates.PN, dbo.BarCodesBetweenDates.Description, dbo.Site.SiteID
HAVING (dbo.Site.SiteID = 58)
) AS PivotData
PIVOT
(
COUNT(Expr1)
FOR LocationName IN (' + @Columns + ')
) AS PivotResult
ORDER BY dbo.BarCodesBetweenDates.Description'
select @SQL

dbo.Site.SiteID, [Da Forrest]  FROM  (SELECT TOP (100) PERCENT dbo.Site.SiteID, 
dbo.BarCodesBetweenDates.SiteName, dbo.BarCodesBetweenDates.WBName, dbo.BarCodesBetweenDates.PN, dbo.BarCodesBetweenDates.Description,  SUM(dbo.BarCodesBetweenDates.Quant) AS Expr1  FROM dbo.Site INNER JOIN  
dbo.BarCodesBetweenDates ON dbo.Site.SiteName = dbo.BarCodesBetweenDates.SiteName  GROUP BY dbo.BarCodesBetweenDates.SiteName, dbo.BarCodesBetweenDates.WBName, 
dbo.BarCodesBetweenDates.PN, dbo.BarCodesBetweenDates.Description, dbo.Site.SiteID  HAVING (dbo.Site.SiteID = 58)  ) AS PivotData  PIVOT  (  COUNT(Expr1)  FOR LocationName IN ([Da Forrest])  
) AS PivotResult  ORDER BY dbo.BarCodesBetweenDates.Description

--drop table BarCodesBetweenDates
--drop table Site

New_Creative · February 6, 2020, 9:31pm

I made a change and got the column headings correct, however the [[ still there.

    (SELECT DISTINCT WBName
FROM            dbo.Site INNER JOIN
                         dbo.BarCodesBetweenDates ON dbo.Site.SiteName = dbo.BarCodesBetweenDates.SiteName
WHERE        (dbo.site.SiteID = 58)) AS B

yosiasz · February 6, 2020, 9:44pm

Post the change you made so we can help you fix it

Also the output you would like to see

mike01 · February 6, 2020, 9:48pm

The MQ is what you have hardcoded because it's in quotes ( QUOTENAME('MyQ')). I'm not sure why you are getting 2 brackets though. If you post the new code you have so far, we might be able to spot it (hundreds of eyes are better than 2)

New_Creative · February 6, 2020, 9:50pm

Okay. This is kind of working...

I am simply makign a silly error somewhere...

DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
--COALESCE(@Columns + ', ','') + QUOTENAME(Name)
COALESCE(@Columns + ', ','') + QUOTENAME(Description)
FROM
(SELECT DISTINCT Description
FROM            dbo.Site INNER JOIN
                         dbo.BarCodesBetweenDates ON dbo.Site.SiteName = dbo.BarCodesBetweenDates.SiteName
WHERE        (dbo.site.SiteID = 58)) AS B
ORDER BY B.Description
--Works to here
DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 'SiteID, ' + @Columns + '
FROM
(SELECT        dbo.Site.SiteID, dbo.BarCodesBetweenDates.SiteName, dbo.BarCodesBetweenDates.WBName, dbo.BarCodesBetweenDates.PN, dbo.BarCodesBetweenDates.Description, 
                         SUM(dbo.BarCodesBetweenDates.Quant) AS Expr1
FROM            dbo.Site INNER JOIN
                         dbo.BarCodesBetweenDates ON dbo.Site.SiteName = dbo.BarCodesBetweenDates.SiteName
GROUP BY dbo.BarCodesBetweenDates.SiteName, dbo.BarCodesBetweenDates.WBName, dbo.BarCodesBetweenDates.PN, dbo.BarCodesBetweenDates.Description, dbo.Site.SiteID
HAVING        (dbo.Site.SiteID = 58)
) AS PivotData
PIVOT
(
   COUNT(Expr1)
   FOR Description IN (' + @Columns + ')
) AS PivotResult
ORDER BY dbo.BarCodesBetweenDates.Description'
select @SQL
EXEC(@SQL);

Besides bringing up a follow on question on Trim as some of the company descriptions are full of leading or trailing spaces, the output so far is:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SiteID, [1l Clarifier], [1l Non Copper Algaecide ], [Chemical - Calcium Chloride Per Kg], [Chemical - Sodium Bicarbonate Per Kg], [Chemical-Cyanuric Acid Per Kg], [CLARIFIER 20 LITRES                ], [Drum Return], [HYDROCHLORIC ACID 20 LTR], [LIQUID CHL

(1 row affected)

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.

Completion time: 2020-02-07T05:43:56.3460609+08:00

Which is heading in the right direction.

What I am looking for is

I have a lot of reports like this one, I just thought I would use this one to get my head around it.

New_Creative · February 6, 2020, 9:54pm

What is frustrating is the

SELECT        dbo.Site.SiteID, dbo.BarCodesBetweenDates.SiteName, dbo.BarCodesBetweenDates.WBName, dbo.BarCodesBetweenDates.PN, dbo.BarCodesBetweenDates.Description, 
                         SUM(dbo.BarCodesBetweenDates.Quant) AS Expr1
FROM            dbo.Site INNER JOIN

etc works if I paste it and run it externally...

mike01 · February 6, 2020, 9:55pm

I got this to work, there were a couple of things going on. I also made them temp tables. I only had the data that was provided, so if you have more, we can test it on a bigger scale.

DECLARE @Columns as VARCHAR(MAX)

create table #BarCodesBetweenDates(WBName varchar(50), 
SiteName varchar(250), PN int, Description varchar(250), Quant int );
create table #Site(SiteID int , SiteName varchar(50));

insert into #Site
select 58, 'Tatooine'

insert into #BarCodesBetweenDates
select 'Da Forrest', 'Tatooine', 88989898, 'Desert planet and childhood home of Anakin Skywalker and Luke Skywalker. Location of Jabba the Hutt''s palace', 550 

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
WHERE S.SiteID = 58
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)

New_Creative · February 6, 2020, 9:58pm

Thanks @mike01 but why are you creating the temp table? Sorry if this is obvious.

mike01 · February 6, 2020, 10:01pm

I didn't want to muck up my database. It was for testing, you are good to use the tables you have. Didn't want you to copy the code and it doesn't work

yosiasz · February 6, 2020, 10:09pm

I have a dedicated SQL team db locally I try all things SQL team on

yosiasz · February 6, 2020, 10:11pm

You have to understand that query alone is not sufficient when we answer questions. We need data to make sure it all works. Hence the creation of sample data cause we ain't got access to your SQL server. Otherwise it's like a skeleton walking into doc office and asking to have doc check his muscle pain

DavidCreative · February 8, 2020, 10:00pm

So how do you do this when there are literally hundreds or thousands of data lines to enter?

Is there a tool to help extract the data (Not script of database as it seems to create oddles of lines of stuff), or perhaps a SQL SP or View that helps?

DavidCreative · February 8, 2020, 10:10pm

gives me

Msg 208, Level 16, State 0, Line 2
Invalid object name '#BarCodesBetweenDates'.

I dont follow why # is added and I am unsure about taking it away. Sorry if this is simple?

Thanks

DavidCreative · February 8, 2020, 10:23pm

Is this helpful?

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)

yosiasz · February 9, 2020, 12:01am

very good! that is the way you provide sample data!! But you first have to test it on a local sql server of yours to work on our sql server locally.

Did you test it on yours first, because there are errors in that script you provided.

yosiasz · February 9, 2020, 12:06am

that is called a temporary table #BarCodesBetweenDates. It helps so that you don't mistakenly overwrite data in or delete data in the real dbo.BarCodesBetweenDates table in production. You might be looking for a new job if you mistakenly run stuff we provide you as a possible solution if we used the real table name

New_Creative · February 9, 2020, 9:25pm

Hahaha...wont be looking for a new job...This is like my extra curricular 2 hours a day dedication to the job. And I think if somehow I completely stuffed it, I would probably get away with a stern telling off and don't do it again. But I don't willingly stuff things up, so good point.