Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sb2304
Starting Member
2 Posts |
 Posted - 2010-12-09 : 04:10:54
|
| Hi,Wondering if someone will be able to help me. I have the following query which is returning the most recent address details created from the s_add_per table connected to an active or in progess policy but on some occasions the address connected to the policy is not the most recent in the s_addr_per table. In other words someone may have created a non UK address on their policy on 01/11/2010 but on 01/12/2010 changed it back to an older UK address. At the moment my query is returning non UK addresses even though they have a UK address attached to the policy.select from s_doc_agree a, s_contact b, s_addr_per cwhere a.con_per_id = b.row_idand b.row_id = c.per_idand c.created = (select max(created) from s_addr_per d where c.per_id = d.per_id)and a.stat_cd in ('Active','In Progress')and c.country not in ('United Kingdom', 'Ireland')Many Thanks in advance, and hope this makes senseSophiep.s Haven't decided on the selects I need yet |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-12-09 : 04:18:27
|
Your query can't return a UK address because of this part:and c.country not in ('United Kingdom', 'Ireland')But it is not easy to understand what you really want.Best is to give us table structure, sample data and wanted result.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
sb2304
Starting Member
2 Posts |
Posted - 2010-12-09 : 04:43:47
|
| Hi,Sorry for not making much sense.I want to get a list of policys that have Non UK address attached to them however at the moment my querying is returning a list stating Non UK address however when I look at the policy it actually has a UK address attached to it. The Non UK address was created 01/11/2010 and the UK was created on 15/10/2009 but the address went from Non UK to UK on 01/12/2010 and so I do not want it to return that policy.s_doc_agree a con_per_id Policy number Status1 123456 Active s_contact b row_id Name Last updated1 Smith 01/12/2010 s_addr_per c per_id country created1 UK 15/10/20091 France 01/11/2010Is this any clearer?Sophie |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-12-09 : 04:47:27
|
But in s_addr_per you can't see that the address went from Non UK to UK on 01/12/2010 ...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-09 : 04:54:06
|
| You presumably have a table that links the policy to the addresses? It might be s_contact.That should have the type of link - maybe current, preferred or just a date when it is active from.You need to use that to fid the current address. I think you are using the address created date which presumably is when the address is added to the system so if the current address is set to an old one your query won't pick that up. You assume that the latest created is the current one used.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Address help