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
