Conditions 1

[Snakeroot]

I'm working on adding a column to a temp table. I'd like it to be true/false. I would like to condition it from the field called CDTxnDate. (If the difference between the two dates is more than one month, then true, else false.) For now I've tried to simplify by just putting a static date in my datediff statement.

Here's the simplified version of what I'm TRYING to do:

   SELECT 
     *,RentalDaysOverMonth = 0
     IF (SELECT DATEDIFF(Month,'1/1/2007',GETDATE())) > 0  THEN RentalDaysOverMonth = 1 ELSE RentalDaysOverMonth = 0 END
   FROM ##TempOutstandingCylinders cd 
   ORDER BY CDFranchise,CDCustName,RentalDaysOverMonth

Here's what I'd eventually LIKE to do:

   SELECT 
     *,RentalDaysOverMonth = 0
     IF (SELECT DATEDIFF(Month,[highlight]cd.CDTxnDate[/highlight],GETDATE())) > 0  THEN RentalDaysOverMonth = 1 ELSE RentalDaysOverMonth = 0 END
   FROM ##TempOutstandingCylinders cd 
   ORDER BY CDFranchise,CDCustName,RentalDaysOverMonth

I can't seem to figure out how to do this. Can anyone help me out and point me in the right direction? Thanks!

 

[gmmastros]

Are you using Microsoft SQL Server, or is this an Access question?

For SQL Server, you cannot use IF within a query. You'll need to use case/when instead.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Snakeroot]

Oops, sorry, SQL 2000. That would explain a lot then I'll check out CASE/WHEN. Thanks!

 

[gmmastros]

Honestly... the IF within the query threw me off a bit. Sometimes people mistaken post Access questions in the SQL Server forum. This is the appropriate place to post a SQL 2000 question.

Anyway... I suggest you try...

[COLOR=blue]SELECT[/color] *,
       [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]DATEDIFF[/color]([COLOR=#FF00FF]Month[/color],[COLOR=#FF00FF]GetDate[/color](), cd.CDTxnDate)> 0 
            [COLOR=blue]THEN[/color] 1 
            [COLOR=blue]ELSE[/color] 0 
            [COLOR=blue]END[/color] [COLOR=blue]As[/color] RentalDaysOverMonth
[COLOR=blue]FROM[/color] ##TempOutstandingCylinders cd
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] CDFranchise,CDCustName,RentalDaysOverMonth

Notice how I structured the Case/When statement. I suggest you study this a bit. If this doesn't make sense, let me know and I will explain more.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Snakeroot]

Ahhh... thanks George! I had it all except for this part:

THEN 1
ELSE 0
END As RentalDaysOverMonth

I kept trying to put RentalDaysOverMonth = 1 after then then and =0 after the else.

Thanks for your help!