locked
How to find NetTime in 60 mins RRS feed

  • Question

  • Hi

    I have  following column in table  <tbl_Woked>

    Mon_Hrs = 7.30  ( 7 and half hrs)

    Tue_Hrs = 7.30

    Wed_hrs = 7.15

    Thu_Hrs = 7.00

    Deduct_hrs = 3.15 Hrs ( 3 Hrs  and  15 mins)

    IsDeducted = 'Y'

    Extra_Hrs = 1.45 ( One Hrs and 45 mins)

    I would like to get NetHrs = (Mon_Hrs +Tue_Hrs +Wed_hrs +Thu_Hrs + Extra_Hrs ) - Deduct_hrs (only if  IsDeducted = 'Y)

    If isdeducted = 'N' then nethrs should be 31 Hrs,  else it should be 27.45  Hrs

    Please help to generate an sql for the above.

    Regards

    Pol


    polachan

    Wednesday, December 2, 2015 12:54 PM

Answers

  • Create TABLE  tbl_Woked (Mon_Hrs decimal(6,2), 
    Tue_Hrs  decimal(6,2), 
    Wed_hrs  decimal(6,2), 
    Thu_Hrs  decimal(6,2),  
    Deduct_hrs decimal(6,2), 
    IsDeducted Char(1)
    ,Extra_Hrs  decimal(6,2), )
    
    --Insert into tbl_Woked ( Mon_Hrs,Tue_Hrs,Wed_hrs,Thu_Hrs,Deduct_hrs,IsDeducted, Extra_Hrs) Values(7.30,7.30,7.15,7.00,3.15,'Y',1.45) 
    
    Insert into tbl_Woked ( Mon_Hrs,Tue_Hrs ) Values(1.55,1.15)    
    
    select  
    parsename( 
    ( parsename(Mon_Hrs,2) +parsename(Mon_Hrs,1)/60.0
    +parsename(Tue_Hrs,2) +parsename(Tue_Hrs,1)/60.0) ,2) +'.' 
    + 
    Cast(Cast(Round(((Cast((parsename( parsename(Mon_Hrs,2) +parsename(Mon_Hrs,1)/60.0
    +parsename(Tue_Hrs,2) +parsename(Tue_Hrs,1)/60.0 ,1) ) as decimal(10,2)) /1000000 *60.0  )),0) as int) as varchar(10))
    
    from tbl_Woked
     --3.10
    drop table   tbl_Woked

    • Proposed as answer by Eric__Zhang Thursday, December 3, 2015 6:18 AM
    • Marked as answer by Eric__Zhang Wednesday, December 16, 2015 9:09 AM
    Wednesday, December 2, 2015 5:05 PM

All replies

  • Just use 

    select Mon_Hrs +Tue_Hrs +Wed_hrs +Thu_Hrs + Extra_Hrs ) - CASE when IsDeducted = 'Y' then Deduct_hrs else 0 end as NetHrs from myTable


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, December 2, 2015 1:04 PM
  • No it will not work. It should be needed to convert mins into seconds and also hours into secones and then have to calculate and do it back again.

    Please anybody help me with proper sql

    Pol


    polachan

    Wednesday, December 2, 2015 2:07 PM
  • Please provide DDL, example data and a required output. 

    When you provide this, please use something like:

    DECLARE @table TABLE (col1 INT, col2 DATE, col3 MONEY)
    INSERT INTO @table (col1, col2, col3) VALUES
    (1, '2015-11-21', 3.21),
    (2, '2015-12-21', 1.23)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles. Help me change the face of men’s health this Movember by making a donation to my moustache: http://mobro.co/reeb

    Wednesday, December 2, 2015 2:36 PM
  • Create TABLE  tbl_Woked (Mon_Hrs decimal(6,2), 
    Tue_Hrs  decimal(6,2), 
    Wed_hrs  decimal(6,2), 
    Thu_Hrs  decimal(6,2),  
    Deduct_hrs decimal(6,2), 
    IsDeducted Char(1)
    ,Extra_Hrs  decimal(6,2), )
    
    Insert into tbl_Woked ( Mon_Hrs,Tue_Hrs,Wed_hrs,Thu_Hrs,Deduct_hrs,IsDeducted, Extra_Hrs) Values(7.30,7.30,7.15,7.00,3.15,'Y',1.45)   
    
    select  
    parsename( 
    ( parsename(Mon_Hrs,2) +parsename(Mon_Hrs,1)/60.0
    +parsename(Tue_Hrs,2) +parsename(Tue_Hrs,1)/60.0
    +parsename(Wed_hrs,2) +parsename(Wed_hrs,1)/60.0
    +parsename(Thu_Hrs,2) +parsename(Thu_Hrs,1)/60.0
    +parsename(Extra_Hrs,2) +parsename(Extra_Hrs,1)/60.0
     
    -Case when IsDeducted='Y' Then (parsename(Deduct_hrs,2) +parsename(Deduct_hrs,1)/60.0) else 0 END ) ,2) +'.' 
    +
     Cast(Cast(parsename( 
    ( parsename(Mon_Hrs,2) +parsename(Mon_Hrs,1)/60.0
    +parsename(Tue_Hrs,2) +parsename(Tue_Hrs,1)/60.0
    +parsename(Wed_hrs,2) +parsename(Wed_hrs,1)/60.0
    +parsename(Thu_Hrs,2) +parsename(Thu_Hrs,1)/60.0
    +parsename(Extra_Hrs,2) +parsename(Extra_Hrs,1)/60.0
     
    -Case when IsDeducted='Y' Then (parsename(Deduct_hrs,2) +parsename(Deduct_hrs,1)/60.0) else 0 END ) ,1) /10000*0.6 as int) as varchar(2))
    
    from tbl_Woked
     
    drop table   tbl_Woked
    

    Wednesday, December 2, 2015 2:58 PM
  • table name : tbl_Worked

    All columns are decimal except IsDeducted (Boolean)


    polachan

    Wednesday, December 2, 2015 2:59 PM
  • Many Thanks your reply .  Your sql converting .15 into .25 mins.  

    I wan to calculate  same value  in the column  and should be calculated by converting into second  integer part and decimal part boh after calculation  reverse back it .


    polachan

    Wednesday, December 2, 2015 3:09 PM
  • I want the following formula in excel want to convert into sqlserver

    =INT((60*(INT(A1)+INT(D1))+100*(MOD(A1,1)+MOD(D1,1)))/60)+MOD(60*(INT(A1)+INT(D1))+100*(MOD(A1,1)+MOD(D1,1)),60)/100

    A1, D1 is tablecolumn with datatype is decimal. How can I bring the above equation into sql

    Pol


    polachan

    Wednesday, December 2, 2015 3:14 PM
  • DECLARE 
    	@Mon_Hrs DECIMAL(5, 2),
    	@Tue_Hrs DECIMAL(5, 2),
    	@Wed_Hrs DECIMAL(5, 2),
    	@Thu_Hrs DECIMAL(5, 2),
    	@Extra_Hrs DECIMAL(5, 2),
    	@Deduct_hrs DECIMAL(5, 2),
    	@IsDeducted BIT
    SET @Mon_Hrs = 7.30
    SET @Tue_Hrs = 7.30
    SET @Wed_Hrs = 7.15
    SET @Thu_Hrs = 7.00
    SET @Extra_Hrs = 1.45
    SET @Deduct_hrs = 3.15
    SET @IsDeducted = 1
    
    SELECT (@Mon_Hrs + @Tue_Hrs + @Wed_Hrs + @Thu_Hrs + @Extra_Hrs + CASE WHEN @IsDeducted = 0 THEN @Deduct_hrs ELSE 0 END)


    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 2, 2015 3:47 PM
  • Many Thanks your reply .  Your sql converting .15 into .25 mins.  

    I wan to calculate  same value  in the column  and should be calculated by converting into second  integer part and decimal part boh after calculation  reverse back it .


    polachan

    The 0.15 in your data is converted to 0.25 hour not mins as you think.

    The calculation should be correct. It is based on hours and minutes (not seconds).

    Wednesday, December 2, 2015 4:14 PM
  • Many thanks for your effort.

    I am looking for the solution to get back it as Hours and Mins . Please check my formula in excel

    =INT((60*(INT(A1)+INT(D1))+100*(MOD(A1,1)+MOD(D1,1)))/60)+MOD(60*(INT(A1)+INT(D1))+100*(MOD(A1,1)+MOD(D1,1)),60)/100

    A1 is decimal, D1 also decimal. If I calculate the following value

    (A1) 1.55 + (D1) 1.15 =  time is 3.10. I want the same equation in sqlserver. But When I apply your formula , it will not get  the timeas 3.10

    Many Thanks

    Pol


    polachan

    Wednesday, December 2, 2015 4:32 PM
  • Create TABLE  tbl_Woked (Mon_Hrs decimal(6,2), 
    Tue_Hrs  decimal(6,2), 
    Wed_hrs  decimal(6,2), 
    Thu_Hrs  decimal(6,2),  
    Deduct_hrs decimal(6,2), 
    IsDeducted Char(1)
    ,Extra_Hrs  decimal(6,2), )
    
    --Insert into tbl_Woked ( Mon_Hrs,Tue_Hrs,Wed_hrs,Thu_Hrs,Deduct_hrs,IsDeducted, Extra_Hrs) Values(7.30,7.30,7.15,7.00,3.15,'Y',1.45) 
    
    Insert into tbl_Woked ( Mon_Hrs,Tue_Hrs ) Values(1.55,1.15)    
    
    select  
    parsename( 
    ( parsename(Mon_Hrs,2) +parsename(Mon_Hrs,1)/60.0
    +parsename(Tue_Hrs,2) +parsename(Tue_Hrs,1)/60.0) ,2) +'.' 
    + 
    Cast(Cast(Round(((Cast((parsename( parsename(Mon_Hrs,2) +parsename(Mon_Hrs,1)/60.0
    +parsename(Tue_Hrs,2) +parsename(Tue_Hrs,1)/60.0 ,1) ) as decimal(10,2)) /1000000 *60.0  )),0) as int) as varchar(10))
    
    from tbl_Woked
     --3.10
    drop table   tbl_Woked

    • Proposed as answer by Eric__Zhang Thursday, December 3, 2015 6:18 AM
    • Marked as answer by Eric__Zhang Wednesday, December 16, 2015 9:09 AM
    Wednesday, December 2, 2015 5:05 PM
  • Why not - I don't see a need for back and forth conversion. It should work as is provided all information is in hours (with decimals).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, December 2, 2015 6:33 PM
  • DECLARE 
    	@Mon_Hrs DECIMAL(5, 2),
    	@Tue_Hrs DECIMAL(5, 2),
    	@Wed_Hrs DECIMAL(5, 2),
    	@Thu_Hrs DECIMAL(5, 2),
    	@Extra_Hrs DECIMAL(5, 2),
    	@Deduct_hrs DECIMAL(5, 2),
    	@IsDeducted BIT
    SET @Mon_Hrs = 7.30
    SET @Tue_Hrs = 7.30
    SET @Wed_Hrs = 7.15
    SET @Thu_Hrs = 7.00
    SET @Extra_Hrs = 1.45
    SET @Deduct_hrs = 3.15
    SET @IsDeducted = 1
    
    SELECT 
    (@Mon_Hrs + @Tue_Hrs + @Wed_Hrs + @Thu_Hrs + @Extra_Hrs + CASE WHEN @IsDeducted = 0 THEN @Deduct_hrs ELSE 0 END) AS 'TotalHrs',
    CAST((@Mon_Hrs + @Tue_Hrs + @Wed_Hrs + @Thu_Hrs + @Extra_Hrs + CASE WHEN @IsDeducted = 0 THEN @Deduct_hrs ELSE 0 END) AS INT) AS 'Hrs',
    CAST(((@Mon_Hrs + @Tue_Hrs + @Wed_Hrs + @Thu_Hrs + @Extra_Hrs + CASE WHEN @IsDeducted = 0 THEN @Deduct_hrs ELSE 0 END) - CAST((@Mon_Hrs + @Tue_Hrs + @Wed_Hrs + @Thu_Hrs + @Extra_Hrs + CASE WHEN @IsDeducted = 0 THEN @Deduct_hrs ELSE 0 END) AS INT)) * 60 AS INT) AS 'Mins'


    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 2, 2015 7:43 PM