Conditional date testing... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional date testing...

Author

Topic

lynda
Starting Member

21 Posts

Posted - 2009-06-10 : 14:33:45

I have some int values that get set by code in a web page based on user choice for month/day/year. One of their options is ALL for month or day.

In the query I have this:

CASE WHEN TMMUS.adminUserID = 4 AND (DATEPART (year,

CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@year
AND DATEPART (month,
CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@month
AND DATEPART (day,
CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@day
)
THEN 'YES'

WHEN TMMUS.adminUserID = 3 AND (DATEPART (year,

CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@year
AND DATEPART (month,
CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@month
AND DATEPART (day,
CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@day
)
THEN 'YES'
ELSE 'NO'
END AS isOnlineMMUS,

Currently, the server code will construct the DATEPART tests based on the user's selection or if they picked ALL it will omit that test.

Obviously it will always test for @year as the user cannot select ALL years :)

I want to change this so that SQL determines if it should do the test. If @month or @day are 0, it would omit the test. I have tried a few things here to get this to happen but no dice so I toss it out to you experts here for some advice!

Thanks!

Mike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-10 : 14:39:17

do you mean this?


DECLARE @Date datetime
SET @Date=CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate 
ELSE PAY.paymentDate 
END
...


CASE WHEN TMMUS.adminUserID = 4 AND DATEPART (year, @Date)=@year
AND (@month=0 OR  DATEPART (month, @Date)=@month)
AND (@day=0 OR DATEPART (day, @Date)=@day)
THEN 'YES' 
....

lynda
Starting Member

21 Posts

Posted - 2009-06-10 : 14:49:33

Well, let my try to simplify this a bit.

-------- This section ALWAYS happens -----------

WHEN TMMUS.adminUserID = 3 AND (DATEPART (year,

CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@year

-------- This section ONLY happens if @month > 0 -----------

AND DATEPART (month,
CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@month

-------- This section ONLY happens if @day > 0 -----------

AND DATEPART (day,
CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@day
)

-------- This section ALWAYS happens -----------

THEN 'YES'
ELSE 'NO'
END AS isOnlineMMUS,

lynda
Starting Member

21 Posts

Posted - 2009-06-11 : 10:49:47

Wow! Did I stump the panel?

:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 11:24:00

did you try what i suggested before?


WHEN TMMUS.adminUserID = 3 AND (DATEPART (year,

CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@year
AND (@month=0 OR DATEPART (month,
CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@month)
AND (@day=0 OR DATEPART (day,
CASE WHEN PAY.paymentTypeId = 2 AND T.transactionTypeID = 9 -- 'Enter Refund Check Number'
THEN T.transactionDate
ELSE PAY.paymentDate
END)=@day)
)
THEN 'YES'
ELSE 'NO'
END AS isOnlineMMUS,

lynda
Starting Member

21 Posts

Posted - 2009-06-11 : 11:57:35

Ahhh ok - but shouldn't it be AND (@month>0 AND DATEPART (month,

Instead of =0 OR or am I missing something?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 12:31:03

quote:
Originally posted by lynda

Ahhh ok - but shouldn't it be AND (@month>0 AND DATEPART (month,

Instead of =0 OR or am I missing something?

no it should be or...so that it bypasses the datepart condition when value is 0

lynda
Starting Member

21 Posts

Posted - 2009-06-11 : 12:33:48

But it should test for > 0 and not = 0 correct?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 12:36:02