asked on

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

Brian Crowe

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.

Philippe Renaud

ASKER

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.

Philippe Renaud

ASKER

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

Brian Crowe

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"?

Philippe Renaud

ASKER

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+"

Philippe Renaud

ASKER

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

Brian Crowe

That helps immensely...working

Brian Crowe

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

qhanrahan

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).

Philippe Renaud

ASKER

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 ?

Philippe Renaud

ASKER

oh sorry i didnt understand your question correctly.. you were asking per code first if it was good... hold on im looking

Brian Crowe

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.

Brian Crowe

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.

Philippe Renaud

ASKER

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.

Brian Crowe

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

Brian Crowe

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

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

Philippe Renaud

ASKER

I think it looks awesome...
thank you alot... it was very helpful.

Philippe Renaud

ASKER

Hey Brian, next question regarding this !

https://www.experts-exchange.com/questions/28737774/Select-Query-Step-2.html