Selecting a row in a series last date in a 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 2005 Forums

Transact-SQL (2005)

Selecting a row in a series last date in a month!!

Author

Topic

LostShrew
Starting Member

5 Posts

Posted - 2009-10-06 : 07:16:11

Hi all,

Hopefully you can help with this one or at least set me in the correct direction.

I have writen a series of sprocs report in T-SQL which format/Run calculations on series on data. The return data is akin to the below:-

.......K1,01-Jan-09,3.4
K1,08-Jan-09,3.6
K1,15-Jan-09,3.8
K1,22-Jan-09,3.9
K1,29-Jan-09,4.3
K1,02-Feb-09,6.2
K2,01-Jan-09,7.2
K2,08-Jan-09,6.6
K2,15-Jan-09,7.3
K2,22-Jan-09,12.3
K2,29-Jan-09,14.1.....

What i need is to only select the last reading each month per K(n), so just for the above selection i would expect the following back:-

.......K1,29-Jan-09,4.3
K1,02-Feb-09,6.2
K2,29-Jan-09,14.1.....

How could i do this when the last reading could be on the first of the month or last?

If you need more info please ask.

Cheers for reading.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-06 : 07:47:49

SELECT .*
FROM yourTable a
INNER JOIN

(select col1,[col2] = max(col2) from yourTable group by col1) b
ON
a.col1 = b.Col1
and a.col2 = b.col2

Jim

Everyday I learn something that somebody else already knew

LostShrew
Starting Member

5 Posts

Posted - 2009-10-06 : 08:11:31

quote:
Originally posted by jimf

SELECT .*
FROM yourTable a
INNER JOIN

(select col1,[col2] = max(col2) from yourTable group by col1) b
ON
a.col1 = b.Col1
and a.col2 = b.col2

Jim

Everyday I learn something that somebody else already knew

Cheers Jim thanks for answering,
but i need to select the last reading in each month over a period of approx. a year. I haven't tested this code but i presume that this will give me the last reading in the dataset per K(n). I will check it out now.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-06 : 09:19:03

Okay, I got it. You're right about what my query will return

try this new and improved query

DECLARE @Table TABLE (Kn char(2),Date datetime,aValue float)
INSERT INTO @Table

SELECT 'K1','01-Jan-09',3.4 UNION ALL
SELECT 'K1','08-Jan-09',3.6 UNION ALL
SELECT 'K1','15-Jan-09',3.8 UNION ALL
SELECT 'K1','22-Jan-09',3.9 UNION ALL
SELECT 'K1','29-Jan-09',4.3 UNION ALL
SELECT 'K1','02-Feb-09',6.2 UNION ALL
SELECT 'K2','01-Jan-09',7.2 UNION ALL
SELECT 'K2','08-Jan-09',6.6 UNION ALL
SELECT 'K2','15-Jan-09',7.3 UNION ALL
SELECT 'K2','22-Jan-09',12.3 UNION ALL
SELECT 'K2','29-Jan-09',14.1

SELECT * FROM @table
WHERE DATE IN
(select max(date) from @table group by dateadd(month,datediff(month,date,0),0))

Jim

Everyday I learn something that somebody else already knew

LostShrew
Starting Member

5 Posts

Posted - 2009-10-06 : 10:33:35

quote:
Originally posted by jimf

Okay, I got it. You're right about what my query will return

try this new and improved query

DECLARE @Table TABLE (Kn char(2),Date datetime,aValue float)
INSERT INTO @Table

SELECT 'K1','01-Jan-09',3.4 UNION ALL
SELECT 'K1','08-Jan-09',3.6 UNION ALL
SELECT 'K1','15-Jan-09',3.8 UNION ALL
SELECT 'K1','22-Jan-09',3.9 UNION ALL
SELECT 'K1','29-Jan-09',4.3 UNION ALL
SELECT 'K1','02-Feb-09',6.2 UNION ALL
SELECT 'K2','01-Jan-09',7.2 UNION ALL
SELECT 'K2','08-Jan-09',6.6 UNION ALL
SELECT 'K2','15-Jan-09',7.3 UNION ALL
SELECT 'K2','22-Jan-09',12.3 UNION ALL
SELECT 'K2','29-Jan-09',14.1

SELECT * FROM @table
WHERE DATE IN
(select max(date) from @table group by dateadd(month,datediff(month,date,0),0))

Jim

Everyday I learn something that somebody else already knew

Thanks again Jim, i have been thinking this one through and could quite get there this looks good. I will try to integrate this at the end of my sproc.

LostShrew
Starting Member

5 Posts

Posted - 2009-10-06 : 14:30:20

Cheers Jim worked great, I just had to add the K(n) field after the group by and before the dataadd.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources