April 2, 2013 at 10:40 am
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,
April 4, 2013 at 10:30 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply