Selecting last record for a given month 1

[alh1002]

I have a DB with daily records. I want to summarize it, and make a new table with just monthly information.

structure:
date/values/values/values ...

How do I get the values just for last entered day of a month (it won't always be the 31st)

 

[PHV]

SQL snippet:
WHERE [date field] = DateSerial(Year([date field]), 1 + Month([date field]), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alh1002]

Thanks, that kinda works.

It is giving my the last record in January (which is on the 31st) but Feb. has only partial data. And I expect to see something listed on Feb 9th (as there was no data for the rest of Feb)

so what I want is: for the entire year 12 rows (corresponding to last entered infromation for each month)

Is there a way to fix that

here is the code as is:

SELECT ladder_date, BOM, NAV, gMTDRT, gMTDPER
FROM dailySummary
WHERE [ladder_date]=DateSerial(Year([ladder_date]),1+Month([ladder_date]),0);

 

[PHV]

SELECT A.ladder_date, A.BOM, A.NAV, A.gMTDRT, A.gMTDPER
FROM dailySummary AS A INNER JOIN (
SELECT Month([ladder_date]), MAX([ladder_date]) AS LastDay FROM dailySummary GROUP BY Month([ladder_date])
) AS L ON A.ladder_date = L.LastDay

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alh1002]

One last issue. Different years

How do I make sure

1/30/05
is
different from
1/31/06

 

[PHV]

SELECT A.ladder_date, A.BOM, A.NAV, A.gMTDRT, A.gMTDPER
FROM dailySummary AS A INNER JOIN (
SELECT Format([ladder_date],'yyyymm'), MAX([ladder_date]) AS LastDay FROM dailySummary GROUP BY Format([ladder_date],'yyyymm')
) AS L ON A.ladder_date = L.LastDay

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886