# How to find NetTime in 60 mins

• ### 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

Regards

Pol

polachan

Wednesday, December 2, 2015 12:54 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 Thursday, December 3, 2015 6:18 AM
• Marked as answer by 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

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

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 Thursday, December 3, 2015 6:18 AM
• Marked as answer by 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