How to Sum from child table, then calculate differences, etc. using LINQ
I'm working on an ASP.NET website that's connecting to SQL Server 2005 data. I'm using LINQ to-SQL for data access.
I have the following 3 tables (I've also attached an image of what they look like in a Database Diagram).
The Facility table represents different plants where employees put in shifts. the WorkShift table records the employees start and end dates, total hours worked, and the facility they worked at. Finally, there's a PayPeriod table (pay periods do NOT fall neatly at regular intervals).
My end goal is to display the following (in a gridview) for a specific employee (e.g. FkEmployeeId = 1):
The part I'm not sure about is calculating the HrsWorked fields, which needs to be a SUM of WorkShift records that match the pay period and facility for that Employee Id. I'm thinking I should use a LINQ SUM clause, but unsure how to do so from the "parent" table's query.
Then, for each row, I'll need to calculate the Over/Under field by subtracting HrsWorked from ReqHours. Then I need to display YES or NO in the "Req. Met?" field, based on whether Over/Under is >= 0.
Is there a relatively clean way to do this in LINQ? I'm tempted to do it using brute-force code in my code-behind, but am hesitant because I assume it'll be both more efficient, plus cleaner (separation of data access from client code) to use LINQ (or T-SQL?).
Tables.png
I have the following 3 tables (I've also attached an image of what they look like in a Database Diagram).
The Facility table represents different plants where employees put in shifts. the WorkShift table records the employees start and end dates, total hours worked, and the facility they worked at. Finally, there's a PayPeriod table (pay periods do NOT fall neatly at regular intervals).
My end goal is to display the following (in a gridview) for a specific employee (e.g. FkEmployeeId = 1):
FkFacilityId | StartDate | EndDate | ReqHrs | HrsWorked | Over/Under | Req. Met?
1 | 1/1/2010 | 1/27/2010 | 60 | 45 | -15 | NO
1 | 1/28/2010 | 2/20/2010 | 20 | 25 | 5 | YES
2 | 4/1/2010 | 4/22/2010 | 30 | 30 | 0 | YESThe part I'm not sure about is calculating the HrsWorked fields, which needs to be a SUM of WorkShift records that match the pay period and facility for that Employee Id. I'm thinking I should use a LINQ SUM clause, but unsure how to do so from the "parent" table's query.
Then, for each row, I'll need to calculate the Over/Under field by subtracting HrsWorked from ReqHours. Then I need to display YES or NO in the "Req. Met?" field, based on whether Over/Under is >= 0.
Is there a relatively clean way to do this in LINQ? I'm tempted to do it using brute-force code in my code-behind, but am hesitant because I assume it'll be both more efficient, plus cleaner (separation of data access from client code) to use LINQ (or T-SQL?).
Tables.png
Dale Burrell
I just wrote an entire answer to this... then you deleted the question!
Something like the following should do what you are looking for...
Two things to consider, firstly is there any change a shift can start in one period and end in another, and if so how do you determine which period it belongs in? You can probably adjust the query yourself to correct that.
Secondly it can be worth properly linking the shift to a period maybe by using a trigger on insert to detect which period it should be part of then storing it.
The reason for this is it means you only need the logic to work out which period a shift belongs to in one place, and also it means that the number don't change later if the shifts are edited. For example now, you might have a shift belonging to a period and someone prints off the report. Then later a shift is edited to correct something and it changes which period it is in and no longer looks like the report printed earlier.
Whether or not this would be helpful to you depends entirely on the overall application - just throwing it out there for your information.
Two things to consider, firstly is there any change a shift can start in one period and end in another, and if so how do you determine which period it belongs in? You can probably adjust the query yourself to correct that.
Secondly it can be worth properly linking the shift to a period maybe by using a trigger on insert to detect which period it should be part of then storing it.
The reason for this is it means you only need the logic to work out which period a shift belongs to in one place, and also it means that the number don't change later if the shifts are edited. For example now, you might have a shift belonging to a period and someone prints off the report. Then later a shift is edited to correct something and it changes which period it is in and no longer looks like the report printed earlier.
Whether or not this would be helpful to you depends entirely on the overall application - just throwing it out there for your information.
select x.*, ReqHrs-HoursWorked, case when HoursWorked >= ReqHrs then 'YES' else 'NO' end
from
(
select FkFacilityId, StartDate, EndDate, ReqHours
, (select sum(W.HoursWorked) from WorkShift W where W.FkFacilityId = P.W.FkFacilityId and W.StartDate >= P.StartDate and W.StartDate < dateadd(day, 1, P.EndDate)) HoursWorked
from PayPeriod P
) x
ASKER
Dale, thanks for the quick reply! And sorry for the deletion. For some reason, I was having a heck of a time finding the zones I wanted (e.g. LINQ), submitted the question, and then when looking at EE's help, they said we can't move questions from zone to zone, but should delete the question instead if now solutions have been submitted yet (I did check for replies right before deleting it.) Thanks for sticking with me!
In regards to the first 2 things to consider. We need to keep it flexible (i.e. WorkShifts and/or PayPeriods may be edited after-the-fact), so we don't want to explicitly assign WorkShifts to specific PayPeriods (hence the need to compare dates.
Also, the WorkShift's EndDate determines which PayPeriod it falls into.
Regarding your code, my LINQ skills are still pretty rudimentary, but it *looks* like what I'm after. I need to step away from the computer for a bit, but will try it out shortly and reply with how it goes.
Again, many thanks!
In regards to the first 2 things to consider. We need to keep it flexible (i.e. WorkShifts and/or PayPeriods may be edited after-the-fact), so we don't want to explicitly assign WorkShifts to specific PayPeriods (hence the need to compare dates.
Also, the WorkShift's EndDate determines which PayPeriod it falls into.
Regarding your code, my LINQ skills are still pretty rudimentary, but it *looks* like what I'm after. I need to step away from the computer for a bit, but will try it out shortly and reply with how it goes.
Again, many thanks!
Yeah I was still writing it as you deleted - but no worries. You should be able to adapt what I've given you, just need to change W.StartDate to W.EndDate, and correct any field names I got wrong.
Also double check the end logic i.e. W.EndDate < dataadd(day, 1, P.EndDate) - which assumes P.EndDate is inclusive.
Also double check the end logic i.e. W.EndDate < dataadd(day, 1, P.EndDate) - which assumes P.EndDate is inclusive.
ASKER
I copied and pasted your code into a View window in SQL Server Management Studio. It complained about ReqHours instead of RequiredHours, so I fixed that. The query runs now without any errors, but the results are funky. Here's the code (formatting and "Expr<X>" was inserted by SSMS):
SELECT FkFacilityId, StartDate, EndDate, RequiredHours, HoursWorked, RequiredHours - HoursWorked AS Expr1,
CASE WHEN HoursWorked >= RequiredHours THEN 'YES' ELSE 'NO' END AS Expr2
FROM (SELECT FkFacilityId, StartDate, EndDate, RequiredHours,
(SELECT SUM(HoursWorked) AS Expr1
FROM dbo.WorkShift AS W
WHERE (FkFacilityId = FkFacilityId) AND (StartDate >= P.StartDate) AND (StartDate < DATEADD(day, 1, P.EndDate))) AS HoursWorked
FROM dbo.PayPeriod AS P) AS x
I'm getting a couple of "<Unsupported Data Type>" messages, and the Sum(HoursWorked) aren't calculating (please see attached image). Does the code need to be tweaked, or am I doing something majorly wrong? Thanks!
SSMS-Query.png
SELECT FkFacilityId, StartDate, EndDate, RequiredHours, HoursWorked, RequiredHours - HoursWorked AS Expr1,
CASE WHEN HoursWorked >= RequiredHours THEN 'YES' ELSE 'NO' END AS Expr2
FROM (SELECT FkFacilityId, StartDate, EndDate, RequiredHours,
(SELECT SUM(HoursWorked) AS Expr1
FROM dbo.WorkShift AS W
WHERE (FkFacilityId = FkFacilityId) AND (StartDate >= P.StartDate) AND (StartDate < DATEADD(day, 1, P.EndDate))) AS HoursWorked
FROM dbo.PayPeriod AS P) AS x
I'm getting a couple of "<Unsupported Data Type>" messages, and the Sum(HoursWorked) aren't calculating (please see attached image). Does the code need to be tweaked, or am I doing something majorly wrong? Thanks!
SSMS-Query.png
ASKER
Also, here's an image of my test data, in case that helps.
TestData.png
TestData.png
I can't think why its giving you those errors? Can you try it as a straight query without making a view? Using the view creator means it adds some of those things for you. Then once the query works you can turn it into a view with 'create view ViewName as' in front of the query anyway.
You could try converting those columns to the datatype they should be, but I don't think you should have to.
And you can rename Expr12/2 to anything you like.
e.g. convert(datetime, StartDate)
Also don't forget to change this
(StartDate >= P.StartDate) AND (StartDate < DATEADD(day, 1, P.EndDate)))
to this
(EndDate >= P.StartDate) AND (EndDate < DATEADD(day, 1, P.EndDate)))
Hope that helps...
You could try converting those columns to the datatype they should be, but I don't think you should have to.
And you can rename Expr12/2 to anything you like.
e.g. convert(datetime, StartDate)
Also don't forget to change this
(StartDate >= P.StartDate) AND (StartDate < DATEADD(day, 1, P.EndDate)))
to this
(EndDate >= P.StartDate) AND (EndDate < DATEADD(day, 1, P.EndDate)))
Hope that helps...
ASKER
I didn't even think about the Query window (tend to play in View window out of habit). When I pasted the following into a Query window:
select x.*, RequiredHours-HoursWorked,
from
(
select FkFacilityId, StartDate, EndDate, RequiredHours
, (select sum(W.HoursWorked) from WorkShift W where W.FkFacilityId = P.W.FkFacilityId and W.StartDate >= P.StartDate and W.StartDate < dateadd(day, 1, P.EndDate)) HoursWorked
from PayPeriod P
) x
I get 1 error message:
Msg 207, Level 16, State 1, Line 5
Invalid column name 'W'.
I tried a few things, such as changing "... from WorkShift W ..." to "... from WorkShift AS W ...", but couldn't get rid of the error.
select x.*, RequiredHours-HoursWorked,
from
(
select FkFacilityId, StartDate, EndDate, RequiredHours
, (select sum(W.HoursWorked) from WorkShift W where W.FkFacilityId = P.W.FkFacilityId and W.StartDate >= P.StartDate and W.StartDate < dateadd(day, 1, P.EndDate)) HoursWorked
from PayPeriod P
) x
I get 1 error message:
Msg 207, Level 16, State 1, Line 5
Invalid column name 'W'.
I tried a few things, such as changing "... from WorkShift W ..." to "... from WorkShift AS W ...", but couldn't get rid of the error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Closer! Query runs now, but the Sum is off. For example, it should be 26 for the first pay period at facility 1, but the query shows 31. I'm attaching another screenshot, showing the query and sample data.
Query-vs-Data.png
Query-vs-Data.png
I might be mistaken, because I'm getting confused with the different date formats... but it looks to me to be correct. The WorkShift with id 3 looks like it has an end date of 1/2/2010 which I think is the 2nd Jan 2010? And which therefore fits in the range For PayPeriod id 1. And that being the case 31 is the correct sum?
ASKER
Hmmm... I'll take a look again in the morning. It's almost 2am here, so I'm getting a bit fuzzy. Thanks again for all the assistance so far!
ASKER
Yep, you're right about that date! I was (mistakenly) thinking that we were filtering for FkFacilityId = 1 when I was summing up the HoursWorked valued.
Last piece of the puzzle. If the SUM function returns Null, it needs to instead return 0. I tried the ISNULL() function in a couple of places, but it kept throwing errors (even crashed SSMS sometimes!).
Here's the code I'm using now:
SELECT CONVERT(int, FkFacilityId) AS FkFacility, CONVERT(datetime, StartDate) AS StartDate, CONVERT(datetime, EndDate) AS EndDate, CONVERT(float, RequiredHours)
AS ReqHours, HoursWorked, RequiredHours - HoursWorked AS ReqLessWorked,
CASE WHEN HoursWorked >= RequiredHours THEN 1 ELSE 0 END AS blnReqMet
FROM (SELECT FkFacilityId, StartDate, EndDate, RequiredHours,
(SELECT SUM(HoursWorked) AS Expr1
FROM dbo.WorkShift AS W
WHERE (FkFacilityId = FkFacilityId) AND (StartDate >= P.StartDate) AND (StartDate < DATEADD(day, 1, P.EndDate))) AS HoursWorked
FROM dbo.PayPeriod AS P) AS x
Can you let me know how to return a 0 instead of a Null for HoursWorked?
Last piece of the puzzle. If the SUM function returns Null, it needs to instead return 0. I tried the ISNULL() function in a couple of places, but it kept throwing errors (even crashed SSMS sometimes!).
Here's the code I'm using now:
SELECT CONVERT(int, FkFacilityId) AS FkFacility, CONVERT(datetime, StartDate) AS StartDate, CONVERT(datetime, EndDate) AS EndDate, CONVERT(float, RequiredHours)
AS ReqHours, HoursWorked, RequiredHours - HoursWorked AS ReqLessWorked,
CASE WHEN HoursWorked >= RequiredHours THEN 1 ELSE 0 END AS blnReqMet
FROM (SELECT FkFacilityId, StartDate, EndDate, RequiredHours,
(SELECT SUM(HoursWorked) AS Expr1
FROM dbo.WorkShift AS W
WHERE (FkFacilityId = FkFacilityId) AND (StartDate >= P.StartDate) AND (StartDate < DATEADD(day, 1, P.EndDate))) AS HoursWorked
FROM dbo.PayPeriod AS P) AS x
Can you let me know how to return a 0 instead of a Null for HoursWorked?
ASKER
Oh, one more thing. If HoursWorked is Null, it need to be converted to 0 early enough in the query that "RequiredHours - HoursWorked AS ReqLessWorked" returns 0 too, instead of Null.
NullsInsteadOfZeros.png
NullsInsteadOfZeros.png
ASKER
FYI that I figured this out, with the following (I was originally hoping to convert the NULL to 0 in the inner SELECT, but this works:
SELECT CONVERT(int, FkFacilityId) AS FkFacility, CONVERT(datetime, StartDate) AS StartDate, CONVERT(datetime, EndDate) AS EndDate,
CONVERT(float, RequiredHours) AS ReqHours,
ISNULL(SumHoursWorked, 0) AS SumHoursWorkedNotNull,
ISNULL(RequiredHours - SumHoursWorked, CONVERT(float, RequiredHours)) AS ReqLessWorked,
CASE WHEN SumHoursWorked >= RequiredHours THEN 1 ELSE 0 END AS blnReqMet
FROM (SELECT FkFacilityId, StartDate, EndDate, RequiredHours,
(SELECT SUM(HoursWorked) AS Expr
FROM dbo.WorkShift AS W
WHERE (FkFacilityId = FkFacilityId) AND (StartDate >= P.StartDate) AND (StartDate < DATEADD(day, 1, P.EndDate)))
AS SumHoursWorked
FROM dbo.PayPeriod AS P) AS x
Again, many thanks Dale, especially for proactively offering advice on the "gotchas" that I would've run into, like converting the Query into a View, and how to properly take care of the "<Unsupported Data Type>" errors!
SELECT CONVERT(int, FkFacilityId) AS FkFacility, CONVERT(datetime, StartDate) AS StartDate, CONVERT(datetime, EndDate) AS EndDate,
CONVERT(float, RequiredHours) AS ReqHours,
ISNULL(SumHoursWorked, 0) AS SumHoursWorkedNotNull,
ISNULL(RequiredHours - SumHoursWorked, CONVERT(float, RequiredHours)) AS ReqLessWorked,
CASE WHEN SumHoursWorked >= RequiredHours THEN 1 ELSE 0 END AS blnReqMet
FROM (SELECT FkFacilityId, StartDate, EndDate, RequiredHours,
(SELECT SUM(HoursWorked) AS Expr
FROM dbo.WorkShift AS W
WHERE (FkFacilityId = FkFacilityId) AND (StartDate >= P.StartDate) AND (StartDate < DATEADD(day, 1, P.EndDate)))
AS SumHoursWorked
FROM dbo.PayPeriod AS P) AS x
Again, many thanks Dale, especially for proactively offering advice on the "gotchas" that I would've run into, like converting the Query into a View, and how to properly take care of the "<Unsupported Data Type>" errors!
ASKER
Dale provided, by far, the best (and most patient) assistance I've EVER received on Experts-Exchange!
No worries :)