Searching records by date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Searching records by date

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2011-10-06 : 22:27:14

Hello.

My site contains approximately 2000 records for places to visit in different cities. Each table row has a name (eg. "old farm") and a city field (eg. "Toronto") along with a "new" field which indicates if the location is new to the site (added in the last 15 days or less). People add locations and select the city it is located in (or nearest to it) so that people can search by city name. Beside each city is the number of locations in that city.

eg.
Toronto 3
Barrie 1

I used to manually count the records in a loop until EOF but with 2000+ records this was time consuming and non-practical. So I asked for your help to:

- count total location per city (held in the "closest" column) using a better method than a loop
- count the total NEW locations per city (by reading the "new" field for a "Y" status)

I then added a second statement to count the locations with new photos by reading the "new" column again for "U" status). Not sure if the two SUM commands could be combined or not - they need two seperate variables for results.

With some help from the members of SQLTeam forum I was able to successfully come up with the following SQL statement for my site.

strSQL = "SELECT closest, COUNT(*) AS CityCnt, SUM(CASE new WHEN 'y' THEN 1 ELSE 0 END) as NewCount,"
strsql = strsql & " SUM(CASE new when 'u' THEN 1 ELSE 0 END) as NewPics from sites GROUP BY closest ORDER by closest"

table:
closest location new
--------------------------
Toronto CN Tower y
Toronto Bay St. building y
Toronto Park Place u
Barrie the farm n
Barrie this place u

display results:
Barrie 2 [no new locations / 1 with new pics]
Toronto 3 [2 new locations/1 with new pics]

Barrie: citycnt = 2, newcount =0, newpics = 1
Toronto: citycnt = 3, newcount = 2, newpics = 1

The count(*) works perfect for counting the number of places per city but I'd like to simplify the procedure for displaying whether new photos or new locations exist in each city.

Currently I set the 'new' field to 'y' when a new location is added, then revert it back to 'n' after 15 days when it is no longer new. I do the same for when new photos are added to a location but I use 'u' instead of 'y'.

Y- yes its a new location, n-no it isn't new, u -updated with photos

However I am having to manually run a script daily to remove any 'y' or 'u' status of places older than 15 days. This keeps the new results showing only freshly added locations or photos.

Each row also just so happens to contain a date field with the date the place was added and the date any new photos were added.

closest location new created picsadded
--------------------------------------------
Toronto CN Tower y 9/10/2010 9/3/2011
Toronto Bay St. building y 8/11/2010 9/12/2011
Toronto Park Place u 8/12/2011 8/12/2011
Barrie the farm n 5/3/2011 5/4/2011
Barrie this place u 6/5/2011 6/5/2011

So is there a way to count the number of locatons per city (count * grouped by closest) and also count the number of locations per city where the date is less than 15 days prior?

This would allow me to remove the 'new' column entirely and no longer need to run a script. I could also allow visitors to select how many days they'd like to search back.

Thank you.

oap
Yak Posting Veteran

60 Posts

Posted - 2011-10-06 : 23:45:50

I think I may have it :)

strSQL = "SELECT closest, COUNT(*) AS CityCnt,"
strsql=strsql & " SUM(CASE WHEN DateDiff(d, crdate, getdate()) <=13 THEN 1 END) as newcount"
strsql=strsql & ", SUM(CASE WHEN DateDiff(d, lastmod, getdate()) <=13 THEN 1 END) as newpics"
strsql = strsql & " from sites GROUP BY closest ORDER by closest"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-07 : 08:47:39

Hehe..so much effort into clarifying the problem and typing it out here on sqlteam only to solve it yourself a short while later :)

The solution you found is the way to go. personally I would add an "ELSE 0 END" to each of the case statements but that's just a personal preference. Good thing you've started to open your eyes towards the powerful world of sql! :)

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources