Repost from sqlservers re: summing using a function
- From: P.S.Bell <pat@xxxxxxxxxxxxx>
- Date: Mon, 06 Feb 2006 12:23:45 GMT
Perhaps I posted this in the wrong section
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
Pat Bell
.
- Prev by Date: Re: dbgrid field display format
- Next by Date: SQL Server Application roles
- Previous by thread: dbgrid field display format
- Next by thread: SQL Server Application roles
- Index(es):
Relevant Pages
|
