Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Help with a SQL Query

Tags:

sql

sql-server

I have a question on a SQL query and im wondering where to start. Thoughts so far include creating a table in memory with a range of dates, and joining on to it to get the sum of hours entered for a particular day.

Just to give an idea of the background of this question here is a little information.

The database is structured like so: enter image description here

The result im trying to achieve with the query im currently writing needs to look like this: enter image description here

The Query I have written so far:

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],
    [JobSheet].[ContractID],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode]

And this outputs the result set below: enter image description here

As you can see the result set is almost there, I just need to total the number of hours worked on that particular day, grouping on ContractID, JobID, ProjectID, CostRateCode so I can get the number of hours for each cost rate on each unique project.

Is this possible with the current table design? or by using a temporary calendar table for the dates between the two passed in to the query?

EDIT: Updated Query:

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],
[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,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 1 
THEN SUM(JobSheetLineHours.Hours )
END 
AS MON,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 2 
THEN SUM(JobSheetLineHours.Hours )
END 
AS TUE, 
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 3 
THEN SUM(JobSheetLineHours.Hours )
END 
AS WED,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 4 
THEN SUM(JobSheetLineHours.Hours )
END 
AS THU,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 5 
THEN SUM(JobSheetLineHours.Hours )
END 
AS FRI,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 6 
THEN SUM(JobSheetLineHours.Hours )
END 
AS SAT,
CASE DATEPART(WEEKDAY, 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].[ContractID],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode],
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
    WHEN 1 
    THEN     JobSheetLineHours.Hours 
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 2 
    THEN     JobSheetLineHours.Hours 
    END, 
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 3 
    THEN     JobSheetLineHours.Hours 
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 4 
    THEN     JobSheetLineHours.Hours 
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 5 
    THEN     JobSheetLineHours.Hours 
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 6 
    THEN     JobSheetLineHours.Hours 
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 7 
    THEN     JobSheetLineHours.Hours 
    END

Updated Result Set: enter image description here

What I'm trying to do now is remove the JobSheetDate from the result set so the Total hours for each day are not all on seperate rows eg. The values for the same contract, project etc are put in the same row, but in the Mon, Tues, Wed etc column, rather than on multiple rows with the hours for one day populated, and the rest of the days null (see screenshot)

EDIT 2: Updated Query:

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],
[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,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 1 
THEN JobSheetLineHours.Hours 
END )
AS MON,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 2 
THEN JobSheetLineHours.Hours 
END )
AS TUE, 
SUM(CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 3 
THEN JobSheetLineHours.Hours 
END)
AS WED,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 4 
THEN JobSheetLineHours.Hours 
END) 
AS THU,
SUM(CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 5 
THEN JobSheetLineHours.Hours 
END )
AS FRI,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
WHEN 6 
THEN JobSheetLineHours.Hours 
END )
AS SAT,
SUM( CASE DATEPART(WEEKDAY, 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].[ContractID],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode],
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) 
    WHEN 1 
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 2 
    THEN     JobSheetLineHours.Hours 
    END, 
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 3 
    THEN     JobSheetLineHours.Hours 
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 4 
    THEN     JobSheetLineHours.Hours 
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 5 
    THEN     JobSheetLineHours.Hours 
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 6 
    THEN     JobSheetLineHours.Hours 
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 7 
    THEN     JobSheetLineHours.Hours 
    END

Results: enter image description here

FINAL Update with finished Query:

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].[ContractCode],
[JobSheet].[ContractTitle],
[JobSheet].[ProjectID],
[JobSheet].[ProjectCode],
[JobSheet].[ProjectTitle],
[JobSheet].[JobID],
[JobSheet].[JobCode],
[JobSheet].[JobTitle],
[JobSheet].[SageDatabaseID],
[JobSheetLineHours].[CostRateCode],
SUM([JobSheetLineHours].[Hours]) AS TotalCostRateHours,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Monday
     WHEN 1 
     THEN [JobSheetLineHours].[Hours]
     END )
AS MON,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Tuesday
     WHEN 2 
     THEN [JobSheetLineHours].[Hours]
     END )
AS TUE, 
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Wednesday
     WHEN 3 
     THEN [JobSheetLineHours].[Hours]
     END )
AS WED,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Thursday
     WHEN 4 
     THEN [JobSheetLineHours].[Hours]
     END ) 
AS THU,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Friday
     WHEN 5 
     THEN [JobSheetLineHours].[Hours]
     END )
AS FRI,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Saturday
     WHEN 6 
     THEN [JobSheetLineHours].[Hours]
     END )
AS SAT,
SUM( CASE DATEPART(WEEKDAY, [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],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode]
like image 584
WraithNath Avatar asked Jun 02 '11 15:06

WraithNath

3 Answers

It is perfectly acceptable (and often optimal) to create a concrete calendar table of days.

The table would be small and light, you could JOIN to it easily and group by any date combo you wanted. You could add a column to denote work-days versus off-days too if you like.

Otherwise it is awkward to get "zero hour" days because you're asking for an aggregation from data which doesn't exist.

like image 24
Matthew Avatar answered Sep 22 '22 09:09

Matthew

If I understand correctly you'll need to add this to your SELECT

EDIT Since you want sums you'll need to ADD SUMs around the case

SUM(CASE WHEN DATEPART(WEEKDAY, JobSheet.JobSheetDate) = 1 THEN 
    JobSheetLineHours.Hours 
END) AS MON,
SUM(CASE WHEN DATEPART(WEEKDAY, JobSheet.JobSheetDate) = 2 THEN 
    JobSheetLineHours.Hours 
END) AS TUE, 
SUM(CASE WHEN DATEPART(WEEKDAY, JobSheet.JobSheetDate) = 3 THEN 
    JobSheetLineHours.Hours 
END) AS WED,
SUM(CASE WHEN DATEPART(WEEKDAY, JobSheet.JobSheetDate) = 4 THEN 
    JobSheetLineHours.Hours 
END) AS THU,
SUM(CASE WHEN DATEPART(WEEKDAY, JobSheet.JobSheetDate) = 5 THEN 
    JobSheetLineHours.Hours 
END) AS FRI,

You'll also need to remove any [JobSheet].[JobSheetDate] from the SELECT and GROUP BY to get the days all on one line

As noted in the comments this depends on how SET DATEFIRST is set

Also it should be noted that whenever you have a SUM(CASE there's definitely a PIVOT alternative

like image 99
Conrad Frix Avatar answered Sep 22 '22 09:09

Conrad Frix

I look into it using the sample table scheme. Hope it will help you. Never underwent such situation. Glad to learn something new today. Thanks for the Post WraithNath

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

I inserted few records like below

Insert into #temp(projectId, ContractID, CostRateCode, JobSheetDate, Hours)
Values(1, 1, 'A', '2011-06-02 22:00:35.337', 1)

Insert into #temp(projectId, ContractID, CostRateCode, JobSheetDate, Hours)
Values(1, 1, 'A', '2011-06-01 22:00:35.337', 2)

Insert into #temp(projectId, ContractID, CostRateCode, JobSheetDate, Hours)
Values(1, 1, 'A', '2011-05-31 22:00:35.337', 3)

Insert into #temp(projectId, ContractID, CostRateCode, JobSheetDate, Hours)
Values(1, 1, 'A', '2011-06-03 22:00:35.337', 6)

Insert into #temp(projectId, ContractID, CostRateCode, JobSheetDate, Hours)
Values(1, 1, 'A', '2011-05-30 22:00:35.337', 5)

Insert into #temp(projectId, ContractID, CostRateCode, JobSheetDate, Hours)
Values(1, 2, 'B', '2011-05-31 22:00:35.337', 4)

Insert into #temp(projectId, ContractID, CostRateCode, JobSheetDate, Hours)
Values(1, 2, 'B', '2011-06-01 22:00:35.337', 7)

Following is the situation after insertion

enter image description here

Finally the select statement

SET DATEFIRST 1

Select projectId ,ContractID ,CostRateCode,Sum(Hours) Total,
SUM(Case When DATEPART(WEEKDAY, JobSheetDate) = 1 Then Hours Else 0 End) as 'Mon',
SUM(Case When DATEPART(WEEKDAY, JobSheetDate) = 2 Then Hours Else 0 End) as 'Tue',
SUM(Case When DATEPART(WEEKDAY, JobSheetDate) = 3 Then Hours Else 0 End) as 'Wed',
SUM(Case When DATEPART(WEEKDAY, JobSheetDate) = 4 Then Hours Else 0 End) as 'Thu',
SUM(Case When DATEPART(WEEKDAY, JobSheetDate) = 5 Then Hours Else 0 End) as 'Fri',
SUM(Case When DATEPART(WEEKDAY, JobSheetDate) = 6 Then Hours Else 0 End) as 'Sat',
SUM(Case When DATEPART(WEEKDAY, JobSheetDate) = 7 Then Hours Else 0 End) as 'Sun'
From #temp
Group By projectId ,ContractID ,CostRateCode

Drop table #temp

ResultSet

enter image description here

like image 42
Pankaj Avatar answered Sep 20 '22 09:09

Pankaj
Sign in to Comment

Related questions

Please can anyone why explain dropping and recreating stored procedures in SQL Server 2005 causes much more of an initial slow down than expected?
how do i filter out non-numeric values in a text field in teradata?
MySQL: Split column into two
Select all projects that have matching tags
Select BETWEEN column values
Recursive CTE to find parent records
How do I get several highest values from a table?
python psycogp2 inserting into postgresql help
SQL SELECT * FROM XXX WHERE columnName in Array
Find duplicate strings in database
How can I improve performance of average method in SQL?
MySQL: Shuffle a limited query result? [duplicate]
How do I call an Oracle stored procedure from an Excel VBA script?
Why does this SQL result in Index Scan instead of an Index Seek?
what are the best practices to prevent sql injections
SQL Server Stored Procedure add two declared values for total
Get the ID of last inserted records [duplicate]
ALTER DATABASE / ADD FILE / VARIABLE FILENAME?
Using variables in SQL queries in asp.net (C#)
select more than one count in mysql

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!