How to Query Last Day of every Month records from a daily record table 2

[ljsmith91]

Hope someone can help. I have a sql db table that contains daily records, there is 1 record each day for each storage device. I need to query (pull) only the last day of each month for the last 24 months. How can I query the table to extract only the last day of each month for the last 24 months ?

Table sample data looks something like this:

device_name date_captured capacity capacity_used
bosnv002 2011-03-27 04:00:00 5432 4321
bosnv001 2011-03-27 11:00:00 7542 2123

Assuming that there are many more records for many more devices going back 2 years, how would I query a table like this to get output that conatins ONLY the records for last day of the month?

Sample query output:

bosnv002 2011-02-28 04:00:00 5432 3455
bosnv002 2011-01-31 04:00:00 5432 3890
bosnv002 2010-12-31 04:00:00 5432 4563
bosnv001 2011-02-28 04:00:00 7542 5467
bosnv001 2011-01-31 04:00:00 7542 6654
bosnv001 2010-12-31 04:00:00 7542 5983

Is there a way of producing a query output given that the last day of each of the last 24 months is unknown at the time of query? Thanks for any help or direction you can offer.

LS

 

[gmmastros]

What version of SQL Server are you using? SQL 2000, 2005, 2008, 2008R2?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ljsmith91]

Hi gmmastros...SQL Server 2005.

 

[imex]

Try something like that:

select * from MyTable T
join (select max(date_captured) as LastDate from MyTable
      where DateDiff(mm, date_captured, GetDate()) < 24
      group by Year(date_captured), Month(date_captured)) as L
on T.date_captured = L.LastDate

I hope this helps.


Casas a venda em Suzano

 

[gmmastros]

I have an idea that may (or may not) work ok for you. Basically, you want the last row (and all associated data) for each device_name, Year, and Month combinations. If you didn't care about the associated data (capacity and capacity_used), you could have used a group by query. Since you do want the associated data, you can use the Row_Number feature (added in SQL2005) to get the last row for each device/year/month combination. Something like this:

;With cte
AS
(
  Select device_name, 
         date_captured,
         capacity,
         capacity_used, 
         Row_Number() Over (Partition By device_name, Year(date_captured), Month(date_captured) Order By date_captured DESC) As RowID
  From   [!]YourTableNameHere[/!]
  )
Select * From cte where RowId = 1

Note that this is not exactly what you wanted, but the results will probably be the same. In this query, you will get the last row for each device/year/month combination even if it's not the last day of the month.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ljsmith91]

Thanks imex and gmmastros. I learned from both of your examples and it has solved my problem. Good stuff.