帮助SQL查询

我有一个关于SQL查询的问题,我想知道从哪里开始.到目前为止的想法包括在记忆中创建一个包含一系列日期的表格,并加入其中以获得特定日期输入的小时数.

只是想知道这个问题的背景,这里有一些信息.

数据库的结构如下：

我尝试使用当前正在编写的查询实现的结果需要如下所示：

我到目前为止所写的查询：

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进行分组,这样我就可以得到每个唯一成本率的小时数.项目.

这可能与当前的表设计有关吗？或者通过使用临时日历表来传递给查询的两个日期之间的日期？

编辑：
更新的查询：

SET DATEFIRST 1 -- Set the first day of week to monday
GO

DECLARE @Deleted AS BIT
DECLARE @RequestedByID AS BIGINT
DECLARE @WeekStartDate AS DATETIME
DECLARE @WeekEndDate AS DATETIME
DECLARE @WaitingForUserID AS BIGINT
DECLARE @WaitingForUserTypeID AS BIGINT
DECLARE @WaitingForTypeUser AS VARCHAR(50)
DECLARE @WaitingForTypeUserType AS VARCHAR(50)

SET @Deleted = 0
SET @WeekStartDate = '2009/05/01 00:00'
SET @WeekEndDate = '2012/05/07 00:00'

SELECT 
[JobSheet].[JobSheetDate],CASE DATEPART(WEEKDAY,JobSheet.JobSheetDate) 
WHEN 1 
THEN SUM(JobSheetLineHours.Hours )
END 
AS MON,JobSheet.JobSheetDate) 
WHEN 2 
THEN SUM(JobSheetLineHours.Hours )
END 
AS TUE,JobSheet.JobSheetDate) 
WHEN 3 
THEN SUM(JobSheetLineHours.Hours )
END 
AS WED,JobSheet.JobSheetDate) 
WHEN 4 
THEN SUM(JobSheetLineHours.Hours )
END 
AS THU,JobSheet.JobSheetDate) 
WHEN 5 
THEN SUM(JobSheetLineHours.Hours )
END 
AS FRI,JobSheet.JobSheetDate) 
WHEN 6 
THEN SUM(JobSheetLineHours.Hours )
END 
AS SAT,JobSheet.JobSheetDate) 
WHEN 7 
THEN SUM(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].[JobSheetDate],JobSheet.JobSheetDate) 
    WHEN 1 
    THEN     JobSheetLineHours.Hours 
    END,JobSheet.JobSheetDate) WHEN 2 
    THEN     JobSheetLineHours.Hours 
    END,JobSheet.JobSheetDate) WHEN 3 
    THEN     JobSheetLineHours.Hours 
    END,JobSheet.JobSheetDate) WHEN 4 
    THEN     JobSheetLineHours.Hours 
    END,JobSheet.JobSheetDate) WHEN 5 
    THEN     JobSheetLineHours.Hours 
    END,JobSheet.JobSheetDate) WHEN 6 
    THEN     JobSheetLineHours.Hours 
    END,JobSheet.JobSheetDate) WHEN 7 
    THEN     JobSheetLineHours.Hours 
    END

更新的结果集：

我现在要做的是从结果集中删除JobSheetDate,以便每天的总小时数不是全部在单独的行上,例如.同一合同,项目等的值放在同一行,但是在Mon,Tues,Wed等列中,而不是在多行中填充一天的小时数,其余时间为null(参见屏幕截图) )

编辑2：
更新的查询：

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].[JobSheetDate],SUM( CASE DATEPART(WEEKDAY,JobSheet.JobSheetDate) 
WHEN 1 
THEN JobSheetLineHours.Hours 
END )
AS MON,JobSheet.JobSheetDate) 
WHEN 2 
THEN JobSheetLineHours.Hours 
END )
AS TUE,SUM(CASE DATEPART(WEEKDAY,JobSheet.JobSheetDate) 
WHEN 3 
THEN JobSheetLineHours.Hours 
END)
AS WED,JobSheet.JobSheetDate) 
WHEN 4 
THEN JobSheetLineHours.Hours 
END) 
AS THU,JobSheet.JobSheetDate) 
WHEN 5 
THEN JobSheetLineHours.Hours 
END )
AS FRI,JobSheet.JobSheetDate) 
WHEN 6 
THEN JobSheetLineHours.Hours 
END )
AS SAT,JobSheet.JobSheetDate) 
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].[JobSheetDate],JobSheet.JobSheetDate) 
    WHEN 1 
    THEN     JobSheetLineHours.Hours
    END,JobSheet.JobSheetDate) WHEN 7 
    THEN     JobSheetLineHours.Hours 
    END

结果：

完成查询的最终更新：

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]

解决方法

create table #temp
(
    projectId int,ContractID int,CostRateCode varchar(10),JobSheetDate datetime,Hours Int   
)

我插入了如下记录

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语句

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

结果集

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!