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:
...
Thread: Selecting ultimo dates of a month  |
|||||||||||||
|
Started 3 weeks, 5 days ago by MartinFalch
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:
...
|
|
||||||||||||
|
|||||||||||||
Other posts in this thread:
MartinFalch Registered User replied 3 weeks, 5 days ago
Size: 716 bytes
Customize: 
r937 replied 3 weeks, 5 days ago
WHERE DAY(DATEADD(DAY,1,ObsDateCAD)) = 1
Size: 182 bytes
Customize:
r937 SQL Consultant replied 3 weeks, 5 days ago
WHERE DAY(DATEADD(DAY,1,ObsDateCAD)) = 1
Size: 182 bytes
Customize:
MartinFalch replied 3 weeks, 5 days ago
Arh thanks alot, that was a really smart way to do it!
- However, I'm quite dumb for not including this info, but unfortunately the dataset is slightly "flawed" in the sense that it doesn't always have the 1st as the first date of a new month, sometimes the first date is the second or the third - meaning that some months are left out via that method.
Problem is that adding an "or" and ...
Size: 673 bytes
Customize:
MartinFalch Registered User replied 3 weeks, 5 days ago
Arh thanks alot, that was a really smart way to do it!
- However, I'm quite dumb for not including this info, but unfortunately the dataset is slightly "flawed" in the sense that it doesn't always have the 1st as the first date of a new month, sometimes the first date is the second or the third - meaning that some months are left out via that method.
Problem is that adding an "or" and ...
Size: 673 bytes
Customize:
r937 replied 3 weeks, 5 days ago
Quote:
Originally Posted by MartinFalch
However, I'm quite dumb for not including this info, but unfortunately the dataset is slightly "flawed" in the sense that it doesn't always have the 1st as the first date of a new month, sometimes the first date is the second or the third - meaning that some months are left out via that method....
Size: 1,276 bytes
Customize:
r937 SQL Consultant replied 3 weeks, 5 days ago
Quote:
Originally Posted by MartinFalch
However, I'm quite dumb for not including this info, but unfortunately the dataset is slightly "flawed" in the sense that it doesn't always have the 1st as the first date of a new month, sometimes the first date is the second or the third - meaning that some months are left out via that method....
Size: 1,276 bytes
Customize:
MartinFalch replied 3 weeks, 5 days ago
Hmm seems you are right, it's probably not due to that since it works in some cases, even through the above flaw is there. However, it seems to leave out specific months, 3-4 pr . year.
Can it be because the last date of these months in the dataset don't correspond to the actual last date of these years? Meaning that if the code expects the 31st to be the last date and it's the 30st in the ...
Size: 569 bytes
Customize:
MartinFalch Registered User replied 3 weeks, 5 days ago
Hmm seems you are right, it's probably not due to that since it works in some cases, even through the above flaw is there. However, it seems to leave out specific months, 3-4 pr . year.
Can it be because the last date of these months in the dataset don't correspond to the actual last date of these years? Meaning that if the code expects the 31st to be the last date and it's the 30st in the ...
Size: 569 bytes
Customize:
Pat Phelan replied 3 weeks, 5 days ago
An NZDF solution might be:
Code:
SELECT *
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))
-PatP
Size: 588 bytes
Customize:
|
Top contributing authorsRelated threads on "dBforums - Database Support Community":Related threads on other sites: |
||||||||||||
Thread profile page for "Selecting ultimo dates of a month" on http://www.dbforums.com.
This report page is a snippet summary view from a single thread "Selecting ultimo dates of a month", located on the Message Board at http://www.dbforums.com.
This thread profile page shows the thread statistics for: Total Authors, Total Thread Posts, and Thread Activity
Home |
About Us |
Submit Your Site |
Update Your Site |
Get Search For Your Site |
Partners |
Contact |
Search Plugin |
Blog |
 |
Last Searches | Top Searches |
Designated brands and trademarks are property of their respective owners.
Use of this website constitutes acceptance of BoardReader's User Agreement and Privacy Policy.