Counting and filtering by year

Question

Post reply

Counting and filtering by year

jguerra2

SSC Veteran

Points: 277
More actions
December 2, 2010 at 5:03 am

#157342

Okay. So what I'm trying to do here is Count the number of times we ordered a particular item. Then I want to break that up into 1 year , 3 year , 5 year intervals.
Here is the case statment I'm trying to work with:
COUNT(CASE WHEN [Year Purchased] > YEAR(DateAdd(Year, - 1, GetDate()))
THEN [# of PO] ELSE 0 END) AS [1yr PO Line Items],
COUNT(CASE WHEN [Year Purchased] > YEAR(DateAdd(Year, - 4, GetDate()))
THEN [# of PO] ELSE 0 END) AS [3yr PO Line Items],
COUNT(CASE WHEN [Year Purchased] > YEAR(DateAdd(Year, - 6, GetDate()))
THEN [# of PO] ELSE 0 END) AS [5yr PO Line Items]
Now, I know the [Year Purchased] and the Date add code works fine, because I have used it in previous queries. here is were the trouble lies I think:
[# of PO] = COUNT(Part) AS [# of PO] .
I want [# of PO] to count the number of times we ordered a part. In theory I guess it should work.. but I'm having trouble piecing it all together. Any advice or help would be appreciated. I can post more info if needed.
You can reach me on MSN:
jeffguerra@hotmail.com
or post here.
-Jeff

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could try

SUM(CASE WHEN [Year Purchased]>YEAR(DateAdd(Year, - 1, GetDate()))

THEN 1 ELSE 0 END) AS [1yr PO Line Items]

It might be a good idea to store the result of YEAR(DateAdd(Year, - 1, GetDate())) in a variable and use that variable in your query...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

jguerra2 SSC Veteran Points: 277 More actions · Answer 2

Your code worked. I understand it too now. Thanks alot.

-Jeff