SQL Server Performance Forum – Threads Archive

TSQL Mental Block

Please see the post below by me for the updated version which hopefully a little more simplified. Regards Toni Chaffin
aka Toni

> Does that make any sense at all? Not a lot, no <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Why not paste some simplified data output, its difficult to pick the bones out of the problem without examples

OK, I will do that and try to make it a little more simplified. Back shortly Regards Toni Chaffin
aka Toni

OK, I have a SELECT query within a function that contains the follwoing code; SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype, src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus, src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent, src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate, src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat FROM src_terrier INNER JOIN src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no WHERE (src_terrier.datadate = @dt_src_date) AND (@chr_div is null or src_terrier.Areacode = @chr_div) AND (@vch_portfolio_no is null or src_centre_list.Portfolio_no = @vch_portfolio_no) AND (@vch_prop_cat is null or src_centre_list.propcat = @vch_prop_cat) How can I modify this function in two ways 1. I want the value of src_terrier.siteref to be matched to a different table src_tbl_budget.siteref. I want all rows in src_terrier.siteref and only the one row from the new table where the siteref is matched. 2. Also part of the new query, not only do I want it to match the siteref, I also want it to only match the mm/yyyy of the parameter entered (src_terrier.datadate = @dt_src_date) in the WHERE statement. Is that any simpler? Regards Toni Chaffin
aka Toni

1)
Which row do you want? Can use the PK on src_tbl_budget join src_tbl_budget
on src_tbl_budget.PK = (select max(t2.PK) from src_tbl_budget t2 where t2.siteref = src_terrier.siteref) maybe should be a left join? 2)
Not sure what the question is – maybe
on src_tbl_budget.PK = (select max(t2.PK) from src_tbl_budget t2 where t2.siteref = src_terrier.siteref and t2.datadate = @dt_src_date)

I can’t work out how to explain what I am trying to do so I will just end up confusing everybody even more. I will go away and think on it. Toni Chaffin
aka Toni

This is usually a great help to figure out what you want:<a target="_blank" href=http://www.aspfaq.com/etiquette.asp?id=5006>http://www.aspfaq.com/etiquette.asp?id=5006</a> Maybe it also helps you? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

This is getting way too complicated. I kind of know now what direction to head in so this thread will just confuse me even more. My sincerest thanks to all Toni Chaffin
aka Toni

]]>