Question

基本上,我想要做的是,有些用户在日间(月度)内拥有适用服务。

我愿向用户展示服务可用时间(小时),以及他们安排多少小时的表格上载有任命数据。

因此,我希望它基本上照样。

user             service             available         scheduled
-----------------------------------------------------------------
User1            Service1            120               7
User1            Service2            40                0

附表一栏的计算方法是,在我的<代码>指定表内,采用和 End领域。

现在我的问题是,如果没有安排任命,我不敢知道如何继续展示我所希望的信息。

表格之间的关系如下:

1 NOA per User
Multiple Services per NOA
1 表格职类
任用 FormTypeID as a FK
ClientAppointment holds the AppointmentID and UserID (the same user with the assigned NOA)

SELECT

 --NOA
 noa.noaID, noa.userID as  ClientUserID , 

 --Service
 service.BillingCode + service.MOD1 + service.MOD2 + service.MOD3 + service.MOD4 AS     BillingCode, service.FormTypeID, service.StartDate, service.EndDate, service.CurrentUnits, service.Adjustment,

 --Scheduler
 scheduler.* 
 FROM LEL_NOA noa
 LEFT JOIN LEL_Service service ON service.noaID = noa.noaID
 LEFT OUTER JOIN 
 (
 SELECT ca.ClientID, SUM(DATEDIFF(second, a.Start, a.[End]) / 3600) as ScheduledUnits        FROM LEL_Scheduler_Appointment a
 LEFT JOIN LEL_Scheduler_ClientAppointment ca ON a.ApptID = ca.ApptID
 WHERE a.Start BETWEEN  1/1/2012  and  1/31/2012 
   AND a.[End] BETWEEN  1/1/2012  and  1/31/2012 
 GROUP BY ca.ClientID

 ) AS scheduler ON noa.UserID = scheduler.ClientID

WHERE noa.UserID = 4
AND service.StartDate BETWEEN  1/1/2012  and  1/31/2012 
AND service.EndDate BETWEEN  1/1/2012  and  1/31/2012 

ORDER BY  ClientUserID , BillingCode, FormTypeID, StartDate

上述程序将撤回该月21日,预定7月21日,但拥有的人在此期间有另一个服务,我谨以0小时时间在我的表格中作为下一个项目显示这一信息。

(noaID, ClientUserID, BillingCode, FormTypeID, StartDate, EndDate, CurrentUnits,     Adjustment, ScheduledUnits) VALUES
(203, 25,  x888 , 6,  4/16/2012 4:24:19 PM ,  4/16/2012 4:24:19 PM , 0, 0, 5.000000), 
(203, 25,  x999 , 1,  4/1/2012 12:00:00 AM ,  4/30/2012 12:00:00 AM , 10, 0, 5.000000)

问题是,第一个条目没有排定的任用,但是仍然显示5人(第二笔数额正确)。

Answer 1

请重新研究您的询问,以使用一条线子座。业绩可能受到影响,但获得正确询问会比较容易。

SELECT
--NOA
noa.noaID, noa.userID as ClientUserID, 
--Service
service.BillingCode + service.MOD1 + service.MOD2 + service.MOD3 + service.MOD4 AS BillingCode,
service.FormTypeID, service.StartDate, service.EndDate, service.CurrentUnits, service.Adjustment,
--Scheduler
(
    SELECT COALESCE(SUM(DATEDIFF(second, a.Start, a.[End]) / 3600), 0) as ScheduledUnits
    FROM LEL_Scheduler_Appointment a
        INNER JOIN LEL_Scheduler_ClientAppointment ca ON a.ApptID = ca.ApptID
    WHERE a.Start BETWEEN  1/1/2012  and  1/31/2012 
        AND a.[End] BETWEEN  1/1/2012  and  1/31/2012 
        AND ca.ClientID = noa.UserID
        AND a.ServiceID = FormTypeID
) as ScheduledUnits
FROM LEL_NOA noa
    LEFT JOIN LEL_Service service ON service.noaID = noa.noaID
WHERE noa.UserID = 4
    AND service.StartDate BETWEEN  1/1/2012  and  1/31/2012 
    AND service.EndDate BETWEEN  1/1/2012  and  1/31/2012 
ORDER BY ClientUserID, BillingCode, FormTypeID, StartDate

Answer 2

Above the ORDER BY clause, add a UNION ALL line and then a new SELECT query that specifically pulls the info for that same user with no schedule. Just make sure the new query has the same columns as the top query, even if you have to hard-code NULL values. Here s the general syntax:

SELECT [stuff] FROM Table1 WHERE [A]
UNION ALL
SELECT [samestuff] FROM Table1 WHERE [B]
ORDER BY [somestuff]

友情链接