# Calculate and show DATETIME difference on PIVOTING query

I have the following `PIVOTING` query:

``````SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

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
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:

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`.

• @NelsonCasanova it would probably do the job, but what about something more nitty and elegant, that would possibly perform better and would be easier to maintain? Jun 22, 2017 at 11:15
• Oh no, that was not my intention. Apologies. Jun 22, 2017 at 11:19
• I'm with @NelsonCasanova - you've already spent time tweaking your PIVOT to return the correct answer. Why not just wrap that in a CTE (knowing exactly what comes out of the CTE) and then utilize the CTE while applying the DATEDIFF logic - I would think that's no more difficult to maintain than what you already have. If the current PIVOT is performing badly, you probably need to make sure it's as good as it gets before adding the DATEDIFF layer. PIVOTs are already 'interesting' enough without trying to add more complexity. Jun 22, 2017 at 11:27
• very good points. thanks for posting. @NelsonCasanova you won, let's just keep this simple! Jun 22, 2017 at 11:46

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

• spot on, thanks for the query and for the hard work of creating and populating the table `foo`. also the `dbfiddle` is helpful, I had forgotten about it. Jun 22, 2017 at 13:53
• I'm glad to help. @marcellomiorelli Jun 22, 2017 at 13:53