CODEWITHSUNDEEP
sql-serverwpfc#-4.0
lsieting
lsieting

Reputation: 95

Determine remaining minutes in SQL query

I am a little stumped on this. I can get a number of hours or a total number of minutes, but I need both hours and minutes (if job took more than 60 minutes). Start time and end time (datetime datatype) are stored in fields in tuples in the database.

I need to get hours (if over 60 mins) and remaining minutes. I have the following calculations in my sql query. I know the giving me a total minutes, I just need to make any left over after removing hours.

If the start time is 11:15am and the end time is 12:25pm, I need 1 hour 10 minutes.

                sqlQuery = "select distinct(job_#1) as 'Job Number', " +
                "client_name as 'Client Name', " +
                "Convert(varchar,min(date),101) as 'Start Date', " +
                "convert(varchar, max(date), 101) as 'End Date', " +
                "FORMAT(sum(datediff(mi, START_TIME,END_TIME)%(60*24)/60),'#','en-us' ) as 'Hours', " + // calculate the total time taken on the job - formatted numeric
                "FORMAT(sum(datediff(mi, START_TIME,END_TIME)%(60*24)%60),'#','en-us' ) as 'Minutes', " + // calculate the total time taken on the job - formatted numeric
                "FORMAT(sum((datediff(MINUTE,start_time,end_time))/60.00)*25.00, 'C', 'en-us') as 'Total Labor' " + // multiple the job time by a $$ per hour rate - formatted as currency
                "from " + ssqltable + " " +                                      // table from which data is pulled 
                "where job_#1 IS NOT NULL " +                        // find all jobs that have a job number leaving out blank job numbers
                "and date>='" + myDate + "' " +                      // filled in when selected or passes null and shows all data
                "group by job_#1, Client_Name " +                    // aggregate grouping 
                "order by job_#1;"; // column(s) to order the data

Here is an example of my output.. 

237452  AADC        05/18/2017  05/18/2017  1   **208** $111.67
237353  Wolverine   05/18/2017  05/18/2017      **110** $45.83
237492  Beeman      05/11/2017  05/16/2017      **74**  $30.83

Upvotes: 1

Views: 1452

Answers (4)

SqlZim
SqlZim

Reputation: 38063

You can use datediff() with hour for hours (if you were not summing), and minute for minutes, and divide or modulo as needed:

select 
    [Job Number] = [job_#1]
  , [Client Name]= client_name
  , [Start Date] = convert(char(10),min(date),101)
  , [End Date]   = convert(char(10),max(date),101)
  , Hours        = sum(datediff(minute,Start_Time,End_Time))/60
  , Minutes      = sum(datediff(minute,Start_Time,End_Time))%60
  , [Total Labor]= (sum(datediff(minute,Start_Time,End_Time))/60.00) * 25.00
from tbl
where job_#1 is not null
  and [date] >= @date_parameter
group by [job_#1], client_name
order by [job_#1]

Notes:

Upvotes: 2

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

I think simple divide by 60 is enough as below:

        sum( datediff(mi,START_TIME,END_TIME)/60 ) as [Hours]
        sum( datediff(mi,START_TIME,END_TIME)%60 ) as [Minutes]

Format is not high performant. For your scenario you can avoid

Upvotes: 0

John Pasquet
John Pasquet

Reputation: 1842

The DIV (/) and MOD/Remainder (%) functions will do the trick if you have the Total Minutes.

DECLARE @TotalMinutes int
SET @TotalMinutes = 70

SELECT @TotalMinutes / 60 AS Hours, 
       @TotalMinutes % 60 AS Minutes

This returns the following results:

Total Minutes    Hours    Minutes
     70            1         10
    121            2          1

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17146

Your code should be like below. Please note the changes in the order of parenthesis for hours and minutes. First SUM all minutes and then divide or modulo by 60 (integer) to get hours and minutes.

      sqlQuery = "select distinct(job_#1) as 'Job Number', " +
            "client_name as 'Client Name', " +
            "Convert(varchar,min(date),101) as 'Start Date', " +
            "convert(varchar, max(date), 101) as 'End Date', " +
            "FORMAT(sum(datediff(mi, START_TIME,END_TIME)/60),'#','en-us' ) as 'Hours', " + // calculate the total time taken on the job - formatted numeric
            "FORMAT(sum(datediff(mi, START_TIME,END_TIME))%60),'#','en-us' ) as 'Minutes', " + // calculate the total time taken on the job - formatted numeric
            "FORMAT(sum(datediff(MINUTE,start_time,end_time)/60.00)*25.00, 'C', 'en-us') as 'Total Labor' " + // multiple the job time by a $$ per hour rate - formatted as currency
            "from " + ssqltable + " " +                                      // table from which data is pulled 
            "where job_#1 IS NOT NULL " +                        // find all jobs that have a job number leaving out blank job numbers
            "and date>='" + myDate + "' " +                      // filled in when selected or passes null and shows all data
            "group by job_#1, Client_Name " +                    // aggregate grouping 
            "order by job_#1;"; // column(s) to order the data

Upvotes: 0

Related Questions