April 9, 2003 at 1:51 pm
I have a parent table (Site) and two child tables (Customer and Opportunity) that each have the columns CreateDate and DateChgd.
How can I find the most recent date of a parent record?
Thanks
AndreQ
April 9, 2003 at 2:00 pm
How about:
SELECT MAX(DateChgd)
FROM Site
April 9, 2003 at 2:04 pm
The columns are:
Site.CreateDate
Site.DateChgd
Contact.CreateDate
Contact.DateChgd
Opportunity.CreateDate
Opportunity.DateChgd
There is a 1 to many relationship with Site being the parent table for Contact and Opportunity.
All tables have the InternalID column in common. I'd like to find the absolute Max of the six columns.
I think Max(Date) alone would only give me the one column.
I tried using Case with only limited success. I will say that DateChgd is always either more than CreateDate or 1/1/1900 so I combined those 2 with a Case/When.
Regards
Andre
April 9, 2003 at 2:59 pm
I have a possible solution, but its pretty ugly and uses a number of temporary tables. Can someone think of a more elegant solution? I'm a big fan of simplicity.
thanks
drop table #contactDate
drop table #groupDate
drop table #opportunDate
drop table #siteDate
drop table #topDate
-- best date for each _internid record from Contact
select_internid
, max( case _date_chgd
when convert(datetime, '1/1/1900') then _created_date
else _date_chgd
end ) as MaxDate
into #contactDate
fromcontact
group by _internid
-- best date for each _internid record from Opportun
select_internid
, max( case _date_chgd
when convert(datetime, '1/1/1900') then _create_date
else _date_chgd
end ) as MaxDate
into#opportunDate
fromopportun
group by _internid
-- best date for each _internid record from Site
select_internid
, MaxDate = case _date_chgd
when convert(datetime, '1/1/1900') then _create_date
else _date_chgd
end
into#siteDate
fromsite
-- combine results together - just copy Contact
select _internid
, MaxDate as BestDate
into#groupDate
from#contactDate
-- only change the date if the new temporary table has a later date
update #groupDate
set#groupDate.BestDate = #opportunDate.MaxDate
from #groupDate
, #opportunDate
where#groupDate._internid = #opportunDate._internid
and#opportunDate.MaxDate > #groupDate.BestDate
update #groupDate
set#groupDate.BestDate = #siteDate.MaxDate
from #groupDate
, #siteDate
where#groupDate._internid = #siteDate._internid
and#siteDate.MaxDate > #groupDate.BestDate
April 9, 2003 at 3:36 pm
I'm not sure exactly what you are looking for. But if you only want to return a single date the max date whether it is CreateDate or DateChgd by and InternalID common to all tables. Then possibly some form or the UNION will work for you. Here is an example (SITE_ID and/or ID is the InternalID common across all tables). Let me know if this is what you were looking for.
create table Site (id int,CreateDate datetime,DateChgd datetime)
create table contact (id int,site_id int, CreateDate datetime, DateChgd datetime)
create table opportunity (id int,site_id int, CreateDate datetime, datechgd datetime)
insert into site values(1,'2003-01-01','2003-01-05')
insert into site values(2,'2003-02-01','2003-02-05')
insert into site values(3,'2003-03-01','2003-03-05')
insert into site values(3,'2003-03-01','2003-03-05')
insert into contact values(1,1,'2003-01-02','2003-01-04')
insert into contact values(2,2,'2003-02-02','2003-02-06')
insert into contact values(3,3,'2003-02-02','2003-02-06')
insert into contact values(4,4,'2003-04-02','1900-01-01')
insert into opportunity values(1,1,'2003-01-02','2003-01-04')
insert into opportunity values(2,2,'2003-02-02','2003-02-05')
insert into opportunity values(3,3,'2003-03-02','2003-03-07')
insert into opportunity values(4,4,'2003-04-08','1900-01-01')
select site_id, max(datex) "Max CreateDate or DateChgd" from
(select id site_id, max(CreateDate) datex from site group by id
union
select site_id site_id, max(CreateDate) datex from contact group by site_id
union
select site_id site_id, max(CreateDate) datex from opportunity group by site_id
union
select id site_id, max(DateChgd) datex from site group by id
union
select site_id site_id, max(DateChgd) datex from contact group by site_id
union
select site_id site_id, max(DateChgd) datex from opportunity group by site_id ) a
group by site_id
drop table site, contact, opportunity
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 9, 2003 at 3:59 pm
That did it. Thanks very much.
April 15, 2003 at 2:44 pm
That all seems to complicated... why not use Sql 2000's table data type in a stored procedure?
DECLARE @Dates TABLE ( recent_date datetime )
INSERT INTO @Dates SELECT Site.CreateDate FROM <TABLE>
INSERT INTO @Dates SELECT Site.DateChgd FROM <TABLE>
INSERT INTO @Dates SELECT Contact.CreateDate FROM <TABLE>
INSERT INTO @Dates SELECT Contact.DateChgd FROM <TABLE>
INSERT INTO @Dates SELECT Opportunity.CreateDate FROM <TABLE>
INSERT INTO @Dates SELECT Opportunity.DateChgd FROM <TABLE>
Select Max(recent_date)
From @Dates
-Will
April 15, 2003 at 2:46 pm
I would really love to. Unfortunately, the database is a little old: SQL Server 6.5.
I can't tell you how many queries I've rewritten to dumb them down.
April 15, 2003 at 3:06 pm
Your method works as well and might be easier to read, but my quick test suggests it takes about twices as long as the set based UNION example.
Guess it depends what is important which might be better. I almost always go for the effecient code.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 16, 2003 at 6:14 am
Hey Greg-
What type of tests did you perform? It will be interesting to see the differences.
-Will
April 16, 2003 at 9:28 am
Here is the test I ran. The memory table consistantly takes longer. Now this is only on a small set of records. I replicated the insert statements a couple thousand times and found that on a large set of records the in memory table is quite a bit slow. If for some reason you think this test is flawed please let me know....
set nocount on
create table Site (id int,CreateDate datetime,DateChgd datetime)
create table contact (id int,site_id int, CreateDate datetime, DateChgd datetime)
create table opportunity (id int,site_id int, CreateDate datetime, datechgd datetime)
insert into site values(1,'2003-01-01','2003-01-05')
insert into site values(2,'2003-02-01','2003-02-05')
insert into site values(3,'2003-03-01','2003-03-05')
insert into site values(3,'2003-03-01','2003-03-05')
insert into contact values(1,1,'2003-01-02','2003-01-04')
insert into contact values(2,2,'2003-02-02','2003-02-06')
insert into contact values(3,3,'2003-02-02','2003-02-06')
insert into contact values(4,4,'2003-04-02','1900-01-01')
insert into opportunity values(1,1,'2003-01-02','2003-01-04')
insert into opportunity values(2,2,'2003-02-02','2003-02-05')
insert into opportunity values(3,3,'2003-03-02','2003-03-07')
insert into opportunity values(4,4,'2003-04-08','1900-01-01')
declare @s-2 datetime
declare @D1 datetime
declare @dt INT
declare @i int
declare @t1 int
declare @t2 datetime
set @s-2 = getdate()
set @i = 0
while @I < 100
begin
select @t1=site_id, @t2=max(datex) from
(select id site_id, max(CreateDate) datex from site group by id
union
select site_id site_id, max(CreateDate) datex from contact group by site_id
union
select site_id site_id, max(CreateDate) datex from opportunity group by site_id
union
select id site_id, max(DateChgd) datex from site group by id
union
select site_id site_id, max(DateChgd) datex from contact group by site_id
union
select site_id site_id, max(DateChgd) datex from opportunity group by site_id ) a
group by site_id
set @i = @i + 1
end
print 'Time using union'
select datediff(ms,@s,getdate())
DECLARE @Dates TABLE ( recent_date datetime )
set @s-2 = getdate()
SET @dt = 0
set @i = 0
while @I < 100
begin
INSERT INTO @Dates SELECT Site.CreateDate FROM SITE
INSERT INTO @Dates SELECT Site.DateChgd FROM SITE
INSERT INTO @Dates SELECT Contact.CreateDate FROM CONTACT
INSERT INTO @Dates SELECT Contact.DateChgd FROM CONTACT
INSERT INTO @Dates SELECT Opportunity.CreateDate FROM OPPORTUNITY
INSERT INTO @Dates SELECT Opportunity.DateChgd FROM OPPORTUNITY
Select @t2=Max(recent_date)
From @Dates
SELECT @D1 = GETDATE()
DELETE FROM @DATES
SET @dt = DATEDIFF(MS,@D1,GETDATE()) + @dt
SET @I = @I + 1
end
-- Remove the time it takes to delete the in memory table
Print 'time using memory table'
select datediff(ms,@s,getdate()) - @dt
drop table site, contact, opportunity
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 16, 2003 at 12:34 pm
This variation (using UNION) halves the number of table scans since you only need to scan each table once
create table Site (id int,CreateDate datetime,DateChgd datetime)
create table contact (id int,site_id int, CreateDate datetime, DateChgd datetime)
create table opportunity (id int,site_id int, CreateDate datetime, datechgd datetime)
insert into site values(1,'2003-01-01','2003-01-05')
insert into site values(2,'2003-02-01','2003-02-05')
insert into site values(3,'2003-03-01','2003-03-05')
insert into site values(3,'2003-03-01','2003-03-05')
insert into contact values(1,1,'2003-01-02','2003-01-04')
insert into contact values(2,2,'2003-02-02','2003-02-06')
insert into contact values(3,3,'2003-02-02','2003-02-06')
insert into contact values(4,4,'2003-04-02','1900-01-01')
insert into opportunity values(1,1,'2003-01-02','2003-01-04')
insert into opportunity values(2,2,'2003-02-02','2003-02-05')
insert into opportunity values(3,3,'2003-03-02','2003-03-07')
insert into opportunity values(4,4,'2003-04-08','1900-01-01')
select site_id, max(LastDate) "LastMovement" from
(select id site_id,
max(dateadd(day,sign(1+sign(datediff(day,CreateDate,DateChgd)))*datediff(day,CreateDate,DateChgd),CreateDate)) LastDate
from site group by id
union
select site_id,
max(dateadd(day,sign(1+sign(datediff(day,CreateDate,DateChgd)))*datediff(day,CreateDate,DateChgd),CreateDate)) LastDate
from contact group by site_id
union
select site_id,
max(dateadd(day,sign(1+sign(datediff(day,CreateDate,DateChgd)))*datediff(day,CreateDate,DateChgd),CreateDate)) LastDate
from opportunity group by site_id) a
group by site_id
drop table site, contact, opportunity
April 16, 2003 at 12:57 pm
Now there is an interesting twist to get the max date between two different dates. Great way to eliminate multiple pass of the data.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 16, 2003 at 1:58 pm
Took CQL's concept and created solution without union. Don't try this at home......it is not as fast, and I pulled a little hair out trying to write this weird logic. Thanks for the concept CQL.
set nocount on
create table Site (id int,CreateDate datetime,DateChgd datetime)
create table contact (id int,site_id int, CreateDate datetime, DateChgd datetime)
create table opportunity (id int,site_id int, CreateDate datetime, datechgd datetime)
insert into site values(1,'2003-01-01','2003-01-05')
insert into site values(2,'2003-02-01','2003-02-05')
insert into site values(3,'2003-03-01','2003-03-05')
insert into site values(4,'2003-03-01','2003-03-05')
insert into contact values(1,1,'2003-01-02','2003-01-04')
insert into contact values(2,2,'2003-02-02','2003-02-06')
insert into contact values(3,3,'2003-02-02','2003-02-06')
insert into contact values(4,4,'2003-04-02','1900-01-01')
insert into opportunity values(1,1,'2003-01-02','2003-01-04')
insert into opportunity values(2,2,'2003-02-02','2003-02-05')
insert into opportunity values(3,3,'2003-03-02','2003-03-07')
insert into opportunity values(4,4,'2003-04-08','1900-01-01')
select s.id,
dateadd(day,sign(1+sign(datediff(day,dateadd(day,sign(1+sign(datediff(day,dateadd(day,sign(1+sign(datediff(day,s.CreateDate,s.DateChgd)))*datediff(day,s.CreateDate,s.DateChgd),s.CreateDate),dateadd(day,sign(1+sign(datediff(day,o.CreateDate,o.DateChgd)))*datediff(day,o.CreateDate,o.DateChgd),o.CreateDate))))*datediff(day,dateadd(day,sign(1+sign(datediff(day,s.CreateDate,s.DateChgd)))*datediff(day,s.CreateDate,s.DateChgd),s.CreateDate),dateadd(day,sign(1+sign(datediff(day,o.CreateDate,o.DateChgd)))*datediff(day,o.CreateDate,o.DateChgd),o.CreateDate)),dateadd(day,sign(1+sign(datediff(day,s.CreateDate,s.DateChgd)))*datediff(day,s.CreateDate,s.DateChgd),s.CreateDate)),dateadd(day,sign(1+sign(datediff(day,c.CreateDate,c.DateChgd)))*datediff(day,c.CreateDate,c.DateChgd),c.CreateDate))))*datediff(day,dateadd(day,sign(1+sign(datediff(day,dateadd(day,sign(1+sign(datediff(day,s.CreateDate,s.DateChgd)))*datediff(day,s.CreateDate,s.DateChgd),s.CreateDate),dateadd(day,sign(1+sign(datediff(day,o.CreateDate,o.DateChgd)))*datediff(day,o.CreateDate,o.DateChgd),o.CreateDate))))*datediff(day,dateadd(day,sign(1+sign(datediff(day,s.CreateDate,s.DateChgd)))*datediff(day,s.CreateDate,s.DateChgd),s.CreateDate),dateadd(day,sign(1+sign(datediff(day,o.CreateDate,o.DateChgd)))*datediff(day,o.CreateDate,o.DateChgd),o.CreateDate)),dateadd(day,sign(1+sign(datediff(day,s.CreateDate,s.DateChgd)))*datediff(day,s.CreateDate,s.DateChgd),s.CreateDate)),dateadd(day,sign(1+sign(datediff(day,c.CreateDate,c.DateChgd)))*datediff(day,c.CreateDate,c.DateChgd),c.CreateDate)),dateadd(day,sign(1+sign(datediff(day,dateadd(day,sign(1+sign(datediff(day,s.CreateDate,s.DateChgd)))*datediff(day,s.CreateDate,s.DateChgd),s.CreateDate),dateadd(day,sign(1+sign(datediff(day,o.CreateDate,o.DateChgd)))*datediff(day,o.CreateDate,o.DateChgd),o.CreateDate))))*datediff(day,dateadd(day,sign(1+sign(datediff(day,s.CreateDate,s.DateChgd)))*datediff(day,s.CreateDate,s.DateChgd),s.CreateDate),dateadd(day,sign(1+sign(datediff(day,o.CreateDate,o.DateChgd)))*datediff(day,o.CreateDate,o.DateChgd),o.CreateDate)),dateadd(day,sign(1+sign(datediff(day,s.CreateDate,s.DateChgd)))*datediff(day,s.CreateDate,s.DateChgd),s.CreateDate)))
from site s join opportunity o on s.id = o.site_id join contact c on s.id = c.site_id
drop table site, contact, opportunity
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply