?
Solved

Case Statment

Posted on 2008-10-30
3
Medium Priority
?
165 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

762 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