Avatar of Dale Fye
Dale FyeFlag for United States of America asked on

Get last record for each CaseID within a date range

I've inherited a database that contains a StaffOnCase table that looks like:

CaseID - Integer

Eff_Date - Date

Thru_Date - Date  (may be NULL if the combination of workers is still valid

WorkerID - Long

SuperID - Long

MngrID - Long

with data that might look like:

ID  CaseID  Eff_Date   Thru_Date    WorkerID  SuperID  MngrID
 213   1    06/01/2020  11/01/2020       1        2        3
 980   1    11/02/2020  03/15/2021       1        4        3
1325   1    03/16/2021    NULL           6        2        3

Open in new window

I have a report with parameters @StartDate and @EndDate, what I need is the most efficient way to find the Eff_Date, Thru_Date, WorkerID, and SuperID for the last record where the EffDate <= @EndDate AND [ThruDate] is >= @StartDate.  

So for a report with @StartDate = 10/2/2020 and @EndDate = 9/30/2021, the last record (#1325, not just the ID, the whole record) would be returned.

For a report with @StartDate = 1/1/2021 and @EndDate = 1/31/2021 the report should return record #980.


I can do this with something like:

SELECT SOC.*
FROM tblStaffOnCase as SOC
WHERE SOC.ID = (SELECT MAX ID FROM tblStaffOnCase
                WHERE tblStaffOnCase.CaseID = SOC.CaseID
                AND tblStaffOnCase.Eff_Date <= @EndDate
                AND (tblStaffOnCase.Thru_Date IS NULL OR
                     tblStaffOnCase.Thru_Date >= @StartDate))
GROUP BY SOC.CaseID

Open in new window

but this does not seem very efficient or elegant.  Just wondering whether there is a better way to do this using SQL Server 2014.

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Jim Horn

For the most part you had it.  Also one would think that CaseID would be in there..
SELECT *
FROM tblStaffOnCase 
WHERE ID = (
   SELECT Max(ID)
   FROM tblStaffOnCase
   WHERE [EffDate] >= @StartDate AND (ThruDate <= @EndDate OR ThruDate IS NULL)) 

Open in new window

ASKER
Dale Fye

Thanks, Jim.  Just wasn't sure if there was a more efficient or elegant method.  Aren't you the one that left out the CaseID?  Don't I need the CaseID in the Criteria for grouping purposes?
SELECT SOC.* 
FROM tblStaffOnCase as SOC 
WHERE SOC.ID = (SELECT MAX(ID) FROM tblStaffOnCase
                WHERE tblStaffOnCase.CaseID = SOC.CaseID
                  AND tblStaffOnCase.Eff_Date <= @EndDate
                  AND (tblStaffOnCase.Thru_Date IS NULL OR
                       tblStaffOnCase.Thru_Date >= @StartDate)) 
GROUP BY SOC.CaseID

Open in new window

slightwv (䄆 Netminder)

See if this works better for you.  It only accesses the table once:
select 
	ID,
	CaseID,
	Eff_Date,
	Thru_Date,
	WorkerID,
	SuperID,
	MngrID
from
(
	select 
		ID,
		CaseID,
		Eff_Date,
		Thru_Date,
		WorkerID,
		SuperID,
		MngrID,
		row_number() over(order by id desc) rn
   from tblStaffOnCase
   where Eff_Date >= @StartDate and (Thru_Date <= @EndDate or Thru_Date is null)
) x 
where rn=1

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Dale Fye

OK, guys, we are missing something here.  
I must not have been clear in my original post, so although my example only had one CaseID and only wanted 1 record, what the query should return is a recordset of several thousand records, consisting of all of the fields in the table (although ID and MngrID really aren't necessary), one record for every CaseID where there is one or more record which fall within the date range for that CaseID.

This is going to be used as a subquery to identify the last worker\supervisor combination for each CaseID in another table, and actually, all I really need are the CaseID, WorkerID and SuperID for the last record belonging to the CaseID.

Hope that clarifies it, at least a little.
slightwv (䄆 Netminder)

If you cannot take what has been posted and tweak it to meet your exact requirements, please provide more representative sample data and expected results.

Here is the fiddle I set up to run Jim's and my SQL:
https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=eb9b0c9b5f5485d6117b4f6d01930e6c

Please add to it and provide the results for the dates you desire as parameters.
ASKER
Dale Fye

@slightwv, I've never used Fiddle, is there an instruction manual?
I've added another CaseID with two records.
I'm not sure where the ID = 2000 in your result sets came from, but with the new set of cases
with these records:
insert into tblStaffOnCase values
(213,1,'06/01/2020','11/01/2020',1,2,3),
(980,1,'11/02/2020','03/15/2021',1,4,3),
(1325,1,'03/16/2021',NULL,6,2,3),
(1700,2,'01/16/2020','10/15/2020',1,7,9),
(1950,2,'10/02/2020',NULL,8,10,11);

Open in new window

and these parameters:
declare @StartDate as date = '10/2/2020'
declare @EndDate as date = '9/30/2021'

Open in new window

I would expect to get 2 records:
(1325,1,'03/16/2021',NULL,6,2,3) and
(1950,2,'10/02/2020',NULL,8,10,11);

Open in new window

With parameters:
declare @StartDate as date = '7/1/2020'
declare @EndDate as date = '9/30/2020'

Open in new window

I would expect to get:
(213,1,'06/01/2020','11/01/2020',1,2,3) and
(1700,2,'01/16/2020','10/15/2020',1,7,9),

Open in new window

 
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
_agx_

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Partha Mandayam

@slightwv in your query what is the rn=1 doing?
slightwv (䄆 Netminder)

>>@slightwv in your query what is the rn=1 doing?

The row_number() function generates, well, a row number based on the partition by and order by clauses.  It aliases it to 'rn'.

So, rn=1 returns only the first row based on the above criteria.

If you aren't familiar with the window aggregate functions, start reading up on them.  They are quite powerful and pretty efficient for what they do.

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15
slightwv (䄆 Netminder)

@Dale,

If the revised query posted by _agx_ doesn't work, just let us know.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Dale Fye

Thanks, guys, exactly what I was looking for.
Partha Mandayam

@slightwv But why rn=1
Don't we want the last record?
slightwv (䄆 Netminder)

>>Don't we want the last record?

Last is relative depending on which end of the list you start with:
... ORDER BY ID DESC ...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Dale Fye

I actually modified is slightly to sort by Eff_Date DESC, as that ensures that the records have not been played with and that the largest ID for a particular CaseID is not the record with the most recent Eff_Date.

@Partha,
There is really no other way to handle it than sorting in reverse and selecting RowNum = 1.  Otherwise, because some CaseIDs have multiple records, you would have no way of determining the maximum RowNum value for a particular CaseID