Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft SQL Server

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old January 31st, 2013, 04:07 PM
Karen426 Karen426 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2013
Posts: 2 Karen426 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
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
from sitehistory
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.

I have
Select count(site_id) as lastmonthcount, capture_date
from sitehistory
group by capture_date
having capture_date =max(capture_date)
order by capture_date desc

I get the following results
lastmonthcount capture_date
29918 09/30/2012
39048 08/31/2012
36768 07/31/2012
38689 06/30/2012

What I really want is just the top record.

Can someone help me? I know I am close just
missing something

Thanks
Karen

Reply With Quote
  #2  
Old February 26th, 2013, 01:17 AM
June7's Avatar
June7 June7 is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2010
Location: The Great Land
Posts: 256 June7 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 9 h 29 m 29 sec
Reputation Power: 4
Try:

Select TOP 1 capture_date, count(site_id) as lastmonthcount ...
__________________
I like to know if my suggestion helped you. One way to let me know is by clicking scales at top right corner of post.
Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft SQL Server > Count with max(date)


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap