Selective data from a table

[jkfeagle]

This sounds like a amateurish question but here goes anyway. I am trying to get the records from a table with entries for every day of the year up until present. What I want is the last entry for each month and all of the current month entries. I have considered several possible avenues but seem to hit a snag with each. Before I spend a lot of time going down the wrong road, has anybody tried something similar to this with any success?

 

[Jon K]

Try this query:

SELECT *
FROM TableName
WHERE DateField in (Select max(DateField) from TableName group by month(DateField)) or month(DateField)=month(Date())
ORDER BY DateField;

 

[jkfeagle]

Jon,

Thanks! That code worked great ....except it uncovered another problem I have. When the last day of the month falls on a weekend, there are no entries. Is there any way to modify the code the look for the last entry of the month with data? Thanks again!

 

[Jon K]

You can add:
where not isnull(FieldName)

in the subquery to exclude records that have no data in the field.

The query becomes:

SELECT *
FROM TableName
WHERE DateField in (Select max(DateField) from TableName where not isnull(FieldName) group by month(DateField)) or month(DateField)=month(Date())
ORDER BY DateField;

 

[jkfeagle]

Worked great Jon!! Thanks for your help!