March 17, 2010 at 10:50 am
Hello, I have been trying to figure this out.
I have one report wherein are two CASE Statements
The first statement is like this:
CASE WHEN UseStartDate = 1
THEN CustStartDate
ELSE dbo.GetNewestOrderDate(CustomerID)
END As UseThisDate
The second CASE Statement uses the results of this first statement:
CASE
WHEN dbo.DateOnly(UseThisDate) >= dbo.DateOnly(ISNULL(CustStartDate,UseThisDate)) AND DATEDIFF(m, UseThisDate, getdate()) <= 13 THEN 0
WHEN dbo.DateOnly(UseThisDate) >= dbo.DateOnly(ISNULL(CustStartDate,UseThisDate)) AND DATEDIFF(m, UseThisDate, getdate()) BETWEEN 14 AND 26 THEN 1
ELSE 2
END As NumberValue
Can someone show me how I can use these two statements in one query? Or, if there is a different way to get the "NumberValue" results?
Thank you in advance for your time and expertise!
March 17, 2010 at 12:16 pm
You can nest Case statements. You could do this:
CASE
WHEN
CASE
WHEN UseStartDate = 1
THEN CustStartDate
ELSE dbo.GetNewestOrderDate(CustomerID)
END >= dbo.DateOnly(ISNULL(CustStartDate,UseThisDate)) AND DATEDIFF(m, UseThisDate, getdate()) <= 13
THEN 0
WHEN
CASE
WHEN UseStartDate = 1
THEN CustStartDate
ELSE dbo.GetNewestOrderDate(CustomerID)
END >= dbo.DateOnly(ISNULL(CustStartDate,UseThisDate)) AND DATEDIFF(m, UseThisDate, getdate()) BETWEEN 14 AND 26
THEN 1
ELSE 2
END As NumberValue
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2010 at 12:18 pm
As far as replacing the code with something more efficient, I'd need to know what the UDFs do and have an actual table structure and some sample data. Given those things, I could almost certainly help you make this more efficient.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply