2 more queries for dates

Aleks
Aleks used Ask the Experts™
on
I have a table with news, one of the fields is 'date'.

I am using ASP/VBscript. and MS SQL 2008.

I need two queries.

First one will group all news posted in previous years by year display the year and the number of news which have that year. So for example it will display, this ONLY for the previous 3 years


2011 (34)
2010 (21)
2009 (11)

OLDER (74)

Second query will display the total of all news older than the past three years (current doesnt count), example above would be any from Dec 31st, 2008 and before then.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
Something like this:
DECLARE @ToDate date = DATEADD(DAY, -DATEPART(dayofyear, GETDATE()), GETDATE())
DECLARE @FromDate date = DATEADD(year, -3, @ToDate)

SELECT YEAR([date]), COUNT(*)
FROM NewsTable
WHERE [date] > @FromDate AND [date] <= @ToDate
GROUP BY YEAR([date])

SELECT 'OLDER', COUNT(*)
FROM NewsTable
WHERE [date] <= @FromDate

Open in new window

Author

Commented:
Lets try this ... since I can't run the above inside Dreamweaver to create a recordset.

I have the following sql :

SELECT Year([NewsDate])as year, Count(1) AS newscount
FROM WebsiteNews
WHERE Year([NewsDate]) < Year (getdate()) group by Year([Newsdate])
ORDER BY year desc

it returns :

2011      10
2010      11
2009      12
2008      7
2007      6
2006      5
2005      6
2002      1
2000      1
1999      1

Can we modify the above sql and separate it in two, one that will return only the top 3 years (2011,2010 and 2009) separate like they are.  
And the second statement to group all the others into one call it 'older' and display the total .. ?  
So .. previous three years in one statement, older than 3 years back in one group.
Top Expert 2012
Commented:
since I can't run the above inside Dreamweaver to create a recordset
Sure you can.  You can even wrap it in a Stored Procedure.

But if you insist, try it this way (it just won't be as a efficient):

SELECT  YEAR([NewsDate]) [year],
        COUNT(*) newscount
FROM    WebsiteNews
WHERE   [NewsDate] > CAST(DATEADD(year, -3, DATEADD(DAY, -DATEPART(dayofyear, GETDATE()), GETDATE())) AS date)
        AND [NewsDate] <= CAST(DATEADD(DAY, -DATEPART(dayofyear, GETDATE()), GETDATE()) AS date)
GROUP BY YEAR([Newsdate])
ORDER BY year DESC


SELECT  'OLDER',
        COUNT(*)
FROM    WebsiteNews
WHERE   [NewsDate] <= CAST(DATEADD(year, -3, DATEADD(DAY, -DATEPART(dayofyear, GETDATE()), GETDATE())) AS date)

Open in new window

Author

Commented:
PERFECT !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial