| Author |
 Topic  |
|
|
kiddoOnSQL
Starting Member
Australia
16 Posts |
 Posted - 06/25/2012 : 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
USA
355 Posts |
Posted - 06/26/2012 : 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 |
Edited by - flamblaster on 06/26/2012 01:11:36 |
 |
|
|
kiddoOnSQL
Starting Member
Australia
16 Posts |
Posted - 06/26/2012 : 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
USA
355 Posts |
Posted - 06/26/2012 : 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
USA
355 Posts |
Posted - 06/26/2012 : 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
India
22469 Posts |
Posted - 06/26/2012 : 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
USA
355 Posts |
Posted - 06/26/2012 : 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
India
22469 Posts |
Posted - 06/26/2012 : 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
USA
355 Posts |
Posted - 06/26/2012 : 08:33:57
|
| Ah, ok...3h60m....crafty! Just have to fight the instinct to assume that the. 6 is 36 monutes :p. |
|
|
| |
Topic |
|
|
|
Adding hh:mm from 3 columns
