I want to be able to allow users to select a time unit, which will determine the DateAdd Datepart parameter.

In this instance:
Select the customerID where the customer's age in 50-65 within the time period that runs from @ReportRunDate back to (@ReportRunDate minus the number of time units). The CalcAge function returns an age given a birthdate. I would like the 'd' of the DateAdd Function to be able to be variable (d,w,n,y)... Any ideas?



Declare @MinAge INT, @MaxAge INT, @ReportRunDate DateTime, @UnitsPrior INT, @Units varChar(5)
Set @MinAge = 50
Set @MaxAge = 65
Set @ReportRunDate = '11/24/2009'
Set @UnitsPrior = 365
Set @Units = 'd'


Select CustomerID
From CustomerProfile
Where CalcAge(Birthdate, DateAdd(d,-@UnitsPrior,@ReportRunDate)) BETWEEN (@MinAge-1) AND (@MaxAge-1)
Group By CustomerID
Order By CustomerID

What about wrapping your WHERE clause in CASE statements for d, w, m, y? I'm supposing you'll have a drop-down for Interval and one for Number.

Myself, I'd probably simplify this and just use two sets of dates.