Find the last record by date in a sub group of records.

cybertaz69

Looking to see if thier is a better way to find the last record entered in a group of records.

What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.

Table example using simplied data.

PolId	CoveragId	EffDate	Status	Limit1
2	1	9/7/2007	a	10000
2	2	9/7/2007	a	150000
2	2	10/1/2007	a	200000
3	1	9/7/2007	a	10000

The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.

The current query returns a single value. from a single table.

Current code used in the select protion on a larger query. bpi = basicpolicyInformation.

( Select c1.limit1
From AFW_Coverage as c1
Where c1.PolId=bpi.PolId
and c1.CoverageId = (select max(CoverageId) as CoverageId
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageCode = 'Dwelling'
and status <> 'D'
)
and c1.effDate = (select max(Effdate) as Effdate
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageID = c1.CoverageId
)

Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.

Kent Waldrop Oc07

If you are using SQL Sever 2005 you ought to be able to use the ROW_NUMBER() function to improve on this. Another possibility is to take the max of a "tag along" vector to retain the information. Which version of SQL Server are you using The SQL Server 2005 code might look something like:

Code Block

declare @xample table
( PollId integer,
CoveragId integer,
EffDate datetime,
Status char(1),
Limit1 integer
)
insert into @xample
select 2, 1, '9/7/2007', 'a', 10000 union all
select 2, 2, '9/7/2007', 'a', 15000 union all
select 2, 2, '10/1/2007','a', 20000 union all
select 3, 1, '9/7/2007', 'a', 10000
--select * from @xample

select pollId,
CoveragId,
EffDate,
Status,
Limit1
from ( select PollId,
row_number() over
( partition by PollId
order by CoveragId desc, EffDate desc, Limit1
) as Seq,
CoveragId,
EffDate,
Status,
Limit1
from @xample
) a
where seq = 1

/*
pollId CoveragId EffDate Status Limit1
----------- ----------- ----------------------- ------ -----------
2 2 2007-10-01 00:00:00.000 a 20000
3 1 2007-09-07 00:00:00.000 a 10000
*/

AKuz

If you are running 2005, you can use

ROW_NUMBER() OVER(PARTITION BY PolId ORDER BY CoverageId DESC, Effdate DESC)

Kent Waldrop Oc07

If you are using SQL Server 2000, you might be able to do something like:

declare @xample table
( PollId integer,
CoveragId integer,
EffDate datetime,
Status char(1),
Limit1 integer
)
insert into @xample
select 2, 1, '9/7/2007', 'a', 10000 union all
select 2, 2, '9/7/2007', 'a', 15000 union all
select 2, 2, '10/1/2007','a', 20000 union all
select 3, 1, '9/7/2007', 'a', 10000

select pollId,
cast(left(tVector,11) as integer) as CoveragId,
convert(datetime, substring(tVector, 13, 23)) as EffDate,
substring(tVector, 37, 1) as Status,
cast(substring(tVector, 39, 11) as integer) as Limit1
from ( select pollId,
max
( right(' ' + convert(varchar(11), coveragId), 11) + ' ' +
convert(char(24), EffDate, 121) +
Status + ' ' +
convert(char(11), Limit1)
) as tVector
from @xample
group by pollId
) a

/*
pollId CoveragId EffDate Status Limit1
----------- ----------- ------------------------ ------ -------
2 2 2007-10-01 00:00:00.000 a 20000
3 1 2007-09-07 00:00:00.000 a 10000
*/

cybertaz69

Thank You all for the quick reply....

I'm using SQL server 2000.

Kent I'm not sure if that does what I need. I'm going to walk though it and could you let me know if I'm right

bypass temp table portion

declare @xample table
( PollId integer,
CoveragId integer,
EffDate datetime,
Status char(1),
Limit1 integer
)
insert into @xample
select 2, 1, '9/7/2007', 'a', 10000 union all
select 2, 2, '9/7/2007', 'a', 15000 union all
select 2, 2, '10/1/2007','a', 20000 union all
select 3, 1, '9/7/2007', 'a', 10000

bypass temp table

select pollId,
cast(left(tVector,11) as integer) as CoveragId,
convert(datetime, substring(tVector, 13, 23)) as EffDate,
substring(tVector, 37, 1) as Status,
cast(substring(tVector, 39, 11) as integer) as Limit1 Creates Input Row
from ( select pollId,
max Finds the highest string value
( right(' ' + convert(varchar(11), coveragId), 11) + ' ' +
convert(char(24), EffDate, 121) +
Status + ' ' +
convert(char(11), Limit1) Creates a String From the data
) as tVector
from @xample
group by pollId
)

Is converting the data to string and back efficient for record sets greater than 100K records

I think I've simplfied the example data too much. Here is a real sample.... For One PolID

PolId	CovageID	EffDate	Limit1
6/7/07 16:52	6/7/07 18:40	39951.00002	250000
6/7/07 16:52	6/7/07 18:40	39251.00001	150000
6/7/07 16:52	6/8/07 14:37	39251.00001	490400
6/7/07 16:52	6/8/07 14:37	39251.00002	490400
6/7/07 16:52	6/8/07 14:37	39251.00003	490400
6/7/07 16:52	6/8/07 14:37	39251.00005	490400
6/7/07 16:52	6/8/07 14:37	39822.00001	490400

Given Need newest Need Newest

for newest CovageID

The line in yellow in the data row needed for the output.

Notice. Multiple CoverageID for a single PolID and Multiple Effdate for single CoverageID.

Last record isn't always the correct record.

Pit falls: Effdate can be the same for different CoverageID and greater than current CovageID. See Line One.

hunchback

Can you post the table structure and sample data in form of "insert" statements, please

select

*

from

dbo.t1 as a

where

not exists (

select

*

from

dbo.t1 as b

where

b.PolId = a.PolId

and (b.CoverageId < a.CoverageId or (b.CoverageId = a.CoverageId and b.EffDate < a.EffDate))

)

go

AMB

cybertaz69

declare @xample table
( PolId datetime,
CoverageId datetime,
EffDate datetime,
Limit1 integer
)
insert into @xample

select '1997-08-11 09:30:38.573','1997-07-08 12:25:33.627','1997-08-30 00:00:00.000',158000 union all
select '1997-08-11 09:30:38.573','1998-03-11 08:11:18.513','1998-03-26 00:00:00.000',100000 union all
select '2007-06-07 16:52:00.797','2007-06-08 14:37:21.233','2007-06-18 00:00:04.000',490400 union all
select '2007-06-07 16:52:00.797','2007-06-08 14:37:21.233','2007-08-28 00:00:01.000',490400 union all
select '1997-08-11 09:30:38.573','1998-04-15 11:44:05.310','1997-08-30 00:00:00.000',160000 union all
select '1997-08-11 09:30:38.573','1998-04-15 11:44:05.310','1998-03-26 00:00:00.000',160000 union all
select '1997-08-11 09:30:38.573','1998-04-17 10:52:10.100','1998-03-26 00:00:00.000',160000 union all
select '1997-08-11 09:30:38.573','1998-04-17 10:52:40.100','1998-03-26 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-04-21 09:02:29.907','1998-03-26 00:00:00.000',160000 union all
select '1997-08-11 09:30:38.573','1998-04-21 09:02:29.907','1998-05-16 00:00:00.000',160000 union all
select '1997-08-11 09:30:38.573','1998-04-21 09:02:59.907','1998-03-26 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-04-21 09:02:59.907','1998-05-16 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-05-26 08:10:19.040','1998-05-16 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-06-03 13:58:01.743','1998-05-16 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-06-03 13:58:01.743','1998-05-18 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-06-15 13:08:28.727','1998-05-18 00:00:00.000',100000 union all
select '2007-03-28 13:48:08.810','2004-03-24 15:59:56.060','2007-03-21 00:00:00.000',177000 union all
select '2007-03-28 13:48:08.810','2004-03-24 15:59:56.060','2007-03-21 00:00:01.000',177000 union all
select '2007-03-28 13:48:08.810','2004-03-24 15:59:56.060','2007-03-27 00:00:00.000',177000 union all
select '2007-03-28 13:48:08.810','2004-03-24 15:59:56.060','2007-05-24 00:00:00.000',188000 union all
select '2007-03-28 13:48:08.810','2004-03-24 15:59:56.060','2007-05-24 00:00:01.000',188000 union all
select '2007-03-29 11:03:54.060','2005-05-13 08:38:02.857','2007-05-20 00:00:00.000',213000 union all
select '2007-03-29 11:03:54.060','2005-05-13 08:38:02.857','2007-05-20 00:00:01.000',213000 union all
select '2007-03-29 11:03:54.060','2005-05-13 08:38:02.857','2007-07-12 00:00:00.000',213000 union all
select '2007-03-29 11:03:54.060','2005-05-13 08:38:02.857','2007-07-12 00:00:01.000',213000 union all
select '2007-06-07 16:52:00.797','2007-06-08 14:37:21.233','2007-06-18 00:00:01.000',490400 union all
select '2007-06-07 16:52:00.797','2007-06-08 14:37:21.233','2007-06-18 00:00:02.000',490400 union all
select '2007-06-07 16:52:00.797','2007-06-08 14:37:21.233','2007-06-18 00:00:03.000',490400

select *
From @xample as parent
Where PolId =
(select top 1 PolId -- REturns more than one result
From @xample as Child
Where Child.PolId =Parent.PolId
and ( Child.CoverageId > parent.CoverageId
or (

child.CoverageID = Parent.CoverageID
and child.Effdate < parent.Effdate)
)
)

cybertaz69

select PolID, max (CoverageID) as CoverageID, max (Effdate) as Effdate
From @xample
group by PolID

Works with sample date but doen't with real data because the max effdate may not be in a record with the max coverageID.

-------------------------------------------------------------------------------------------

select PolID, CoverageID , Effdate
From @xample as c1
Where c1.CoverageID = (select max(c2.coverageID)
From @xample as c2
Where c1.PolId = c2.PolId)

and c1.EffDAte = (select max (effdate)
From @xample as c2
Where c1.PolId = c2.PolId
and c1.CoverageID = c2.CoverageID)

This works but is slow....

-------------------------------------------------------------------------------------------

select distinct PolID
From @xample
- Just shows the four PolID's

DaleJ

Do you have indexes on PolId, CoverageID, and EffDate

cybertaz69

DaleJ wrote:

Do you have indexes on PolId, CoverageID, and EffDate

If your refering to a key field, PolID, CoverageID, and Effdate are used in combination to create a key field.

If your referring to what the data in PolID is, that's just the original date the row was entered into the database. Same for coverageID and EffDate.

If your refering to a record by record index the answer is no besides it wouldn't work becase last in isn't alway the current record.

DaleJ



Have you looked at the query plan to see why the following is "slow"

select PolID, CoverageID , Effdate
From @xample as c1
Where c1.CoverageID = (select max(c2.coverageID)
From @xample as c2
Where c1.PolId = c2.PolId)


and c1.EffDAte = (select max (effdate)
From @xample as c2
Where c1.PolId = c2.PolId
and c1.CoverageID = c2.CoverageID)





Also,
Here's another option:

select PolID, CoverageID , Effdate, Limit1
From @xample as c1
inner join
(
select PolID, CoverageID , max(Effdate) as Effdate
From @xample as c2
inner join
(
select PolID, max(CoverageID) as CoverageID
From @xample
GROUP BY PolID
) as maxcov
on c2.PolID = maxcov.PolID
and c2.CoverageID = maxcov.CoverageID
GROUP BY c2.PolID, c2.CoverageID
) as maxlist
on c1.PolID = maxlist.PolID
and c1.CoverageID = maxlist.CoverageID
and c1.Effdate = maxlist.Effdate

cybertaz69

ty for the example it gave me a different way to look at the query.

The only issue I see is it has the same problem this code has

select PolID, max (CoverageID) as CoverageID, max (Effdate) as Effdate
From @xample
group by PolID

Works with sample date but doen't with real data because the max effdate may not be in a record with the max coverageID.

It looks for the highest Effdate no matter what the CoverageID. It will connect a High Effdate that isn't in a row with high coverageID.

Here the code showing the two codes side by side.

Code Block

declare @xample table
( PolId datetime,
CoverageId datetime,
EffDate datetime,
Limit1 integer
)
insert into @xample

select '1997-08-11 09:30:38.573','1997-07-08 12:25:33.627','1997-08-30 00:00:00.000',158000 union all
select '1997-08-11 09:30:38.573','1998-03-11 08:11:18.513','1998-03-26 00:00:00.000',100000 union all
select '2007-06-07 16:52:00.797','2007-06-08 14:37:21.233','2007-06-18 00:00:04.000',490400 union all
select '2007-06-07 16:52:00.797','2007-06-08 14:37:21.233','2007-08-28 00:00:01.000',490400 union all
select '1997-08-11 09:30:38.573','1998-04-15 11:44:05.310','1997-08-30 00:00:00.000',160000 union all
select '1997-08-11 09:30:38.573','1998-04-15 11:44:05.310','1998-03-26 00:00:00.000',160000 union all
select '1997-08-11 09:30:38.573','1998-04-17 10:52:10.100','1998-03-26 00:00:00.000',160000 union all
select '1997-08-11 09:30:38.573','1998-04-17 10:52:40.100','1998-03-26 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-04-21 09:02:29.907','1998-03-26 00:00:00.000',160000 union all
select '1997-08-11 09:30:38.573','1998-04-21 09:02:29.907','1998-05-16 00:00:00.000',160000 union all
select '1997-08-11 09:30:38.573','1998-04-21 09:02:59.907','1998-03-26 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-04-21 09:02:59.907','1998-05-16 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-05-26 08:10:19.040','1998-05-16 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-06-03 13:58:01.743','1998-05-16 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-06-03 13:58:01.743','1998-06-18 00:00:00.000',100000 union all
select '1997-08-11 09:30:38.573','1998-06-15 13:08:28.727','1998-05-18 00:00:00.000',100000 union all
select '2007-03-28 13:48:08.810','2004-03-24 15:59:56.060','2007-03-21 00:00:00.000',177000 union all
select '2007-03-28 13:48:08.810','2004-03-24 15:59:56.060','2007-03-21 00:00:01.000',177000 union all
select '2007-03-28 13:48:08.810','2004-03-24 15:59:56.060','2007-03-27 00:00:00.000',177000 union all
select '2007-03-28 13:48:08.810','2004-03-24 15:59:56.060','2007-05-24 00:00:00.000',188000 union all
select '2007-03-28 13:48:08.810','2004-03-24 15:59:56.060','2007-05-24 00:00:01.000',188000 union all
select '2007-03-29 11:03:54.060','2005-05-13 08:38:02.857','2007-05-20 00:00:00.000',213000 union all
select '2007-03-29 11:03:54.060','2005-05-13 08:38:02.857','2007-05-20 00:00:01.000',213000 union all
select '2007-03-29 11:03:54.060','2005-05-13 08:38:02.857','2007-07-12 00:00:00.000',213000 union all
select '2007-03-29 11:03:54.060','2005-05-13 08:38:02.857','2007-07-12 00:00:01.000',213000 union all
select '2007-06-07 16:52:00.797','2007-06-08 14:37:21.233','2007-06-18 00:00:01.000',490400 union all
select '2007-06-07 16:52:00.797','2007-06-08 14:37:21.233','2007-06-18 00:00:02.000',490400 union all
select '2007-06-07 16:52:00.797','2007-06-08 14:37:21.233','2007-06-18 00:00:03.000',490400

select PolID, max (CoverageID) as CoverageID, max (Effdate) as Effdate
From @xample
group by PolID

select PolID, CoverageID , Effdate
From @xample as c1
Where c1.CoverageID = (select max(c2.coverageID)
FRom @xample as c2
Where c1.PolId = c2.PolId)
and c1.EffDAte = (select max (effdate)
From @xample as c2
Where c1.PolId = c2.PolId
and c1.CoverageID = c2.CoverageID)

--------------------------------------------------------------------

select c1.PolID, c1.CoverageID , c1.Effdate, c1.Limit1
From @xample as c1
inner join
(
select c2.PolID, c2.CoverageID , max(c2.Effdate) as Effdate
From @xample as c2
inner join
(
select c3.PolID, max(c3.CoverageID) as CoverageID
From @xample as c3
GROUP BY PolID
) as maxcov
on c2.PolID = maxcov.PolID
and c2.CoverageID = maxcov.CoverageID
GROUP BY c2.PolID, c2.CoverageID
) as maxlist
on c1.PolID = maxlist.PolID
and c1.CoverageID = maxlist.CoverageID
and c1.Effdate = maxlist.Effdate

DaleJ


I'm still a little confused on exactly what processing you're trying to accomplish.

Can you clarify what you expect (including exceptions)

cybertaz69

Simply find the newest CoverageID for a PolID and then find the newest effdate related to the CoverageID all at the best performance possable.

There are four distinctive PolID in the test data. The end results should only show four records with the limit1 related to the newest CoverageID and the last entry for that coverageID (Effdate).

The code I posted at the top works but slow. I going to have to run this query over and over so if I can shave some time off it would be nice.

DaleJ

That's what the example I gave you will do.

Have you looked at the execution plan for the one that is slow