# Select Query Step 2

Hi Brian,
i tried to see for only 1 code what it would like. i have removed temporarly the SUM to see only 1 by one...
the dates of this code is the following (see picture)

It looks good except... that I see there is a 1 in day 3 for grade and also 1 in day 4 of grade... for 1 code its silly to have 1 and 1 for same Grade in two different days.. is it becasue i removed the sum or is there a little bug maybe ?

``````DECLARE @MaximumDays	INT = 30;

WITH cteRange AS
(
SELECT 1 AS Minimum, 2 AS Maximum, CAST('1' AS VARCHAR(4)) AS Label
UNION ALL
SELECT Minimum + 1,
CASE WHEN Maximum = @MaximumDays THEN 9999 ELSE Maximum + 1 END,
CAST(CASE WHEN Maximum = @MaximumDays THEN CAST(Maximum AS VARCHAR) + '+' ELSE CAST(Maximum AS VARCHAR) END AS VARCHAR(4))
FROM cteRange
WHERE Minimum < @MaximumDays
),
cteCode AS
(
SELECT s.Season_No, [Code] = v.Lot,
DATEDIFF(DAY, DateEntered, ISNULL(CPDate, GETDATE())) + 1 AS CPDays,
CASE
WHEN QCDate IS NOT NULL THEN DATEDIFF(DAY, CPDate, QCDate) + 1
ELSE 0
END AS QCDays,
CASE
WHEN GradeDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(QCDate, CPDate), GradeDate) + 1
ELSE 0
CASE
WHEN MarkingDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(GradeDate, QCDate, CPDate), MarkingDate) + 1
ELSE 0
END AS MarkingDays,
CASE
WHEN FabricDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(MarkingDate, GradeDate, QCDate, CPDate), FabricDate) + 1
ELSE 0
END AS FabricDays,
CASE
WHEN TableDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(FabricDate, MarkingDate, GradeDate, QCDate, CPDate), TableDate) + 1
ELSE 0
END AS TableDays,
CASE
WHEN CutDate IS NOT NULL THEN DATEDIFF(Day, COALESCE(TableDate, FabricDate, MarkingDate, GradeDate, QCDate, CPDate), CutDate) + 1
ELSE 0
END AS CutDays
FROM View_Dashboard_ProductionCutting v
INNER JOIN Style s on s.Style1 = v.Style
),
cteSeasonGrp AS
(
SELECT distinct Season_No, Code, R.Minimum, R.Label AS [Days],
CASE WHEN C.CPDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END AS CP,
CASE WHEN C.QCDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END AS QC,
CASE WHEN C.GradeDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END AS Grade,
CASE WHEN C.MarkingDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END AS Marking,
CASE WHEN C.FabricDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END AS Fabric,
CASE WHEN C.TableDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END AS [Table],
CASE WHEN C.CutDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END AS Cut
FROM cteCode AS C
CROSS JOIN cteRange AS R
--GROUP BY C.Season_No, R.Minimum, R.Label
)
SELECT Season_No, Code, [Days], CP, QC, Grade, Marking, Fabric, [Table], Cut
FROM cteSeasonGrp
WHERE Season_No in ('153', '154', '161')
and code = '028214'
ORDER BY Season_No, Minimum
``````
ForBrian.png
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The best way to see how it is going to calculate the values for a single Code is to run the code within the cteCode section and add a WHERE clause.  Try that and see if you still get something that looks "funny".

``````	SELECT Season, Code,
--DateEntered,
--CPDate,
--QCDate,
--MarkingDate,
--FabricDate,
--TableDate,
--CutDate,
DATEDIFF(DAY, DateEntered, ISNULL(CPDate, GETDATE())) + 1 AS CPDays,
CASE
WHEN QCDate IS NOT NULL THEN DATEDIFF(DAY, CPDate, QCDate) + 1
ELSE 0
END AS QCDays,
CASE
WHEN GradeDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(QCDate, CPDate), GradeDate) + 1
ELSE 0
CASE
WHEN MarkingDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(GradeDate, QCDate, CPDate), MarkingDate) + 1
ELSE 0
END AS MarkingDays,
CASE
WHEN FabricDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(MarkingDate, GradeDate, QCDate, CPDate), FabricDate) + 1
ELSE 0
END AS FabricDays,
CASE
WHEN TableDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(FabricDate, MarkingDate, GradeDate, QCDate, CPDate), TableDate) + 1
ELSE 0
END AS TableDays,
CASE
WHEN CutDate IS NOT NULL THEN DATEDIFF(Day, COALESCE(TableDate, FabricDate, MarkingDate, GradeDate, QCDate, CPDate), CutDate) + 1
ELSE 0
END AS CutDays
FROM tempSQL
WHERE Code = ???
``````
If that runs okay then run the entire query leaving the WHERE clause in place and see what the output looks like.  With a single code record you definitely shouldn't see multiple "Days" with values in them unless we have a bug.
Author Commented:
I believe we have a bug.

in my TempSQL i only put the dates I gave you. only 1 row.
I ran it and I see 1 and 1 on two rows for 1 column

can you check ?
Are Lot and Style synonymous with Season and Code respectively?
Author Commented:
actually what I did, is that lot = Code yes.
but in tempSQL I did not put column Style. I followed what we said by having only Code and Season.

so I put season 153. with dates.  only 1 row.

and i still have those 2 lines
Author Commented:
try it out :

``````CREATE TABLE tempSQL
(

[Code]			varchar(50)
,[Season]   varchar(50)
,[DateEntered]	datetime
,[CPDate]			datetime
,[QCDate]			datetime
,[MarkingDate]	datetime
,[FabricDate]		datetime
,[TableDate]		datetime
,[CutDate]		datetime
);

insert into tempSQL
values  ('028214', '153', '2015-06-25 00:00:00.000', '2015-06-26 00:00:00.000', null, '2015-06-29 00:00:00.000', '2015-06-29 00:00:00.000', '2015-06-29 00:00:00.000', '2015-06-30 00:00:00.000', '2015-07-09 00:00:00.000')
select * from tempSQL
``````

``````DECLARE @MaximumDays	INT = 31;

WITH cteRange AS
(
SELECT 1 AS Minimum, 2 AS Maximum, CAST('1' AS VARCHAR(4)) AS Label
UNION ALL
SELECT Minimum + 1,
CASE WHEN Maximum = @MaximumDays THEN 9999 ELSE Maximum + 1 END,
CAST(CASE WHEN Maximum = @MaximumDays THEN CAST(Maximum AS VARCHAR) + '+' ELSE CAST(Maximum AS VARCHAR) END AS VARCHAR(4))
FROM cteRange
WHERE Minimum < @MaximumDays
),
cteCode AS
(
SELECT Season, Code,
DATEDIFF(DAY, DateEntered, ISNULL(CPDate, GETDATE())) + 1 AS CPDays,
CASE
WHEN QCDate IS NOT NULL THEN DATEDIFF(DAY, CPDate, QCDate) + 1
ELSE 0
END AS QCDays,
CASE
WHEN GradeDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(QCDate, CPDate), GradeDate) + 1
ELSE 0
CASE
WHEN MarkingDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(GradeDate, QCDate, CPDate), MarkingDate) + 1
ELSE 0
END AS MarkingDays,
CASE
WHEN FabricDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(MarkingDate, GradeDate, QCDate, CPDate), FabricDate) + 1
ELSE 0
END AS FabricDays,
CASE
WHEN TableDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(FabricDate, MarkingDate, GradeDate, QCDate, CPDate), TableDate) + 1
ELSE 0
END AS TableDays,
CASE
WHEN CutDate IS NOT NULL THEN DATEDIFF(Day, COALESCE(TableDate, FabricDate, MarkingDate, GradeDate, QCDate, CPDate), CutDate) + 1
ELSE 0
END AS CutDays
FROM tempSQL v
),
cteSeasonGrp AS
(
SELECT Season, R.Minimum, R.Label AS [Days],
SUM(CASE WHEN C.CPDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END) AS CP,
SUM(CASE WHEN C.QCDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END) AS QC,
SUM(CASE WHEN C.GradeDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END) AS Grade,
SUM(CASE WHEN C.MarkingDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END) AS Marking,
SUM(CASE WHEN C.FabricDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END) AS Fabric,
SUM(CASE WHEN C.TableDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END) AS [Table],
SUM(CASE WHEN C.CutDays BETWEEN R.Minimum AND R.Maximum THEN 1 ELSE 0 END) AS Cut
FROM cteCode AS C
CROSS JOIN cteRange AS R
GROUP BY C.Season, R.Minimum, R.Label
)

SELECT Season, [Days], CP, QC, Grade, Marking, Fabric, [Table], Cut
FROM cteSeasonGrp
WHERE Season in ('153', '154', '161')
ORDER BY Season, Minimum
``````
I see the problem.  I used BETWEEN on the ranges which is boundary inclusive so it is getting a match on two separate values in the range.  When Days = 3 It is getting a match on the 3-4 range AND the 2-3 range.  I thought of it while I was designing it but forgot to address it in the code.

Replace the code within the cteSeasonGrp common table expression with the code below:

``````	SELECT Season, R.Minimum, R.Label,
SUM(CASE WHEN C.CPDays >= R.Minimum AND C.CPDays < R.Maximum THEN 1 ELSE 0 END) AS CP,
SUM(CASE WHEN C.QCDays >= R.Minimum AND C.QCDays < R.Maximum THEN 1 ELSE 0 END) AS QC,
SUM(CASE WHEN C.MarkingDays >= R.Minimum AND C.MarkingDays < R.Maximum THEN 1 ELSE 0 END) AS Marking,
SUM(CASE WHEN C.FabricDays >= R.Minimum AND C.FabricDays < R.Maximum THEN 1 ELSE 0 END) AS Fabric,
SUM(CASE WHEN C.TableDays >= R.Minimum AND C.TableDays < R.Maximum THEN 1 ELSE 0 END) AS [Table],
SUM(CASE WHEN C.CutDays >= R.Minimum AND C.CutDays < R.Maximum THEN 1 ELSE 0 END) AS Cut
FROM cteCode AS C
CROSS JOIN cteRange AS R
GROUP BY C.Season, R.Minimum, R.Label
``````

Experts Exchange Solution brought to you by