0

I have the following Access query that needs turned into SQL -

TRANSFORM 
Sum([anaes_downtime]![ANAESENDTIME]-[anaes_downtime]![anaesstarttime])/24/60 AS Expr1

SELECT downtime_seq.THEATRE, downtime_seq.OPSESSION, downtime_seq.OPDATE
FROM ANAES_Downtime
INNER JOIN downtime_seq ON ANAES_Downtime.opnumber = downtime_seq.opnumber
WHERE (((ANAES_Downtime.ANAESSTARTTIME) <> 0 )
    AND ((ANAES_Downtime.ANAESENDTIME) <> 0 ))
GROUP BY downtime_seq.THEATRE, downtime_seq.OPSESSION, downtime_seq.OPDATE
PIVOT "d" & [opseq] IN ("d0","d1","d2","d3","d4","d5","d6","d7","d8","d9","d10","d11","d12","d13","d14","d15","d16","d17","d18","d19","d20");

I understand the basics of sql pivot but have been unable to convert this successfully. Any help?

Morpheus
  • 1,616
  • 1
  • 21
  • 31
bodeep
  • 1
  • 1

1 Answers1

0

I have found an answer to my question using the CASE statement -

SELECT ad.THEATRE, ad.OPSESSION, ad.OPDATE,
   SUM(CASE WHEN opseq = 0
        THEN ad.ANAESENDTIME - ad.anaesstarttime ELSE 0 END) AS 'd0',
   SUM(CASE WHEN opseq = 1
        THEN ad.ANAESENDTIME - ad.anaesstarttime ELSE 0 END) AS 'd1',
   SUM(CASE WHEN opseq = 2
        THEN ad.ANAESENDTIME - ad.anaesstarttime ELSE 0 END) AS 'd2',
   SUM(CASE WHEN opseq = 3
        THEN ad.ANAESENDTIME - ad.anaesstarttime ELSE 0 END) AS 'd3',
   SUM(CASE WHEN opseq = 4
        THEN ad.ANAESENDTIME - ad.anaesstarttime ELSE 0 END) AS 'd4',
   SUM(CASE WHEN opseq = 5
        THEN ad.ANAESENDTIME - ad.anaesstarttime ELSE 0 END) AS 'd5'...    

 ...FROM #ANAES_Downtime ad INNER JOIN #DowntimeSeq ds ON ad.opnumber = ds.opnumber

WHERE ad.ANAESSTARTTIME <> 0 AND  ad.ANAESENDTIME <> 0
GROUP BY ad.THEATRE, ad.OPSESSION, ad.OPDATE
ORDER BY ad.THEATRE, ad.OPSESSION, ad.OPDATE
bodeep
  • 1
  • 1