Retrieving complete, latest record in linked table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieving complete, latest record in linked table

Author

Topic

Smokey
Starting Member

3 Posts

Posted - 2013-08-27 : 20:56:43

Hi All

I have 2 tables in SQL Server 2005 with a one to many relationship and I need to pull the latest of several records of the desired status from the second table.

Basically the main table is the order header and the second table has a record for each status change of which there can be more than one of each eg an order can be modified hence has a second confirmation.

The tables are linked by OrderNum and I need to pull the latest entry with a status of Confirm. Initial selection is based on OrderTable.OrderDate plus some other criteria not really relevant to this question.

Here is some pseudo input & output data which I think will explain what I'm trying to do.

OrderHeaderTable H
OrderNum  OrderDate  Customer
123       10/5/2013  Cust1
456       10/5/2013  Cust2
789       10/5/2013  Cust3

OrderStatusTable S
OrderNum  Modified          ModifiedBy  Status
123       15/5/2013  10:55  Fred        Open
123       15/5/2013  11:00  Fred        Confirm
123       15/5/2013  11:05  Fred        Compl
456       15/5/2013  15:00  Tom         Confirm
456       15/5/2013  15:00  Tom         Confirm
456       16/5/2013  09:00  Dick        Confirm
789       15/5/2013  10:00  Harry       Confirm

As I'm only looking at the latest Confirm status, the result I'm after is :

OrderTable  OrderTable   OrderTable  StatusTable      StatusTable   StatusTable
H.OrderNum  H.OrderDate  H.Customer  S.Modified       S.ModifiedBy  S.Status
123         10/5/2013    Cust1       15/5/2013 11:00  Fred          Confirm
456         10/5/2013    Cust2       16/5/2013 09:00  Dick          Confirm
789         10/5/2013    Cust3       15/5/2013 10:00  Harry         Confirm

/* 123 the only Confirm record for this order number, ignores Open & Compl status */
/* 456 the latest Confirm record for this order with the associated ModifiedBy name */
/* 789 the only Confirm record for this order number */

I've found quite a few solutions online that look close but I don't seem to be able to translate those into what I actually need.

I've been trying to resolve this for a couple of months now so any help would be greatly appreciated.

Mark

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-28 : 00:17:45

[code]

select
H.OrderNum
,H.orderDate
,H.Customer
,S.Modified
,S.ModifiedBy
,S.Status
from OrderHeaderTable H
cross apply
(select top 1
S.Modified,
S.ModifiedBy,
S.Status
from OrderStatusTable S
where H.OrderNum=S.OrderNum
and S.Status='Confirm'
order by Modified desc) S

[/code]

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Smokey
Starting Member

3 Posts

Posted - 2013-08-28 : 01:19:19

Hey Stepson, that nailed it, thanks heaps.

I knew Top would come into it but I don't think I've seen Cross Apply before, I've got some study to do.

Thanks again.

Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 03:29:07

quote:
Originally posted by Smokey

Hey Stepson, that nailed it, thanks heaps.

I knew Top would come into it but I don't think I've seen Cross Apply before, I've got some study to do.

Thanks again.

Mark

see some practical uses of apply here

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-28 : 03:35:59

with welcome

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Smokey
Starting Member

3 Posts

Posted - 2013-08-28 : 19:21:27

quote:
http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

Thanks Visakh, I'll check it out.

Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-29 : 03:52:42

quote:
Originally posted by Smokey

quote:
http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

Thanks Visakh, I'll check it out.

Mark

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-08-31 : 20:36:22

I hope this helps.. Not tested.

select
B.OrderNum
,B.orderDate
,B.Customer
,B.Modified
,B.ModifiedBy
,B.Status
FROM
(
Select
Row_Number () Over (Partition by OrderNum Order By OrderDate Desc) as LatestDate
H.OrderNum
,H.orderDate
,H.Customer
S.Modified,
S.ModifiedBy,
S.Status
FROM OrderHeaderTable as H
Inner Join
OrderStatusTable as S on
H.OrderNum=S.OrderNum
WHERE
S.Status='Confirm'
order by Modified desc)
AS B
WHERE
B.LatestDate = 1

M. Ncube

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-08-31 : 20:40:09

[quote]Originally posted by marcusn25

I hope this helps.. Not tested.

select
B.OrderNum
,B.orderDate
,B.Customer
,B.Modified
,B.ModifiedBy
,B.Status
FROM
(
Select
Row_Number () Over (Partition by OrderNum Order By OrderDate Desc) as LatestDate
H.OrderNum
,H.orderDate
,H.Customer
S.Modified,
S.ModifiedBy,
S.Status
FROM OrderHeaderTable as H
Inner Join
OrderStatusTable as S on
H.OrderNum=S.OrderNum
WHERE
S.Status='Confirm'
order by Modified desc)
AS B
WHERE
B.LatestDate = 1

M. Ncube

M. Ncube

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources