sql server 2012 - Adding relatively complex business logic to a sql query -

i've had superb assistance fellow user on last few days, knowledge of sql way above mine. advice more knowledgeable me once again. question follows on here.

i have adapted original query meet requirements such looks this:

declare @startdate date = '2015-01-05',         @enddate date = '2015-06-05',         @tempdeduction decimal(10, 4) = null    select (select     contactid '@contactid',     vesselowner '@owner',     owed '@owed',     weeklydeductionrate '@weeklydeductionrate',     fromminimumreturn '@fromminimumreturn',     deductionrate '@deductionrate',     totaldeductions '@totaldeductions',     totaltobereturned '@totaltobereturned',     internalcommission '@internalcommissionrate',     internaldeduction '@internaldeductionrate',        (select distinct       ld1.productid '@productid',       format(avg(ld1.unitprice), 'n2') '@cost',       format(sum(ld1.quantity), 'n2') '@quantity'      landingdetails ld1     inner join dbo.landingheaders lh1       on ld1.landingid = lh1.landingid     posted = 0     , lh1.vesselowner = a.vesselowner     group ld1.productid     xml path ('products'), type)    (select     contacts.contactid contactid,     landingheaders.vesselowner vesselowner,     format(sum(landingdetails.quantity * landingdetails.unitprice), 'n2') owed,     societymemberships.weeklydeductionrate weeklydeductionrate,     societymemberships.fromminimumreturn fromminimumreturn,     deductions.deductionrate,     vessels.internalcommission internalcommissionrate,     vessels.internaldeduction internaldeductionrate,     format(case       when sum(landingdetails.quantity * landingdetails.unitprice) - (sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate) > fromminimumreturn sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate       else sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate     end, 'n2') totaldeductions,       --need add logic here presume utiles sql below being used calculate total returned       format(sum(landingdetails.quantity * landingdetails.unitprice) -     (case       when sum(landingdetails.quantity * landingdetails.unitprice) - (sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate) > fromminimumreturn sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate       else sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate     end), 'n2') totaltobereturned     dbo.landingdetails   inner join dbo.landingheaders     on landingdetails.landingid = landingheaders.landingid   inner join dbo.vessels     on landingheaders.vesselid = vessels.vesselid   inner join dbo.contacts     on vessels.ownerid = contacts.contactid   inner join dbo.societymemberships     on contacts.societyid = societymemberships.societyid   inner join dbo.deductions     on vessels.deductionid = deductions.deductionid   landingheaders.posted = 0   , landingdate1 between @startdate , @enddate   group contactid,            landingheaders.vesselowner,            societymemberships.weeklydeductionrate,            societymemberships.fromminimumreturn,            deductions.deductionrate)   order contactid    xml path ('owner'), type)  xml path ('purchaseorders'), type 

i had added logic xml show final amount ((once deductions had been removed amount owed) returned , (other occasional 1 penny discrepancy odd record caused rounding errors on monetary datatypes) works like.

i'm trying establish best way implement last little piece of logic. there 2 new columns in first select statement, internalcommission , internaldeduction. these mutually exclusive (in other words if 1 contains value greater 0 other 0) , used calculate amount (a percentage of totaltobereturned) either added or deducted. goal end xmlattributes show amount paid or deducted in commsion or deduction , totaltobereturned adjusted reflect these new requirements.

my feeling need assign section creates totaltobereturned temporary value (added top of sql @tempdeduction , use perform necessary logic. if try set variable below have added comment in query sql compiler doesn't it.

so reasoning correct, , if how ought setting variable , using apply business logic?

thanks

just in case should stumble across , want see resolution. bottom line absolutely necessary refer actual underlying table names rather aliases when execution logic. in end following worked.

the language has been deliberately set british english want £ symbol in resultant xml. sqlserver ends english default.

set language 'british english' declare @startdate date ,         @enddate date      select (select     contactid '@contactid',     vesselowner '@owner',     format(owed, 'n2') '@owed',     format(weeklydeductionrate, 'c') '@weeklydeductionrate',     format(fromminimumreturn, 'c') '@fromminimumreturn',     format(deductionrate, 'p') '@deductionrate',     format(totaldeductions, 'n2') '@totaldeductions',     format(tempreturn, 'n2') '@tempreturn',     format(internalcommission, 'p') '@internalcommissionrate',     format(internaldeduction, 'p') '@internaldeductionrate',     format(internalcommissionamount, 'n2') '@internalcommissionamount',     format(internaldeductionamount, 'n2') '@internaldeductionamount',     format(totaltobereturned, 'n2') '@totaltobereturned',      (select distinct       ld1.productid '@productid',       format(avg(ld1.unitprice), 'n2') '@cost',       format(sum(ld1.quantity), 'n2') '@quantity'      landingdetails ld1     inner join dbo.landingheaders lh1       on ld1.landingid = lh1.landingid     posted = 0     , lh1.vesselowner = a.vesselowner     group ld1.productid     xml path ('products'), type)    (select     contacts.contactid contactid,     landingheaders.vesselowner vesselowner,     sum(landingdetails.quantity * landingdetails.unitprice) owed,     societymemberships.weeklydeductionrate weeklydeductionrate,     societymemberships.fromminimumreturn fromminimumreturn,     deductions.deductionrate,     vessels.internalcommission,     vessels.internaldeduction,     case       when sum(landingdetails.quantity * landingdetails.unitprice) - (sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate) > fromminimumreturn sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate       else sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate     end totaldeductions,       sum(landingdetails.quantity * landingdetails.unitprice) -     (case       when sum(landingdetails.quantity * landingdetails.unitprice) - (sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate) > fromminimumreturn sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate       else sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate     end) tempreturn,        (sum(landingdetails.quantity * landingdetails.unitprice) -     (case       when sum(landingdetails.quantity * landingdetails.unitprice) - (sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate) > fromminimumreturn sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate       else sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate     end)) * internalcommission internalcommissionamount,      (sum(landingdetails.quantity * landingdetails.unitprice) -     (case       when sum(landingdetails.quantity * landingdetails.unitprice) - (sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate) > fromminimumreturn sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate       else sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate     end)) * internaldeduction internaldeductionamount,       case       when (sum(landingdetails.quantity * landingdetails.unitprice) -         (case           when sum(landingdetails.quantity * landingdetails.unitprice) - (sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate) > fromminimumreturn sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate           else sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate         end)) * internalcommission > 0 (sum(landingdetails.quantity * landingdetails.unitprice) -         (case           when sum(landingdetails.quantity * landingdetails.unitprice) - (sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate) > fromminimumreturn sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate           else sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate         end)) + ((sum(landingdetails.quantity * landingdetails.unitprice) -         (case           when sum(landingdetails.quantity * landingdetails.unitprice) - (sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate) > fromminimumreturn sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate           else sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate         end)) * internalcommission)        else (sum(landingdetails.quantity * landingdetails.unitprice) -         (case           when sum(landingdetails.quantity * landingdetails.unitprice) - (sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate) > fromminimumreturn sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate           else sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate         end)) + ((sum(landingdetails.quantity * landingdetails.unitprice) -         (case           when sum(landingdetails.quantity * landingdetails.unitprice) - (sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate) > fromminimumreturn sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate + weeklydeductionrate           else sum(landingdetails.quantity * landingdetails.unitprice) * deductionrate         end)) * internaldeduction)     end totaltobereturned        dbo.landingdetails   inner join dbo.landingheaders     on landingdetails.landingid = landingheaders.landingid   inner join dbo.vessels     on landingheaders.vesselid = vessels.vesselid   inner join dbo.contacts     on vessels.ownerid = contacts.contactid   inner join dbo.societymemberships     on contacts.societyid = societymemberships.societyid   inner join dbo.deductions     on vessels.deductionid = deductions.deductionid   landingheaders.posted = 0   , landingdate1 between @startdate , @enddate   group contactid,            landingheaders.vesselowner,            societymemberships.weeklydeductionrate,            societymemberships.fromminimumreturn,            deductions.deductionrate,            vessels.internalcommission,            vessels.internaldeduction)   order contactid    xml path ('owner'), type)  xml path ('purchaseorders'), type