Solved

Case Statment

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Modifying SQL 2008/2012 PARTITIONS 3 55
Why does this keep coming up NULL? 2 45
SQL Server Remove all trailing commas 10 72
Passing value to a stored procedure 8 93
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 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

20 Experts available now in Live!

Get 1:1 Help Now