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 2008 Forums
 Transact-SQL (2008)
 retrieve records by joining history table

Author  Topic 

nbalraj
Starting Member

21 Posts

Posted - 2013-04-02 : 11:25:39
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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-02 : 12:49:54
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')
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-02 : 13:04:46
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 - 2013-04-02 : 13:04:50
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-02 : 15:34:46
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 - 2013-04-03 : 11:06:09
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
   

- Advertisement -