我有一个关于SQL查询的问题,我想知道从哪里开始.到目前为止的想法包括在记忆中创建一个包含一系列日期的表格,并加入其中以获得特定日期输入的小时数.
只是想知道这个问题的背景,这里有一些信息.
数据库的结构如下：
我尝试使用当前正在编写的查询实现的结果需要如下所示：
我到目前为止所写的查询：
@H_403_10@DECLARE @Deleted AS BIT DECLARE @WeekStartDate AS DATETIME DECLARE @WeekEndDate AS DATETIME SET @Deleted = 0 SET @WeekStartDate = '2011/05/01 00:00' SET @WeekEndDate = '2011/05/07 00:00' SELECT [JobSheet].[JobSheetDate],[JobSheet].[ContractID],[JobSheet].[ContractCode],[JobSheet].[ContractTitle],[JobSheet].[ProjectID],[JobSheet].[ProjectCode],[JobSheet].[ProjectTitle],[JobSheet].[JobID],[JobSheet].[JobCode],[JobSheet].[JobTitle],[JobSheet].[SageDatabaseID],[JobSheetLineHours].[CostRateCode],SUM([JobSheetLineHours].[Hours]) AS TotalCostRateHours,'???' AS 'Mon','???' AS 'Tue','???' AS 'Wed','???' AS 'Thu','???' AS 'Fri','???' AS 'Sat','???' AS 'Sun' FROM [JobSheet] INNER JOIN [JobSheetLine] ON [JobSheetLine].[JobSheetID] = [JobSheet].[JobSheetID] INNER JOIN [JobSheetLineHours] ON [JobSheetLineHours].[JobSheetLineID] = [JobSheetLine].[JobSheetLineID] WHERE [JobSheet].[Deleted]=@Deleted AND [JobSheet].[JobSheetDate] >= @WeekStartDate AND [JobSheet].[JobSheetDate] <= @WeekEndDate AND [JobSheetLine].[Deleted]=@Deleted AND [JobSheetLineHours].[Deleted]=@Deleted GROUP BY [JobSheet].[JobSheetDate],[JobSheetLineHours].[CostRateCode]并输出以下结果集：
正如您所看到的那样,结果集几乎就在那里,我只需要计算该特定日期的工作小时数,对ContractID,JobID,ProjectID,CostRateCode进行分组,这样我就可以得到每个唯一成本率的小时数.项目.
这可能与当前的表设计有关吗？或者通过使用临时日历表来传递给查询的两个日期之间的日期？
编辑：
更新的查询：
更新的结果集：
我现在要做的是从结果集中删除JobSheetDate,以便每天的总小时数不是全部在单独的行上,例如.同一合同,项目等的值放在同一行,但是在Mon,Tues,Wed等列中,而不是在多行中填充一天的小时数,其余时间为null(参见屏幕截图) )
编辑2：
更新的查询：
结果：
完成查询的最终更新：
@H_403_10@SET DATEFIRST 1 -- Set the first day of week to monday GO DECLARE @Deleted AS BIT DECLARE @WeekStartDate AS DATETIME DECLARE @WeekEndDate AS DATETIME SET @Deleted = 0 SET @WeekStartDate = '2009/05/01 00:00' SET @WeekEndDate = '2012/05/07 00:00' SELECT [JobSheet].[ContractID],[JobSheet].[JobSheetDate]) -- Get Total Value for Monday WHEN 1 THEN [JobSheetLineHours].[Hours] END ) AS MON,[JobSheet].[JobSheetDate]) -- Get Total Value for Tuesday WHEN 2 THEN [JobSheetLineHours].[Hours] END ) AS TUE,[JobSheet].[JobSheetDate]) -- Get Total Value for Wednesday WHEN 3 THEN [JobSheetLineHours].[Hours] END ) AS WED,[JobSheet].[JobSheetDate]) -- Get Total Value for Thursday WHEN 4 THEN [JobSheetLineHours].[Hours] END ) AS THU,[JobSheet].[JobSheetDate]) -- Get Total Value for Friday WHEN 5 THEN [JobSheetLineHours].[Hours] END ) AS FRI,[JobSheet].[JobSheetDate]) -- Get Total Value for Saturday WHEN 6 THEN [JobSheetLineHours].[Hours] END ) AS SAT,[JobSheet].[JobSheetDate]) -- Get Total Value for Sunday WHEN 7 THEN [JobSheetLineHours].[Hours] END ) AS SUN FROM [JobSheet] INNER JOIN [JobSheetLine] ON [JobSheetLine].[JobSheetID] = [JobSheet].[JobSheetID] INNER JOIN [JobSheetLineHours] ON [JobSheetLineHours].[JobSheetLineID] = [JobSheetLine].[JobSheetLineID] WHERE [JobSheet].[Deleted]=@Deleted AND [JobSheet].[JobSheetDate] >= @WeekStartDate AND [JobSheet].[JobSheetDate] <= @WeekEndDate AND [JobSheetLine].[Deleted]=@Deleted AND [JobSheetLineHours].[Deleted]=@Deleted GROUP BY [JobSheet].[ContractID],[JobSheetLineHours].[CostRateCode]解决方法
我使用示例表方案调查它.希望它会对你有所帮助.从未经历过这种情况.很高兴今天学到新东西.感谢Post WraithNath
@H_403_10@create table #temp
(
projectId int,ContractID int,CostRateCode varchar(10),JobSheetDate datetime,Hours Int
)
我插入了如下记录
@H_403_10@Insert into #temp(projectId,ContractID,CostRateCode,JobSheetDate,Hours) Values(1,1,'A','2011-06-02 22:00:35.337',1) Insert into #temp(projectId,'2011-06-01 22:00:35.337',2) Insert into #temp(projectId,'2011-05-31 22:00:35.337',3) Insert into #temp(projectId,'2011-06-03 22:00:35.337',6) Insert into #temp(projectId,'2011-05-30 22:00:35.337',5) Insert into #temp(projectId,2,'B',4) Insert into #temp(projectId,7)以下是插入后的情况
最后是select语句
@H_403_10@SET DATEFIRST 1 Select projectId,Sum(Hours) Total,SUM(Case When DATEPART(WEEKDAY,JobSheetDate) = 1 Then Hours Else 0 End) as 'Mon',JobSheetDate) = 2 Then Hours Else 0 End) as 'Tue',JobSheetDate) = 3 Then Hours Else 0 End) as 'Wed',JobSheetDate) = 4 Then Hours Else 0 End) as 'Thu',JobSheetDate) = 5 Then Hours Else 0 End) as 'Fri',JobSheetDate) = 6 Then Hours Else 0 End) as 'Sat',JobSheetDate) = 7 Then Hours Else 0 End) as 'Sun' From #temp Group By projectId,CostRateCode Drop table #temp结果集
