使用GridViews和Groupby管理数据

| 我正在使用带有SQL Server后端的ASP.NET（C＃）GridView和模板字段开发时间表报告应用程序。我基本上是在抓取员工本周的任务清单，使用RowDataBound在GridView中循环，并检查EndTime是否落在Sun-Sat上，然后将那些Hours / Minutes放置在该列标签中。您可以在下面看到我的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
    
2020-01-26 3 条评论
分享

没有找到相关结果

    已邀请:

    哭木算

    我不是100％知道您的要求是什么，但这几乎就像您想要组合数据透视表一样。 我很快就做了，所以它可能并不完全适合您的需求，但是无论如何... 我创建了一个名为#timesheet的临时表，并添加了一些值： 
    CREATE TABLE #timesheet
(
  Task VARCHAR(10),
  employee INT,
  StartDate DATETIME,
  endDate DATETIME
)

 INSERT  INTO #timesheet (Task, employee, StartDate, endDate) VALUES (\'Task1\',10,\'2011-5-10 17:00:00\',\'2011-5-10 20:00:00\' ) 
 INSERT  INTO #timesheet (Task, employee, StartDate, endDate) VALUES (\'Task1\', 10, \'2011-5-12 17:00:00\', \'2011-5-12 20:00:00\' ) 
 INSERT  INTO #timesheet (Task, employee, StartDate, endDate) VALUES  (\'Task2\',10,\'2011-5-12 17:00:00\',\'2011-5-12 20:00:00\' )         
 INSERT  INTO #timesheet (Task, employee, StartDate, endDate) VALUES  (\'Task1\', 12, \'2011-5-08 17:00:00\', \'2011-5-08 20:00:00\'  )
    我从Kodyaz Development Services创建了一个GetWeekDayNameOfDate函数，以获取星期几。 我首先创建了一个摘要查询，如下所示： 
    SELECT  #timesheet.Task,
    #timesheet.employee,
    MIN(#timesheet.StartDate) AS [Start Date],
    MAX(#timesheet.endDate) AS [End Date],
    SUM(DATEDIFF(n, #timesheet.StartDate, #timesheet.endDate)) / 60 AS [Hours],
    SUM(DATEDIFF(n, #timesheet.StartDate, #timesheet.endDate)) % 60 AS [Minutes]
FROM    #timesheet
GROUP BY #timesheet.Task,
        #timesheet.employee
    这将为我提供有关任务和员工的摘要。它将显示整个持续时间的开始日期和结束日期以及多少小时和分钟。 我的结果将是： 
    Task       employee    Start Date              End Date                Hours       Minutes
---------- ----------- ----------------------- ----------------------- ----------- -----------
Task1      10          2011-05-10 17:00:00.000 2011-05-12 20:00:00.000 6           0
Task2      10          2011-05-12 17:00:00.000 2011-05-12 20:00:00.000 3           0
Task1      12          2011-05-08 17:00:00.000 2011-05-08 20:00:00.000 3           0
    接下来，我创建了一个详细数据透视查询，以汇总一周中每一天所花费的小时数。 
     SELECT Task,
    Employee,
    ISNULL([Sunday], 0) AS Sunday,
    ISNULL([Monday], 0) AS Monday,
    ISNULL([Tuesday], 0) AS Tuesday,
    ISNULL([Wednesday], 0) AS Wendesday,
    ISNULL([Thursday], 0) AS Thursday,
    ISNULL([Friday], 0) AS Friday,
    ISNULL([Saturday], 0) AS Saturday
 FROM   ( SELECT    #timesheet.Task,
                #timesheet.employee,
                dbo.GetWeekDayNameOfDate(#timesheet.StartDate) AS [Day],
                SUM(DATEDIFF(n, #timesheet.StartDate, #timesheet.endDate))
                / 60 AS [Hours],
                SUM(DATEDIFF(n, #timesheet.StartDate, #timesheet.endDate))
                % 60 AS [Minutes]
      FROM      #timesheet
      GROUP BY  #timesheet.Task,
                #timesheet.employee,
                dbo.GetWeekDayNameOfDate(#timesheet.StartDate)
    ) p PIVOT ( SUM(Hours) FOR [DAY] IN ( [Sunday], [Monday], [Tuesday],
                                          [Wednesday], [Thursday],
                                          [Friday], [Saturday] ) ) AS PivotTable
    我的结果将是： 
    Task       Employee    Sunday      Monday      Tuesday     Wendesday   Thursday    Friday      Saturday
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Task1      10          0           0           3           0           3           0           0
Task1      12          3           0           0           0           0           0           0
Task2      10          0           0           0           0           3           0           0
    在船尾我将我的两个查询合并在一起以获取整个图片 
    SELECT  summary.Task,
    summary.employee,
    summary.[Start Date],
    summary.[End Date],
    details.Sunday,
    details.Monday,
    details.Tuesday,
    details.Wendesday,
    details.Thursday,
    details.Friday,
    details.Saturday,
    summary.Hours,
    summary.Minutes
 FROM    ( SELECT    #timesheet.Task,
                #timesheet.employee,
                MIN(#timesheet.StartDate) AS [Start Date],
                MAX(#timesheet.endDate) AS [End Date],
                SUM(DATEDIFF(n, #timesheet.StartDate, #timesheet.endDate))
                / 60 AS [Hours],
                SUM(DATEDIFF(n, #timesheet.StartDate, #timesheet.endDate))
                % 60 AS [Minutes]
      FROM      #timesheet
      GROUP BY  #timesheet.Task,
                #timesheet.employee
    ) summary
    INNER JOIN ( SELECT Task,
                        Employee,
                        ISNULL([Sunday], 0) AS Sunday,
                        ISNULL([Monday], 0) AS Monday,
                        ISNULL([Tuesday], 0) AS Tuesday,
                        ISNULL([Wednesday], 0) AS Wendesday,
                        ISNULL([Thursday], 0) AS Thursday,
                        ISNULL([Friday], 0) AS Friday,
                        ISNULL([Saturday], 0) AS Saturday
                 FROM   ( SELECT    #timesheet.Task,
                                    #timesheet.employee,
                                    dbo.GetWeekDayNameOfDate(#timesheet.StartDate) AS [Day],
                                    SUM(DATEDIFF(n, #timesheet.StartDate,
                                                 #timesheet.endDate)) / 60 AS [Hours],
                                    SUM(DATEDIFF(n, #timesheet.StartDate,
                                                 #timesheet.endDate)) % 60 AS [Minutes]
                          FROM      #timesheet
                          GROUP BY  #timesheet.Task,
                                    #timesheet.employee,
                                    dbo.GetWeekDayNameOfDate(#timesheet.StartDate)
                        ) p PIVOT ( SUM(Hours) FOR [DAY] IN ( [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday] ) ) AS PivotTable
               ) details ON summary.Task = details.Task
                            AND summary.Employee = details.Employee
    这样，我将得到以下结果： 
    Task       employee    Start Date              End Date                Sunday      Monday      Tuesday     Wendesday   Thursday    Friday      Saturday    Hours       Minutes
---------- ----------- ----------------------- ----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Task1      10          2011-05-10 17:00:00.000 2011-05-12 20:00:00.000 0           0           3           0           3           0           0           6           0
Task1      12          2011-05-08 17:00:00.000 2011-05-08 20:00:00.000 3           0           0           0           0           0           0           3           0
Task2      10          2011-05-12 17:00:00.000 2011-05-12 20:00:00.000 0           0           0           0           3           0           0           3           0
    提醒您，我几乎忽略了会议记录部分，但我希望至少这对您有帮助。     
    2020-01-26 0 0
    分享

    拟僚疽刊剔

    您需要按周分组。在这里寻找一个开始，并让我们知道您是否需要完整解决方案的帮助。     
    2020-01-26 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.052618980407715