Skip to Content
0
Oct 15, 2019 at 04:03 PM

Case Statement to convert data into minute format in SQL Query

117 Views

I have the below Query which is pulling through the duration of time spent on a Service Call ticket. However the 'Duration' field spits out numbers that could be hours or minutes, the next field shows whether it is an hour or minute.

I need to add a case statement so that it knows to format the Duration field as number of minutes by refering to the DurType field.

------------------------------------------------------------------------------------------------------------

/*SELECT FROM [dbo].[OSCL] T0*/ declare @fdate as datetime /* WHERE */ set @fdate = /* T0.CreateDate */ '[%0]' /*SELECT FROM [dbo].[OSCL] T0*/ declare @tdate as datetime /* WHERE */ set @tdate = /* T0.CreateDate */ '[%1]' SELECT T0.[callID] , T0.[customer] , T0.[custmrName] , T0.[manufSN] as 'Serial No.' , T1.[Name] as 'Status' , T0.[createDate] , T0.[closeDate] , T0.[itemCode] , T0.[itemName] , T2.[Name] as 'Origin' , T3.[Name] as 'Call Type' , T4.[firstName]+ ' ' + T4.[lastName] as 'Technician' , T0.[U_Outcome] , T0.[U_Fault1] , T0.[U_Subfault1] , T0.[U_Fault2] , T0.[U_Subfault2] , T0.[U_EUFirstName] , T0.[U_EULastName] , t0.[U_CustRef] as 'MM/SFX Ref' , T0.[U_EvoBatch] as 'Evo Batch No.' ,T5.[Duration] AS 'Service Call Duration' ,T5.[DurType] FROM OSCL T0 LEFT OUTER JOIN OSCS T1 ON T0.status = T1.statusID LEFT OUTER JOIN OSCO T2 ON T0.origin = T2.originID LEFT OUTER JOIN OSCT T3 ON T0.callType = T3.callTypeID LEFT OUTER JOIN OHEM T4 ON T0.technician = T4.empID LEFT OUTER JOIN OCLG T5 ON T0.Duration = T5.Duration WHERE T0.createDate BETWEEN @fdate AND @tdate