Adding hh:mm from 3 columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding hh:mm from 3 columns

Author

Topic

kiddoOnSQL
Starting Member

16 Posts

Posted - 2012-06-25 : 21:24:42

Hi there

I have a table with values like this:

EmpID FromDate NormalHours OTHours PHOLHours
-----------------------------------------------------
E001 04/06/2012 1.3 3.4 0
E001 05/06/2012 5.3 0 0
E001 05/06/2012 2.0 0 0
E001 06/06/2012 0 0 7.35

The NormalHours, OTHours and PholHours, are actual time (hh:mm) worked even though the client sends them through with decimals. So for e.g. 1.3 is actually 01:30, 7.35 is 07:35

What I want to do is for a given day, add NormalHours, OTHours and PholHours, and then evaluate if that total for the day is greater than 4 hours or not. If greater than 4 hours then I need to return a certain value e.g. 1 else return 0.

I have a function to add the hh:mm (see below), but what I am not able to do is add all those columns values for a given day, especially as for some days there might be more than 1 record in the table.

Can someone please help? Thanks in advance.

--------function I use to add hh:mm-------------

CAST
(
(SUM (datepart(hh, convert (varchar, Replace(NormalHours, '.',':'), 108))) +
(sum(datepart(mi, convert (varchar, Replace(NormalHours,'.',':'), 108)))/60) ) AS VARCHAR(10)
)
+ ':' +
CAST
(
sum(datepart(mi, convert (varchar,REPLACE(NormalHours, '.',':'), 108))) - 60 * (sum(datepart(mi, convert (varchar, Replace(NormalHours,'.',':'), 108)))/60)
as VARCHAR(10))

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-26 : 01:01:24

Ok this is a LOT of steps which is exactly the reason it is a better idea to store in a valid date/time data type if you have the ability to do so. I used a different method to get the time portion as we ultimately want to get to a Time data type. I wrap up the conversions in a subquery just to make the math easier to read.

DECLARE @table table (empid char(4), fromdate date, normalhours float, othours float, pholhours float)
INSERT INTO @table (empid, fromdate, normalhours, othours, pholhours)
values
('E001', '04/06/2012', 1.3, 3.4, 0),
('E001', '05/06/2012', 5.3, 0, 0),
('E001', '05/06/2012', 2.0, 0, 0),
('E001', '06/06/2012', 0, 0, 7.35)

SELECT CONVERT(TIME, normalhours+othours+pholhours),
--Added following column to get your test
CASE WHEN DATEPART(HH, CONVERT(TIME, normalhours+othours+pholhours))>4 then 1 ELSE 0 END as YourTest
FROM (

SELECT
CONVERT(DATETIME,
REPLACE(
CASE WHEN CHARINDEX('.', normalhours)=0 THEN CONVERT(VARCHAR(2), normalhours)+'.00'
WHEN CHARINDEX('.', normalhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),normalhours)))=2 THEN CONVERT(VARCHAR(4), normalhours)+'0'
WHEN CHARINDEX('.', normalhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),normalhours)))=3 THEN CONVERT(VARCHAR(4), normalhours) ELSE '0.00' END,'.',':')) as normalhours,
CONVERT(DATETIME,
REPLACE(
CASE WHEN CHARINDEX('.', othours)=0 THEN CONVERT(VARCHAR(2), othours)+'.00'
WHEN CHARINDEX('.', othours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),othours)))=2 THEN CONVERT(VARCHAR(4), othours)+'0'
WHEN CHARINDEX('.', othours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),othours)))=3 THEN CONVERT(VARCHAR(4), othours) ELSE '0.00' END,'.',':')) as othours,
CONVERT(DATETIME,
REPLACE(
CASE WHEN CHARINDEX('.', pholhours)=0 THEN CONVERT(VARCHAR(2), pholhours)+'.00'
WHEN CHARINDEX('.', pholhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),pholhours)))=2 THEN CONVERT(VARCHAR(4), pholhours)+'0'
WHEN CHARINDEX('.', pholhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),pholhours)))=3 THEN CONVERT(VARCHAR(4), pholhours) ELSE '0.00' END,'.',':')) as pholhours

FROM @table) A

kiddoOnSQL
Starting Member

16 Posts

Posted - 2012-06-26 : 01:46:21

Thanks for that.

One thing though - that evaluation of >4 has to be for the total for a given day.
So for e.g., if you change NormalHours of '5.3' for the entry dated '05/06/2012' to something like 3.0, it should return a 1 for date 05/06/2012. But it doesn't.
How do I get it to total and evaluate Mytest for a day?

Thanks again

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-26 : 02:04:46

Try like this:

declare @table table (empid char(4), fromdate date, normalhours float, othours float, pholhours float)
insert into @table (empid, fromdate, normalhours, othours, pholhours)
values
('E001', '04/06/2012', 1.3, 3.4, 0),
('E001', '05/06/2012', 5.3, 0, 0),
('E001', '05/06/2012', 2.0, 0, 0),
('E001', '06/06/2012', 0, 0, 7.35)

SELECT fromdate,

CASE WHEN SUM(DATEPART(HH, CONVERT(TIME, normalhours+othours+pholhours)))>4 then 1 ELSE 0 END as YourTest, SUM(CAST(REPLACE(CONVERT(CHAR(5), CONVERT(TIME, normalhours+othours+pholhours),108),':','.') AS FLOAT)) as Total
FROM (

SELECT Fromdate,
CONVERT(DATETIME,
REPLACE(
CASE WHEN CHARINDEX('.', normalhours)=0 THEN CONVERT(VARCHAR(2), normalhours)+'.00'
WHEN CHARINDEX('.', normalhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),normalhours)))=2 THEN CONVERT(VARCHAR(4), normalhours)+'0'
WHEN CHARINDEX('.', normalhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),normalhours)))=3 THEN CONVERT(VARCHAR(4), normalhours) ELSE '0.00' END,'.',':')) as normalhours,
CONVERT(DATETIME,
REPLACE(
CASE WHEN CHARINDEX('.', othours)=0 THEN CONVERT(VARCHAR(2), othours)+'.00'
WHEN CHARINDEX('.', othours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),othours)))=2 THEN CONVERT(VARCHAR(4), othours)+'0'
WHEN CHARINDEX('.', othours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),othours)))=3 THEN CONVERT(VARCHAR(4), othours) ELSE '0.00' END,'.',':')) as othours,
CONVERT(DATETIME,
REPLACE(
CASE WHEN CHARINDEX('.', pholhours)=0 THEN CONVERT(VARCHAR(2), pholhours)+'.00'
WHEN CHARINDEX('.', pholhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),pholhours)))=2 THEN CONVERT(VARCHAR(4), pholhours)+'0'
WHEN CHARINDEX('.', pholhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),pholhours)))=3 THEN CONVERT(VARCHAR(4), pholhours) ELSE '0.00' END,'.',':')) as pholhours

FROM @table) A

Group by fromdate

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-26 : 02:09:59

And if you need by empid:

declare @table table (empid char(4), fromdate date, normalhours float, othours float, pholhours float)
insert into @table (empid, fromdate, normalhours, othours, pholhours)
values
('E001', '04/06/2012', 1.3, 3.4, 0),
('E001', '05/06/2012', 5.3, 0, 0),
('E001', '05/06/2012', 2.0, 0, 0),
('E001', '06/06/2012', 0, 0, 7.35)

SELECT fromdate, empid,

CASE WHEN SUM(DATEPART(HH, CONVERT(TIME, normalhours+othours+pholhours)))>4 then 1 ELSE 0 END as YourTest, SUM(CAST(REPLACE(CONVERT(CHAR(5), CONVERT(TIME, normalhours+othours+pholhours),108),':','.') AS FLOAT)) as Total
FROM (

SELECT Fromdate,empid,
CONVERT(DATETIME,
REPLACE(
CASE WHEN CHARINDEX('.', normalhours)=0 THEN CONVERT(VARCHAR(2), normalhours)+'.00'
WHEN CHARINDEX('.', normalhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),normalhours)))=2 THEN CONVERT(VARCHAR(4), normalhours)+'0'
WHEN CHARINDEX('.', normalhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),normalhours)))=3 THEN CONVERT(VARCHAR(4), normalhours) ELSE '0.00' END,'.',':')) as normalhours,
CONVERT(DATETIME,
REPLACE(
CASE WHEN CHARINDEX('.', othours)=0 THEN CONVERT(VARCHAR(2), othours)+'.00'
WHEN CHARINDEX('.', othours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),othours)))=2 THEN CONVERT(VARCHAR(4), othours)+'0'
WHEN CHARINDEX('.', othours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),othours)))=3 THEN CONVERT(VARCHAR(4), othours) ELSE '0.00' END,'.',':')) as othours,
CONVERT(DATETIME,
REPLACE(
CASE WHEN CHARINDEX('.', pholhours)=0 THEN CONVERT(VARCHAR(2), pholhours)+'.00'
WHEN CHARINDEX('.', pholhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),pholhours)))=2 THEN CONVERT(VARCHAR(4), pholhours)+'0'
WHEN CHARINDEX('.', pholhours)>0 AND CHARINDEX('.',REVERSE(CONVERT(VARCHAR(4),pholhours)))=3 THEN CONVERT(VARCHAR(4), pholhours) ELSE '0.00' END,'.',':')) as pholhours

FROM @table) A

Group by fromdate, empid

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-06-26 : 02:19:57

Try this

select empid,fromdate , case when sum(normalhours+othours+pholhours)>3.6 then 1 else 0 end as status from your_table
group by empid,fromdate

Madhivanan

Failing to plan is Planning to fail

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-26 : 03:05:32

Madhivan, won't there be a problem with the date-math though? For example, if we don't bring it to "time" and leave it as an integer as is native to the original table, then what would we do with this:

row 1: normalhours=1.3 (actually 1h30m)
row 2: normalhours=1.5 (actually 1h50m)

The total 3h20m...but if you add the rows together with the decimal, you only get 2.8. Unless I'm misunderstanding the initial request, it seems we'd always have a problem. That's why I converted back to time so that we could get the proper date math.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-06-26 : 04:13:47

quote:
Originally posted by flamblaster

Madhivan, won't there be a problem with the date-math though? For example, if we don't bring it to "time" and leave it as an integer as is native to the original table, then what would we do with this:

row 1: normalhours=1.3 (actually 1h30m)
row 2: normalhours=1.5 (actually 1h50m)

The total 3h20m...but if you add the rows together with the decimal, you only get 2.8. Unless I'm misunderstanding the initial request, it seems we'd always have a problem. That's why I converted back to time so that we could get the proper date math.

Did you see my calculation? 3.6 hours is acutally 4 hours. So it will work as expected.

Madhivanan

Failing to plan is Planning to fail

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-26 : 08:33:57

Ah, ok...3h60m....crafty! Just have to fight the instinct to assume that the. 6 is 36 monutes :p.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources