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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 retrieve records by joining history table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

21 Posts

Posted - 04/02/2013 :  11:25:39  Show Profile  Send nbalraj a Yahoo! Message  Reply with Quote


pk status_date status amount
1 1/1/13 approved 100
2 12/1/12 denied 0
3 11/1/11 in progress 200
4 10/1/12 approved 300

Status History table
pk pk_master_table status_date status
1 1 11/30/12 in progress
3 1 12/10/2 under review
5 1 1/1/13 approved
2 2 10/1/12 in progress
4 2 11/1/12 under review
6 2 12/1/12 denied
7 3 11/1/11 in progress
8 4 9/1/12 in progress
9 4 9/15/12 under review
10 4 10/1/12 approved

The query will have an input: Status date, example 11/15/12

Output required: All the master_table records on their snap shot status <= 'input date' - As of the input date, what was the status of the record

The below will be the output of the query


1 record - pk is 4


0 record


1 record - pk is 3 - note: pk 4 does not show up in this list as the 'approved date' is the higher, though the history has 'in progress' less than input 11/15


1 record - pk is 2

Can you help me with the query. Thanks for any help.

Edited by - nbalraj on 04/02/2013 11:26:27

Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/02/2013 :  12:49:54  Show Profile  Reply with Quote
I'm not sure I'm following. Do you just want the "last" status from the history table for each PK in the Master table? Or do you want the status from the Master table if it is less than or equal to the date variable and, if the Master table row is greater than that date, then get the last status from the History table? (I'm not even sure I follow myself.. )

Let me ask another way... Of the results you have shown above, which table does that result come from?

It's also a good idea to put your data in a consumable format. I formatted it for you so that others might be able to run queries against it:
DECLARE @Master_table TABLE (pk int, status_date date, status VARCHAR(50), amount INT)
INSERT @Master_table VALUES
(1, '1/1/13', 'approved', 100),
(2, '12/1/12', 'denied', 0),
(3, '11/1/11', 'in progress', 200),
(4, '10/1/12', 'approved', 300)

DECLARE @History TABLE (pk int, pk_master_table int, status_date date, status VARCHAR(50))
(1, 1, '11/30/12', 'in progress'),
(3, 1, '12/10/2', 'under review'),
(5, 1, '1/1/13', 'approved'),
(2, 2, '10/1/12', 'in progress'),
(4, 2, '11/1/12', 'under review'),
(6, 2, '12/1/12', 'denied'),
(7, 3, '11/1/11', 'in progress'),
(8, 4, '9/1/12', 'in progress'),
(9, 4, '9/15/12', 'under review'),
(10, 4, '10/1/12', 'approved')

Edited by - Lamprey on 04/02/2013 12:51:29
Go to Top of Page

Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/02/2013 :  13:04:46  Show Profile  Reply with Quote
I'm still not sure of the logic, but I was able to get the results you aked for. If that doesn't work for your full data set, let us know and we can refine the logic. This may not be the best solution, but I think it works:
			,CASE WHEN Master.status_date <= '20121115' THEN Master.status ELSE History.status END AS Status
			,ROW_NUMBER() OVER (PARTITION BY ORDER BY CASE WHEN Master.status_date <= '20121115' THEN Master.status_date ELSE History.status_date END DESC) AS RowNum
			@Master_table AS Master
			@History AS History
			ON = History.pk_master_table
			Master.status_date <= '20121115'
			OR (Master.status_date > '20121115' AND History.status_date <= '20121115')
	) AS A
	RowNum = 1
Go to Top of Page

Starting Member

21 Posts

Posted - 04/02/2013 :  13:04:50  Show Profile  Send nbalraj a Yahoo! Message  Reply with Quote
Thanks Lamprey for formatting, I tried to keep the sample table in a tabbed format, but it did not work.

To answer your question, the result will come from master_table. The result will display just the PK and the amount. But selection of the PK is decided based on the input date (@date) to the query and the @date will be used to analyze in the status history table.

Basically all the records from master_table will be analyzed and classified into 4 categories: APPROVED, DENIED, IN PROGRESS, UNDER REVIEW

Hope this clarifies.

Thanks again for your time.

Go to Top of Page

Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/02/2013 :  15:34:46  Show Profile  Reply with Quote
Maybe this?
SELECT pk_master_table as pk, status
	SELECT *, ROW_NUMBER() OVER (PARTITION BY pk_master_table ORDER BY status_date DESC) AS RowNum
	FROM @History
	WHERE status_date <= '20121115'
) AS A
	RowNum = 1
Go to Top of Page

Starting Member

21 Posts

Posted - 04/03/2013 :  11:06:09  Show Profile  Send nbalraj a Yahoo! Message  Reply with Quote
Thanks Lamprey, I'm validating the first query and will try the second as well today and post my updates later today. Appreciate your response.
Go to Top of Page
  Previous Topic Topic Next 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.04 seconds. Powered By: Snitz Forums 2000