July 11, 2013 at 11:07 am
I'm thinking there's some clever way of doing this, probably involving a CTE and recursion, but I'm not seeing how to get started....
Given a [Table1] with a primary key called [Table1Key].
There are 30 tables with [Table1Key] as a foreign key pointing back to [Table1], call these [RTable01] through [RTable30]. Any specific Table1 row may have related rows in 0 to 30 of these tables.
Each of the related tables has a [LastModifiedDate], as does the parent Table1 row.
Given two Table1 rows, I want to flag one as 'Most Current' based on the latest of ANY of the LastModifiedDates in related rows and the parent row.
I can certainly do this in a brute force ROAR fashion, but I'm trying to stop doing things like that. Any pointers?
July 11, 2013 at 12:36 pm
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
Certainly no need to post ALL 30 tables but maybe 2 or 3 would illustrate what you are trying to here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2013 at 1:16 pm
Something like this should give you the most recent LastModifiedDate for each Table1Key.
select Table1Key, MAX(LastModifiedDate)
from Table1
outer apply (
select Table1.LastModifiedDate
union all
select LastModifiedDate
from RTable01
where RTable01.Table1Key = Table1.Table1Key
union all
select LastModifiedDate
from RTable02
where RTable02.Table1Key = Table1.Table1Key
...
union all
select LastModifiedDate
from RTable30
where RTable30.Table1Key = Table1.Table1Key
) AllTables(LastModifiedDate)
group by Table1.Table1Key
However, as Sean said, if you want a tested solid answer, post the information requested 😉
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
July 11, 2013 at 1:24 pm
Does this give you what you need?
--------------------------
--DDL
--------------------------
CREATE SCHEMA [SAMPLE]
GO
IPCode is PK
CREATE TABLE [sample].127.0.0.1(
[IPCode] [int] NOT NULL,
[UpdateDate] [datetime] NULL)
GO
--IPCode is FK
CREATE TABLE [sample].[AddressProfile](
[IPCode] [int] NOT NULL,
[UpdateDate] [datetime] NULL)
GO
--IPCode is FK
CREATE TABLE [sample].[EmailProfile](
[IPCode] [int] NOT NULL,
[UpdateDate] [datetime] NULL)
GO
--IPCode is FK
CREATE TABLE [sample].[PhoneProfile](
[IPCode] [int] NOT NULL,
[UpdateDate] [datetime] NULL)
GO
--------------------------
--Sample data inserts
--------------------------
INSERT INTO [SAMPLE].127.0.0.1( [UpdateDate] ) VALUES(1000000,'2013-07-11 18:51:32')
INSERT INTO [SAMPLE].127.0.0.1( [UpdateDate] ) VALUES(1000001,'2012-07-11 18:51:32')
INSERT INTO [SAMPLE].127.0.0.1( [UpdateDate] ) VALUES(1000002,'2011-07-11 18:51:32')
INSERT INTO [SAMPLE].127.0.0.1( [UpdateDate] ) VALUES(1000003,'2010-07-11 18:51:32')
INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000000,'2013-06-11 18:54:24')
INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000000,'2013-04-11 18:54:24')
INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000001,'2013-05-11 18:54:24')
INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000001,'2013-02-11 18:54:24')
INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000001,'2013-03-11 18:54:24')
INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000002,'2013-11-11 18:54:24')
INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000002,'2013-12-11 18:54:24')
INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000003,'2013-09-11 18:54:24')
INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000003,'2013-06-11 18:54:24')
INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000000,'2013-06-21 18:54:24')
INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000000,'2013-04-23 18:54:24')
INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000001,'2013-05-02 18:54:24')
INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000001,'2013-02-12 18:54:24')
INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000001,'2013-03-05 18:54:24')
INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000002,'2013-11-07 18:54:24')
INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000002,'2013-12-02 18:54:24')
INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000003,'2013-09-09 18:54:24')
INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000003,'2013-06-10 18:54:24')
INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000000,'2010-06-02 18:54:24')
INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000000,'2012-04-05 18:54:24')
INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000001,'2012-05-12 18:54:24')
INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000001,'2012-02-08 18:54:24')
INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000001,'2012-03-09 18:54:24')
INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000002,'2012-11-10 18:54:24')
INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000002,'2012-12-11 18:54:24')
INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000003,'2012-09-01 18:54:24')
INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES ( 1000003,'2012-06-02 18:54:24')
Sample output would look like:
myFunction(1000000) returns '2013-07-11 18:51:32'
myFunction(1000001) returns '2013-05-11 18:54:24'
myFunction(1000002) returns '2013-12-11 18:54:24'
myFunction(1000003) returns '2013-09-11 18:54:24'
IP is the parent table, the profiles are the 0 to 30 related tables from which I need to extract the earliest date. The column names are consistent across all tables.
July 11, 2013 at 1:28 pm
Ah ha, OUTER APPLY....
I've never use that. I barely know it exists. Can you give me any good links to help figuring out how it works? I can sort of see what's going on, but I haven't grokked the fullness.
Now I get to figure out something new! Thanks!
p.s. The requested DDL and data inserts are posted.
July 11, 2013 at 1:50 pm
Thanks for the ddl and sample data. Using MM's excellent example...
select IPCode, Max(LastUpdateDate)
from
IP i
outer apply
(
select UpdateDate from IP where IPCode = i.IPCode
union all
select UpdateDate from AddressProfile where IPCode = i.IPCode
union all
select UpdateDate from EmailProfile where IPCode = i.IPCode
union all
select UpdateDate from PhoneProfile where IPCode = i.IPCode
) x (LastUpdateDate)
group by i.IPCode
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2013 at 4:19 pm
SELECT IP.IPCode, MAX(AllSubtables.UpdateDate) AS LastUpdateDate
FROM SAMPLE.IP IP
INNER JOIN (
SELECT IPCode, MAX(UpdateDate) AS UpdateDate FROM SAMPLE.IP IP2 GROUP BY IP2.IPCode
UNION ALL
SELECT IPCode, MAX(UpdateDate) AS UpdateDate FROM SAMPLE.AddressProfile AP GROUP BY AP.IPCode
UNION ALL
SELECT IPCode, MAX(UpdateDate) AS UpdateDate FROM SAMPLE.EmailProfile EP GROUP BY EP.IPCode
UNION ALL
SELECT IPCode, MAX(UpdateDate) AS UpdateDate FROM SAMPLE.PhoneProfile PP GROUP BY PP.IPCode
--UNION ALL ...
) AS AllSubtables ON
AllSubtables.IPCode = IP.IPCode
GROUP BY
IP.IPCode
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply