1

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.

  • @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? – Marcello Miorelli Jun 22 '17 at 11:15
  • 1
    Oh no, that was not my intention. Apologies. – Marcello Miorelli Jun 22 '17 at 11:19
  • 2
    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. – Scott Hodgin Jun 22 '17 at 11:27
  • very good points. thanks for posting. @NelsonCasanova you won, let's just keep this simple! – Marcello Miorelli Jun 22 '17 at 11:46
2

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. – Marcello Miorelli Jun 22 '17 at 13:53
  • I'm glad to help. @marcellomiorelli – McNets Jun 22 '17 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.