Dynamic CASE statement based on list of dates

I have the following table of data. I need to take a date from a large table and do the following case:
CASE
When date < date(0)
Then '0'
When date between date(0) and date(1)
Then '1'
When date between date(1) and date(2)
Then '2'
When date >= date(3)
Then '3'
What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)
I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.
thanks
Milton

Dates TableDate4/1/20031/1/20064/2/2007
Fee Table

DatePeriodClassFee1DailyTrue3291Half DayTrue1781OTTrue491HourlyTrue411DailyFalse1561Half DayFalse861OTFalse271HourlyFalse192DailyTrue3552Half DayTrue1922OTTrue502HourlyTrue442DailyFalse1712Half DayFalse922OTFalse282HourlyFalse213DailyTrue3643Half DayTrue1973OTTrue513HourlyTrue453DailyFalse1753Half DayFalse943OTFalse293HourlyFalse21

If you're using SQL Server 2005, you could write something like this. It combines two of the new features of SQL Server 2005: ROW_NUMBER T-SQL Function, and COMMON TABLE EXPRESSIONS (CTE).

--------------------------------------
USE NORTHWIND
GO

WITH CTE_ALL_DATES (ID, DATE)
AS (
SELECT ROW_NUMBER()OVER (ORDER BY DATEASC)AS ID
, DATE
FROM (
SELECTCAST('1995-01-01'AS DATETIME) DATE
UNION
SELECTCAST('1996-08-01'AS DATETIME)
UNION
SELECTCAST('1997-04-01'AS DATETIME)
UNION
SELECTCAST('1998-03-01'AS DATETIME)
UNION
SELECTCAST('2000-01-01'AS DATETIME)
) T1
)
SELECT D1.ID
, D1.DATE
, O.*
FROM ORDERS O
JOIN CTE_ALL_DATES D1
ON D1.DATE <= O.[ORDERDATE]
--------------------------------------

You would just have to replacee all the UNION, UNION, UNION with your Dates table. You can test out this code against Northwind database.

Hope this helps,

|||

Ok, thanks. I am using sql server 2000.

|||

SQL Server 2000 Version, it is not as simple as the 2005 version but it works, you should probably encapsulate it in a stored procedure.

--

USE NORTHWINDGO-- CREATE TEMP, FOR DATES NUMBERINGCREATE TABLE #INDEX_ALL_DATES(IDINT IDENTITY(1,1) ,DATESMALLDATETIME )CREATE CLUSTERED INDEX IX_INDEX_ALL_DATES_DATE_IDON #INDEX_ALL_DATES(DATE)INSERT #INDEX_ALL_DATES(DATE)SELECT DATEFROM (-- REPLACE THIS WITH YOUR SELECT * FROM DATES TABLESELECTDATEADD(M, -3,MIN([ORDERDATE])) DATEFROM ORDERSUNIONSELECTDATEADD(M, -3,DATEADD(DAY, (DATEDIFF(DAY,MAX([ORDERDATE]),MIN([ORDERDATE])))/2,MIN([ORDERDATE])))FROM ORDERSUNIONSELECTDATEADD(M, -3,MAX([ORDERDATE]))FROM ORDERS-- REPLACE THIS WITH YOUR SELECT * FROM DATES TABLE)AS T1ORDER BY DATEASC SELECT D1.ID, D1.DATE, O.*FROM ORDERS OJOIN #INDEX_ALL_DATES D1ON D1.DATE <= O.[ORDERDATE]-- CLEAN UPDROP TABLE #INDEX_ALL_DATES--

Hope this helps,