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 2008 Forums
 Transact-SQL (2008)
 retrieve records by joining history table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nbalraj
Starting Member

21 Posts

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


Master_table

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

APPROVED

1 record - pk is 4

DENIED

0 record

IN PROGRESS

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

UNDER REVIEW

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

Lamprey
Flowing Fount of Yak Knowledge

4358 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))
INSERT @History VALUES
(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

Lamprey
Flowing Fount of Yak Knowledge

4358 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:
SELECT 
	pk
	,status
FROM
	(
		SELECT
			Master.pk
			,CASE WHEN Master.status_date <= '20121115' THEN Master.status ELSE History.status END AS Status
			,ROW_NUMBER() OVER (PARTITION BY Master.pk ORDER BY CASE WHEN Master.status_date <= '20121115' THEN Master.status_date ELSE History.status_date END DESC) AS RowNum
		FROM 
			@Master_table AS Master
		FULL OUTER JOIN
			@History AS History
			ON Master.pk = History.pk_master_table
		WHERE 
			Master.status_date <= '20121115'
			OR (Master.status_date > '20121115' AND History.status_date <= '20121115')
	) AS A
WHERE
	RowNum = 1
Go to Top of Page

nbalraj
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

Lamprey
Flowing Fount of Yak Knowledge

4358 Posts

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

nbalraj
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  
 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.06 seconds. Powered By: Snitz Forums 2000