I am on my first week of a summer programming internship and have been tasked with converting SQL Server 2008 statements into C# using LINQPad. Once I do that and get it working properly, I can enter it into VS and tweak as needed.
The problem is that I am running across SQL statements I have no idea how to convert. In my C# class we did not convert SQL statements into C#, we just created a string variable, assigned the SQL statement to variable and then assigned the variable to an OleDbCommand object. My mentor took the day off for the long weekend and I'm pretty much on my own and clueless on what to do and need some help. There are a couple of dozen like this one and if I can see how to do one I can figure the rest out. Here is the SQL statement:
,[hrs].{Hours] - SUM(
CASE
WHEN [UnitState].[UnitStateTye] <> 'ACTIVE'
THEN [Allocation].[AllocatedEnergyMwh]
ELSE 0
END
/ CAST([Unit].[NetDependableCapacity] AS FLOAT)) AS SH
I'm pretty sure this is an if statement along the lines of:
if [UnitState].[UnitStateType] does not equal active
then SH equals [hrs].[Hours] minus the the sum of
[Allocation].[AllocatedEnergyMwh] / (float)[Unit].[NetDependableCapacity].
else
SH = [hrs].[Hours]
I tried the following code, which I figured wasn't going to work but needed to start somewhere:
var results =
(from v in VDimUnit
join vf in VFactEnergyAllocation on v.UnitKey equals vf.UnitKey
join vd in VDimGadsEvent on vf.GadsEventKey equals vd.GadsEventKey
join vt in VDimTime on vf.TimeKey equals vt.TimeKey
join vus in VDimUnitState on vf.UnitKey equals vus.UnitKey
where vd.GadsEventEndTime.Year == 2011 && v.UnitId == "THL3"
group vf by new {vus.UnitStateType, vf.AllocatedEnergyMwh v.NetDependableCapacity,
vt.QuarterNum} into groupItem
select new {groupItem.Key.QuarterNum, SUM1 = groupItem.Sum(x=> (float)x.AllocatedEnergyMwh /
groupItem.Key.NetDependableCapacity)}).ToArray();
var resultHours =
(from v in VDimTime
group v by v.QuarterNum into groupItem
select new {Hours = groupItem.Count(), groupItem.Key}).ToDictionary(x=> x.Key, x=> x.Hours);
var finalResults =
(from r in results
select new {SH_IF_NOT_ACTIVE = resultHours[r.QuarterNum] - r.SUM1,
Hours =
SH_IF_ACTIVE = resultHours[r.QuarterNum]});
if (SH != "ACTIVE")
{
SH_IF_NOT_ACTIVE;
}
else
{
SH_IF_ACTIVE;
}
I would appreciate it is someone could point me in the right direction.
Something like this:
Hours = condition ? code when true : code when false;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With