December 3, 2008 at 3:31 pm
Here's what I'm trying to accomplish:
Identify rows from table1 where table1.reg_id = table2.reg_id AND the most recent status from table2 <> table1.status
table2 is a contact history table so there can be multiple records for the same reg_id.
I'm convinced I need a derived table or CTE to accomplish this but I can't seem to get it to give me what I want. Below is a setup script as well as the query I attempted. An example of the result from the query should be:
reg_id status status
------ ------- ------
1 2 1
... this tells me that the status value in table1 is no longer in sync with the most recent status value in table2.
I'm somewhat new to the world of SQL. Any help or advice would be greatly appreciated.
CREATE TABLE dbo.table1(
reg_idintNOT NULL,
statustinyintNULL
);
CREATE TABLE dbo.table2(
reg_idintNOT NULL,
statustinyintNULL,
history_dateSMALLDATETIMENOT NULL DEFAULT GETDATE()
);
INSERT INTOtable1
( reg_id, status )
SELECT1,2UNION ALL
SELECT2,3UNION ALL
SELECT3,1UNION ALL
SELECT4,2UNION ALL
SELECT5,4UNION ALL
SELECT6,2UNION ALL
SELECT7,3UNION ALL
SELECT8,1UNION ALL
SELECT9,1UNION ALL
SELECT10,3;
INSERT INTOtable2
( reg_id, status, history_date )
SELECT1,2,'01/01/2008'UNION ALL
SELECT2,1,'01/01/2008'UNION ALL
SELECT3,1,'01/03/2008'UNION ALL
SELECT4,2,'01/05/2008'UNION ALL
SELECT5,4,'01/05/2008'UNION ALL
SELECT1,3,'01/08/2008'UNION ALL
SELECT4,1,'01/09/2008'UNION ALL
SELECT6,2,'01/09/2008'UNION ALL
SELECT1,1,'01/11/2008'UNION ALL
SELECT5,2,'01/11/2008'UNION ALL
SELECT8,1,'01/13/2008'UNION ALL
SELECT8,2,'01/14/2008'UNION ALL
SELECT9,2,'01/14/2008'UNION ALL
SELECT10,3,'01/15/2008';
SELECTt1.reg_id, t1.status,
t2.status
FROMtable1 t1
INNER JOIN(
SELECTreg_id, status, MAX(history_date) AS history_date
FROMtable2
GROUP BYreg_id, status
) t2
ONt2.reg_id = t1.reg_id
ANDt2.status <> t1.status;
December 3, 2008 at 3:49 pm
i think something like this will do it.
select * from
( select t1.reg_id, t1.status, t2.status as prior_status, t2.history_date,
row_number() over (partition by t2.reg_id order by t2.history_date desc ) as seq
from table1 t1 join table2 t2 on t1.reg_id = t2.reg_id
where t1.status != t2.status ) as x
where x.seq = 1
[font="Courier New"]
reg_id status prior_status history_date seq
1212008-01-11 00:00:001
2312008-01-01 00:00:001
4212008-01-09 00:00:001
5422008-01-11 00:00:001
8122008-01-14 00:00:001
9122008-01-14 00:00:001
[/font]
December 3, 2008 at 3:51 pm
Something like this?
SELECT *
FROM Table1 T1
INNER JOIN
(SELECT T2I.Reg_ID, Status
FROM Table2 T2I
INNER JOIN (SELECT Reg_ID, MAX(history_date) History_Date
FROM Table2
GROUP BY Reg_ID) T2D
ON T2I.Reg_ID = T2D.Reg_ID and T2I.history_date = T2D.history_date) T2
ON T1.Reg_id = T2.Reg_ID
WHERE T1.Status <> T2.Status
ORDER BY T1.Reg_ID
December 3, 2008 at 4:04 pm
Is this what you are looking for?
;With history (regid, status, history_date, rn)
As (Select reg_id
,status
,history_date
,row_number() over(partition By reg_id Order By history_date desc)
From #table2)
Select *
From history h
Join #table1 t On t.reg_id = h.regid
Where h.rn = 1
And h.status <> t.status;
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 3, 2008 at 4:11 pm
Jeffrey Williams (12/3/2008)
Is this what you are looking for?
;With history (regid, status, history_date, rn)
As (Select reg_id
,status
,history_date
,row_number() over(partition By reg_id Order By history_date desc)
From #table2)
Select *
From history h
Join #table1 t On t.reg_id = h.regid
Where h.rn = 1
And h.status <> t.status;
no row will be returned if the most recent table2.status is = table1.status. the status comparison must be done within the cte (or derived table expression).
December 3, 2008 at 4:18 pm
antonio.collins (12/3/2008)
Jeffrey Williams (12/3/2008)
Is this what you are looking for?
;With history (regid, status, history_date, rn)
As (Select reg_id
,status
,history_date
,row_number() over(partition By reg_id Order By history_date desc)
From #table2)
Select *
From history h
Join #table1 t On t.reg_id = h.regid
Where h.rn = 1
And h.status <> t.status;
no row will be returned if the most recent table2.status is = table1.status. the status comparison must be done within the cte (or derived table expression).
According to the OP
Identify rows from table1 where table1.reg_id = table2.reg_id AND the most recent status from table2 <> table1.status
That is exactly what he is looking for. Not sure why you think this won't work.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 4, 2008 at 8:46 am
i misinterpreted it and thought he wanted the the current status and the prior status (which is a requirement we often have to meet with history tables).
December 4, 2008 at 8:49 am
Thank you all who replied. I went with Jeffery Williams' solution as it produced the results I was looking for and was a nice use of a CTE.
December 4, 2008 at 11:20 am
antonio.collins (12/4/2008)
i misinterpreted it and thought he wanted the the current status and the prior status (which is a requirement we often have to meet with history tables).
Yeah - I can see that kind of use also.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply