Grouping and time diff calculation?

Question

OK, I've come once again to the Temple of LINQ for help in a query that's melting my brain.  I'm doing essentially a simple listview of hours worked for a given time period by employee and then calculating gross wages.  I'm joining a table that logs their clocking in and out, and a table with their wages, etc.  I've played around with several different attempts but at some point my brain starts to bubble... this is what my most recent (unfinished) attempt looks like so far...

 var serverTotals = from h in dc.Hours
                               join s in dc.Servers on h.Name equals s.Name
                                where h.Time_End < staffEndCal.SelectedDate && h.Time_End > staffStartCal.SelectedDate
                                group h by new { h.Name,h.Time_End,h.Time_Start, s.Wage} into g

As always, I'm deeply grateful for the help I've received in the past, and any suggestions for this dilemma.

Answer 1

I don't have access to the actual data to do a proper join and I believe this groupby doesn't allow for the sql like lambdas, so here is my suggestion:

dc.Hours
  .Where(h => h.Time_End < staffEndCal.SelectedDate && h.Time_End > staffStartCal.SelectedDate)
  .GroupBy(h => h.Name,
           h =>  h.Time_End - h.Time_Start,
	   (name, hoursworked) => new
				{
					Name = name,
					Pay = hoursworked.Sum() * dc.Servers.First(sv => sv.Name == name).Wage
				}
			);

Sorry for the bad formatting, I dream of a day when MSDN will use the proper font to allow things to line up.

---

William Wegerson (www.OmegaCoder.Com)

Answer 2

Thanks very much William... I suspect it's very very close although VS is balking at the sum method. 

Answer 3

> Balking sum

try adding .AsEnumerable() after the Where().

---

William Wegerson (www.OmegaCoder.Com)

Answer 4

No joy... I had tried (out of desperation) converting to a decimal but LINQ didn't think too highly of that either :S

Answer 5

Hi there William... Just to let you know, I "cheated".  Apparently you can't sum time spans and slogging through some of the other go arounds were so painful that I rewrote the "log out" procedure to store the time as a double in a new column and just be done with it.  Not the most elegant solution but it works ;)

Thanks very much for your time and all your help!

Answer 6

Apparently you can't sum time spans

No one shouldn't. :-)

One should only sum up minutes then do the conversion. Here I just sum up the hours worked:

void Main()
{
	var data = new List<TimeData>()
	{
		new TimeData { User = "Bob", Start = DateTime.Now.AddHours(-1), End = DateTime.Now },
		new TimeData { User = "Bob", Start = DateTime.Now.AddHours(-3), End = DateTime.Now },
		new TimeData { User = "Frank", Start = DateTime.Now.AddHours(-2), End = DateTime.Now }
	};

	data.GroupBy (d => d.User,
	              time => time.End.Subtract( time.Start).TotalHours,
				  (user, time) => new
	                {
						Name = user,
						Time = time.Sum (),
	                });		 
					
/* result is
Bob 4 
Frank 2 
*/
	
}


public class TimeData
{
	public string User { get; set; }
	public DateTime Start { get; set; }
	public DateTime End { get; set; }

}

---

William Wegerson (www.OmegaCoder.Com)

Answer 7

Thanks for that William... next time (if I get amnesia ;) ) I find myself facing this I'll remember that. 

Seemed so simple at the time... 5 hours later, ugh.

Thanks again, I'm grateful for all your time and help!

Answer 8

No problem Paul and glad to help. Just throw me a "Vote as helpful" bone. :-)

---

William Wegerson (www.OmegaCoder.Com)