Query - is this possible? 2

[SQLScholar]

Hey all,

We have data that looks like this for table one

Date Policy number
01/10/2006 JA10
01/11/2006 JA10
01/10/2007 JA10
01/11/2007 JA10

and a another that looks like this

Policy number Start Date End date GUID
JA10 01/12/2005 31/11/2006 12312712312
JA10 01/12/2006 31/11/2007 91274728167

(i know the guids are real - they are on the DB).

Basically there should have been a join between these two tables - and there isnt. What we really need is to corrolate the date within the TABLE1 to get the guid out of TABLE2. So effectively the first two rows in TABLE1 fall between the start and end dates in table 2 of the first record (for this policy number - there will be many others). So it gets assigned "12312712312". The second two fall into the dates of the second line and be given "91274728167".

Is there any code that could do this? I cant think of how this would work?

Also we have another issue - there may be a few where they dont fall into either. In this case i need to leave them blank for me to work on later (or null).

Any ideas what to look at?

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[Otto]

select T1.*,
(select top 1 GUID from T2 where PNUMBER=T1.PNUMBER and T1.DATE between STARTDATE and ENDDATE) as GUID
from T1

 

[gmmastros]

I think you can do this on the join between the two tables. Here's an example that I worked up for you. Double check that it's right.

Set DATEFORMAT DMY

Declare @T1 Table(Date DateTime, PolicyNumber VarChar(20))

Insert Into @T1 Values('01/10/2006','JA10')
Insert Into @T1 Values('01/11/2006','JA10')
Insert Into @T1 Values('01/10/2007','JA10')
Insert Into @T1 Values('01/11/2007','JA10')
Insert Into @T1 Values('01/11/2002','JA10')

Declare @T2 Table(PolicyNumber VarChar(20), StartDate DateTime, EndDate DateTime, GUID VarCHar(20))
Insert Into @T2 Values('JA10','01/12/2005','30/11/2006','12312712312')
Insert Into @T2 Values('JA10','01/12/2006','30/11/2007','91274728167')

Select  T1.PolicyNumber, T1.Date, T2.Guid
From    @T1 As T1
        Left Join @T2 T2
          On T1.PolicyNumber = T2.PolicyNumber
          And T1.Date Between T2.StartDate And T2.EndDate

Notice that I added another record to T1 that falls outside any date range, and the query returns NULL. I think this is what you want.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLScholar]

Aha - i didnt think of it like that. Using Georges code, as it makes more sense to me (each to there own).

Thanks!

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

Actually - can i ask one other question.

Is there any way to modify this code to bring back all the ones that end up null (without having to run this then find out afterwards)

Its just before i start i want to know if there is many that do not fall into a date band for a policy?

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

Add a where clause.

Where T2.GUID Is NULL

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom