Solved

# Case Statment

Posted on 2008-10-30
151 Views
I keep getting an error in this case statement.  Any help would be appreciated:

``````CASE

WHEN DATEDIFF(dd,AL2.AU_TIME,AL1.AU_TIME)> 0 THEN DATEDIFF(dd,AL2.AU_TIME,AL1.AU_TIME) "DAYS_IN_STATUS"

WHEN DATEDIFF(hh,AL2.AU_TIME,AL1.AU_TIME)> 0

AND DATEDIFF(dd,AL2.AU_TIME,AL1.AU_TIME)< 0 THEN DATEDIFF(hh,AL2.AU_TIME,AL1.AU_TIME) "DAYS_IN_STATUS"

WHEN DATEDIFF(mm,AL2.AU_TIME,AL1.AU_TIME)> 0

AND DATEDIFF(dd,AL2.AU_TIME,AL1.AU_TIME)< 0

AND DATEDIFF(hh,AL2.AU_TIME,AL1.AU_TIME) < 0 THEN DATEDIFF(mm,AL2.AU_TIME,AL1.AU_TIME) "DAYS_IN_STATUS"

WHEN DATEDIFF(ss,AL2.AU_TIME,AL1.AU_TIME)> 0

AND DATEDIFF(dd,AL2.AU_TIME,AL1.AU_TIME)< 0

AND DATEDIFF(hh,AL2.AU_TIME,AL1.AU_TIME) < 0

AND DATEDIFF(mm,AL2.AU_TIME,AL1.AU_TIME) < 0 THEN DATEDIFF(ss,AL2.AU_TIME,AL1.AU_TIME) "DAYS_IN_STATUS"

END AS ID
``````
0
Question by:daintysally
• 2

LVL 60

Accepted Solution

chapmandew earned 500 total points
ID: 22843523
select CASE
WHEN DATEDIFF(dd,AL2.AU_TIME,AL1.AU_TIME)> 0 THEN DATEDIFF(dd,AL2.AU_TIME,AL1.AU_TIME)
WHEN DATEDIFF(hh,AL2.AU_TIME,AL1.AU_TIME)> 0
AND DATEDIFF(dd,AL2.AU_TIME,AL1.AU_TIME)< 0 THEN DATEDIFF(hh,AL2.AU_TIME,AL1.AU_TIME)
WHEN DATEDIFF(mm,AL2.AU_TIME,AL1.AU_TIME)> 0
AND DATEDIFF(dd,AL2.AU_TIME,AL1.AU_TIME)< 0
AND DATEDIFF(hh,AL2.AU_TIME,AL1.AU_TIME) < 0 THEN DATEDIFF(mm,AL2.AU_TIME,AL1.AU_TIME)
WHEN DATEDIFF(ss,AL2.AU_TIME,AL1.AU_TIME)> 0
AND DATEDIFF(dd,AL2.AU_TIME,AL1.AU_TIME)< 0
AND DATEDIFF(hh,AL2.AU_TIME,AL1.AU_TIME) < 0
AND DATEDIFF(mm,AL2.AU_TIME,AL1.AU_TIME) < 0 THEN DATEDIFF(ss,AL2.AU_TIME,AL1.AU_TIME)
END AS ID
from tablename
0

Author Comment

ID: 22843529
I'm trying to compare records against one another with different date timestamps.
0

LVL 60

Expert Comment

ID: 22843776
You're statement was great...just needed to take out your "alias"
0

## Featured Post

### Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

#### Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!