How to find the difference between two dates in same column in SQL

Question

0.00/5 (No votes)

See more:

Hi,

I have a following data as below.

CREATE TABLE #TaskTable
(TaskId INT, CategoryID INT, [Type] INT, MailBoxId INT, [Date] DateTime, StatusName VarChar(50), StatusId INT);
GO

INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-05', 'PA', 189);
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-07', 'IND', 199);

INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-18', 'PA', 189);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-19', 'IND', 199);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-19', 'WFI', 190);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-19', 'CP', 191);

INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-19', 'WFI', 190);
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-20', 'CP', 191);

INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-19', 'PA', 189);
INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-20', 'IND', 199);
INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-22', 'WFI', 190);

INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-21', 'CLD', 197);

INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-21', 'CLD', 197);

INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-25', 'CLD', 197);

INSERT INTO #TaskTable VALUES(11, 1, 5, 1, '2017-06-21', 'PA', 189);
INSERT INTO #TaskTable VALUES(11, 1, 5, 1, '2017-06-22', 'IND', 199);
INSERT INTO #TaskTable VALUES(11, 1, 5, 1, '2017-06-23', 'CLD', 197);

TaskId	CategoryId	TypeId	MailBoxId	StartTime	StatusName	StatusTypeId
3	1	5	1	2017-06-05	PA	189
3	1	5	1	2017-06-07	IND	199
8	1	8	1	2017-06-18	PA	189
8	1	8	1	2017-06-19	IND	199
8	1	8	1	2017-06-19	WFI	190
8	1	8	1	2017-06-19	CP	191
3	1	8	1	2017-06-19	WFI	190
3	1	8	1	2017-06-20	CP	191
10	1	8	1	2017-06-19	PA	189
10	1	8	1	2017-06-20	IND	199
10	1	8	1	2017-06-22	WFI	190
3	1	5	1	2017-06-21	CLD	197
8	1	8	1	2017-06-21	CLD	197
10	1	8	1	2017-06-25	CLD	197
11	1	5	1	2017-06-21	PA	189
11	1	5	1	2017-06-22	IND	199
11	1	5	1	2017-06-23	CLD	197

My requirement is to get data as below.

CategoryId	TypeId	MailBoxId	PA-IND	IND-CLD	IND-WFI	WFI-CLD
1	5	1	1	1.5	0	0
1	8	1	1	1.00	1	2

Here PA-IND,IND-CLD,IND-WFI,WFI-CLD is the average days taken for same type.
Is it achievable. Please help.

What I have tried:

Plotting data - [result i require] is it can be done by pivot.
I tried sample of pivot as below.

SQL

CREATE TABLE yt 
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);

INSERT INTO yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59)

select *
from 
(
  select store, week, xCount
  from yt
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

Below is the insert query for data into table.

CREATE TABLE #TaskTable
(TaskId INT, CategoryID INT, [Type] INT, MailBoxId INT, [Date] DateTime, StatusName VarChar(50), StatusId INT);
GO

INSERT INTO #TaskTable VALUES(3,    1,  5,  1,  '2017-06-05',   'PA',   189);
INSERT INTO #TaskTable VALUES(3,	1,	5,	1,	'2017-06-07',	'IND',	199);

INSERT INTO #TaskTable VALUES(8,	1,	8,	1,	'2017-06-18',	'PA',	189);
INSERT INTO #TaskTable VALUES(8,	1,	8,	1,	'2017-06-19',	'IND',	199);
INSERT INTO #TaskTable VALUES(8,	1,	8,	1,	'2017-06-19',	'WFI',	190);
INSERT INTO #TaskTable VALUES(8,	1,	8,	1,	'2017-06-19',	'CP',	191);

INSERT INTO #TaskTable VALUES(3,	1,	5,	1,	'2017-06-19',	'WFI',	190);
INSERT INTO #TaskTable VALUES(3,	1,	5,	1,	'2017-06-20',	'CP',	191);

INSERT INTO #TaskTable VALUES(10,	1,	8,	1,	'2017-06-19',	'PA',	189);
INSERT INTO #TaskTable VALUES(10,	1,	8,	1,	'2017-06-20',	'IND',	199);
INSERT INTO #TaskTable VALUES(10,	1,	8,	1,	'2017-06-22',	'WFI',	190);

INSERT INTO #TaskTable VALUES(3,	1,	5,	1,	'2017-06-21',	'CLD',	197);

INSERT INTO #TaskTable VALUES(8,	1,	8,	1,	'2017-06-21',	'CLD',	197);

INSERT INTO #TaskTable VALUES(10,	1,	8,	1,	'2017-06-25',	'CLD',	197);

INSERT INTO #TaskTable VALUES(11,	1,	5,	1,	'2017-06-21',	'PA',	189);
INSERT INTO #TaskTable VALUES(11,	1,	5,	1,	'2017-06-22',	'IND',	199);
INSERT INTO #TaskTable VALUES(11,	1,	5,	1,	'2017-06-23',	'CLD',	197);

Thanks in advance

Posted 7-Aug-17 20:29pm

Member 8478234

Updated 9-Aug-17 7:12am

v8

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2017-08-09T07:12:00

Assuming I've understood your question correctly; that you're using a relatively recent version of Microsoft SQL Server; and that the set of status transitions is fixed; something like this should work:

SQL

WITH cteTasks As
(
    SELECT
        CategoryID,
        Type,
        MailBoxId,
        StatusName As FromStatus,
        LEAD(StatusName) OVER (PARTITION BY CategoryID, Type, MailBoxId ORDER BY [Date]) As ToStatus,
        [Date] As StartDate,
        LEAD([Date]) OVER (PARTITION BY CategoryID, Type, MailBoxId ORDER BY [Date]) As EndDate
    FROM
        #TaskTable
),
cteTime As
(
    SELECT
        CategoryID,
        Type,
        MailBoxId,
        FromStatus + '-' + ToStatus As Status,
        DateDiff(minute, StartDate, EndDate) / 1440. As DaysTaken
    FROM
        cteTasks
    WHERE
        ToStatus Is Not Null
)
SELECT
    CategoryID,
    Type,
    MailBoxId,
    [PA-IND], 
    [IND-CLD], 
    [IND-WFI], 
    [WFI-CLD], 
    [CLD-PA], 
    [CLD-WFI], 
    [WFI-CP], 
    [CP-PA], 
    [CP-CLD]
FROM
    cteTime As T
    PIVOT
    (
        SUM(DaysTaken)
        FOR Status In ([PA-IND], [IND-CLD], [IND-WFI], [WFI-CLD], [CLD-PA], [CLD-WFI], [WFI-CP], [CP-PA], [CP-CLD])
    ) As P
;

Using PIVOT and UNPIVOT | Microsoft Docs[^]
LEAD (Transact-SQL) | Microsoft Docs[^]

Using your sample data, the output is:

CategoryID    Type    MailBoxId    PA-IND    IND-CLD    IND-WFI    WFI-CLD    CLD-PA    CLD-WFI    WFI-CP    CP-PA    CP-CLD
-----------------------------------------------------------------------------------------------------------------------------
1             5    	  1            3         1          12         NULL       0         NULL       1         NULL     1
1             8    	  1            2         1           0         3          NULL      1          0         0        NULL

Demo[^]