SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


retrieve records by joining history table


retrieve records by joining history table

Author
Message
naveen_balraj
naveen_balraj
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5
HI,

Can you help me with the query. Thanks for any help. The below is the example scenario:

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

Thanks,
j.miner
j.miner
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 358
Hi There,

I think you will find this script to solve your problem. It creates a sample database called TEST with two tables named MY_MASTER & MY_STATUS_HISTORY.

Both these tables were loaded with the sample data that was supplied.

The key to the solution is to use a Common Table Expression (CTE) with a Row Number Grouping calculation. The where clause filters out any data < '11/15/2012'. The row number orders the record is date descending order.

Since this result can not be directly referenced in one query (WHERE clause), it is wrapped in a CTE. The results are joined to the master table and filtered for the TOP_STATUS = 1.

I hope this helps you in your coding.

If you have any questions, please reply.

Sincerely

John

PS: Follow me on twitter - always doing something new with my blog or PASS!




--
-- Create a test database
--

-- Which database to use?
USE [master];
GO

-- Delete existing databases.
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TEST')
DROP DATABASE [TEST]
GO

-- Create a test database
CREATE DATABASE [TEST];
GO


--
-- Create the master table
--

-- Which database to use?
USE [TEST];
GO

-- Delete existing table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MY_MASTER]') AND type in (N'U'))
DROP TABLE [dbo].[MY_MASTER]
GO

-- Create a new table
CREATE TABLE MY_MASTER
(
MASTER_ID INT NOT NULL,
STATUS_DATE DATE,
STATUS_DESC VARCHAR(25),
MASTER_AMOUNT MONEY
);

-- Surrogate key
ALTER TABLE [dbo].[MY_MASTER]
ADD CONSTRAINT PK_MASTER_ID PRIMARY KEY CLUSTERED (MASTER_ID);
GO


--
-- Load the master table with data
--

INSERT INTO [dbo].[MY_MASTER] VALUES
(1, '1/1/2013', 'approved', 100),
(2, '12/1/2012', 'denied', 0),
(3, '11/1/2011', 'in progress', 200),
(4, '10/1/2012', 'approved', 300);



--
-- Create the status history table
--

-- Which database to use?
USE [TEST];
GO

-- Delete existing table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MY_STATUS_HISTORY]') AND type in (N'U'))
DROP TABLE [dbo].[MY_STATUS_HISTORY]
GO

-- Create a new table
CREATE TABLE MY_STATUS_HISTORY
(
HISTORY_ID INT NOT NULL,
MASTER_ID INT NOT NULL,
STATUS_DATE DATE,
STATUS_DESC VARCHAR(25)
);

-- Surrogate key
ALTER TABLE [dbo].[MY_STATUS_HISTORY]
ADD CONSTRAINT PK_HISTORY_ID PRIMARY KEY CLUSTERED (HISTORY_ID);
GO

-- Foreign Key
ALTER TABLE [dbo].[MY_STATUS_HISTORY]
ADD CONSTRAINT FK_MASTER_ID FOREIGN KEY (MASTER_ID)
REFERENCES [dbo].[MY_MASTER] (MASTER_ID);


--
-- Load the status history table with data
--

INSERT INTO [dbo].[MY_STATUS_HISTORY] VALUES
(1, 1, '11/30/2012', 'in progress'),
(3, 1, '12/10/2002', 'under review'),
(5, 1, '1/1/2013', 'approved'),
(2, 2, '10/1/2012', 'in progress'),
(4, 2, '11/1/2012', 'under review'),
(6, 2, '12/1/2012', 'denied'),
(7, 3, '11/1/2011', 'in progress'),
(8, 4, '9/1/2012', 'in progress'),
(9, 4, '9/15/2012', 'under review'),
(10, 4, '10/1/2012', 'approved');


--
-- Join the two tables
--

-- Sample date
DECLARE @MY_DATE DATE;
SET @MY_DATE = '11/15/2012';

-- Sample query (cte w/ row num to get most recent status) join to master to get $
;WITH CTE_STATUS_BY_DATE AS
(
SELECT
H.MASTER_ID,
H.STATUS_DATE,
H.STATUS_DESC,
ROW_NUMBER() OVER(PARTITION BY H.MASTER_ID ORDER BY H.STATUS_DATE DESC) AS TOP_STATUS
FROM dbo.MY_STATUS_HISTORY AS H
WHERE H.STATUS_DATE < @MY_DATE
)

SELECT
M.MASTER_ID,
M.MASTER_AMOUNT,
C.STATUS_DATE,
C.STATUS_DESC
FROM dbo.MY_MASTER as M
LEFT JOIN CTE_STATUS_BY_DATE AS C
ON M.MASTER_ID = C.MASTER_ID
WHERE C.TOP_STATUS = 1;

John Miner
Crafty DBA
www.craftydba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search