从RosterResource获取用户ID表

Question

我有5个表：位置，收费，资源，名册和SpecialRoster。

CREATE TABLE Location(
LocationID int identity, 
StartDate datetime, 
DaysInRoster int) 

CREATE TABLE Charge(
ChargeID int identity, 
TotalAmount money, 
ChargeDate datetime, 
UserID int) 

CREATE TABLE [Resource](
ResourceID int identity, 
ResourceName varchar(100)) 

CREATE TABLE Roster(
RosterDay int, 
ResourceID int, 
StartDate datetime, 
EndDate datetime, 
UserID int) 

CREATE TABLE SpecialRoster(
RosterDate datetime, 
ResourceID int, 
UserID int) 

我需要构建一个报告，显示在不同的日子Charge.TotalAmount的总和，通过资源ID。

费只有一个用户名，但是规则很简单：如果日期和资源ID在SpecialRoster时，用户名是从SpecialRoster 采取如果不是，对于资源和RosterDay的日期的用户名取 从名单当前在当时

基本上，我需要映射ResourceID，通过用户名从名册或SpecialRoster，充电。

的RosterDay是名册的当前日期，并且可以从位置，来计算其中RosterDay = DateDiff(day, Location.RosterStartDate, GetDate()) % Location.DaysInRoster

即，在该位置的整个名册的开始之间的差的模量，用数字阵容期间的天数。

我写一个函数来做到这一点：

CREATE FUNCTION dbo.GetRosteredUser 
(
@ResourceID int, 
@Date datetime, 
@LocationID int 
) 
RETURNS int 
AS 
BEGIN 

DECLARE @UserID int 
DECLARE @RosterOffset int 

Select @UserID = UserID from SpecialRoster Where ResourceID = @ResourceID and RosterDate = @Date 

If @UserID is NOT NULL 
    return @UserID 
else 
    Select @RosterOffset = DATEDIFF(day, StartDate, @Date) % DaysInRoster 
    from Location Where LocationID = @LocationID 

    Select @UserID = UserID from Roster Where ResourceID = @ResourceID and RosterDay = @RosterOffset 
    and (@Date between StartDate and EndDate or @Date > StartDate and EndDate is NULL) 
    return @UserID 
END 
GO 

但因为它是缓慢的，并且只允许该报告在一次单日运行的功能也不是很大：

Select a.ResourceID, Name, a.UserID, Sum(c.TotalAmount) as AmountCharged 
from Charge c 
left outer join (
Select ResourceID, Name, 
dbo.GetUserByResourceDate(ResourceID, '1/FEB/2013', 1) as UserID 
from [Resource]) a 
on c.UserID = a.UserID 
Where ChargeDate between '1/FEB/2013' and '2/FEB/2013' 
group by a.ResourceID, Name, a.UserID 

有没有更好的方法来做到这一点？用户将希望运行此操作来比较资源在一段时间内的生产力。例如：

Date   ResourceID Name UserID TotalAmount 
01/FEB/2013 1   Sales1 22  $1024 
02/FEB/2013 1   Sales1 11  $1454 
03/FEB/2013 1   Sales1 14  $1900 
04/FEB/2013 1   Sales1 23  $3045 

Answer 1

也许，这样的事情可能会有所帮助：

SELECT 
    re.ResourceID, 
    re.ResourceName, 
    ch.UserID, 
    AmountCharged = SUM(ch.TotalAmount) 
FROM 
    Location lo 
    CROSS JOIN Charge ch 
    CROSS APPLY (
    SELECT DATEDIFF(DAY, lo.StartDate, ch.ChargeDate) % lo.DaysInRoster 
) x (RosterDay) 
    INNER JOIN 
    [Resource] re 
    LEFT JOIN Roster ro 
    ON 
     ro.RosterDay = x.RosterDay 
     AND ch.ChargeDate BETWEEN ro.StartDate 
     AND ro.RecourceID = re.ResourceID 
    LEFT JOIN SpecialRoster sr 
    ON 
     sr.RosterDate = ch.ChargeDate 
     AND sr.RecourceID = re.ResourceID 
    ON 
    ch.UserID = ISNULL(sr.UserID, ro.UserID) 
GROUP BY 
    re.ResourceID, 
    re.ResourceName, 
    ch.UserID 
WHERE 
    lo.LocationID = @LocationID 
    AND ch.ChargeDate BETWEEN ... 
; 

在上面的脚本中，假定所有的日期时间值实际上只有日期。

此外，我有点惊讶没有找到Location和指定的任何其他表之间的联系。如果有一个，你只是忘了提及它，请让我知道，如果你需要任何帮助将必要的条件（S）纳入脚本。