Solved

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

Posted on 2010-09-05
17
478 Views
Last Modified: 2013-11-11
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

Open in new window

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
Comment
Question by:cdakz
  • 10
  • 7
17 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
Comment Utility
I just wrote an entire answer to this... then you deleted the question!
0
 
LVL 21

Expert Comment

by:Dale Burrell
Comment Utility
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

Open in new window

0
 

Author Comment

by:cdakz
Comment Utility
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

by:Dale Burrell
Comment Utility
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

by:cdakz
Comment Utility
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

by:cdakz
Comment Utility
Also, here's an image of my test data, in case that helps.
TestData.png
0
 
LVL 21

Expert Comment

by:Dale Burrell
Comment Utility
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

by:cdakz
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 21

Accepted Solution

by:
Dale Burrell earned 500 total points
Comment Utility
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

Open in new window

0
 

Author Comment

by:cdakz
Comment Utility
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

by:Dale Burrell
Comment Utility
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

by:cdakz
Comment Utility
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

by:cdakz
Comment Utility
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

by:cdakz
Comment Utility
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
0
 

Author Comment

by:cdakz
Comment Utility
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

by:cdakz
Comment Utility
Dale provided, by far, the best (and most patient) assistance I've EVER received on Experts-Exchange!
0
 
LVL 21

Expert Comment

by:Dale Burrell
Comment Utility
No worries :)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now