cancel
Showing results for 
Search instead for 
Did you mean: 

Case Statement to convert data into minute format in SQL Query

0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

Johan_H
Active Contributor
0 Kudos

Hi Robert,

As the DurType field is reasonably descriptive, perhaps this simple solution can work for you:

SELECT CAST(CAST(T5.[Duration] AS MONEY) AS NVARCHAR) + ' ' + T5.DurType

Otherwise a CASE statement would go something like this:

CAST(CAST(T5.[Duration] AS MONEY) AS NVARCHAR) + ' ' + CASE WHEN T5.DurType = 'M' THEN 'minutes' WHEN T5.DurType = 'H' THEN 'hours' ELSE 'mayday' END

Regards,

Johan