Skip to Content

Case Statement to convert data into minute format in SQL Query

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Oct 17, 2019 at 12:28 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.