Composite Key Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Composite Key Query

Author

Topic

cg1980
Starting Member

1 Post

Posted - 2014-03-18 : 17:34:41

I am new to sql and have been scratching my head all day. I'm using sql server 2008.

My table (run_history) contains a composite key (runddate & id) and lists anytime a transaction occurs for that record including the modification date & time, which user made the change, and what the change was.

rundate id moddate modtime name what
2014-03-18 001-A 2014-03-18 14:15:30 mary created record
2014-03-18 002-A 2014-03-18 14:17:30 john created record
2014-03-18 010-A 2014-03-18 14:18:30 mary created record
2014-03-18 001-A 2014-03-18 14:19:45 rob added phone number
2014-03-18 011-A 2014-03-18 14:20:50 mary added address
2014-03-18 001-A 2014-03-18 14:34:32 john changed phone number
2014-03-18 002-A 2014-03-18 14:45:30 sue added phone number
2014-03-18 002-A 2014-03-18 15:25:30 mary changed phone number

I am trying to create a view which will return the rundate, id, and who made the change when the reason contains 'phone number' changed or added. I also only want to include the most recent modification time and disregard any previous changes.

Suggestions on how to proceed?

Thanks! Chris

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-18 : 17:45:32

Not a lot of details, but here's my best guess:



SELECT derived.*
FROM (
    SELECT
        rundate, id, moddate, modtime, name, what,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY moddate DESC, modtime DESC) AS row_num
    FROM dbo.tablename
    WHERE
        what LIKE '%phone number%'
) AS derived
WHERE
    row_num = 1
ORDER BY
    id

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-18 : 17:46:16

For the final view, naturally add
CREATE VIEW viewname AS
and remove the ORDER BY.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources