January 31st, 2013, 05:07 PM
Join Date: Jan 2013
Time spent in forums: 35 m 16 sec
Reputation Power: 0
Count with max(date)
I have a historical data table where most of the data is captured once a month. The sites can change but most is the same.
I need to pull the count from only the most current month for a report and was using something basic:
Select count(site_id) as lastmonthcount
where capture_date = '09/30/2012'
Now I need to make it so I don't have to maintain the report every month and the capture date can pull the most recent counts.
Select count(site_id) as lastmonthcount, capture_date
group by capture_date
having capture_date =max(capture_date)
order by capture_date desc
I get the following results
What I really want is just the top record.
Can someone help me? I know I am close just