SQL Server Performance Forum – Threads Archive
Min() Function
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_occ FROM dbo.fnWTRalldataReport(@dt_src_date, @chr_div, @vch_portfolio_no, @vch_prop_cat) AS fnWTRalldataReport LEFT OUTER JOIN dbo.fnWTRbudgetdata(@dt_src_date) AS fnWTRbudgetdata_1 ON fnWTRalldataReport.siteref = fnWTRbudgetdata_1.site_ref The 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. Regards Toni Chaffinaka Toni
Yes. It is still confusing.
Here are few suggestion
– You should simplified the table structure and leave only those columns that are required for the joins and as well s the PKs.
– Use simple table name and column name. fnWTRalldataReport will mean something to you but to others it does not mean anything.
– Also post some sample records for each of the tables and the result that you want. You can treat the function table as one table and post the structure and sample records of the function table. I will give it a try to your problem if it isn’t what you want, try what i am suggesting above.
select . . . .
fnWTRalldataReport.unit_ref,
fnWTRbudgetdata_1.budgeted_net_rent AS budget_rent, fnWTRbudgetdata_1.budgeted_occupancy AS budget_occ,
FROM dbo.fnWTRalldataReport(@dt_src_date, @chr_div, @vch_portfolio_no, @vch_prop_cat) AS fnWTRalldataReport LEFT OUTER JOIN
dbo.fnWTRbudgetdata(@dt_src_date) AS fnWTRbudgetdata_1
ON fnWTRalldataReport.siteref = fnWTRbudgetdata_1.site_ref
AND fnWTRalldataReport.unit_ref = (select min(unit_ref)
from dbo.fnWTRalldataReport(@dt_src_date, @chr_div, @vch_portfolio_no, @vch_prop_cat) x
where x.site_ref = fnWTRalldataReport.siteref)
KH
]]>