Update info in table2 with data from table1 - with nothing to JOIN

[bmacbmac]

Hi.

In SQL 2008 I have a table representing the beginning and ending date of each year:

create table #temp1 (begdate datetime, enddate datetime, mindocno int, maxdocno int)

insert into #temp1 (begdate, enddate) values (2011-01-01 00:00:00.000, 2011-12-31 23:59:59.997)
insert into #temp1 (begdate, enddate) values (2010-01-01 00:00:00.000, 2010-12-31 23:59:59.997)
insert into #temp1 (begdate, enddate) values (2009-01-01 00:00:00.000, 2009-12-31 23:59:59.997)

I have another table with document numbers and dates:

create table #temp2 (docno int, recordeddate datetime)
insert into #temp2 values (11111, '01-01-2009')
insert into #temp2 values (22222, '03-01-2009')
insert into #temp2 values (33333, '12-01-2009')
insert into #temp2 values (44444, '01-01-2010')
insert into #temp2 values (55555, '05-01-2010')
insert into #temp2 values (66666, '12-31-2010')
insert into #temp2 values (77777, '01-28-2011')
insert into #temp2 values (88888, '01-29-2011')
insert into #temp2 values (99999, '10-13-2011')

I am wanting to update the #temp1.mindocno with the lowest temp2.docno between #temp1.begdate and #temp2.begdate

and update #temp2.maxdocno with the highest temp2.docno between #temp1.begdate and #temp2.begdate

So something like this:

#temp1
01-01-2011, 12-31-2011, 77777, 99999
01-01-2010, 12-31-2010, 44444, 66666
01-01-2009, 12-31-2009, 11111, 33333


Is there a way to do this? I don't really have a way to join the two tables so I'm not sure which way to go.

Thanks!

 

[gmmastros]

nothing to JOIN

This isn't exactly true, you have a date in temp 2 that falls within the date range in temp 1, so you can run a query like this:

Select #Temp1.begdate, 
       Min(#Temp2.docno) As MinDocNo, 
       Max(#Temp2.docno) As MaxDocNo
From   #Temp1
       Inner Join #Temp2 
         On #Temp2.RecordedDate Between #Temp1.begdate and #Temp1.enddate
Group By #Temp1.begdate

Notice that the ON clause has a between statement for the condition. This is completely valid syntax.

Unfortunately, you cannot directly update the first table with an aggregate from the second, so you need to use a temp table, table variable, or CTE to accomplish the update.

;With Data as
(
Select #Temp1.begdate, Min(#Temp2.docno) As MinDocNo, Max(#Temp2.docno) As MaxDocNo
From   #Temp1
       Inner Join #Temp2 
         On #Temp2.RecordedDate Between #Temp1.begdate and #Temp1.enddate
Group By #Temp1.begdate
) 
Update #Temp1
Set    #Temp1.maxdocno = Data.maxdocno,
       #Temp1.mindocno = data.mindocno
From   #Temp1
       Inner Join Data
         On #Temp1.begdate = data.begdate

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Another perfect solution. Thanks!