Get Latest Revision of Attribute Based on Date
-
Hi all,
First real post here although an avid reader of some of the posts. Mainly a DBA but started to get more in the TSQL development side of things. Came across a problem that I'm struggling to get a good set-based solution to (I can do this with cursors, but I'd rather stay away from them if possible).
Basically, I am having to create a table that is the child of the parent table. It mirrors the structure of the parent table with an additional 3 columns, a PK, a date changed (inserted) and a user_id field.
The application will insert a row (which is essentially a change record to the parent) for every change to the parent record that a user makes. So if they change all 3 attributes, all the col1/2/3 are populated and so on.
I basically need to bring back the latest revisions to the attributes based on the date. I need to bring back the foreign key (fk) and cols 1, 2 and 3 to display to the user. See the code.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))DROP TABLE [dbo].[test]
GO
CREATE TABLE [dbo].[test](
id [int] NOT NULL,
[fk] [int] NOT NULL,
[col1] [int] NULL,
[col2] [int] NULL,
[col3] [int] NULL,
[changed] [datetime] NOT NULL,
[userid] [int] NOT NULL
)
GO
ALTER TABLE dbo.test ADD CONSTRAINT pk_test PRIMARY KEY(id)
GO
INSERT INTO test VALUES (1,1,1,NULL,3,GETDATE()+1,1)
INSERT INTO test VALUES (2,1,NULL,2,NULL,GETDATE()+2,2)
INSERT INTO test VALUES (3,1,4,NULL,NULL,GETDATE()+3,3)
GO
So for this example, I would like a single row displayed with the values
1,4,2,3 for cols fk,col1,col2,col3
I have no requirement to display anything other than that - so effectively it'd be like collapsing the row to the latest date. I've looked a using ROLLUP, but I'm not sure it will give me what I need. Like I say, I could do this using cursors, but I'm hoping there's a better way. Anyone else had to do something like this? Any help would be greatly appreciated!
-
Works but I suspect there's a better way (using CROSS APPLY?)
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY fk ORDER BY CASE WHEN col1 IS NOT NULL THEN 0 ELSE 1 END, changed DESC) AS rn1,
ROW_NUMBER() OVER(PARTITION BY fk ORDER BY CASE WHEN col2 IS NOT NULL THEN 0 ELSE 1 END, changed DESC) AS rn2,
ROW_NUMBER() OVER(PARTITION BY fk ORDER BY CASE WHEN col3 IS NOT NULL THEN 0 ELSE 1 END, changed DESC) AS rn3
FROM test)
SELECT fk,
MAX(CASE WHEN rn1=1 THEN col1 END) AS col1,
MAX(CASE WHEN rn2=1 THEN col2 END) AS col2,
MAX(CASE WHEN rn3=1 THEN col3 END) AS col3
FROM CTE
GROUP BY fk;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Based on your post, the following:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
DROP TABLE [dbo].[test]
GO
CREATE TABLE [dbo].[test](
id [int] NOT NULL,
[fk] [int] NOT NULL,
[col1] [int] NULL,
[col2] [int] NULL,
[col3] [int] NULL,
[changed] [datetime] NOT NULL,
[userid] [int] NOT NULL
)
GO
ALTER TABLE dbo.test ADD CONSTRAINT pk_test PRIMARY KEY(id)
GO
INSERT INTO test VALUES (1,1,1,NULL,3,GETDATE()+1,1)
INSERT INTO test VALUES (2,1,NULL,2,NULL,GETDATE()+2,2)
INSERT INTO test VALUES (3,1,4,NULL,NULL,GETDATE()+3,3)
GO
select * from dbo.Test;
go
select
fk,
max(col1) as col1,
max(col2) as col2,
max(col3) as col3
from
dbo.Test
group by
fk
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
DROP TABLE [dbo].[test]
GO
-
Cheers guys,
I'm glad to see I'm not losing my mind. I'll let you know how it goes and I hope this helps some other people!
Greatly appreciated
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply