CODEWITHSUNDEEP
sqlsql-serverdatabase
Murylo Batista
Murylo Batista

Reputation: 19

Multiply TIME in SQL Server

I have this query below, basically I'm trying to subtract 2 dates and get the hours.

However, I need the subtracted time to be multiplied by the number of cleaners

SELECT
    CONVERT(TIME, ClientBooking.TimeEnd - ClientBooking.TimeStart) AS HoursWorked2,
    ClientBooking.NumberOfCleaners AS NumberOfCleaners,
    ClientBooking.TimeStart,
    ClientBooking.TimeEnd,
    ClientBooking.ClientID,
    ((((ClientInfo.FirstName + N' ') +
        ClientInfo.LastName) + N'  ') +
        ClientInfo.Company) AS ClientName,
    ((((ClientInfo.Address + N' - ') +
        ClientInfo.City) + N' - ') +
        ClientInfo.ZipCode) AS Address,
    ((ClientInfo.PhoneNumber + N'  ') +
      ClientInfo.EmailAddress) AS Contact,
    (ClientBooking.HourlyRate / 60) AS MinRate,
    (DATEDIFF(MINUTE,ClientBooking.TimeStart,ClientBooking.TimeEnd) * ClientBooking.NumberOfCleaners) AS Quantity,
    ClientBooking.HourlyRate,
    DATEDIFF(HOUR, ClientBooking.TimeStart, ClientBooking.TimeEnd) AS HoursWorked
FROM 
    (dbo.ClientBooking ClientBooking
INNER JOIN 
    dbo.ClientInfo ClientInfo ON (ClientInfo.ClientID = ClientBooking.ClientID))

Basically, I need to multiply the result of this:

 CONVERT(TIME,"ClientBooking"."TimeEnd" - "ClientBooking"."TimeStart" )

Upvotes: 0

Views: 2468

Answers (3)

Helgato
Helgato

Reputation: 151

How About using this: 

    Select 
convert(time,DATEADD(MINUTE, ( convert(float,(DATEDIFF(minute, ClientBooking.TimeStart, ClientBooking.TimeEnd) * ClientBooking.NumberOfCleaners))/60), ''))
FROM 
    (dbo.ClientBooking ClientBooking
INNER JOIN 
    dbo.ClientInfo ClientInfo ON (ClientInfo.ClientID = ClientBooking.ClientID))

Sorry if i have missed a parenthesis !!

Upvotes: 1

Dan Stef
Dan Stef

Reputation: 773

If I understand you correctly this could help you:

declare @start datetime = '2018-11-02 07:00:00'
declare @end datetime = '2018-11-02 08:03:00'
declare @diff int

Select @diff = DATEDIFF(minute,@start,@end)

Select case 
    when @diff < 60 then concat('00:', right('0' + convert(varchar,@diff), 2))
    when @diff >= 60 and @diff < 120 then '01:' + right('0' + convert(varchar,@diff - 60), 2)
    when @diff >= 120 and @diff < 180 then '02:' + right('0' + convert(varchar,@diff - 120), 2)
    when @diff >= 180 and @diff < 240 then '03:' + right('0' + convert(varchar,@diff - 180), 2)
end

Of course you would need to add the following hours as well.

I've splitted everything up, so it is easier to understand. But you should be able to write it in one line and without variables as well

Hope this helps.

Upvotes: 0

Helgato
Helgato

Reputation: 151

You can use DATEDIFF() function.. Something like:

DATEDIFF(hour, ClientBooking.TimeStart, ClientBooking.TimeEnd) * ClientBooking.NumberOfCleaners

as your desired column!

Upvotes: 0

Related Questions