SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Retrieving complete, latest record in linked table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Smokey
Starting Member

Australia
3 Posts

Posted - 08/27/2013 :  20:56:43  Show Profile  Reply with Quote
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

Edited by - Smokey on 08/27/2013 20:58:11

stepson
Constraint Violating Yak Guru

Romania
420 Posts

Posted - 08/28/2013 :  00:17:45  Show Profile  Reply with Quote


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




S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

Smokey
Starting Member

Australia
3 Posts

Posted - 08/28/2013 :  01:19:19  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/28/2013 :  03:29:07  Show Profile  Reply with Quote
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
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
420 Posts

Posted - 08/28/2013 :  03:35:59  Show Profile  Reply with Quote
with welcome

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

Smokey
Starting Member

Australia
3 Posts

Posted - 08/28/2013 :  19:21:27  Show Profile  Reply with Quote
quote:
http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html


Thanks Visakh, I'll check it out.

Mark
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/29/2013 :  03:52:42  Show Profile  Reply with Quote
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
Go to Top of Page

marcusn25
Starting Member

Germany
39 Posts

Posted - 08/31/2013 :  20:36:22  Show Profile  Reply with Quote
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

Edited by - marcusn25 on 08/31/2013 20:41:13
Go to Top of Page

marcusn25
Starting Member

Germany
39 Posts

Posted - 08/31/2013 :  20:40:09  Show Profile  Reply with Quote
[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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000