asked on

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

Brian Crowe

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 Crowe

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.

Philippe Renaud

ASKER

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 ?

Brian Crowe

Are Lot and Style synonymous with Season and Code respectively?

Philippe Renaud

ASKER

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

Philippe Renaud

ASKER

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

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Crowe

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.

Philippe Renaud

ASKER

Thanks man !!! seems better!