May 20, 2013 at 10:23 am
Hi all i have a table in which results are stored every 30 minutes.
tblResults
TestNamesample_timevalue
Test1 5/17/2013 12:00657
Test2 5/17/2013 12:30634
Test3 5/17/2013 13:00576
Test4 5/17/2013 13:30641
I would like to sum results for the sample_time of 1 hour so for 12PM-1PM value = 1867
I'm using this query
SELECT sample_time AS sample_time_start
, sample_time AS sample_time_end
, SUM(VALUE) FROM tblResults
WHERE
GROUP BY DATEPART(HOUR, SAMPLE_TIME)
but i need to see TestName and sample_time column also like this
tblResults
TestNamesample_time_start sample_time_end value
Test1 5/17/2013 12:00 5/17/2013 01:00 1867
any help appreciated.
May 20, 2013 at 10:32 am
Like this?
SELECT TestName, sample_time AS sample_time_start
, DATEADD(HOUR, 1, sample_time) as sample_time_end
, SUM(VALUE) FROM tblResults
WHERE
GROUP BY TestName, DATEPART(HOUR, SAMPLE_TIME)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2013 at 11:45 am
In my proposed solution below, I am making a few assumptions about your data and the results, some of which vary from your explicit description, so please reply and correct me if my assumptions are incorrect. Here are my assumptions:
1. You do not want to include the same value in two different hours. I am treating the 576 in your sample data as belonging in the second hour of the day and not as part of the first hour of the day; so really, 12:00 to 12:59 is one hour and 13:00 to 13:59 is the second hour. This varies from your explicit description of 12:00 to 13:00 being the first hour.
2. You want the TestName to indicate the hour of the day for the test results and not necessarily the original TestName that increments every half an hour. (So the TestName of hour 1 should be Test1 and the TestName of hour 2 should be Test2 even though it is the aggregation of Test3 and Test4 in the original data).
3. I included two versions of the TestName. For the TestName, I am further assuming the data span more than one day, so you may need to include the date of the test along with the hour of the test in the test name.
4. For the sample_time_end, I am giving you an option of including the 1/2 hour time as the end time or the 00:59 time. Also, your "results" example switches the time designation from military time to am/pm time. I have included a third example in this sample that does the conversion.
with
raw_data as
(select TestName = 'Test1', sample_time = CAST('5/17/2013 12:00' as datetime), value = 657 union all
select 'Test2', CAST('5/17/2013 12:30' as datetime), 634 union all
select 'Test3', CAST('5/17/2013 13:00' as datetime), 576 union all
select 'Test4', CAST('5/17/2013 13:30' as datetime), 641)
select
TestName = 'Test' + cast(ROW_NUMBER() over (order by DATEPART(HOUR, sample_time)) as varchar(2)) ,
TestName_v2 = 'Test_' + convert(varchar(8), sample_time, 112) + '_' + cast(ROW_NUMBER() over (order by DATEPART(HOUR, sample_time)) as varchar(2)),
sample_time_start = MIN(sample_time),
sample_time_end = MAX(sample_time),
sample_time_end_v2 = DATEADD(MINUTE, 59, MIN(sample_time)),
sample_time_end_v3 = CONVERT(VARCHAR, DATEADD(MINUTE, 59, MIN(sample_time)), 100),
value = SUM(value)
from
raw_data
group by
convert(varchar(8), sample_time, 112), DATEPART(HOUR, sample_time)
May 20, 2013 at 12:07 pm
Thank you Sean and Geoff.
Geoff you're right about
#2 it is 12:00 - 12:59
#4 thank you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply