Selecting "most current info" without using a date datatyped field. 2

DSect · Sep 8, 2004

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

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!

davejam · Sep 9, 2004

you could get the month from the date
month(date)
then check to see if its less than 7 (july)
if month(date) < 7 then
then you can get the years for the previous year and current year
searchVal = year(date) - 1 & " - " & year(date)
else (if month is july or above)
searchVal = year(date) & " - " & year(date) +1

think this is the easiest way.... not sure if all the syntax works with what your writing but the logic is there!!!

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

davejam · Sep 9, 2004

sorry me confused, didn't read your q properly, i'll have a look and try again

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

davejam · Sep 9, 2004

On your query you could link on cust_id and group_ID on cust_id and select the max record for year

sorry just an idea

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

spence74 · Sep 9, 2004

If you are using stored procedures to hold your querys, try this:

Code:

DECLARE @CurYear as char(4)
DECLARE @Years   as char(9)

SET @CurYear = DATEPART(YEAR, GETDATE())

IF DATEDIFF(DAY, GETDATE(),'7/1/' + @CurYear) < 0
    BEGIN
        --Date is before July 1st of this year
        SET @Years = CAST(DATEPART(YEAR, DATEADD(YEAR, -1, GETDATE())) as char(4)) + '-' + @CurYear
    END
ELSE
    BEGIN
        --Date is after July 1st of this year
        SET @Years = @CurYear + '-' + CAST(DATEPART(YEAR, DATEADD(YEAR, 1, GETDATE())) as char(4))
    END

The code above will build a char string with the correct value based on the current date. Use @Years in the WHERE criteria. You may have to convert your Year field to char(9) to work with the string builder above

Code:

SELECT ... 
FROM ... 
WHERE CAST(Year as char(9)) = @Years ...
....

You could also pass in a date (as datetime) and replace the GETDATE()s with the passed in variable if you need to check a specific date that may not be today.

I hope this helps!

DSect · Sep 9, 2004

I'm a little confused at the responses. I think it's because you are thinking "current" info has something to do with a DATE datatype or something to do with DATES in general.

Please look at the data table examples and the desired results of the query and you will see that what I'm doing doesn't involve "dates" or "date datatypes". The YEAR column could very well be a singe digit int representing version # and I could say - give me data for version # 3, etc..

I'm going to tell the query what "year" I want info. for - this will never be automatically pulled and will always be in the format of xxxx-xxxx representing a fiscal year.

This database is used to assemble a publication. For each customer record we have information about various things. Each one of these things are updated for a certain "year range".

So, if I said -

"Give me 2002-2003 info for Bobs" there would be no results.

If I said -

"Give me 2003-2004 info for Bobs" it would yield one row.

I just need to select the LATEST date range without going over. So if I say - give me 2004-2005 info - I will get:
2004-2005 info - if no 2004-2005 info then
get 2003-2004 info - if no 2003-2004 info then
get 2002-2003 info... etc..

Hope that helps -

As you can see above ^^ I could do it with a lot of IF statements, but that seems crazy and impossible to maintain and update as more and more year ranges are added..

Thanks!!

ttba · Sep 9, 2004

DSect,

How about something like this. Should be able to easily adapt it if you want to get all users rather than just one at a time.

ttba

CREATE PROCEDURE stp_Get_Info
(
@Cust_ID INT,
@DateRange nvarchar(9)
)
AS
SET NOCOUNT ON

DECLARE @LeftYear INT, @ClosestYear INT

SET @LeftYear = Left(@DateRange, 4)

SELECT DISTINCT TOP 1 @ClosestYear = Left(Year, 4)
FROM tblInfo
WHERE Cust_ID = @Cust_ID AND Left(Year, 4) <= @LeftYear
ORDER BY Left(Year, 4) DESC

SELECT *
FROM tblInfo
WHERE Cust_ID = @Cust_ID AND Left(Year, 4) = @ClosestYear

RETURN

spence74 · Sep 9, 2004

Is tblDates something that you already have built? You should be able to do without unless you need it elsewhere in your system.

If you do not need tblDates and just built it to work around this issue, try this:

Code:

<procedure declaration>
(--input parameters
...
@Year     char(4)
...
)
...
    SELECT ...
    FROM ...
    WHERE (SUBSTRING(tblInfo.Year, 1, 4) = @Year OR SUBSTRING(tblInfo.Year, 5, 4) = @Year) ...
    ORDER BY ...
...
END

otherwise, if you do need tblDates and tblInfo.Year is an integer, something like this could be used:

Code:

<procedure declaration>
(--input parameters
...
@Year     char(4)
...
)
...

    SELECT ...
    FROM ...
    WHERE tblInfo.Year IN (SELECT tblDates.Id
                           FROM tblDates
                           WHERE (SUBSTRING(tblDates.Year, 1, 4) = @Year OR SUBSTRING(tblDates.Year, 5, 4) = @Year))
    ORDER BY ...
...
END

Either of these examples should give you all records you select with the passed in year in the date range. Some CASTing or CONVERTing may be necessary to get this to work, depending on the datatypes you have your fields set for.

DSect · Sep 9, 2004

spence - that tblDates is not real. I made that as an example. It was meant to be a way to define which is the high and low of those date ranges.

I guess as a varchar, the date would have sorted ok (1999 to 2005) but I figured if I made a table like that I could do something useful, but I don't know how to do it.

Like here's the LAMEST way I could do this whole thing:

Code:

-- This is a mix of pseudo code & real stuff.


IF EXISTS (SELECT Cust_ID 
           FROM tblDATA 
           WHERE Year = "2004-2005")
BEGIN
-- Do my select maybe populate a variable so I can tell if I fetched a row
SET @MyVar = 1
END

IF @MyVar > 0 then
BEGIN
IF EXISTS (SELECT Cust_Id
                FROM tblData
                WHERE Year = "2003-2004")
BEGIN 
-- Do My select, etc..
SET @MyVar = 1
END
END

I'm going to have to mess with ttba's thinger. It looks good but I'm still learning so I'll need to take it piece by piece..

So thanks for the ideas.

I think you guys are seeing where the tblDates comes into play? I was hoping to be able to do something like:

IF EXISTS (SELECT bah from blah WHERE YEAR = (SELECT YEAR FROM tblDates ORDERBY Row_Id DESC))

That way - the highest row is the latest and it step down through the dates until it hits a match..

heh - I'm still a little confused, because I'm hoping there's ways to do this without doing it super duper longhand, like a newbie would do (myself!).

Thanks again guys - Am I making sense and can you see what I'm trying to do? It seems simple, but without the data tables YEAR being a dattime field, I have to do a little extra to grab the most current info.

Thanks again as always!!

ttba · Sep 9, 2004

DSect,

Another alternate way of thinking about this could possibly be related to the Row_ID. If it is an identity field and always in chronological order then you could probably drum up some creative ideas sorting by Row_ID (desc for instance) and use a cursor.

However, this would be significantly more difficult and slower than what I posted before.

Good Luck,
ttba

ESquared · Sep 9, 2004

Code:

SELECT
      M.Cust_ID,
      I.Name,
      I.Data,
      I.Year
   FROM tblMain M
      INNER JOIN tblInfo I ON M.CustID = I.Cust_ID
      INNER JOIN (
         SELECT
            Cust_ID, Max(Year)
            FROM tblInfo
            GROUP BY Cust_ID
      ) Y ON M.Cust_ID = Y.Cust_ID

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

DSect · Sep 9, 2004

Thanks guys!

It helped solve another issue also!

ESquared · Sep 9, 2004

Oops, my query was missing something... sorry!

Code:

SELECT
      M.Cust_ID,
      I.Name,
      I.Data,
      I.Year
   FROM tblMain M
      INNER JOIN tblInfo I ON M.CustID = I.Cust_ID
      INNER JOIN (
         SELECT
            Cust_ID,
            [blue]MYear = [/blue]Max(Year)
            FROM tblInfo
            GROUP BY Cust_ID
      ) Y ON M.Cust_ID = Y.Cust_ID [blue]AND Y.MYear = I.Year[/blue]

And if you want an arbitrary number of current years info (top 3 here):

Code:

SELECT
      M.Cust_ID,
      I.Name,
      I.Data,
      I.Year
   FROM tblMain M
      INNER JOIN tblInfo I ON M.CustID = I.Cust_ID
      INNER JOIN (
         [blue]SELECT
            I1.Cust_ID, I1.Year, Cnt = Count(*)
         FROM tblInfo I1
            INNER JOIN tblInfo I2
            ON I1.Cust_ID = I2.CustID
            AND I1.Year >= I2.Year
         GROUP BY I1.Cust_ID, I1.Year[/blue]
      ) Y ON M.Cust_ID = Y.Cust_ID AND Y.MYear = I.Year
   [blue]WHERE
      Cnt <= 3[/blue]

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

DSect · Sep 16, 2004

I'll post the implementation soon.

ESquared!! That "arbitrary number" query was the next one I had to figure out.

Serious thanks to everyone in this. I have learned a lot and it will not be forgotten!!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Selecting "most current info" without using a date datatyped field. 2

DSect

Programmer

davejam

Technical User

davejam

Technical User

davejam

Technical User

spence74

Programmer

DSect

Programmer

ttba

Programmer

spence74

Programmer

DSect

Programmer

ttba

Programmer

ESquared

Programmer

DSect

Programmer

ESquared

Programmer

DSect

Programmer

Similar threads

Part and Inventory Search

Sponsor

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Selecting &quot;most current info&quot; without using a date datatyped field. 2

Programmer

Technical User

Technical User

Technical User

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor

Selecting "most current info" without using a date datatyped field. 2