April 25, 2012 at 2:41 am
The following fields in the table have a time datatype-
MonHours
TueHours
WedHours
ThuHours
Frihours
The requirement is to add the values for all the fields above after doing a SUM for each of them.
It should be something like this but I am not sure about the exact code and that is the reason for my query-
SUM(MonHours) + SUM(TueHours) + SUM(WedHours) + SUM(ThuHours) + SUM(Frihours).
Back to this thread after a lengthy hiatus.
I'm no expert on the TIME datatype, having never actually used it. However my understanding is that it stores a time of day, not an elapsed time duration.
You're asking SQL server to add (for example): 2PM + 4PM, the result of which has no meaning in the fourth dimension.
Unless this is a dimension not of sight and sound, but of mind ... (queue the theme music from The Twilight Zone).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 25, 2012 at 4:39 am
If I have hit upon the root cause of your issue in my prior post, the correct course of action on your part would be to use an INT datatype to store seconds or minutes.
In the event that you won't or can't do that, your only course is some nasty conversions, like the following. Note that I cannot test the last CTE with a TIME datatype because I don't have access to SQL 2008 at this time, yet I'm fairly confident it will work.
DECLARE @Staff_Test TABLE
(c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(10), c4 VARCHAR(10), c5 VARCHAR(10)
,HrsMon DATETIME, HrsTue DATETIME, HrsWed DATETIME, HrsThu DATETIME, HrsFri DATETIME
,Date DATETIME, WeekHrs VARCHAR(10))
INSERT INTO @Staff_Test
VALUES('1','Paul','CCG','TMD','TCS','07:00:00','06:30:00','07:30:00','06:00:00','07:30:00','2012-01-01','34:30')
INSERT INTO @Staff_Test
VALUES('2','Sam','LAB','OMD','TRT','06:00:00','06:30:00','07:00:00','06:00:00','07:30:00','2012-01-04','33:00')
INSERT INTO @Staff_Test
VALUES('3','Peter','YTR','MJY','RET','07:30:00','07:30:00','07:00:00','07:00:00','07:30:00','2012-01-04','36:30')
INSERT INTO @Staff_Test
VALUES('4','Peter','YTR','MJY','RET','06:30:00','07:00:00','07:00:00','07:00:00','07:30:00','2012-01-04','35:00')
INSERT INTO @Staff_Test
VALUES('5','John','CCG','MJK','RTM','06:30:00','07:00:00','07:00:00','07:00:00','07:30:00','2012-01-01','35:00')
SELECT SUM(CAST(HrsMon AS FLOAT))*86400 As Mon
,SUM(CAST(HrsTue AS FLOAT))*86400 As Tue
,SUM(CAST(HrsWed AS FLOAT))*86400 As Wed
,SUM(CAST(HrsThu AS FLOAT))*86400 As Thu
,SUM(CAST(HrsFri AS FLOAT))*86400 As Fri
,SUM(CAST(SUBSTRING(WeekHrs,1,2) AS INT))*3600+SUM(CAST(SUBSTRING(WeekHrs,4,2) AS INT))*60 As WeekHrs
FROM @Staff_Test
;WITH CTE AS (
SELECT CAST(SUM(CAST(HrsMon AS FLOAT))*86400 AS INT) As Mon
,CAST(SUM(CAST(HrsTue AS FLOAT))*86400 AS INT) As Tue
,CAST(SUM(CAST(HrsWed AS FLOAT))*86400 AS INT) As Wed
,CAST(SUM(CAST(HrsThu AS FLOAT))*86400 AS INT) As Thu
,CAST(SUM(CAST(HrsFri AS FLOAT))*86400 AS INT) As Fri
,SUM(CAST(SUBSTRING(WeekHrs,1,2) AS INT))*3600+SUM(CAST(SUBSTRING(WeekHrs,4,2) AS INT))*60 As WeekHrs
FROM @Staff_Test)
SELECT CAST(Mon/3600 AS VARCHAR) + ':' + CAST((Mon/60)%60 AS VARCHAR) + ':' + CAST(Mon%60 AS VARCHAR) AS Mon
,CAST(Tue/3600 AS VARCHAR) + ':' + CAST((Tue/60)%60 AS VARCHAR) + ':' + CAST(Tue%60 AS VARCHAR) AS Tue
,CAST(Wed/3600 AS VARCHAR) + ':' + CAST((Wed/60)%60 AS VARCHAR) + ':' + CAST(Wed%60 AS VARCHAR) AS Wed
,CAST(Thu/3600 AS VARCHAR) + ':' + CAST((Thu/60)%60 AS VARCHAR) + ':' + CAST(Thu%60 AS VARCHAR) AS Thu
,CAST(Fri/3600 AS VARCHAR) + ':' + CAST((Fri/60)%60 AS VARCHAR) + ':' + CAST(Fri%60 AS VARCHAR) AS Fri
FROM CTE
-- Not Tested: Required for TIME datatype (cannot cast to FLOAT)
;WITH CTE AS (
SELECT CAST(SUM(CAST(CAST(HrsMon AS DATETIME) AS FLOAT))*86400 AS INT) As Mon
,CAST(SUM(CAST(CAST(HrsTue AS DATETIME) AS FLOAT))*86400 AS INT) As Tue
,CAST(SUM(CAST(CAST(HrsWed AS DATETIME) AS FLOAT))*86400 AS INT) As Wed
,CAST(SUM(CAST(CAST(HrsThu AS DATETIME) AS FLOAT))*86400 AS INT) As Thu
,CAST(SUM(CAST(CAST(HrsFri AS DATETIME) AS FLOAT))*86400 AS INT) As Fri
,SUM(CAST(SUBSTRING(WeekHrs,1,2) AS INT))*3600+SUM(CAST(SUBSTRING(WeekHrs,4,2) AS INT))*60 As WeekHrs
FROM @Staff_Test)
SELECT CAST(Mon/3600 AS VARCHAR) + ':' + CAST((Mon/60)%60 AS VARCHAR) + ':' + CAST(Mon%60 AS VARCHAR) AS Mon
,CAST(Tue/3600 AS VARCHAR) + ':' + CAST((Tue/60)%60 AS VARCHAR) + ':' + CAST(Tue%60 AS VARCHAR) AS Tue
,CAST(Wed/3600 AS VARCHAR) + ':' + CAST((Wed/60)%60 AS VARCHAR) + ':' + CAST(Wed%60 AS VARCHAR) AS Wed
,CAST(Thu/3600 AS VARCHAR) + ':' + CAST((Thu/60)%60 AS VARCHAR) + ':' + CAST(Thu%60 AS VARCHAR) AS Thu
,CAST(Fri/3600 AS VARCHAR) + ':' + CAST((Fri/60)%60 AS VARCHAR) + ':' + CAST(Fri%60 AS VARCHAR) AS Fri
FROM CTE
Results:
1st results pane (note add Mon to Fri across to get WeekHrs):
MonTueWedThuFriWeekHrs
120600124200127800118800135000626400
2nd results pane (results as CHAR HH:MM:SS):
MonTueWedThuFri
33:30:034:30:035:30:033:0:037:30:0
Note that the second results pane adds up to the correct number of hours in each day bucket from your sample data.
The first two queries will get an error in SQL 2008 if TIME is datatype for MonHrs, TueHrs, etc.
Hope you can figure something out from this.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 25, 2012 at 9:50 am
dwain.c (4/25/2012)
If I have hit upon the root cause of your issue in my prior post, the correct course of action on your part would be to use an INT datatype to store seconds or minutes.In the event that you won't or can't do that, your only course is some nasty conversions, like the following. Note that I cannot test the last CTE with a TIME datatype because I don't have access to SQL 2008 at this time, yet I'm fairly confident it will work.
DECLARE @Staff_Test TABLE
(c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(10), c4 VARCHAR(10), c5 VARCHAR(10)
,HrsMon DATETIME, HrsTue DATETIME, HrsWed DATETIME, HrsThu DATETIME, HrsFri DATETIME
,Date DATETIME, WeekHrs VARCHAR(10))
INSERT INTO @Staff_Test
VALUES('1','Paul','CCG','TMD','TCS','07:00:00','06:30:00','07:30:00','06:00:00','07:30:00','2012-01-01','34:30')
INSERT INTO @Staff_Test
VALUES('2','Sam','LAB','OMD','TRT','06:00:00','06:30:00','07:00:00','06:00:00','07:30:00','2012-01-04','33:00')
INSERT INTO @Staff_Test
VALUES('3','Peter','YTR','MJY','RET','07:30:00','07:30:00','07:00:00','07:00:00','07:30:00','2012-01-04','36:30')
INSERT INTO @Staff_Test
VALUES('4','Peter','YTR','MJY','RET','06:30:00','07:00:00','07:00:00','07:00:00','07:30:00','2012-01-04','35:00')
INSERT INTO @Staff_Test
VALUES('5','John','CCG','MJK','RTM','06:30:00','07:00:00','07:00:00','07:00:00','07:30:00','2012-01-01','35:00')
SELECT SUM(CAST(HrsMon AS FLOAT))*86400 As Mon
,SUM(CAST(HrsTue AS FLOAT))*86400 As Tue
,SUM(CAST(HrsWed AS FLOAT))*86400 As Wed
,SUM(CAST(HrsThu AS FLOAT))*86400 As Thu
,SUM(CAST(HrsFri AS FLOAT))*86400 As Fri
,SUM(CAST(SUBSTRING(WeekHrs,1,2) AS INT))*3600+SUM(CAST(SUBSTRING(WeekHrs,4,2) AS INT))*60 As WeekHrs
FROM @Staff_Test
;WITH CTE AS (
SELECT CAST(SUM(CAST(HrsMon AS FLOAT))*86400 AS INT) As Mon
,CAST(SUM(CAST(HrsTue AS FLOAT))*86400 AS INT) As Tue
,CAST(SUM(CAST(HrsWed AS FLOAT))*86400 AS INT) As Wed
,CAST(SUM(CAST(HrsThu AS FLOAT))*86400 AS INT) As Thu
,CAST(SUM(CAST(HrsFri AS FLOAT))*86400 AS INT) As Fri
,SUM(CAST(SUBSTRING(WeekHrs,1,2) AS INT))*3600+SUM(CAST(SUBSTRING(WeekHrs,4,2) AS INT))*60 As WeekHrs
FROM @Staff_Test)
SELECT CAST(Mon/3600 AS VARCHAR) + ':' + CAST((Mon/60)%60 AS VARCHAR) + ':' + CAST(Mon%60 AS VARCHAR) AS Mon
,CAST(Tue/3600 AS VARCHAR) + ':' + CAST((Tue/60)%60 AS VARCHAR) + ':' + CAST(Tue%60 AS VARCHAR) AS Tue
,CAST(Wed/3600 AS VARCHAR) + ':' + CAST((Wed/60)%60 AS VARCHAR) + ':' + CAST(Wed%60 AS VARCHAR) AS Wed
,CAST(Thu/3600 AS VARCHAR) + ':' + CAST((Thu/60)%60 AS VARCHAR) + ':' + CAST(Thu%60 AS VARCHAR) AS Thu
,CAST(Fri/3600 AS VARCHAR) + ':' + CAST((Fri/60)%60 AS VARCHAR) + ':' + CAST(Fri%60 AS VARCHAR) AS Fri
FROM CTE
-- Not Tested: Required for TIME datatype (cannot cast to FLOAT)
;WITH CTE AS (
SELECT CAST(SUM(CAST(CAST(HrsMon AS DATETIME) AS FLOAT))*86400 AS INT) As Mon
,CAST(SUM(CAST(CAST(HrsTue AS DATETIME) AS FLOAT))*86400 AS INT) As Tue
,CAST(SUM(CAST(CAST(HrsWed AS DATETIME) AS FLOAT))*86400 AS INT) As Wed
,CAST(SUM(CAST(CAST(HrsThu AS DATETIME) AS FLOAT))*86400 AS INT) As Thu
,CAST(SUM(CAST(CAST(HrsFri AS DATETIME) AS FLOAT))*86400 AS INT) As Fri
,SUM(CAST(SUBSTRING(WeekHrs,1,2) AS INT))*3600+SUM(CAST(SUBSTRING(WeekHrs,4,2) AS INT))*60 As WeekHrs
FROM @Staff_Test)
SELECT CAST(Mon/3600 AS VARCHAR) + ':' + CAST((Mon/60)%60 AS VARCHAR) + ':' + CAST(Mon%60 AS VARCHAR) AS Mon
,CAST(Tue/3600 AS VARCHAR) + ':' + CAST((Tue/60)%60 AS VARCHAR) + ':' + CAST(Tue%60 AS VARCHAR) AS Tue
,CAST(Wed/3600 AS VARCHAR) + ':' + CAST((Wed/60)%60 AS VARCHAR) + ':' + CAST(Wed%60 AS VARCHAR) AS Wed
,CAST(Thu/3600 AS VARCHAR) + ':' + CAST((Thu/60)%60 AS VARCHAR) + ':' + CAST(Thu%60 AS VARCHAR) AS Thu
,CAST(Fri/3600 AS VARCHAR) + ':' + CAST((Fri/60)%60 AS VARCHAR) + ':' + CAST(Fri%60 AS VARCHAR) AS Fri
FROM CTE
Results:
1st results pane (note add Mon to Fri across to get WeekHrs):
MonTueWedThuFriWeekHrs
120600124200127800118800135000626400
2nd results pane (results as CHAR HH:MM:SS):
MonTueWedThuFri
33:30:034:30:035:30:033:0:037:30:0
Note that the second results pane adds up to the correct number of hours in each day bucket from your sample data.
The first two queries will get an error in SQL 2008 if TIME is datatype for MonHrs, TueHrs, etc.
Hope you can figure something out from this.
Thanks for your immense help !! I used your query within another one to calculate the hours.
however, I am only able to display the result as HH:MM when the minutes are not null, if they are null, I get a result like 228:0 instead of 228:00.
I have tried using the replace function but it adds a zero in front of the hours value like:
0228:00. Would you know a way around it ?
Thanks
April 25, 2012 at 6:17 pm
I omitted that because I was in a hurry. Just use something like this:
':' + RIGHT('00'+CAST(Tue%60 AS VARCHAR),2)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 26, 2012 at 2:45 am
dwain.c (4/25/2012)
I omitted that because I was in a hurry. Just use something like this:
':' + RIGHT('00'+CAST(Tue%60 AS VARCHAR),2)
Thank you very much again ! It worked perfectly !
April 26, 2012 at 8:28 pm
Using the same test data previously provided, I believe we can simplify a bit while doing a bit of grouping by date. This code will work in SQL Server 2005 and 2008 and can use DateTime or Time datatypes for the daily hours. If you go over 68 years worth of seconds (2 billion, the limit of INT), you may have to embedd a CAST to BIGINT in the SUM()s.
WITH
ctePreAgg AS
( --=== Convert to seconds and add everything up.
-- Works with DATETIME and TIME datatypes.
SELECT MyGroup = GROUPING(Date),
Date,
HrsMon = SUM(DATEDIFF(ss,0,HrsMon)),
HrsTue = SUM(DATEDIFF(ss,0,HrsTue)),
HrsWed = SUM(DATEDIFF(ss,0,HrsWed)),
HrsThu = SUM(DATEDIFF(ss,0,HrsThu)),
HrsFri = SUM(DATEDIFF(ss,0,HrsFri))
FROM @Staff_Test
GROUP BY Date WITH ROLLUP
),
cteAggWeek AS
( --=== Trust no other's date/time aggregations
SELECT MyGroup, Date,
HrsMon,HrsTue,HrsWed,HrsThu,HrsFri,
WeekHrs = HrsMon+HrsTue+HrsWed+HrsThu+HrsFri
FROM ctePreAgg
)
SELECT Date = CASE
WHEN ISDATE(Date) = 1 THEN CONVERT(CHAR(10),Date,120)
ELSE 'Total'
END,
Mon = CONVERT(VARCHAR(10),HrsMon/ 3600) + RIGHT(CONVERT(CHAR(8),DATEADD(ss,HrsMon% 3600,0),108),6),
Tue = CONVERT(VARCHAR(10),HrsTue/ 3600) + RIGHT(CONVERT(CHAR(8),DATEADD(ss,HrsTue% 3600,0),108),6),
Wed = CONVERT(VARCHAR(10),HrsWed/ 3600) + RIGHT(CONVERT(CHAR(8),DATEADD(ss,HrsWed% 3600,0),108),6),
Thu = CONVERT(VARCHAR(10),HrsThu/ 3600) + RIGHT(CONVERT(CHAR(8),DATEADD(ss,HrsThu% 3600,0),108),6),
Fri = CONVERT(VARCHAR(10),HrsFri/ 3600) + RIGHT(CONVERT(CHAR(8),DATEADD(ss,HrsFri% 3600,0),108),6),
Total = CONVERT(VARCHAR(10),WeekHrs/3600) + RIGHT(CONVERT(CHAR(8),DATEADD(ss,WeekHrs%3600,0),108),6)
FROM cteAggWeek
ORDER BY MyGroup, Date
;
Here's the output...
Date Mon Tue Wed Thu Fri Total
---------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
2012-01-01 13:30:00 13:30:00 14:30:00 13:00:00 15:00:00 69:30:00
2012-01-04 20:00:00 21:00:00 21:00:00 20:00:00 22:30:00 104:30:00
Total 33:30:00 34:30:00 35:30:00 33:00:00 37:30:00 174:00:00
I've always been curious about such things. I can sometimes see why you might collect timecards that have hour and minutes on them, but I'll never understand why totals need to be displayed that way. Why don't you need (or want) the totals displayed as decimal hours so you can multiply them by rates easier?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 26, 2012 at 8:48 pm
Why don't you need (or want) the totals displayed as decimal hours so you can multiply them by rates easier?
I've always figured it's because blue-collar folk relate better to HH:MM than to decimal hours. Our timesheet system reports decimal hours and even I scratch my head sometimes when I see that I worked 12.833 hours on a project.
Nice solution though. Wish I thought of it. I did hit upon a CONVERT yesterday that would have probably made my version a little simpler.
You have no comment on the use of a TIME field for storing HH:MM:SS as this one is doing? SQL needs a "time duration" datatype (IMHO). Haven't checked SQL 2012 to see if they added one.
Glad to see you didn't lose track of this thread again 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 26, 2012 at 8:58 pm
dwain.c (4/26/2012)
Why don't you need (or want) the totals displayed as decimal hours so you can multiply them by rates easier?
I've always figured it's because blue-collar folk relate better to HH:MM than to decimal hours. Our timesheet system reports decimal hours and even I scratch my head sometimes when I see that I worked 12.833 hours on a project.
Nice solution though. Wish I thought of it. I did hit upon a CONVERT yesterday that would have probably made my version a little simpler.
You have no comment on the use of a TIME field for storing HH:MM:SS as this one is doing? SQL needs a "time duration" datatype (IMHO). Haven't checked SQL 2012 to see if they added one.
Glad to see you didn't lose track of this thread again 🙂
I agree on the paystub aspect of such things but, when aggregating multiple workers hours, we're not talking about paystubs anymore.
On the use of the TIME datatype, my Momma said to not say anything instead of saying something bad. 😛
And, yes, I absolutely agree! It would certainly be nice if MS came up with a duration datatype that we could use SUM() and other aggregates on!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 46 through 52 (of 52 total)
You must be logged in to reply to this topic. Login to reply