Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

retrieve records by joining history table Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 10:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 2, 2013 1:23 PM
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,

Post #1438004
Posted Thursday, April 4, 2013 10:30 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:03 PM
Points: 80, Visits: 350
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
Post #1438902
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse