January 14, 2011 at 6:38 am
Could anyone help me to find a solution for the below problem.
I have a similar problem, my table has the following columns:
ReadingDate -
Hour - (0-23)
Trans1
Trans2
Trans3
Trans4
Trans5
Trans6
I have to get the sum of each of the following columns (Trans2,Trans4,Trans) that displays data from hour 9 of today to hour 8 of next day. Also the total hour should be of 24hr if you say hour start of today 9 am means the end should be next a 8am.
For example, assume that i select the date range of the reading date as 2010-1-1 and 2010-1-2 and i have the values for the columns as
ReadingDateHourTrans1Trans2Trans3Trans4Trans5Trans6
2010-1-101202135
2010-1-111202135
2010-1-121202135
2010-1-131202135
2010-1-1412222135
2010-1-151202135
2010-1-161202135
2010-1-171202135
2010-1-181202135
2010-1-191202135
2010-1-1101202135
2010-1-1111202135
2010-1-1121202135
2010-1-1131202135
2010-1-1141202135
2010-1-1151202135
2010-1-1161202135
2010-1-1171202135
2010-1-11812222135
2010-1-11912222135
2010-1-12012222135
2010-1-12112222135
2010-1-12212222135
2010-1-12312222135
2010-1-2012222135
2010-1-2112222135
2010-1-2212222135
2010-1-2312222135
2010-1-2412222135
2010-1-2512222135
2010-1-2612222135
2010-1-2712222135
2010-1-2812222135
2010-1-2912222135
2010-1-21012222135
2010-1-21112222135
2010-1-21212222135
2010-1-21312222135
2010-1-21412222135
2010-1-21512222135
2010-1-21612222135
2010-1-21712222135
2010-1-21812222135
2010-1-11912222135
2010-1-12012222135
2010-1-12112222135
2010-1-12212222135
2010-1-12312222135
output should be:
ReadingDateHourTrans2Trans4Trans6
2010-1-192215
2010-1-1102215
2010-1-1112215
2010-1-1122215
2010-1-1132215
2010-1-1142215
2010-1-1152215
2010-1-1162215
2010-1-1172215
2010-1-1182215
2010-1-1192215
2010-1-1202215
2010-1-1212215
2010-1-1222215
2010-1-1232215
2010-1-202215
2010-1-212215
2010-1-222215
2010-1-232215
2010-1-242215
2010-1-252215
2010-1-262215
2010-1-272215
2010-1-282215
Final Output:
ReadingdateTrans2Trans4Trans4
2010-1-148504120
January 14, 2011 at 7:22 am
DECLARE @Date datetime
SET @Date = '20100101'
SELECT @Date AS [ReadingDate],
SUM(Trans2) AS [Trans2],
SUM(Trans4) AS [Trans4],
SUM(Trans6) AS [Trans6]
FROM @Table
WHERE (ReadingDate = @Date AND [Hour] >= 9)
OR (ReadingDate = DATEADD(day,1,@Date) AND [Hour] <= 8)
Far away is close at hand in the images of elsewhere.
Anon.
January 14, 2011 at 8:35 am
David's approach will work if you're only reporting on a single day, but immediately runs into problems when reporting on multiple days. A much better approach when you're reporting on a time period that does not match the standard time period is to transform your time period so that it does match the standard. Since the standard day starts at 00:00:00 and your day starts at 09:00:00, the simplest way to make this adjustment is to subtract nine hours from your time. For example
WITH Readings AS (
DateAdd(hh -- The adjustment timescale
, ReadingDate -- The base date
, [Hour] -- The actual hour, since it's in a separate field
- 9 -- The adjustment amount
) AS GroupingDate
, ReadingDate
, Trans2
, Trans4
, Trans6
FROM @Table
-- Optional WHERE clause here
)
SELECT Min(ReadingDate) AS ReadingDate
, SUM(Trans2) AS [Trans2]
, SUM(Trans4) AS [Trans4]
, SUM(Trans6) AS [Trans6]
FROM Readings
GROUP BY Day(GroupingDate)
This is untested, because you didn't provide your data in an easily digestible format.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 14, 2011 at 8:43 am
I did think of that Drew as well 🙂
But performance would be poor for a large data set as table/index scan would be used as every row would be read to calc date before selection.
However if there are no indexes (reasonable or otherwise) then it is a moot point.
Edited:
Forget this Drew as I just noticed your dml is intended for the whole dataset :blush:
Far away is close at hand in the images of elsewhere.
Anon.
January 14, 2011 at 8:49 am
Thanks to all for your valuable inputs.
I am sorry about the table format which i posted earlier.
Just an example i am posting it again
ReadingDate | Hour | Trans1 | Trans2 | Trans3 | Trans4 | Trans5 |Trans6
2010-1-1 | 0 | 1 | 2 | 0 | 2 | 1 | 35
2010-1-1 | 1 | 1 | 0 | 21 | 3 | 5 | 0
so on till hour 23.you have put any sample values from 0-23.
2010-1-2 | 0 | 1 | 2 | 22 | 1 | 3 | 5
2010-1-2 | 1 | 1 | 2 | 22 | 1 | 3 | 5
2010-1-2 | 2 | 1 | 2 | 22 | 1 | 3 | 5
2010-1-2 | 3 | 1 | 2 | 22 | 1 | 3 | 5
2010-1-2 | 4 | 1 | 2 | 22 | 1 | 3 | 5
2010-1-2 | 5 | 1 | 2 | 22 | 1 | 3 | 5
2010-1-2 | 6 | 1 | 2 | 22 | 1 | 3 | 5
2010-1-2 | 7 | 1 | 2 | 22 | 1 | 3 | 5
2010-1-2 | 8 | 1 | 2 | 22 | 1 | 3 | 5
January 14, 2011 at 9:34 am
Drew,
I tried your query, but it throws an error message. Incorrect syntax near 'DateADD'.
January 17, 2011 at 2:44 pm
As I said, the code was untested, because your data was not in an easily consumable format. (It still isn't.) I suggest reading Jeff's article about how to post data. http://www.sqlservercentral.com/articles/Best+Practices/61537/
The obvious problem is that the SELECT keyword should appear before the DateAdd, not after it. If you want tested code, read Jeff's article and submit useable data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply