SQL Server – Calculating and Showing DATETIME Difference in PIVOT Query

pivotsql serversql-server-2016

I have the following PIVOTING query:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW

SELECT * 
FROM (select  jsc.applicationid
              ,[the date of change]=MIN(jsc.changedate)
              --,jsc.[oldStatus] 
              ,jsc.[newStatus]
              ,C1.countryName
              ,[Name] = ja.firstname + COALESCE(' ' + JA.LASTNAME, '')
              ,nationality=C2.countryName
              ,[Agency Name] = a.name
      from dbo.applicationStateChange jsc
      INNER JOIN dbo.application app 
            ON app.applicationID = jsc.applicationID
      INNER JOIN dbo.applicant ja
            ON ja.applicantID= app.applicantID
      INNER JOIN dbo.agency a
            ON a.agencyID = app.agencyID
      INNER JOIN dbo.country C1
            ON C1.countryCode = ja.countrycode
      INNER JOIN dbo.country C2
            ON C2.countryCode = ja.nationalitycode
      WHERE 1=1
      --  AND jsc.[oldStatus] = 'OLA'
      AND jsc.[newStatus] IN ('OLA', 'AWA', 'TBM')
      --AND ja.countrycode = 'GB'
      AND YEAR(jsc.changedate) >= 2010
      GROUP BY jsc.applicationid
               --,jsc.changedate
               --,jsc.[oldStatus] 
               ,jsc.[newStatus]
               ,C1.countryName
               ,ja.firstname
               ,JA.LASTNAME
               ,C2.countryName
               ,a.name
) RADHE 
PIVOT (MIN([the date of change]) for [newStatus] IN ([OLA], [AWA], [TBM]) ) as KRISHNA
WHERE 1=1
AND [OLA] IS NOT NULL
AND [AWA] IS NOT NULL
AND [TBM] IS NOT NULL
ORDER BY  applicationid

That gives me results like these:

enter image description here

Question:

How could I calculate X and Y on the above picture and display the result there?

X – should be the amount of time (ok it can be number of days to keep it simple) between AWA and OLA.

Y – should be the amount of time (ok it can be number of days to keep it simple) between TBM and AWA.

Best Answer

Let me know if this produce the desired result.

First I've set up an example on dbfiddle that returns unpivot data similar to the result of your question.

CREATE TABLE foo
(
  applicationId int, 
  countryName varchar(20),
  name varchar(20), 
  nationality varchar(20), 
  agency varchar(10), 
  newStatus varchar(10), 
  dateChange datetime
);

INSERT INTO foo VALUES
(1001, 'Mexico', 'Juan', 'Mexico', 'CIA', 'OLA', '20170101'),
(1001, 'Mexico', 'Juan', 'Mexico', 'CIA', 'AWA', '20170110'),
(1001, 'Mexico', 'Juan', 'Mexico', 'CIA', 'TMB', '20170105'),
(1002, 'Germany', 'Michael', 'Germany', 'FBI', 'OLA', '20170203'),
(1002, 'Germany', 'Michael', 'Germany', 'FBI', 'AWA', '20170207'),
(1002, 'Germany', 'Michael', 'Germany', 'FBI', 'TMB', '20170212'),
(1002, 'Rusia', 'Boris', 'Rusia', 'SNA', 'OLA', '20170301'),
(1002, 'Rusia', 'Boris', 'Rusia', 'SNA', 'AWA', '20170311'),
(1002, 'Rusia', 'Boris', 'Rusia', 'SNA', 'TMB', '20170305');

Then I enclosed your pivot query inside a CTE function, and finally it calculates datediff using data returned by pivot query.

WITH p AS
(
    SELECT *
    FROM   (SELECT *
            FROM   foo) SRC
    PIVOT (MIN(dateChange) for newStatus IN ([OLA], [AWA], [TMB])) pvt
)
SELECT applicationId, 
       countryName, 
       name, 
       nationality, 
       agency, 
       OLA, 
       DATEDIFF(DAY, [OLA], [AWA]) X,
       AWA,
       DATEDIFF(DAY, [AWA], [TMB]) Y,
       TMB
FROM P
ORDER BY applicationId;
GO

This is the final result:

applicationId | countryName | name    | nationality | agency | OLA                 |  X | AWA                 |  Y | TMB                
------------: | :---------- | :------ | :---------- | :----- | :------------------ | -: | :------------------ | -: | :------------------
         1001 | Mexico      | Juan    | Mexico      | CIA    | 01/01/2017 00:00:00 |  9 | 10/01/2017 00:00:00 | -5 | 05/01/2017 00:00:00
         1002 | Germany     | Michael | Germany     | FBI    | 03/02/2017 00:00:00 |  4 | 07/02/2017 00:00:00 |  5 | 12/02/2017 00:00:00
         1002 | Rusia       | Boris   | Rusia       | SNA    | 01/03/2017 00:00:00 | 10 | 11/03/2017 00:00:00 | -6 | 05/03/2017 00:00:00

dbfiddle here

Related Question