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 |
|
Tonic
Starting Member
26 Posts |
 Posted - 2006-05-21 : 09:21:34
|
| Greetings,I have the following problem and can't for the life of me work out the TSQL solution. 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.propcatFROM 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 ways1. 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.RegardsToni Caka Talisa |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-21 : 14:49:30
|
| 1)Which row do you want? Can use the PK on src_tbl_budgetjoin src_tbl_budgeton src_tbl_budget.PK = (select max(t2.PK) from src_tbl_budget t2 where t2.siteref = src_terrier.siteref)2)Not sure what the question is - maybeon 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)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Tonic
Starting Member
26 Posts |
Posted - 2006-05-22 : 05:07:57
|
| OKI have a table that contains two divisions (div_code e.g 'SW') a number of Sites (site_ref e.g. 'AC'), within these sites is a varying amount of properties(unit_ref e.g. AC001.D). This table also contains a significant amount of financial information that is at unit_ref level (rents, discounts etc and so on). All of this data is collated and grouped by etc for a report that is in Reporting Services 2005. The report as is works 100% without error to this point.This report has drill down capability that initially displays Division (NE or SW) which shows SUM info for every site_ref that is in the relevant division, then drill through to the site_ref which shows SUM info for every unit_ref that is located at the site.I have a seperate table that contains budget information (site_ref, src_date, budgetrent, budgetocc). This information is only detailed at site level (example AC, 01/04/2006,£25000,94%)When I collate all of the info I would like to create a query result that shows the budget information on one line. I do not care whether it only shows it once against the Min(unit_ref). Example:- NE, AC, AC001.D,all other fields, budget info, then the next row would just be NE, AC, AC001.A, all other fields, then NE blah blah. The point is that no budget info appears after the first unit on a per site basis.Is that any clearer?RegardsToni Caka Talisa |
|
|
|
|
|
|
|
|
Mental TSQL Block