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
		END AS GradeDays,
		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

Open in new window

ForBrian.png
LVL 1
PhilippeRenaudAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Brian CroweDatabase AdministratorCommented:
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,
		--GradeDate,
		--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
		END AS GradeDays,
		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 = ???

Open in new window

Brian CroweDatabase AdministratorCommented:
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.
PhilippeRenaudAuthor 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 ?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Brian CroweDatabase AdministratorCommented:
Are Lot and Style synonymous with Season and Code respectively?
PhilippeRenaudAuthor 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
PhilippeRenaudAuthor Commented:
try it out :

CREATE TABLE tempSQL
(
		
	   [Code]			varchar(50)
	  ,[Season]   varchar(50)
      ,[DateEntered]	datetime
      ,[CPDate]			datetime
      ,[QCDate]			datetime
      ,[GradeDate]		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

Open in new window



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
		END AS GradeDays,
		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

Open in new window

Brian CroweDatabase AdministratorCommented:
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.GradeDays >= R.Minimum AND C.GradeDays < R.Maximum THEN 1 ELSE 0 END) AS Grade,
		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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
You can perform a quick sanity check on your data by summing up all of the values in the CP column of the final output.  Assuming you always have a date for CPDate that total should equal the total number of rows in your original dataset.
PhilippeRenaudAuthor Commented:
Thanks man !!! seems better!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.