May 23, 2006 at 12:30 am
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
May 23, 2006 at 2:26 am
Clarify, If you want the minimum Budgeted values from all the budgeted figures given a site_ref?
May 23, 2006 at 3:57 am
Hi, ok to clarify. There will only be one set of budget information per site_ref. On the first row that has the min(unit_ref) I would like that budget information placed. The problem is that
src_terrier contains multiple rows per site_ref. Each of those rows has a unique unit_ref
Site_ref unit_ref finanance
AC AC.001 £27
AC AC.002 £45
src_budget contains one row per site_ref
site_ref bud-rent bud_occ
AC £50 94%
So I would like the result table to be as follows
Site_ref unit_ref finanance bud-rent bud-occ
AC AC.001 £27 £50 94%
AC AC.002 £45 Either Null or Zero in these two fields
Any clearer?
May 23, 2006 at 5:42 am
Select your report resultset into temp table ( SELECT ... INTO #Table FROM ...) and LEFT JOIN this table to grouped select from itself.
SELECT A.*, B.bud-rent, COUNT(B.???)*100/COUNT(A.???) as bud-occ
FROM #Table A
INNER JOIN (select Site_ref, MIN(rent) as bud-rent, COUNT(???) as ???
from #Table
group by Site_ref ) B ON A.Site_ref = B.Site_ref and A.rent = B.bud-rent
"???" is the column with values to count to get occurences.
_____________
Code for TallyGenerator
May 23, 2006 at 5:45 am
Thank you, I shall look at that right now and then hopefully will not have to bother anyone for a while.
Thanks Again!
May 23, 2006 at 5:55 am
First of all, this is a trivial problem in Crystal Reports, or most any other reporting application. Depending on the method you are using to display this information, you may not want to do it with T-SQL.
That said...
SELECT T.Site_ref, T.Unit_ref, T.finanance,
(CASE WHEN T.Unit_ref = S.min_unit THEN B.bud-rent ELSE 0 END) as Bud-rent,
(CASE WHEN T.Unit_ref = S.min_unit THEN B.bud_occ ELSE 0 END) as Bud-occ
FROM src_terrier T
JOIN src_budget B
ON T.site_ref = B.site_ref
JOIN (SELECT site_ref, MIN(unit_ref) AS min_unit
FROM src_terrier
GROUP BY site_ref) S
ON T.site_ref = S.site_ref
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply