每日，每周，每月一次任务报告中的个人技术时间

Question

我正在尝试创建每日、每月和每周的SQL查询报告，以了解我们的服务时间--我们花费的int任务和总计费时间--只是想看看我是否在正确的轨道上

GO
--Daily     

SELECT     SUM(billingsTimes.actualTotalTime) AS TotalTime, Tasks.taskName, billableType.billableTypeName
FROM         billingsTimes INNER JOIN
                      aspnet_Users ON billingsTimes.userID = aspnet_Users.UserId INNER JOIN
                      billableType ON billingsTimes.billableTypeID = billableType.rank INNER JOIN
                      Tasks ON billingsTimes.taskID = Tasks.taskID
WHERE     (billingsTimes.taskID = 10045) AND (aspnet_Users.UserId = '178db2a8-be1d-48e0-9ebc-f64f7b1ff63e') AND (CONVERT(DATE, billingsTimes.dateOfService) 
                      = '01/29/2013')
GROUP BY billingsTimes.billableTypeID, Tasks.taskName, billableType.billableTypeName,DATEPART(D, dateOfService)    


GO
--Weekly 

SELECT     SUM(billingsTimes.actualTotalTime) AS TotalTime, Tasks.taskName, billableType.billableTypeName,DATEPART(WEEK, dateOfService)AS weekNumber      
FROM         billingsTimes INNER JOIN
                      aspnet_Users ON billingsTimes.userID = aspnet_Users.UserId INNER JOIN
                      billableType ON billingsTimes.billableTypeID = billableType.rank INNER JOIN
                      Tasks ON billingsTimes.taskID = Tasks.taskID
WHERE     (billingsTimes.taskID = 10045) AND (aspnet_Users.UserId = '178db2a8-be1d-48e0-9ebc-f64f7b1ff63e') 
GROUP BY billingsTimes.billableTypeID, Tasks.taskName, billableType.billableTypeName,DATEPART(WEEK, dateOfService), DATEPART(WEEK, dateOfService)

GO
--Monthly

SELECT     SUM(billingsTimes.actualTotalTime) AS TotalTime, Tasks.taskName, billableType.billableTypeName,DATEPART(MONTH, dateOfService)AS monthNumber      
FROM         billingsTimes INNER JOIN
                      aspnet_Users ON billingsTimes.userID = aspnet_Users.UserId INNER JOIN
                      billableType ON billingsTimes.billableTypeID = billableType.rank INNER JOIN
                      Tasks ON billingsTimes.taskID = Tasks.taskID
WHERE     (billingsTimes.taskID = 10045) AND (aspnet_Users.UserId = '178db2a8-be1d-48e0-9ebc-f64f7b1ff63e') 
GROUP BY billingsTimes.billableTypeID, Tasks.taskName, billableType.billableTypeName,DATEPART(MONTH, dateOfService)
GO            

Answer 1

在不了解模式的情况下，判断起来有些困难，但以下是一些观察结果：

1. billableType表上的联接条件是有问题的:为什么将ID与INNER JOIN billableType ON billingsTimes.billableTypeID = billableType.rank中的级别进行比较？
2. 尝试将GROUP BY限制为将成为结果集的某种“逻辑键”的列。例如，似乎该查询一次仅限于一个任务(billingTimes.taskID = ###)。在选择列表中使用max(taskName) as taskName，并从group子句中排除taskName。
3. 没有必要在datepart(D, dateOfService)中使用GROUP BY：查询已经被限制在一天之内。