Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# How to Sum from child table, then calculate differences, etc. using LINQ

Posted on 2010-09-05
Medium Priority
517 Views
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):
``````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        | YES
``````
The first 4 columns are from the PayPeriod table. That part's easy.

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
0
Question by:cdakz
• 10
• 7

LVL 21

Expert Comment

ID: 33608794
I just wrote an entire answer to this... then you deleted the question!
0

LVL 21

Expert Comment

ID: 33608803
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.
``````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
``````
0

Author Comment

ID: 33608842
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!
0

LVL 21

Expert Comment

ID: 33608861
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.
0

Author Comment

ID: 33609510
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
0

Author Comment

ID: 33609531
Also, here's an image of my test data, in case that helps.
TestData.png
0

LVL 21

Expert Comment

ID: 33609537
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...
0

Author Comment

ID: 33609582
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, case when HoursWorked >= RequiredHours then 'YES' else 'NO' end
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.
0

LVL 21

Accepted Solution

Dale Burrell earned 2000 total points
ID: 33609602
Try...
``````select x.*, RequiredHours-HoursWorked, case when HoursWorked >= RequiredHours then 'YES' else 'NO' end
from
(
select FkFacilityId, StartDate, EndDate, RequiredHours
, (select sum(W.HoursWorked) from WorkShift W where W.FkFacilityId = P.FkFacilityId and W.EndDate >= P.StartDate and W.EndDate < dateadd(day, 1, P.EndDate)) HoursWorked
from PayPeriod P
) x
``````
0

Author Comment

ID: 33609659
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
0

LVL 21

Expert Comment

ID: 33609675
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?
0

Author Comment

ID: 33609681
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!
0

Author Comment

ID: 33612566
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?
0

Author Comment

ID: 33612650
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.
0

Author Comment

ID: 33615046
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!

0

Author Closing Comment

ID: 33615057
Dale provided, by far, the best (and most patient) assistance I've EVER received on Experts-Exchange!
0

LVL 21

Expert Comment

ID: 33615123
No worries :)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
###### Suggested Courses
Course of the Month7 days, 9 hours left to enroll