SQL Sub Query Help

Transact-SQL
1

Hi I am trying to create the second table below:

To summarise the number of days it took to complete a SN_TYPE by SN_STATUS

Hope someone can help, I tried with a sub query but didn't work. Any help/suggestions would be greatly appreciated.

HAVE

SERVICE_NUMBER IDENTIFYER CREATED_DATE COMPLETED_DATE SN_TYPE SN_STATUS
318403853 61030203647 12/05/2017 18/05/2017 De-Activated COMP
318616723 61030203647 13/06/2017 13/06/2017 Re-Activattion N-CO
318637013 61030203647 15/06/2017 15/06/2017 Re-Activattion REJE
318639191 61030203647 15/06/2017 16/06/2017 Re-Activattion COMP
318637791 61030203666 15/06/2017 16/06/2017 Re-Activattion Rejected
318637792 61030203666 14/06/2017 15/06/2017 Re-Activattion N-CO

WANT

IDENTIFYER CREATED_DATE REQUESTED_DATE SN_TYPE SN_STATUS DAYS TO COMPLETE
61030203647 12/05/2017 18/05/2017 De-Activated COMP 6
61030203647 13/06/2017 16/06/2017 Re-Activattion COMP 3
61030203666 14/06/2017 16/06/2017 Re-Activattion Rejected 2

Code

SELECT DISTINCT D.SERVICE_NUMBER
,D.IDENTIFYER
,D.CREATED_DATE
,D.REQUESTED_DATE
,D.SN_TYPE
,D.SN_STATUS
,DATEDIFF(DAY,DATB.FIRSTS_ON_BP,DATB.LASTS_ON_BP) AS '#DAYS_Re-energisation'

FROM [SAP_Reporting_Users].[dbo].[A139075_DAN] D

LEFT JOIN
(
SELECT
FB.SN_TYPE
,FB.IDENTIFYER
,MIN(FB.CREATED_DATE) AS FIRSTS_ON_BP
,MAX(FB.REQUESTED_DATE) AS LASTS_ON_BP
FROM [SAP_Reporting_Users].[dbo].[A139075_DAN] FB
--WHERE FB.SN_STATUS = 'COMP'
GROUP By
FB.SN_TYPE
,FB.IDENTIFYER
) as DATB
ON DATB.SN_TYPE = D.SN_TYPE
AND DATB.IDENTIFYER = D.IDENTIFYER

Tried the query above but with not much success. Hope someone can help me expand.

Thanks

Danii

2

Let's have some consumable test data with ISO dates. (Not everyone uses European date format.)

CREATE TABLE #t
(
	ServiceNumber bigint NOT NULL
	,Identifier bigint NOT NULL
	,CreatedDate date NOT NULL
	,CompletedDate date NOT NULL
	,SN_Type varchar(20) NOT NULL
	,SN_Status varchar(20) NOT NULL
);
INSERT INTO #t
VALUES (318403853, 61030203647, '20170512', '20170518', 'De-Activated', 'COMP')
	,(318616723, 61030203647, '20170613', '20170613', 'Re-Activattion', 'N-CO')
	,(318637013, 61030203647, '20170615', '20170615', 'Re-Activattion', 'REJE')
	,(318639191, 61030203647, '20170615', '20170616', 'Re-Activattion', 'COMP')
	,(318637791, 61030203666, '20170615', '20170616', 'Re-Activattion', 'Rejected')
	,(318637792, 61030203666, '20170614', '20170615', 'Re-Activattion', 'N-CO');

This produces your result:

SELECT Identifier
	,MIN(CreatedDate) AS CreatedDate
	,MAX(CompletedDate) AS RequestedDate
	,SN_Type
	,MAX(CASE WHEN SN_STATUS IN ('COMP', 'Rejected') THEN SN_STATUS ELSE '' END) AS SN_Status
	,DATEDIFF(day, MIN(CreatedDate), MAX(CompletedDate)) AS DaysToComplete
FROM #t
GROUP BY Identifier, SN_Type;
1 Like
3

SQL won't parse them, even if you are SAT in Europe!, unless all the other, fragile, attributes are set appropriately to indicate "Europe default", so risky even for me to use them ... a user with an unusual Language setting spolis the ability to convert non-ISO dates

Pity SQL was ever built to "Try my best, with side effects" rather than "Convert dates but only with appropriate Territory setting"

4

Thank you, taught me something new.
Thanks also for such a quick response!
Greatly appreciated!!

5

Hi Ifor

I have a follow up question, if you have a minute to help me out?

I want to add a column to count the number of days until each SN_STATUS

CREATE TABLE #temp
(
ServiceNumber varchar(20) NOT NULL
,Identifier varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,SN_Type varchar(20) NOT NULL
,SN_Status varchar(20) NOT NULL
);
INSERT INTO #temp
VALUES ('318403853','61030203647','20170512','20170518','De-Activated', 'COMP')
INSERT INTO #temp
VALUES ('318616723','61030203647','20170613','20170613','Re-Activattion', 'N-CO')
INSERT INTO #temp
VALUES ('318637013','61030203647','20170615','20170615','Re-Activattion', 'REJE')
INSERT INTO #temp
VALUES ('318639191','61030203647','20170615','20170616','Re-Activattion', 'COMP')
INSERT INTO #temp
VALUES ('318637791','61030203666','20170615','20170616','Re-Activattion', 'Rejected')
INSERT INTO #temp
VALUES ('318637792','61030203666','20170614','20170615','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('000318634115','64074558782','20170615','20170615','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318636669','64074558782','20170615','20170615','Re-Activattion','COMP')
INSERT INTO #temp
VALUES('000318636873','64074558782','20170615','20170614', 'Re-Activattion','Rejected')
INSERT INTO #temp
VALUES('000318623572','64074558782','20170614','20170614','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318627678','64074558782','20170614','20170614','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318614132','64074558782','20170613','20170613','Re-Activattion','N-CO')

Below is the code:
I am trying to separate the day count into counting each status into separate columns
For example the below ID was rejected on the 14th and completed on the 15th
So i would like to add a column which counts from min date to first rejected (i.e 1 day) and to completed 2 days (two separate columns).
Trying to make the identifier unique (one row) with the added status and how long it took to get to each status.
Hope this makes sense.
Hope you can help.
Thanks

> SELECT 
>       --Customer,
> 	 Identifier
> 	,MIN(CreatedDate) AS CreatedDate
> 	,MAX(CompletedDate) AS RequestedDate
> 	,SN_Type
> 	,MAX(CASE WHEN SN_STATUS IN ('Rejected')  THEN SN_STATUS ELSE '' END) AS SN_Status_Rejected --Rejected should be from the 13th to 14th i.e 1 Day 
> 	--,MAX(CASE WHEN SN_STATUS IN ('COMP') THEN SN_STATUS ELSE '' END) AS SN_Status             --Comp should be 2 days i.e from the 13th to the 15th
> 	,DATEDIFF(day, MIN(CreatedDate), MAX(CompletedDate)) AS DaysToComplete
> 	--CUSTOMER IDENTIFIER COUNT  
> FROM #temp
> WHERE Identifier = '64074558782' GROUP BY Identifier, SN_Type;

Attached is an image of the results I am after

876×447 94.9 KB

6

I do not understand your data.
How can a CreatedDate be greater than a CompletedDate?

7

Not sure where you mean

I dont see where Created Date is bigger than Completed.

I have color coordinated my logic. Hope this makes a little more sense.

CODE2.JPG1689×368 108 KB