[Help Request] - Modified By Date Across Multiple Tables

[Peter Cooney]

Greetings,
I'm looking for some assistance with writing a SQL query...

I have three tables which can be simplified as follows:

ServiceJob: primaryKey | modifiedOn | modifiedBy | modifiedById
| ... ... ...
Task: primaryKey | regardingId | modifiedOn | modifiedBy |
modifiedById
Note: primaryKey | regardingId | modifiedOn | modifiedBy |
modifiedById

In all three tables, primaryKey, regardingId & modifiedById are GUIDs,
modifiedOn is a datetime, modifiedBy is a nvarchar. The regardingId
columns in Taske & Note relate to a primaryKey in the ServiceJob
table.


The query I'm trying to produce needs to return the MAX(modifiedOn),
modifiedBy & modifiedById across all three tables for each row in the
ServiceJob table (along with various other columns from the ServiceJob
table). Performance optimisation, while desirable, is not critical as
this will primarily be used for a report as opposed to a real-time
application, however this may change in the future. To this end, I
would prefer to be able to build this in a view (as such, temporary
tables are out)

I hope I've explained things sufficiently, if further details are
needed, please let me know.

Many thanks in advance!

[Bob Barrows]

Post by Peter Cooney
Greetings,
I'm looking for some assistance with writing a SQL query...
ServiceJob: primaryKey | modifiedOn | modifiedBy | modifiedById

Post by Peter Cooney
... ... ...

Task: primaryKey | regardingId | modifiedOn | modifiedBy |
modifiedById
Note: primaryKey | regardingId | modifiedOn | modifiedBy |
modifiedById
In all three tables, primaryKey, regardingId & modifiedById are GUIDs,
modifiedOn is a datetime, modifiedBy is a nvarchar. The regardingId
columns in Taske & Note relate to a primaryKey in the ServiceJob
table.
The query I'm trying to produce needs to return the MAX(modifiedOn),
modifiedBy & modifiedById across all three tables for each row in the
ServiceJob table (along with various other columns from the ServiceJob
table). Performance optimisation, while desirable, is not critical as
this will primarily be used for a report as opposed to a real-time
application, however this may change in the future. To this end, I
would prefer to be able to build this in a view (as such, temporary
tables are out)
I hope I've explained things sufficiently, if further details are
needed, please let me know.

Could you show us some sample data and desired results using that sample
data?
My initial thought is that you will need to use a union query along with a
group by but that might be too simplistic.
Provide the sample data in the form of insert statements:
insert ServiceJob (primaryKey, modifiedOn, modifiedBy, modifiedById)
select 'abc','20120105','def','ghi'
union
select ...

Also, let us know what version of sql server you're using.

[Erland Sommarskog]

Post by Peter Cooney
The query I'm trying to produce needs to return the MAX(modifiedOn),
modifiedBy & modifiedById across all three tables for each row in the
ServiceJob table (along with various other columns from the ServiceJob
table). Performance optimisation, while desirable, is not critical as
this will primarily be used for a report as opposed to a real-time
application, however this may change in the future. To this end, I
would prefer to be able to build this in a view (as such, temporary
tables are out)

If in understand this correctly:

WITH u AS (
SELECT primaryKey, modifiedOn, modifiedBy, modifiedById
FROM SerivceJob
UNION ALL
SELECT reagrdingId, modifiedOn, modifiedBy, modifiedById
FROM Task
UNION ALL
SELECT regardingId, modifiedOn, modifiedBy, modifiedById
FROM Note
), numebred AS (
SELECT primaryKey, modifiedOn, modifiedBy, modifiedById,
rowno = row_number() OVER(PARTITION BY primaryKey
ORDER BY modifiedOn DESC)
FROM u
)
SELECT primaryKey, modifiedOn, modifiedBy, modifiedById
FROM numbered
WHERE rowno = 1

Since you did not provide CREATE TABLE statements for the table, and INSERT
statements with sample data, I have not been able to test the above.

[Peter Cooney]

Post by Peter Cooney
The query I'm trying to produce needs to return the MAX(modifiedOn),
modifiedBy & modifiedById across all three tables for each row in the
ServiceJob table (along with various other columns from the ServiceJob
table). Performance optimisation, while desirable, is not critical as
this will primarily be used for a report as opposed to a real-time
application, however this may change in the future. To this end, I
would prefer to be able to build this in a view (as such, temporary
tables are out)

WITH u  AS (
   SELECT primaryKey, modifiedOn, modifiedBy, modifiedById
   FROM   SerivceJob
   UNION ALL
   SELECT reagrdingId, modifiedOn, modifiedBy, modifiedById
   FROM   Task
   UNION ALL
   SELECT regardingId, modifiedOn, modifiedBy, modifiedById
   FROM   Note
), numebred AS (
   SELECT primaryKey, modifiedOn, modifiedBy, modifiedById,
          rowno = row_number() OVER(PARTITION BY primaryKey
                                     ORDER BY modifiedOn DESC)
   FROM   u
)
SELECT primaryKey, modifiedOn, modifiedBy, modifiedById
FROM   numbered
WHERE  rowno = 1
Since you did not provide CREATE TABLE statements for the table, and INSERT
statements with sample data, I have not been able to test the above.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Many thanks for the responses, as requested, here's some further
details...
Server version: SQL 2008 R2 (Enterprise, v10.50.2500.0)

Create statements and sample data:

CREATE TABLE ServiceJob (
primaryKey UNIQUEIDENTIFIER,
modifiedOn DATETIME,
modifiedBy NVARCHAR(250),
modifiedById UNIQUEIDENTIFIER,
additionalCol1 NVARCHAR(250),
additionalCol2 NVARCHAR(250)
)

INSERT ServiceJob (primaryKey, modifiedOn, modifiedBy, modifiedById,
additionalCol1, additionalCol2)
SELECT 'A0000000-0000-0000-0000-000000000001', '2012-02-27 08:00:00',
'Mr White', 'B0000000-0000-0000-0000-000000000001', 'Something',
'else'

INSERT ServiceJob (primaryKey, modifiedOn, modifiedBy, modifiedById,
additionalCol1, additionalCol2)
SELECT 'A0000000-0000-0000-0000-000000000002', '2012-02-27 08:05:00',
'Mr Orange', 'B0000000-0000-0000-0000-000000000002', 'Extra', 'data'

INSERT ServiceJob (primaryKey, modifiedOn, modifiedBy, modifiedById,
additionalCol1, additionalCol2)
SELECT 'A0000000-0000-0000-0000-000000000003', '2012-02-27 08:10:00',
'Mr Brown', 'B0000000-0000-0000-0000-000000000003', 'Return', 'value'


CREATE TABLE Task (
primaryKey UNIQUEIDENTIFIER,
regardingId UNIQUEIDENTIFIER,
modifiedOn DATETIME,
modifiedBy NVARCHAR(250),
modifiedById UNIQUEIDENTIFIER
)

INSERT Task (primaryKey, regardingId, modifiedOn, modifiedBy,
modifiedById)
SELECT 'C0000000-0000-0000-0000-000000000001',
'A0000000-0000-0000-0000-000000000001', '2012-02-27 08:05:00', 'Mr
Brown', 'B0000000-0000-0000-0000-000000000003'


CREATE TABLE Note (
primaryKey UNIQUEIDENTIFIER,
regardingId UNIQUEIDENTIFIER,
modifiedOn DATETIME,
modifiedBy NVARCHAR(250),
modifiedById UNIQUEIDENTIFIER
)

INSERT Note (primaryKey, regardingId, modifiedOn, modifiedBy,
modifiedById)
SELECT 'C0000000-0000-0000-0000-000000000002',
'A0000000-0000-0000-0000-000000000001', '2012-02-27 08:10:00', 'Mr
Orange', 'B0000000-0000-0000-0000-000000000002'

INSERT Note (primaryKey, regardingId, modifiedOn, modifiedBy,
modifiedById)
SELECT 'C0000000-0000-0000-0000-000000000003',
'A0000000-0000-0000-0000-000000000002', '2012-02-27 08:15:00', 'Mr
White', 'B0000000-0000-0000-0000-000000000001'


Required results:
primaryKey | modifiedOn | modifiedBy | modifiedById | additionalCol1 |
additionalCol2
'A0000000-0000-0000-0000-000000000001' | '2012-02-27 08:10:00' | 'Mr
Orange' | 'B0000000-0000-0000-0000-000000000002' | 'Something' |
'else'
'A0000000-0000-0000-0000-000000000002' | '2012-02-27 08:15:00' | 'Mr
White' | 'B0000000-0000-0000-0000-000000000001' | 'Extra' | 'data'
'A0000000-0000-0000-0000-000000000003' | '2012-02-27 08:10:00' | 'Mr
Brown' | 'B0000000-0000-0000-0000-000000000003' | 'Return' | 'value'


Many thanks.

[Erland Sommarskog]

Post by Peter Cooney
Many thanks for the responses, as requested, here's some further
details...
Server version: SQL 2008 R2 (Enterprise, v10.50.2500.0)

They were only requested if you couldn't get my query to run. :-)

Here is the query again with typos fixed, and the two additional columns
added:

WITH u AS (
SELECT primaryKey, modifiedOn, modifiedBy, modifiedById
FROM ServiceJob
UNION ALL
SELECT regardingId, modifiedOn, modifiedBy, modifiedById
FROM Task
UNION ALL
SELECT regardingId, modifiedOn, modifiedBy, modifiedById
FROM Note
), numbered AS (
SELECT primaryKey, modifiedOn, modifiedBy, modifiedById,
rowno = row_number() OVER(PARTITION BY primaryKey
ORDER BY modifiedOn DESC)
FROM u
)
SELECT n.primaryKey, n.modifiedOn, n.modifiedBy, n.modifiedById,
SJ.additionalCol1, SJ.additionalCol2
FROM numbered n
JOIN ServiceJob SJ ON SJ.primaryKey = n.primaryKey
WHERE n.rowno = 1

[Peter Cooney]

Post by Erland Sommarskog

Post by Peter Cooney
Many thanks for the responses, as requested, here's some further
details...
Server version: SQL 2008 R2 (Enterprise, v10.50.2500.0)

They were only requested if you couldn't get my query to run. :-)
Here is the query again with typos fixed, and the two additional columns
WITH u  AS (
   SELECT primaryKey, modifiedOn, modifiedBy, modifiedById
   FROM   ServiceJob
   UNION ALL
   SELECT regardingId, modifiedOn, modifiedBy, modifiedById
   FROM   Task
   UNION ALL
   SELECT regardingId, modifiedOn, modifiedBy, modifiedById
   FROM   Note
), numbered AS (
   SELECT primaryKey, modifiedOn, modifiedBy, modifiedById,
          rowno = row_number() OVER(PARTITION BY primaryKey
                                     ORDER BY modifiedOn DESC)
   FROM   u
)
SELECT n.primaryKey, n.modifiedOn, n.modifiedBy, n.modifiedById,
       SJ.additionalCol1, SJ.additionalCol2
FROM   numbered n
JOIN   ServiceJob  SJ ON SJ.primaryKey = n.primaryKey
WHERE  n.rowno = 1
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Erland,

That looks to fit the bill perfectly! Many thanks for the assistance.

-Peter