我正在开发一个时间表报告应用程序，使用 ASP.NET (C#) GridView 和模板字段以及 Sql Server 后端。我基本上是获取员工本周的任务列表，使用 RowDataBound 循环浏览 GridView 并检查 EndTime 是否落在周日 - 周六，然后将这些小时/分钟放在该列标签中。您可以在下面看到我的 GridView 标题的示例（抱歉格式问题）。
Type|Hours|Minutes|StartTime|EndTime|Sun|Mon|Tue|Wed|Thu|Fri|Sat|Total
问题是我的 groupby 没有将我的任务分组在一起，因为我的 StartTime/EndTimes 将是唯一的，但我需要将它们拉回来，以便我可以检查它属于哪一天，然后填充该列（正如您可以看到的两行它在下面返回）。我认为可能有一种方法可以创建第二个 GridView 并根据第一个 Gridview 中的某些值填充它。还有其他人偶然发现过这样的问题吗？
SELECT
C.Task,
CONVERT(VARCHAR(10), A.StartTime, 101) As StartTime,
CONVERT(VARCHAR(10), A.EndTime, 101) As EndTime,
SUM(DATEDIFF(n, A.StartTime, A.EndTime)) / 60 AS Hours,
SUM(DATEDIFF(n, A.StartTime, A.EndTime)) % 60 AS Minutes
FROM dbo.Timesheet A, dbo.Employees B,
dbo.TimeSheet_Master C
WHERE A.CreatedBy = B.ContactId
AND A.Task = C.Task
AND (B.ContactId =@ContactId)
AND (A.StartTime >= @StartTime)
AND (A.EndTime <= @EndTime)
GROUP BY CONVERT(VARCHAR(10), A.StartTime, 101), CONVERT(VARCHAR(10), A.EndTime, 101), C.Task;
Task StartTime EndTime Hours Minutes
-------------------------------------------------------
Install 05/17/2011 05/17/2011 1 0
Install 05/18/2011 05/18/2011 1 30
I'm working on a timesheet reporting application using ASP.NET (C#) GridView and Template Fields with a Sql Server backend. I'm basically grabbing an employee's list of tasks for the week, cycling through GridView using RowDataBound and checking if EndTime falls on Sun - Sat then placing those Hours/Minutes in that column label. You can see an example of my GridView header below (sorry about the formatting).
Type|Hours|Minutes|StartTime|EndTime|Sun|Mon|Tue|Wed|Thu|Fri|Sat|Total
The problem is my groupby isn't grouping my tasks together because my StartTime/EndTimes will be unique, but I need to pull those back so I can check which day it falls under, then populate that column (as you can see the two rows it returns below). I thought there may be a way to create a 2nd GridView and populate it based on certain values in the 1st Gridview. Has anyone else stumbled across a problem like this?
SELECT
C.Task,
CONVERT(VARCHAR(10), A.StartTime, 101) As StartTime,
CONVERT(VARCHAR(10), A.EndTime, 101) As EndTime,
SUM(DATEDIFF(n, A.StartTime, A.EndTime)) / 60 AS Hours,
SUM(DATEDIFF(n, A.StartTime, A.EndTime)) % 60 AS Minutes
FROM dbo.Timesheet A, dbo.Employees B,
dbo.TimeSheet_Master C
WHERE A.CreatedBy = B.ContactId
AND A.Task = C.Task
AND (B.ContactId =@ContactId)
AND (A.StartTime >= @StartTime)
AND (A.EndTime <= @EndTime)
GROUP BY CONVERT(VARCHAR(10), A.StartTime, 101), CONVERT(VARCHAR(10), A.EndTime, 101), C.Task;
Task StartTime EndTime Hours Minutes
-------------------------------------------------------
Install 05/17/2011 05/17/2011 1 0
Install 05/18/2011 05/18/2011 1 30
发布评论
评论（2）
我不能 100% 确定您的要求是什么，但这几乎就像您想要组合一个数据透视表。
我做得很快，所以它可能不完全适合您的需求，但无论如何...
我创建了一个名为 #timesheet 的临时表并添加了一些值：
我创建了一个取自 Kodyaz 开发服务 获取星期几。
我首先创建了一个摘要查询，如下所示：
这将为我提供任务和员工的摘要。它将显示整个持续时间的开始日期和结束日期以及多少小时和分钟。
我的结果是：
接下来我创建了一个详细信息枢轴查询来汇总一周中每一天所花费的小时数。
我的结果将是：
之后，我将两个查询连接在一起以获得整个图片
通过这样做，我将得到以下结果：
请注意，我几乎忽略了分钟部分，但我希望至少这对您有帮助。
I'm not 100% sure what your requirements are, but it's almost like you want to combine a pivot table.
I did this quickly so it may not exactly suit your needs but anyways...
I created a temporary table called #timesheet and added some values:
I created a GetWeekDayNameOfDate function taken from Kodyaz Development Services to get the day of the week.
I first created a summary query as shown below:
That would give me my summary for the task and employee. It will show the start date and the end date of the entire duration and how many hours and minutes.
My results would be:
I next created a details pivot query to sum the number of hours that were spent on each day of the week.
My results would be:
Aftwards I joined my two queries together to get the entire picture
By doing so I would get the following result:
Mind you, I pretty much ignored the minutes portion but I would hope that at least this could be helpful for you.
您需要的是按周分组。请查看此处以获取先机，如果您需要完整的帮助，请告诉我们解决方案。
What you need is to group by week. Look here for a headstart, and let us know if you need help with the full solution.