Date Selection Criteria

[MikeEllis]

Hi all,

I have a big list of data, with a row of data for every day for each client.

I need a selection criteria that will provide show the last day of the every month (historic data) for each client.

I've had a go but I'm not making much progress.

Any help would be appreciated.

Thanks



Michael

[RuralGuy]

Did you know that the 0 day of the month is the last day of the previous month?

[MikeEllis]

Did you know that the 0 day of the month is the last day of the previous month?

I did not know that, thanks!

Although not quite figured it out yet. I have the below, but it is only showing the last day of last month, not the last day of every month.

Day([ExtractDate])=Day(0)

Any thoughts?

[MikeEllis]

Ok, it kind of works now...although I have noticed an issue.

There is no data for weekends and public holidays!

I dont think I can use the 0 day function as a result.

Any thoughts on a work around?

Thanks

[CJ_London]

you can use a subquery

SELECT *
FROM myTable
WHERE ExtractDate=(SELECT Max(ExtractDate) FROM myTable AS T WHERE ClientID=myTable.ClientID AND Month(ExtractDate)=Month(Dateadd("m",-1,Date()))

this will select from all dates in March if date is in April, from all years, but the max will be last month so will return the right result. You can play around with it if you want to get a set relating to last month only.

Note, ensure your ExtractDate is indexed (I would expect it to be anyway). Using subqueries can have an effect on performance which is resolved by suitable indexing