P.S.Bell
Guest
|
 Posted: Fri Feb 03, 2006 5:08 pm Post subject: sum of a function |
 |
|
I have a function that returns a for each record that I want to
sum.
The following query gives me summed results for each record in
licences held
SELECT LicencesHeld.LicNo, LicenceTypes.LicenceType,
LicencesHeld.LicName, LicencesHeld.AreaCode, Areas.AreaManager,
Areas.AreaName,
SUM(ItemActions.VatTotalSales) AS TotalVAT,
round(SUM(ItemActions.CHNet),2,0) AS TotalNet,
round(sum(itemactions.chnet * jobs.appliedfeerate)/100,2,0)
as Fees,
FROM ItemActions INNER JOIN
Items ON ItemActions.ItemID = Items.ItemID RIGHT OUTER JOIN
LicencesHeld LEFT OUTER JOIN
LicenceTypes ON LicenceTypes.LicenceTypeID
=LicencesHeld.LicTypeID LEFT OUTER JOIN
Jobs ON Jobs.LicID = LicencesHeld.LicID ON Items.JobNo =
Jobs.JobNo Left Join
jobstatus on Jobs.JobStatusID=Jobstatus.JobStatusID LEFT
JOIN
Areas on LicencesHeld.AreaCode=Areas.AreaID
WHERE
(Jobs.FinalReport BETWEEN CONVERT(DATETIME, '01/01/05' ,3)
AND
CONVERT(DATETIME, '01/01/10',3)) AND JobStatus.JobStatus =
'Finalised'
GROUP BY LicencesHeld.LicNo,
LicenceTypes.LicenceType,Areas.AreaManager, Areas.AreaName,
LicencesHeld.LicName, LicencesHeld.AreaCode
but if I add
(select dbo.fn_allocationfee(Jobs.JobNo,Jobs.ReportOnly))
I have to add 'Jobs.JobNo,Jobs.ReportOnly' to group by and I get
a results for each row of Jobs. No surprise I hear, but what I
was actually trying to do was something like
(sum(select dbo.fn_allocationfee(Jobs.JobNo,Jobs.ReportOnly)))
and get the summed function results per row of licences held.
Pat Bell
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
