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

Get Latest Revision of Attribute Based on Date Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 9:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:33 AM
Points: 177, Visits: 354
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!
Post #1448874
Posted Thursday, May 2, 2013 10:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:45 AM
Points: 1,694, Visits: 19,552
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;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1448884
Posted Thursday, May 2, 2013 10:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 23,396, Visits: 32,229
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





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)
Post #1448888
Posted Thursday, May 2, 2013 10:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:33 AM
Points: 177, Visits: 354
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
Post #1448892
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse