February 22, 2006 at 10:22 am
I am working with an application that can have mutiple exams. When there are multiple exams I need to populate a attribute in result showing the date of the last failed exam. I began to attempt to right a CASE statement but I have been failing.
Here is the layout of the result set:
ApplicationId ExamID DateOfExam DateFailed APIExamSequence NumOfApps
31992 70249 1999-11-27 2005-01-27 1 2
31992 230038 2005-02-27 2005-01-27 2 2
In the above example I need the second row column date failed to be equal to the dateofexam column.
Any help is apprecatied.
February 22, 2006 at 10:28 am
Whats your ddl ?
Mathew J Kulangara
sqladventures.blogspot.com
February 22, 2006 at 11:12 am
Here is what I had:
This is where I started to write the case statement.
Select top 5
A.ApplicationId,
A.Id as ExamId,
DateOfExam,
B.DateStartEffectiveEligibility,
/*'DateEffectiveFailed' = CASE
WHEN NumOfApps = 1 And APIExamSequence = 1 THEN B.DateStartEffectiveEligibility
WHEN NumOfApps >1 And APIExamSequence = 2 THEN A.DateOfExam
Else getdate()
End,*/
APIExamSequence,
AppCount.NumOfApps
From CW_Exams A
Inner Join CW_Applications B on A.ApplicationId = B.Id And B.DateStartEffectiveEligibility > '2004-12-31' And
DateOfExam is not null
Inner Join (Select ApplicationId, Count(*) as NumOfApps
From CW_Exams A
Inner Join CW_Applications B on A.ApplicationId = B.Id And B.DateStartEffectiveEligibility > '2004-12-31' And
DateOfExam is not null
Group by ApplicationId) AppCount on A.ApplicationId = AppCount.ApplicationId
INNER JOIN (SELECT OrderId, InstanceId
FROM EC_OrderItems
WHERE InstanceId <> 0
GROUP BY OrderId, InstanceId) C ON B.ID = C.InstanceId
INNER JOIN EC_Orders D ON C.OrderId = D.ID
--WHERE DateOfExam < DateStartEffectiveEligibility
Order by A.ApplicationId
February 22, 2006 at 1:31 pm
Try something like this:
Select top 5
A.ApplicationId,
A.Id as ExamId,
DateOfExam,
B.DateStartEffectiveEligibility,
IsNULL(Failure.LatestFailure, B.DateStartEffectiveEligibility) as DateEffectiveFailed
APIExamSequence,
AppCount.NumOfApps
From CW_Exams A
Inner Join CW_Applications B on A.ApplicationId = B.Id And B.DateStartEffectiveEligibility > '2004-12-31' And
DateOfExam is not null
Inner Join (Select ApplicationId, Count(*) as NumOfApps
From CW_Exams A
Inner Join CW_Applications B on A.ApplicationId = B.Id And B.DateStartEffectiveEligibility > '2004-12-31' And
DateOfExam is not null
Group by ApplicationId) AppCount on A.ApplicationId = AppCount.ApplicationId
Left Outer Join (Select ApplicationId, max(DateOfExam) as LatestFailure
From CW_Exams A
Inner Join CW_Applications B on A.ApplicationId = B.Id And B.DateStartEffectiveEligibility > '2004-12-31' And
DateOfExam is not null and FAILURE_INDICATOR = 1
Group by ApplicationId) Failure on A.ApplicationId = Failure.ApplicationId
INNER JOIN (SELECT OrderId, InstanceId
FROM EC_OrderItems
WHERE InstanceId <> 0
GROUP BY OrderId, InstanceId) C ON B.ID = C.InstanceId
INNER JOIN EC_Orders D ON C.OrderId = D.ID
--WHERE DateOfExam < DateStartEffectiveEligibility
Order by A.ApplicationId
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply