Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get Latest Revision of Attribute Based on Date


Get Latest Revision of Attribute Based on Date

Author
Message
UncleFredo
UncleFredo
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 496
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!
Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 22812
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




Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24278 Visits: 37987
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




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
UncleFredo
UncleFredo
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 496
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
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