Summarizing data and DATEPART - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Summarizing data and DATEPART

Author

Topic

nskatp
Starting Member

8 Posts

Posted - 2008-05-26 : 20:53:30

Hi,

I'm new to SQL and I will appreciate your help, my knowledge only goes so far and I think this task is above my skills. I'm trying to get information form a table in a certain format that allows me to summarize data on an hourly basis but I think I need need to use DATEPART.

The table looks like this:

Group Date_changed Object Proc Idx Value Offset
295 2008-05-19 00:05:00.000 OBJ_1 PRC1 10 2000 8544
295 2008-05-19 00:05:00.000 OBJ_1 PRC1 11 3000 6860
295 2008-05-19 00:05:00.000 OBJ_1 PRC1 12 4000 1372
295 2008-05-19 00:05:00.000 OBJ_2 PRC1 10 1000 2058
295 2008-05-19 00:05:00.000 OBJ_2 PRC1 11 2000 2744
295 2008-05-19 00:05:00.000 OBJ_2 PRC1 12 3000 3430
295 2008-05-19 00:05:00.000 OBJ_3 PRC1 10 7000 4116
295 2008-05-19 00:05:00.000 OBJ_3 PRC1 11 6000 4102
295 2008-05-19 00:05:00.000 OBJ_3 PRC1 12 5000 4802
295 2008-05-19 00:10:00.000 OBJ_1 PRC1 10 2500 4602
295 2008-05-19 00:10:00.000 OBJ_1 PRC1 11 3500 4502
295 2008-05-19 00:10:00.000 OBJ_1 PRC1 12 4500 5481
295 2008-05-19 00:10:00.000 OBJ_2 PRC1 10 1200 5482
295 2008-05-19 00:10:00.000 OBJ_2 PRC1 11 2200 5483
295 2008-05-19 00:10:00.000 OBJ_2 PRC1 12 3200 5484
295 2008-05-19 00:10:00.000 OBJ_3 PRC1 10 7450 6174
295 2008-05-19 00:10:00.000 OBJ_3 PRC1 11 6450 6175
295 2008-05-19 00:10:00.000 OBJ_3 PRC1 12 5450 6176
...
...
...
it contains data on 5 minutes intervals for each object and index.

I'm trying to summarize the data by Idx, I used this query:

select datepart(hour,date_changed) as Hour, Object,avg(Value) as Avg_Idx_10
from table1
where group = 295 and idx = 10 and Object = 'OBJ_1'
and datetime_changed < '2008-05-20'
group by datepart(hour,datetime_changed), Object

Hour Object Avg_Idx_10
0 OBJ_1 6346
1 OBJ_1 5039
2 OBJ_1 4533
3 OBJ_1 5102
4 OBJ_1 9019
5 OBJ_1 23511
6 OBJ_1 52575
...
...
...

And I get the information I need but each query shows only the data for 1 Idx, what I was wondering is if I can create a query that will allow me to summarize data on average Idx for all the idx in just 1 row yo obtain an output like this:

Hour Object Idx_10_Avg Idx_11_Avg Idx_12_Avg
0 OBJ_1 7500 3200 9500
0 OBJ_2 8400 7600 5800
0 OBJ_3 4700 6500 3400
...
...
...

Again, I'll appreciate any help, thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 00:11:48

Use PIVOT

DECLARE @Timeinfo table
(
[Group] int,
 Date_changed datetime,
 [Object] varchar(5),
[Proc] char(4),
Idx int,
[Value] int,
Offset int
)
INSERT INTO @Timeinfo
SELECT 295, '2008-05-19 00:05:00.000', 'OBJ_1', 'PRC1', 10, 2000, 8544
UNION ALL
SELECT 295, '2008-05-19 00:05:00.000', 'OBJ_1', 'PRC1', 11, 3000, 6860
UNION ALL
SELECT 295, '2008-05-19 00:05:00.000', 'OBJ_1', 'PRC1', 12, 4000, 1372
UNION ALL
SELECT 295, '2008-05-19 00:05:00.000', 'OBJ_2', 'PRC1', 10, 1000, 2058
UNION ALL
SELECT 295, '2008-05-19 00:05:00.000', 'OBJ_2', 'PRC1', 11, 2000, 2744
UNION ALL
SELECT 295, '2008-05-19 00:05:00.000', 'OBJ_2', 'PRC1', 12, 3000, 3430
UNION ALL
SELECT 295, '2008-05-19 00:05:00.000', 'OBJ_3', 'PRC1', 10, 7000, 4116
UNION ALL
SELECT 295, '2008-05-19 00:05:00.000', 'OBJ_3', 'PRC1', 11, 6000, 4102
UNION ALL
SELECT 295, '2008-05-19 00:05:00.000', 'OBJ_3', 'PRC1', 12, 5000, 4802
UNION ALL
SELECT 295, '2008-05-19 00:10:00.000', 'OBJ_1', 'PRC1', 10, 2500, 4602
UNION ALL
SELECT 295, '2008-05-19 00:10:00.000', 'OBJ_1', 'PRC1', 11, 3500, 4502
UNION ALL
SELECT 295, '2008-05-19 00:10:00.000', 'OBJ_1', 'PRC1', 12, 4500, 5481
UNION ALL
SELECT 295, '2008-05-19 00:10:00.000', 'OBJ_2', 'PRC1', 10, 1200, 5482
UNION ALL
SELECT 295, '2008-05-19 00:10:00.000', 'OBJ_2', 'PRC1', 11, 2200, 5483
UNION ALL
SELECT 295, '2008-05-19 00:10:00.000', 'OBJ_2', 'PRC1', 12, 3200, 5484
UNION ALL
SELECT 295, '2008-05-19 00:10:00.000', 'OBJ_3', 'PRC1', 10, 7450, 6174
UNION ALL
SELECT 295, '2008-05-19 00:10:00.000', 'OBJ_3', 'PRC1', 11, 6450 ,6175
UNION ALL
SELECT 295, '2008-05-19 00:10:00.000', 'OBJ_3', 'PRC1', 12, 5450, 6176
UNION ALL
--
SELECT 295, '2008-05-19 01:05:00.000', 'OBJ_1', 'PRC1', 10, 2000, 8544
UNION ALL
SELECT 295, '2008-05-19 01:05:00.000', 'OBJ_1', 'PRC1', 11, 3000, 6860
UNION ALL
SELECT 295, '2008-05-19 01:05:00.000', 'OBJ_1', 'PRC1', 12, 4000, 1372
UNION ALL
SELECT 295, '2008-05-19 01:05:00.000', 'OBJ_2', 'PRC1', 10, 1000, 2058
UNION ALL
SELECT 295, '2008-05-19 01:05:00.000', 'OBJ_2', 'PRC1', 11, 2000, 2744
UNION ALL
SELECT 295, '2008-05-19 01:05:00.000', 'OBJ_2', 'PRC1', 12, 3000, 3430
UNION ALL
SELECT 295, '2008-05-19 01:05:00.000', 'OBJ_3', 'PRC1', 10, 7000, 4116
UNION ALL
SELECT 295, '2008-05-19 01:05:00.000', 'OBJ_3', 'PRC1', 11, 6000, 4102
UNION ALL
SELECT 295, '2008-05-19 01:05:00.000', 'OBJ_3', 'PRC1', 12, 5000, 4802
UNION ALL
SELECT 295, '2008-05-19 01:10:00.000', 'OBJ_1', 'PRC1', 10, 2500, 4602
UNION ALL
SELECT 295, '2008-05-19 01:10:00.000', 'OBJ_1', 'PRC1', 11, 3500, 4502
UNION ALL
SELECT 295, '2008-05-19 01:10:00.000', 'OBJ_1', 'PRC1', 12, 4500, 5481
UNION ALL
SELECT 295, '2008-05-19 01:10:00.000', 'OBJ_2', 'PRC1', 10, 1200, 5482
UNION ALL
SELECT 295, '2008-05-19 01:10:00.000', 'OBJ_2', 'PRC1', 11, 2200, 5483
UNION ALL
SELECT 295, '2008-05-19 01:10:00.000', 'OBJ_2', 'PRC1', 12, 3200, 5484
UNION ALL
SELECT 295, '2008-05-19 01:10:00.000', 'OBJ_3', 'PRC1', 10, 7450, 6174
UNION ALL
SELECT 295, '2008-05-19 01:10:00.000', 'OBJ_3', 'PRC1', 11, 6450 ,6175
UNION ALL
SELECT 295, '2008-05-19 01:10:00.000', 'OBJ_3', 'PRC1', 12, 5450, 6176

SELECT [Hour],[Object],[10] AS Idx_10_Avg,
[11] AS Idx_11_Avg,
[12] AS Idx_12_Avg
 FROm
(SELECT [Group],DATEPART(hh,Date_changed)AS [Hour],[Object],[Proc],Idx ,[Value] FROM @TimeInfo)m
PIVOT (AVG([Value]) FOR Idx IN ([10],[11],[12]))p


output
-------------------------------------------
Hour        Object Idx_10_Avg  Idx_11_Avg  Idx_12_Avg
----------- ------ ----------- ----------- -----------
0           OBJ_1  2250        3250        4250
0           OBJ_2  1100        2100        3100
0           OBJ_3  7225        6225        5225
1           OBJ_1  2250        3250        4250
1           OBJ_2  1100        2100        3100
1           OBJ_3  7225        6225        5225

nskatp
Starting Member

8 Posts

Posted - 2008-05-27 : 11:38:28

Visakh16, thanks a lot, that was exactly what I was looking for.

Now, is there any way that the 'dateparted' Hour can be shown as YYYY-MM-DD HH:MM:SS? instead of just the hour?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 11:47:43

quote:
Originally posted by nskatp

Visakh16, thanks a lot, that was exactly what I was looking for.

Now, is there any way that the 'dateparted' Hour can be shown as YYYY-MM-DD HH:MM:SS? instead of just the hour?

SELECT m.Date_changed,[Object],[10] AS Idx_10_Avg,
[11] AS Idx_11_Avg,
[12] AS Idx_12_Avg
 FROm
(SELECT [Group],DATEPART(hh,Date_changed)AS [Hour],Date_changed,[Object],[Proc],Idx ,[Value] FROM @TimeInfo)m
PIVOT (AVG([Value]) FOR Idx IN ([10],[11],[12]))p

nskatp
Starting Member

8 Posts

Posted - 2008-05-27 : 12:23:06

Thanks again, but the results are not what I'm looking for:

time Object Idx_10_Avg Idx_11_Avg Idx_12_Avg
----------------------- ------ ----------- ----------- -----------
2008-05-19 00:05:00.000 OBJ_1 2000 3000 4000
2008-05-19 00:05:00.000 OBJ_2 1000 2000 3000
2008-05-19 00:05:00.000 OBJ_3 7000 6000 5000
2008-05-19 00:10:00.000 OBJ_1 2500 3500 4500
2008-05-19 00:10:00.000 OBJ_2 1200 2200 3200
2008-05-19 00:10:00.000 OBJ_3 7450 6450 5450
2008-05-19 01:05:00.000 OBJ_1 2000 3000 4000
2008-05-19 01:05:00.000 OBJ_2 1000 2000 3000
2008-05-19 01:05:00.000 OBJ_3 7000 6000 5000
2008-05-19 01:10:00.000 OBJ_1 2500 3500 4500
2008-05-19 01:10:00.000 OBJ_2 1200 2200 3200
2008-05-19 01:10:00.000 OBJ_3 7450 6450 5450

I was looking for something like this:

Hour Object Idx_10_Avg Idx_11_Avg Idx_12_Avg
----------- ------ ----------- ----------- -----------
00:00:00 OBJ_1 2250 3250 4250
00:00:00 OBJ_2 1100 2100 3100
00:00:00 OBJ_3 7225 6225 5225
01:00:00 OBJ_1 2250 3250 4250
01:00:00 OBJ_2 1100 2100 3100
01:00:00 OBJ_3 7225 6225 5225

adding YYYY-MM-DD also.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 12:34:49

CONVERT(CHAR(8), getdate(), 108)

E 12°55'05.25"
N 56°04'39.16"

nskatp
Starting Member

8 Posts

Posted - 2008-05-27 : 12:43:23

sorry, I meant:

Hour Object Idx_10_Avg Idx_11_Avg Idx_12_Avg
----------------- ------ ----------- ----------- -----------
20080519 00:00:00 OBJ_1 2250 3250 4250
20080519 00:00:00 OBJ_2 1100 2100 3100
20080519 00:00:00 OBJ_3 7225 6225 5225
20080519 01:00:00 OBJ_1 2250 3250 4250
20080519 01:00:00 OBJ_2 1100 2100 3100
20080519 01:00:00 OBJ_3 7225 6225 5225

I'm trying to use DATEPART to round-up the times for hourly summarization but instead of just the hour displayed I need to display the YYYYMMDD HH:MM:SS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 16:12:51

SELECT DATEADD(HOUR, DATEDIFF(HOUR, '19000101 00:00', getdate()), '19000101 00:00')

E 12°55'05.25"
N 56°04'39.16"

nskatp
Starting Member

8 Posts

Posted - 2008-05-28 : 01:07:55

thanks a lot for your help, this is what I ended up using and worked as required:

DATEADD(HOUR, DATEDIFF(DAY, '19000101 00:00', [Hour]),'20080519')

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources