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-23 : 02:24:53
|
| I have the final part of my report to complete. Yesterday I posted the most confusing set of threads ever on a public forum. So to cut the confusion, I am starting this thread again as now I know exactly what I need.I have two tables each of which have a function that gathers data from them based around a set of parameters. This is then manipulated by a third function and the results of which are passed into a Stored Procedure. Here is the T-SQL syntax used for the SP;SELECT fnWTRalldataReport.Areacode, fnWTRalldataReport.siteref, fnWTRalldataReport.estatename, fnWTRalldataReport.Securitised, fnWTRalldataReport.unitref, fnWTRalldataReport.unittype, fnWTRalldataReport.unittype_count, fnWTRalldataReport.tenantname, fnWTRalldataReport.tenantstatus, fnWTRalldataReport.tenantstatus_count, fnWTRalldataReport.unitstatus, fnWTRalldataReport.unitstatus_count, fnWTRalldataReport.floortotal, fnWTRalldataReport.floortotocc, fnWTRalldataReport.floorspaceperc, fnWTRalldataReport.initialvacarea, fnWTRalldataReport.initialvacnet, fnWTRalldataReport.TotalRent, fnWTRalldataReport.NetRent, fnWTRalldataReport.FinalRtLsincSC, fnWTRalldataReport.rentrolldiscperc, fnWTRalldataReport.netrentpersqft, fnWTRalldataReport.ErvTot, fnWTRalldataReport.tenancyterm, fnWTRalldataReport.landact, fnWTRalldataReport.datadate, fnWTRalldataReport.div_mgr, fnWTRalldataReport.portfolio_mgr, fnWTRalldataReport.propcat, fnWTRalldataReport.budgeted_net_rent, fnWTRalldataReport.budgeted_occupancy, fnWTRbudgetdata_1.budgeted_net_rent AS budget_rent, fnWTRbudgetdata_1.budgeted_occupancy AS budget_occFROM dbo.fnWTRalldataReport(@dt_src_date, @chr_div, @vch_portfolio_no, @vch_prop_cat) AS fnWTRalldataReport LEFT OUTER JOINdbo.fnWTRbudgetdata(@dt_src_date) AS fnWTRbudgetdata_1 ON fnWTRalldataReport.siteref = fnWTRbudgetdata_1.site_refThe result of this SQL places a value for budget_rent and budget_occ against every row that the 1st function's result set. What I want to achieve is that where the site_ref is equal in both functions results, I want it to place the budget_rent & budget_occ value against the first row only of each site_ref only.To explain briefly the structure. Table one has various fields including site_ref and unit_ref. There are many unit_ref's per site_ref in this table. Table 2 has only site_ref and budget info. Someone yesterday suggested that I could achieve this my using something along the lines of the Min() function e.g. Min(unit_ref).Could someone please elaborate on this for me. I have gone through my SQL book and read about min() and also BOL, but I can't quite work the syntax out to put the budget info against only one line per site based around the lowest unit_ref per site_ref.This might seem confusing, but it is easier to read than the other thread I assure you.RegardsToni Caka Talisa |
|
|
kristianwhittick
Starting Member
12 Posts |
Posted - 2006-06-06 : 11:09:10
|
| Your problem is still unclear.Simplify the number of columns and add some data examples.Kris |
 |
|
|
|
|
|
|
|
Min() Function