Max in history table for each month

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 2000 Forums

Transact-SQL (2000)

Max in history table for each month

Author

Topic

webforyou
Starting Member

15 Posts

Posted - 2007-10-04 : 11:45:39

Dear all,
Could we do the Max in history table for each item in each month.
The history table
HistoryID Item Value Created Updated
1 A 50 20070509 20070509
2 A 55 20070509 20070510
3 A 45 20070509 20070520
4 A 60 20070509 20070602
5 A 65 20070509 20070611
6 A 30 20070509 20070617
7 A 35 20070509 20070618
8 A 39 20070509 20070628
9 A 35 20070509 20070718
10 A 55 20070509 20070828
11 B 50 20070425 20070425
12 B 55 20070425 20070510
13 B 45 20070425 20070530
14 B 60 20070425 20070602
15 B 65 20070425 20070611
16 B 30 20070425 20070612
17 B 35 20070425 20070613
18 B 39 20070425 20070629

After filter will be
Month HistoryID Item Value Created Updated
200704 11 B 50 20070425 20070425
200705 3 A 45 20070509 20070520
200705 13 B 45 20070425 20070530
200706 8 A 39 20070509 20070628
200706 18 B 39 20070425 20070629
200707 9 A 35 20070509 20070718
200708 10 A 55 20070509 20070828

Is this problem is solved by pivoting with the Max.
Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-04 : 12:01:29

[code]DECLARE @history TABLE
(
HistoryID int,
Item CHAR(1),
Value int,
Created datetime,
Updated datetime
)

INSERT INTO @history
SELECT 1, 'A', 50, '20070509', '20070509' UNION ALL
SELECT 2, 'A', 55, '20070509', '20070510' UNION ALL
SELECT 3, 'A', 45, '20070509', '20070520' UNION ALL
SELECT 4, 'A', 60, '20070509', '20070602' UNION ALL
SELECT 5, 'A', 65, '20070509', '20070611' UNION ALL
SELECT 6, 'A', 30, '20070509', '20070617' UNION ALL
SELECT 7, 'A', 35, '20070509', '20070618' UNION ALL
SELECT 8, 'A', 39, '20070509', '20070628' UNION ALL
SELECT 9, 'A', 35, '20070509', '20070718' UNION ALL
SELECT 10, 'A', 55, '20070509', '20070828' UNION ALL
SELECT 11, 'B', 50, '20070425', '20070425' UNION ALL
SELECT 12, 'B', 55, '20070425', '20070510' UNION ALL
SELECT 13, 'B', 45, '20070425', '20070530' UNION ALL
SELECT 14, 'B', 60, '20070425', '20070602' UNION ALL
SELECT 15, 'B', 65, '20070425', '20070611' UNION ALL
SELECT 16, 'B', 30, '20070425', '20070612' UNION ALL
SELECT 17, 'B', 35, '20070425', '20070613' UNION ALL
SELECT 18, 'B', 39, '20070425', '20070629'

SELECT [MONTH] = DATEADD(MONTH, DATEDIFF(MONTH, 0, h.Updated), 0),
h.HistoryID, h.Item, h.Value, h.Created, h.Updated
FROM @history h
INNER JOIN
(
SELECT [MONTH] = DATEADD(MONTH, DATEDIFF(MONTH, 0, Updated), 0), Item,
Updated = MAX(Updated)
FROM @history
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Updated), 0), Item
) m
ON h.Item = m.Item
AND h.Updated = m.Updated
ORDER BY [MONTH], h.Item

/*

MONTH HistoryID Item Value Created Updated
----------- ----------- ---- ----------- ----------- -----------
2007-04-01 11 B 50 2007-04-25 2007-04-25
2007-05-01 3 A 45 2007-05-09 2007-05-20
2007-05-01 13 B 45 2007-04-25 2007-05-30
2007-06-01 8 A 39 2007-05-09 2007-06-28
2007-06-01 18 B 39 2007-04-25 2007-06-29
2007-07-01 9 A 35 2007-05-09 2007-07-18
2007-08-01 10 A 55 2007-05-09 2007-08-28

(7 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources