MAX MIN OR SUB-QUERY

Question

MAX MIN OR SUB-QUERY

Danii_Pink

Right there with Babe

Points: 778
More actions
June 30, 2017 at 12:07 am

#402234

Hi
I want to add columns to count the number of days until each SN_STATUS (so a separate column for the status and days until that status)
Please find below the data set and the query I tried to write but adding the consecutive columns gives me trouble.
I am trying to achieve unique rows.
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_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;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

53 people have looked at your post and none have replied.
It's very difficult to determine exactly what you want as your result, significant language problem here.
Can you please post your expected result set? It will help.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Danii_Pink Right there with Babe Points: 778 More actions · Answer 2

Hi Chris
Sorry, i could have explained better
I have attached a file below.
With the table below I am able to see how long the ID took to get to each stage so from Not completed to Rejected to finally Completed.
Please see results below, let me know if this makes sense and thank you, for pointing it out and asking for further information.
Your help is greatly appreciated.

Danii_Pink Right there with Babe Points: 778 More actions · Answer 3

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