Solved

Case Statment

Posted on 2008-10-30
3
151 Views
Last Modified: 2012-05-05
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

Open in new window

0
Comment
Question by:daintysally
  • 2
3 Comments
 
LVL 60

Accepted Solution

by:
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

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

Expert Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now