November 13, 2009 at 5:14 am
Hi there,
I have a lot of daily series, with observations on each day. Now I wish to create new tables, containing the same data, but only the last observation of each month. Is there some way to select this ultimo date within SQL, so as to select the 31st when this is the last date, the 30st when this is the last date etc?
My code is quite simple, looks like this:
-------------------
Select *
into ER_CAD_M
from ER_CAD
where ObsDateCAD = ???
-------------------
If someone can help me out on this, I would be really grateful!
Best regards,
Martin Falch
November 13, 2009 at 5:40 am
One easy logical way you can try .. I am not sure this is the best way or not..
SELECT CONVERT(DATETIME,(CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(MONTH(GETDATE()) AS VARCHAR) + '-01'),120) - 1
This is simply doing - 1 from the 1st date fo the next month
November 13, 2009 at 5:45 am
This should get the records in your table with the latest date based on the current month. If this is not what you need you can alter the part that say GETDATE() to specify which month you're concerned with. Try it and see
Select *
into ER_CAD_M
from ER_CAD
where ObsDateCAD = (SELECT MAX(ObsDateCAD) FROM ER_CAD WHERE DATEPART(mm,ObsDateCAD) = DATEPART(mm,GETDATE()))
November 13, 2009 at 5:57 am
Hi both,
I tried both the codes, but unfortunately they yield "0 rows affected" and "1 row affected" respectively (the 1 row is 11/28/2008 for some reason).
Do you have any suggestions as to why this might be?
I tried another suggestion with the following:
---------
Select *
into ER_CAD_M
from ER_CAD
WHERE DAY(DATEADD(DAY,1,ObsDateCAD)) = 1
--------
Which worked, except for the fact that it left out the months, where the first date in the next month was not the xx/01/xxxx of that month, but instead 02 or 03 perhaps. Maybe you have a suggestion for this case? (The dataset isn't perfect which is why this yields a flaw in this way)
Thanks a ton for your help!
- Martin
November 13, 2009 at 6:16 am
Well im guessing the 1 row for 11/28/2008 suggest there is no data in the table for the month of November for 2009, and as the SQL only considers the month part of the date and gets the max, then the max date you have for that month is from an entry last year.
Ok, back to basics here. What exactly is the result set you want from this query? Do you want to return all records where the date of the record is the last day for that month. Or, do you want to return the last (max date) record for each month (subtle difference)? IE - Last day of Oct = 31st. If for example, you have no records for the 31st Oct - do you want no records returned, or all records from 30th etc??
November 13, 2009 at 6:19 am
That would be the former, meaning that I'd be needing the results from Oct the 30th rather than no observations if it's missing the 31st 🙂
But actually I've gotten it to work now, someone suggested using the following code:
----------
SELECT *
into ER_CAD_M
FROM ER_CAD AS a
WHERE a.ObsDateCAD = (SELECT Max(z.ObsDateCAD)
FROM ER_CAD AS z
WHERE DateAdd(m, DateDiff(m, 0, a.ObsDateCAD), 0) <= z.OBSDateCAD
AND z.ObsDateCAD < DateAdd(m, 1 + DateDiff(m, 0, a.ObsDateCAD), 0))
---------
And while I'm not a 100% sure I get why it works, it seems to do the excact thing I was looking for. Maybe some of you know the reason?
Again, thanks a ton for your time and help! Glad to see so many suggestions in so little time, seems like a really nice community here 🙂
Best regards,
Martin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply