Solved

Case Statment

Posted on 2008-10-30
3
157 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 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