I'd like to select information from the current year for each customer.
If there is no information for the current year, I'd like to move to the earlier year range.
The current year changes after July 1st. If you updated your information right now, it would be tagged with "2004-2005".
If you updated your information after July 1st, 2005, your info would be tagged with "2005-2006"
We'll need to select the "most current" information and we'll have to do this year after year.
Table and data Examples
My query would be:
"Show me info for the current year (2004-2005)"
And the dataset would look like this:
I know I can do it with IF EXISTS statements, but I would have to make a lot of IF conditions to handle every date range that's possible. Our date range for info begins @ 1999-2001 and will keep going up.
I was thinking of making a table of date ranges and a sortable value:
And using that to feed my IF conditions (but I'm not a good programmer!)..
I am really new at grabbing something like this and my code would be sooooo bloated if I had to write it out with a condition for each year. I'm hoping that I could write something that iterated [in descending order] through the tblYears table to populate my IF EXISTS and then select when it exists.
I hope this wasn't too confusing and that my data examples have helped. There is a lot to this, but if I can get this it's almost the same for most information tables. Except where I need to take X # of current years info and show it.
Thanks and any suggestions would be super helpful!
If there is no information for the current year, I'd like to move to the earlier year range.
The current year changes after July 1st. If you updated your information right now, it would be tagged with "2004-2005".
If you updated your information after July 1st, 2005, your info would be tagged with "2005-2006"
We'll need to select the "most current" information and we'll have to do this year after year.
Table and data Examples
Code:
tblMain
Cust_Id Name
---------------------
1 Daves
2 Bobs
3 Sams
tblInfo
Row_Id Cust_Id Data Year
---------------------------------
1 1 Oranges 2003-2004
2 1 Apples 2004-2005
3 2 Bananas 2003-2004
4 2 Grapes 2004-2005
5 3 Muffins 1999-2000
6 3 Oats 2001-2002
My query would be:
"Show me info for the current year (2004-2005)"
And the dataset would look like this:
Code:
Cust_Id Name Data Year
---------------------------------
1 Daves Apples 2004-2005
2 Bobs Grapes 2004-2005
3 Sams Oats 2001-2002
I know I can do it with IF EXISTS statements, but I would have to make a lot of IF conditions to handle every date range that's possible. Our date range for info begins @ 1999-2001 and will keep going up.
I was thinking of making a table of date ranges and a sortable value:
Code:
tblDates
Id Year
-------------------
1 1999-2000
2 2000-2001
3 2001-2002
4 2002-2003
5 2003-2004
6 2004-2005
And using that to feed my IF conditions (but I'm not a good programmer!)..
I am really new at grabbing something like this and my code would be sooooo bloated if I had to write it out with a condition for each year. I'm hoping that I could write something that iterated [in descending order] through the tblYears table to populate my IF EXISTS and then select when it exists.
I hope this wasn't too confusing and that my data examples have helped. There is a lot to this, but if I can get this it's almost the same for most information tables. Except where I need to take X # of current years info and show it.
Thanks and any suggestions would be super helpful!