none
Calculate days depending on hours

    Question

  • i have field with total hours (45:20) and our working hours is 7 hours per day and i need to calculate how many days will it be so we can calculate the salary, how can we do it in sql server query?
    Thursday, December 6, 2018 12:10 PM

Answers

  • Hi FarmerIT,

    Per your description, I am sorry that I could not understand what 'remaining hours and minutes' means clearly.

     

    Would you like this one?

    declare @t table
    (
    s varchar(10) 
    )
    insert @t
    values('45:20')
    
    SELECT cast(LEFT(s,CHARINDEX(':',s)-1)/7 as varchar(5))+'days '
    +cast(LEFT(s,CHARINDEX(':',s)-1)%7 as varchar(5))+'hours '
    +STUFF(s,1,CHARINDEX(':',s),'')+'minutes ' as result
    FROM @t
    /*
    result
    -----------------------------
    6days 3hours 20minutes 
    */


     

    Hope it can help you.

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by FarmerIT Friday, December 7, 2018 11:17 PM
    Friday, December 7, 2018 3:05 AM

All replies

  • 45 divide by 7?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 6, 2018 12:21 PM
    Answerer
  • see this illustration

    declare @t table
    (
    s varchar(10) 
    )
    insert @t
    values('45:20')
    
    SELECT CEILING((LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),''))/(7.0 * 60.0)) AS DaysWorked
    FROM @t
    
    /*
    Output
    ---------------------------------------------
    DaysWorked
    -----------------
    7
    
    
    */

    If decimal is allowed you can use this

    declare @t table
    (
    s varchar(10) 
    )
    insert @t
    values('45:20')
    
    SELECT CAST((LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),''))/(7.0 * 60.0) AS decimal(30,2)) AS DaysWorked
    FROM @t
    
    
    /*
    Output
    ------------------------------
    DaysWorked
    -----------------------
    6.48
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, December 6, 2018 12:38 PM
    Thursday, December 6, 2018 12:36 PM
  • i need to keep the remaining hours and minutes for the next month. how can i get that also. 
    Thursday, December 6, 2018 9:25 PM
  • Hi FarmerIT,

    Per your description, I am sorry that I could not understand what 'remaining hours and minutes' means clearly.

     

    Would you like this one?

    declare @t table
    (
    s varchar(10) 
    )
    insert @t
    values('45:20')
    
    SELECT cast(LEFT(s,CHARINDEX(':',s)-1)/7 as varchar(5))+'days '
    +cast(LEFT(s,CHARINDEX(':',s)-1)%7 as varchar(5))+'hours '
    +STUFF(s,1,CHARINDEX(':',s),'')+'minutes ' as result
    FROM @t
    /*
    result
    -----------------------------
    6days 3hours 20minutes 
    */


     

    Hope it can help you.

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by FarmerIT Friday, December 7, 2018 11:17 PM
    Friday, December 7, 2018 3:05 AM
  • thanks that's helped a lot,

    can i ask you another thing,

    if i want to add these hours and minutes to the new month calculation how can i do that for example

    33:20 + 3 hours + 20 minutes 

    Sunday, December 9, 2018 9:07 AM
  • i need to keep the remaining hours and minutes for the next month. how can i get that also. 

    like

    this

    declare @t table
    (
    s varchar(10) 
    )
    insert @t
    values('45:20')
    
    SELECT (LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),''))/(7 * 60) AS DaysWorked,
    FORMAT(DATEADD(minute,(LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),''))%(7 * 60),0),'hh:mm') AS Residual
    FROM @t
    
    /*
    Output
    -----------------------------------------------------------
    DaysWorked	Residual
    -----------------------------
    6	        03:20
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, December 9, 2018 10:56 AM
  • thanks that's helped a lot,

    can i ask you another thing,

    if i want to add these hours and minutes to the new month calculation how can i do that for example

    33:20 + 3 hours + 20 minutes 

    you need to do this as per below illustration

    declare @t table ( s varchar(10) ) insert @t values('33:20') declare @m varchar(10) = '3:20' SELECT CONCAT(((LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),'')) + (LEFT(@m,CHARINDEX(':',@m)-1) * 60 + STUFF(@m,1,CHARINDEX(':',@m),'')))/60,':',((LEFT(s,CHARINDEX(':',s)-1) * 60 + STUFF(s,1,CHARINDEX(':',s),'')) + (LEFT(@m,CHARINDEX(':',@m)-1) * 60 + STUFF(@m,1,CHARINDEX(':',@m),'')))%60) FROM @t


    /*
    Output

    ---------------
    36:40

    */



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, December 9, 2018 11:03 AM
  • declare @t table
    (
    s varchar(10) 
    )
    insert @t
    values('33:20')
    
    declare @m varchar(10) = '3:20'
    
    SELECT cast((cast(LEFT(s,CHARINDEX(':',s)-1) as int)+cast(LEFT(@m,CHARINDEX(':',@m)-1) as int))/7 as varchar(5))+'days '
    +cast((cast(LEFT(s,CHARINDEX(':',s)-1) as int)+cast(LEFT(@m,CHARINDEX(':',@m)-1) as int))%7 as varchar(5))+'hours '
    +cast((cast(STUFF(s,1,CHARINDEX(':',s),'') as int)+cast(STUFF(@m,1,CHARINDEX(':',@m),'') as int))as varchar(5))+'minutes ' as result
    FROM @t
    /*
    result
    ----------------------------------
    5days 1hours 40minutes 
    */

    Best  Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 10, 2018 7:57 AM