Use partition-by- clause instead of function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Use partition-by- clause instead of function

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2013-08-04 : 18:53:07

In looking at samples it seems this could be done without the user defined function in sql 2005 query. I find it really difficult to figure out partition by clauses despite looking at samples and reading on it. (actually any books on this, anyone could suggest, would be helpful since they may go into more detail)

Anyway, here is the query. It is for a report and I have taken out unnecessary fields for simplicity.

SELECT tblSiteContractHeader.SiteID,
tblSiteContractDetail.ActivityID,
tblSiteContractDetail.StartDate,
tblSiteContractDetail.EndDate,

dbo.udf_GetTotalHoursScheduled(tblSiteContractHeader.SiteID, tblSiteContractDetail.ActivityID, @sdate) as schedhrs

FROM tblSiteContractHeader LEFT OUTER JOIN
tblSiteContractDetail ON tblSiteContractHeader.SiteContractID = tblSiteContractDetail.SiteContractID
WHERE ( @sdate BETWEEN tblSiteContractDetail.StartDate AND tblSiteContractDetail.EndDate)

and here is the function (it could also probably be made simpler but it works)

ALTER FUNCTION [dbo].[udf_GetTotalHoursScheduled]
(
@SiteID int,
@actID int,
@sdate smalldatetime
)

RETURNS decimal(4,2)
AS
BEGIN
DECLARE @tothrs decimal(4,2)

select @tothrs = sum(isnull(hours,0)) from tblPersonWeeklyscheduledetail d
inner join (select PersonWeeklyScheduleID, startdate, enddate
from tblPersonWeeklyScheduleHeader
group by PersonWeeklyScheduleID, startdate, enddate ) h on d.PersonWeeklyScheduleID = h.PersonWeeklyScheduleID
and @sdate between h.startdate and h.enddate and d.ActivityID = @actID and d.siteID = @siteID

return isnull(@tothrs,0)
END

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 02:20:38

[code]
SELECT tblSiteContractHeader.SiteID,
tblSiteContractDetail.ActivityID,
tblSiteContractDetail.StartDate,
tblSiteContractDetail.EndDate,
m.schedhrs
FROM tblSiteContractHeader LEFT OUTER JOIN
tblSiteContractDetail ON tblSiteContractHeader.SiteContractID = tblSiteContractDetail.SiteContractID
OUTER APPLY(select sum(isnull(hours,0)) AS schedhrs
from tblPersonWeeklyscheduledetail d
inner join (select PersonWeeklyScheduleID, startdate, enddate
from tblPersonWeeklyScheduleHeader
group by PersonWeeklyScheduleID, startdate, enddate ) h
on d.PersonWeeklyScheduleID = h.PersonWeeklyScheduleID
and @sdate between h.startdate and h.enddate
and d.ActivityID = tblSiteContractDetail.ActivityID
and d.siteID = tblSiteContractHeader.SiteID
)m
WHERE ( @sdate BETWEEN tblSiteContractDetail.StartDate AND tblSiteContractDetail.EndDate)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

smh
Yak Posting Veteran

94 Posts

Posted - 2013-08-05 : 17:48:51

Thanks so much. I see this was totally different from what I was thinking. Is there any difference in performance using outer apply instead of using the function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-06 : 00:33:25

The scalar udf will have much more overhead in performance compared to correlated subquery approach using APPLY for large datasets

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources