Max date/status problem

[belovedcej]

I've seen a few threads on this, but not exactly like what I need.

Here's a brief description:

Table1 columns:  EventDate, EventId, CaseID, typeID
Table2 columns:  typeID, statusID
Table3 columns:  statusID, statusDesc

I am creating a case statistics report.

Desired output:
TotalCases, (grouped by) StatusDesc

Parameter:
final "status" occurs between beginDate and EndDate

I tried starting with something like this:

select max(eventDate), caseID
from table1
inner join table2 on table2.typeID = table1.typeID
and statusID = 0
where table1.eventDate between @startDate and @endDate
group by caseID

But the problem with this is that I need the eventID in order to find out what the status is.

I can't use the max eventID because it is possible to enter things out of order.

also, I will need to make a determination of which status will count if two events occur in the same day.

So I can't even get to the counting yet.

Any thoughts please?

Thank you!

 

[SQLBill]

Can you provide sample data as it would look in each table and what results you desire?

_SQLBill

Posting advice: FAQ481-4875

 

[belovedcej]

Sure - I was trying to be succinct, but I guess I was cryptic!

Events:

EventDate  EventID  CaseID  TypeID
1/3/2007   1        1       1
1/4/2007   2        1       4
1/4/2007   3        1       2
1/5/2007   4        2       1
1/10/2007  5        2       3

EventTypes:

typeID  typeDesc         statusID
1       Case filed       1
2       Began research   2
3       meeting          2
4       remand           3

StatusTypes:

statusID statusDesc
1        NEW
2        OPEN
3        CLOSED

Let's say I want to report on the status between 1/1/2007 and 1/8/2007.

My desired result:

TotalCases    Status
1             NEW
1             CLOSED

Notice, case one will be counted as closed and not open, even though the secretary entered the events "backwards."

 

[SQLBill]

Test this:

SELECT COUNT(e.caseid) AS [TotalCases],
       st.statusdesc AS [Status]
FROM events e
  JOIN eventtypes et
    ON e.typeid = et.typeid
   JOIN statustypes st
     ON et.statusid = st.statusid
WHERE e.eventdate >= '20070101'
  AND e.eventdate <= '20070108'
GROUP BY st.statusid

-SQLBill


Posting advice: FAQ481-4875

 

[belovedcej]

Thanks for the reply - that's on the right track, but it's not addressing the main problem.

The sql you gave me counts every status type that occurs in the time period. So if a case is opened and closed in the same time period, it counts twice.

What I need is just the MAX status of the case. If it has been opened (say 1/1/2007) and closed (say 1/15/2007) in the same date range, only the closed status will count (because it has the later date.) That's why I was trying to do a Max(date) or something like that.

I could do a max id, and just tell the user if they enter things out of order it won't pick it up right, but I hate to do that.

 

[mharroff]

The problem I see from the data you posted is that a case may have more than 1 entry on a given date. That being said, you must be sure that you are retrieving the max(statusid) for the given case during that time period.

I believe the following code will work for what you are after.

Declare @StartDate datetime
Declare @Enddate datetime
Set @StartDate = '20070101'
SET @EndDate = '20070108'

SELECT Count(s.StatusDesc) AS TotalCases
      , s.statusdesc AS Status
	FROM events e
	  JOIN eventtypes t
	    ON e.typeid = t.typeid
	   JOIN statustypes s
	     ON t.statusid = s.statusid
	WHERE e.eventdate >= @StartDate
	  	AND e.eventdate <= @Enddate
	 	AND s.statusid =
			 (Select Max(s1.StatusID)
				FROM events e1
				  	JOIN eventtypes t1
				   	ON e1.typeid = t1.typeid
				  	JOIN statustypes s1
				   	ON t1.statusid = s1.statusid 
					WHERE e1.CaseID = e.caseID 
							AND e1.eventdate >= @StartDate
						  	AND e1.eventdate <= @Enddate
					GROUP BY e1.CaseID) 
	GROUP BY s.StatusDesc

 

[belovedcej]

Thanks - that doesn't work either. There is no guaruntee that the statusIDs go "in order".

I want the maximum event date for a given case, but still know what the event ID is. If I have two or more dates, I would then narrow it down based on the event_id.

I guess I can't do this in a single select statement. I'll post my solution when I find it.

I have done this easily before when I go by the maximum eventID instead of the maximum date. If I have to, that's what I will do - and I'll just tell the users to be sure to enter things "in order."

 

[mflancour]

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY CaseID ORDER BY EventDate DESC) AS sequence
FROM Events
) a
WHERE sequence = 1

 

[mharroff]

SELECT Count(s.StatusDesc) AS TotalCases
		, s.statusdesc AS Status
	FROM #events e
	  JOIN #eventtypes t
	    ON e.typeid = t.typeid
	   JOIN #statustypes s
	     ON t.statusid = s.statusid
	WHERE  
		 s.statusid =
			 (Select Top 1 max(s1.StatusID)
				FROM #events e1
				  	JOIN #eventtypes t1
				   	ON e1.typeid = t1.typeid
				  	JOIN #statustypes s1
				   	ON t1.statusid = s1.statusid 
					WHERE e1.CaseID =  e.caseid
							AND e1.eventdate >= @StartDate
						  	AND e1.eventdate <= @Enddate
					GROUP BY e1.CaseID
							, s1.statusID
					ORDER BY Max(e1.EventDate) DESC
							, s1.StatusID DESC)
	GROUP BY s.StatusDesc