filtering quarterly dates

[striker73]

I have a question that's more logical than technical. I have a query that has the following information:

Year Quarter Amount Customer
2000 1 $40,000 1
2000 2 $45,000 1
2000 3 $30,000 1
2000 4 $25,000 1
2000 1 $17,000 2

I want to filter this query and say only display the entries between 2nd Quarter 2000 and 4th Quarter 2000. What would be the syntax for my sql statement?

I'm coming up with:

([Quarter] Between 2 and 4) And ([Year] Between 2000 and 2000)

But this won't work for restrictions that cover more than one year. Example: Between 2nd Quarter 2000 and 3rd Quarter 2001

I'm not sure how to incorporate the quarter and the year. Any help would be great. Thanks!!!

 

[Databaseguy]

If it's ust for one year, then add a like "*1" (for 2001) to the criteria in your year field.

Otherwise, store or conconcenate a real date field and use the datepart() function to determine the quarter

 

[Ziggurat]

Hi striker73

I have just thought of one way that you might achieve this and that is to concatenate the year and the quarter to form one value and then select between the two values for your data
eg
2000 & 1=20001
2000 & 2=20002
2000 & 3=20003
2000 & 4=20004
2001 & 1=20011
2001 & 2=20012
2001 & 3=20013
2001 & 4=20014

to select between 2nd Quarter 2000 to 3rd Quarter 2001 use
select ([Year]&[Quarter]) >=20002 and ([Year]&[Quarter])<=20013

Have fun

*
***
*****
*******
Ziggurat