Generating the "last 4 quarters"

[Veejc]

Can anyone tell me if there is an easy way to do handle going through years of data to pick out only the last 4 quarters of data? I have a date on the field and I know I pick that apart and get todays date and go back so far to find out what quarter it is and do a bunch of casting and date functions to find out this information, but I am curious if I am taking the long way around, is there any good clean way to get this?

 

[vongrunt]

Probably. Post some sample data + expected results.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Veejc]

Site Quarter Year Datafield1 datafield2 datafield3
101 1 2002 test20021 test20021 test20021
101 2 2002 test20022 test20022 test20022
101 3 2005 test20053 test20053 test20053
101 3 2004 test20043 test20043 test20043
101 2 2005 test20052 test20052 test20052
101 1 2005 test20051 test20051 test20051
101 4 2004 test20044 test20044 test20044

From that, I would like to only return the data for the last 4 quarters, which would be q3 2005, q2 2005, q1 2005 and q4 2004

Does that make sense?

 

[Catadmin]

Do you want all four quarters of data summarized together or grouped by quarter?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Veejc]

sorry, grouped by quarter desc if I can be that picky

2005 03
2005 02
2005 01
2004 04

 

[Veejc]

so nobody has any ideas about this? I thought it would have been a more common task...

 

[vongrunt]

Awright then...

select *
from myTable
where ([Year]-1900)*4 + Quarter > 1+datediff(qq, 0, getdate()) -4
order by [Year] desc, Quarter desc

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[cognoscognos]

Not sure if this would work, but try using TOP (not sure of TOP syntax below) to get the top 4 rows of your result, after doing a group by year, Quarter desc (assuming you have Year and Quarter fields in your table as shown in sample data above)..

Try something like this:

select * from your_table A,
(select TOP 4 Year, Quarter from your_table
group by Year, Quarter
order by Year desc, Quarter desc) B
where A.Year = B.Year
AND A.Quarter= B.Quarter;

Thanks...