Help with SQL Select Query

Hello,

I got a SQL table that looks like this :

Code  Season    DateCreated      CPDate            QCDate           GradeDate        MarkingDate     FabricDate       CutDate
0001    153
0002    153
0003    161
0004    161
0005    161

For each code... i will have dates here. Actually, its by order most of the time, what I mean is you will always have a DateCReated.. then next step will be a CPDate and so on. if the users are not arrived to QC it will be null. when the user is done, they will put a date there so gradeDate will still be null but will be next to be updated etc.... and so on.

I need help to do a SELECT that I would do this:
I would like  column 1 to be the season, column 2 to be number of days outstanding and column 3-4-5-6..to be CP,  QC,  Grade,  Marking, Fabric and Cut that is  the number of CODE inside that numbers of days.

To be more clearer, if a date for 3 Codes above has a null after CPDate and it been 8 days since (getdate()) well you would put 3 in CP
so you see here, i put 3 in CP close to 8 days.
the logic is good for all the rest...

season days    CP          QC         Grade        Marking            Fabric             Cut
153
                6
                7
                8         3
                9
               10
               11
               12
               13
               14
               15 +


Thanks for the help, let me know if you have questions
LVL 1
PhilippeRenaudAsked:
Who is Participating?
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:
You are going to get a much better response to this question if you provide some sample data in the form of a CREATE TABLE statement and an INSERT statement to load it.  You can do this automatically by using the scripting functionality in SSMS.

http://blogs.msdn.com/b/davidlean/archive/2009/09/20/tip-ssms-script-your-entire-table-including-the-data-a-hidden-gem.aspx

We don't necessarily need a ton of data...maybe a couple of dozen rows or enough to cover most of the use cases.
1
PhilippeRenaudAuthor Commented:
ok :

CREATE TABLE tempSQL
(
		
	   [Code]			varchar(50)
      ,[DateEntered]	datetime
      ,[CPDate]			datetime
      ,[QCDate]			datetime
      ,[GradeDate]		datetime
      ,[MarkingDate]	datetime
      ,[FabricDate]		datetime
      ,[TableDate]		datetime
      ,[CutDate]		datetime
);


insert into tempSQL
values  ('029700', '2015-10-04 00:00:00.000', '2015-10-04 00:00:00.000', null, null, null, null, null, '2015-10-06 00:00:00.000')
	,	('029639', '2015-10-01 00:00:00.000', '2015-10-03 00:00:00.000', '2015-10-05 00:00:00.000', '2015-10-05 00:00:00.000', '2015-10-05 00:00:00.000', NULL, '2015-10-06 00:00:00.000', '2015-10-06 00:00:00.000')
	,   ('029701', '2015-10-06 00:00:00.000', '2015-10-06 00:00:00.000', null, null, null, null, null, '2015-10-06 00:00:00.000')
	,   ('029640', '2015-10-01 00:00:00.000', '2015-10-02 00:00:00.000', '2015-10-03 00:00:00.000', '2015-10-02 00:00:00.000', '2015-10-02 00:00:00.000', '2015-10-03 00:00:00.000', '2015-10-06 00:00:00.000', '2015-10-06 00:00:00.000')

Open in new window


i only added 4 rows. but it should do the work.
0
PhilippeRenaudAuthor Commented:
Sorry i forgot 1 column, the season :  


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  ('029700', '153', '2015-10-04 00:00:00.000', '2015-10-04 00:00:00.000', null, null, null, null, null, '2015-10-06 00:00:00.000')
	,	('029639', '153', '2015-10-01 00:00:00.000', '2015-10-03 00:00:00.000', '2015-10-05 00:00:00.000', '2015-10-05 00:00:00.000', '2015-10-05 00:00:00.000', NULL, '2015-10-06 00:00:00.000', '2015-10-06 00:00:00.000')
	,   ('029701', '153', '2015-10-06 00:00:00.000', '2015-10-06 00:00:00.000', null, null, null, null, null, '2015-10-06 00:00:00.000')
	,   ('029640', '163', '2015-10-01 00:00:00.000', '2015-10-02 00:00:00.000', '2015-10-03 00:00:00.000', '2015-10-02 00:00:00.000', '2015-10-02 00:00:00.000', '2015-10-03 00:00:00.000', '2015-10-06 00:00:00.000', '2015-10-06 00:00:00.000')

 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Brian CroweDatabase AdministratorCommented:
Can I assume that DateEntered is synonymous with DateCreated in your explanation above?  What would you expect as output from these 4 rows?  Can you define how you calculate "days outstanding"?
0
PhilippeRenaudAuthor Commented:
1) Sorry, DateEntered will be DateCreated Yes.
2) Das outstanding is hardcoded....

I need to see something like:
1
2
3
4
5
6
7
8
9
10+

you could create a tmpTable hardcode 1 to 10 in it if you want...its fine for me.
if days are more then 10+ I need to see it in the row of "10+"
0
PhilippeRenaudAuthor Commented:
I hope I did not do a mistake. but the idea again is that... every date is when the Action was "completed"

so If CP is   2015-10-06 and Dateentered was  2015-10-04   well it means it was on CP for 2 days.
so on the row days outstanding 2   under column CP I would add 1 to the value.   thats basically the logic for all of them.

for my 4 examples... it would be :

Season    Days         CP        QC      Grade  ... Cut Date
153           1                0           0             0                1
153           2                1           1             0                1
153           3                0           0             0                0
153           4                0           0             0                0
153           5                0           0             0                0
153           6                0           0             0                0
...
153          10+             0           0             0                0

163          1                 1           1             0                0
163          2                 0           0             0                0
163          3                 0           0             0                1
163          4
163         5
0
Brian CroweDatabase AdministratorCommented:
That helps immensely...working
0
Brian CroweDatabase AdministratorCommented:
As an intermediate step in the solution and to make sure I am understanding correctly does this look correct per Code before doing any grouping by Season and the Days?

Season	Code	DateEntered	CP	QC	Grade	Marking	Fabric	Table	Cut
153	029700	2015-10-04 00:00:00.000	0	2	2	2	2	2	2
153	029639	2015-10-01 00:00:00.000	2	4	4	4	5	5	5
153	029701	2015-10-06 00:00:00.000	0	0	0	0	0	0	0
163	029640	2015-10-01 00:00:00.000	1	2	1	1	2	5	5

Open in new window


	SELECT Season, Code, DateEntered,
		DATEDIFF(DAY, DateEntered, ISNULL(CPDate, GETDATE())) AS CP,
		DATEDIFF(DAY, DateEntered, ISNULL(QCDate, GETDATE())) AS QC,
		DATEDIFF(DAY, DateEntered, ISNULL(GradeDate, GETDATE())) AS Grade,
		DATEDIFF(DAY, DateEntered, ISNULL(MarkingDate, GETDATE())) AS Marking,
		DATEDIFF(DAY, DateEntered, ISNULL(FabricDate, GETDATE())) AS Fabric,
		DATEDIFF(DAY, DateEntered, ISNULL(TableDate, GETDATE())) AS [Table],
		DATEDIFF(Day, DateEntered, ISNULL(CutDate, GETDATE())) AS Cut
	FROM tempSQL

Open in new window

0
qhanrahanCommented:
Not really sure what you are looking for but here is an attempt.

Select season, code, DATEDIFF(dd, dateentered, getdate()) as "Outstanding Days Since Date Entered", * from tempsql

You could do this for each separate date field.

Or under what I think is the premise that there is an order to the dates, i.e. the fabric date would always occur on or before the cut date you could do something like:

select SEASON, CODE, DATEDIFF(DD, ISNULL(cpdate, isnull(qcdate, ISNULL(gradedate, isnull(markingdate, isnull(fabricdate, isnull(tabledate, isnull(cutdate, GETDATE() ))))))), dateentered )
                 , * from tempsql

Though you may want to modify this to found out which codes have been sitting the longest since entered and have not had a date entered for the last date field (the last stage of production).
0
PhilippeRenaudAuthor Commented:
Brian,

the only error is that you go by Code also.. which is wrong.
I really need to see :

1
2
3
4
5
6
7
8
9
10+  

as rows...  and if CP Date versus DateEntered as 2 days diff.. you would add 1 to the corresponding row (2) and corresponding column (CP)

if you get another Code that CP and DateEntered is 2 days Diff... well on corresponding row (2) and column (CP) you would add another 1  so now it would be 2 as total

Yuo know what I mean ?
0
PhilippeRenaudAuthor Commented:
oh sorry i didnt understand your question correctly.. you were asking per code first if it was good... hold on im looking
0
Brian CroweDatabase AdministratorCommented:
Are the date columns intended to be sequential such that the logic is for example:

QC Days = QCDate - CPDate (If QCDate IS NULL THEN substitute current date for QCDate)
Grade Days = GradeDate - QCDate (If GradeDate IS NULL THEN substitute current date for GradeDate)
etc.

such that each sequential column is looking at the preceding column.  If the preceding column is NULL then there is no need to calculate the days because the Code is still on the preceding step.  I'm not quite sure how CutDate works into it all.
0
Brian CroweDatabase AdministratorCommented:
Or do the date columns represent actions happening in parallel so each is compared to the DateEntered?  In that case how do you handle the null entries?  I think if we can get this intermediary step where we are evaluating the code.  The aggregate by Season is pretty straight-forward.
0
PhilippeRenaudAuthor Commented:
CPDAte should be compare to DateEntered
QC to CP Date  (if QC null you would add 0)
Grade to QC (if null add 0)
Marking to Grade (if null 0)
Fabric to Marking (if null 0)
Cut to Table (if null 0)

if you look at previous and that previous if null, yes I need to look the other one.
in other words, you look at previous that is NOT NULL. if you get a null you go back until its not null.


for instance, the 1st row of Data... only CP and Cut has a date.
if I want to look at Cut, i need to go back up to CP to do my DateDIFF and not to TableDate.
0
Brian CroweDatabase AdministratorCommented:
I think we're getting closer...

Below is my current code/output for analyzing by Code.  There is a seeming anomaly in that the fourth row of data (Code = 029640) the dates are not sequential.  Are those being handled correctly?

	SELECT Season, Code,
		DATEDIFF(DAY, DateEntered, ISNULL(CPDate, GETDATE())) + 1 AS CPDays,
		CASE
			WHEN CPDate IS NOT NULL THEN DATEDIFF(DAY, CPDate, ISNULL(QCDate, GETDATE())) + 1
			ELSE 0
		END AS QCDays,
		CASE
			WHEN QCDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(QCDate, CPDate), ISNULL(GradeDate, GETDATE())) + 1
			ELSE 0
		END AS GradeDays,
		CASE
			WHEN GradeDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(GradeDate, QCDate, CPDate), ISNULL(MarkingDate, GETDATE())) + 1
			ELSE 0
		END AS MarkingDays,
		CASE
			WHEN MarkingDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(MarkingDate, GradeDate, QCDate, CPDate), ISNULL(FabricDate, GETDATE())) + 1
			ELSE 0
		END AS FabricDays,
		CASE
			WHEN FabricDate IS NOT NULL THEN DATEDIFF(DAY, COALESCE(FabricDate, MarkingDate, GradeDate, QCDate, CPDate), ISNULL(TableDate, GETDATE())) + 1
			ELSE 0
		END AS TableDays,
		CASE
			WHEN TableDate IS NOT NULL THEN DATEDIFF(Day, COALESCE(TableDate, FabricDate, MarkingDate, GradeDate, QCDate, CPDate), ISNULL(CutDate, GETDATE())) + 1
			ELSE 0
		END AS CutDays
	FROM tempSQL

Open in new window


Season	Code	CPDays	QCDays	GradeDays	MarkingDays	FabricDays	TableDays	CutDays
153	029700	1	3	0	0	0	0	0
153	029639	3	3	1	1	2	0	1
153	029701	1	1	0	0	0	0	0
163	029640	2	2	0	1	2	4	1

Open in new window

0
Brian CroweDatabase AdministratorCommented:
Tweak based on rereading your latest comment.

	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

Open in new window


Season	Code	CPDays	QCDays	GradeDays	MarkingDays	FabricDays	TableDays	CutDays
153	029700	1	0	0	0	0	0	3
153	029639	3	3	1	1	0	2	1
153	029701	1	0	0	0	0	0	1
163	029640	2	2	0	1	2	4	1

Open in new window

0
Brian CroweDatabase AdministratorCommented:
This is what I have now assuming my logic on calculating the number of days / step is correct:

DECLARE @MaximumDays	INT = 10;

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,
		--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
),
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
ORDER BY Season, Minimum

Open in new window

0

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
PhilippeRenaudAuthor Commented:
I think it looks awesome...
thank you alot... it was very helpful.
0
PhilippeRenaudAuthor Commented:
0
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
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.