score:1

``````rows
.GroupBy(
r => new { r.Date.Year, r.Date.Month, r.Date.Day, r.Type },
(r, rr) => new { r.Year, r.Month, r.Day, r.Type, Amount = rr.Sum(rrr => rrr.Amount) })
.GroupBy(
r => new { r.Year, r.Month, r.Day },
(r, rr) => new { r.Year, r.Month, r.Day, Amount = rr.OrderByDescending(rrr => rrr.Type).Select(rrr => rrr.Amount).First() })
.GroupBy(
r => new { r.Year, r.Month },
(r, rr) => new { r.Year, r.Month, Amount = rr.Sum(rrr => rrr.Amount) })
``````

Ratio behind this is very simple: the requirement "if there's at least one type 2 record, sum only type 2 records" can be implemented by simply grouping records by type (within days, of course). Why does it work? Because we split all records into two groups, type 2 (which should be used if there is at least one type 2 record), and type 1 (which in fact means "all records when no type 2 present"). Second part (choosing sum) is even simpler: we just order groups (within day) by descending type (i.e. sum for type 2, sum for type 1) and take first one, which gives us type 2 if present and type 1 otherwise.

Frankly, it's a kind of "smart code" everybody hates, because nobody could understand from a glance how it works.

score:1

How would this look to you?

``````var query =
from r in rows.ToArray()
group r by new { r.Date.Year, r.Date.Month } into g
let lookup = g.ToLookup(x => x.Type, x => x.Amount)
let Hours = lookup.Any() ? lookup.Sum() : lookup.Sum()
select new
{
Date = new DateTime(g.Key.Year, g.Key.Month, 1),
Hours,
};
``````

Note the `.ToArray()` as you need to bring your data into memory to make this work.

I assumed that `Type` was an integer with values of either `1` or `2`.

On further thought I don't think it is possible to do this kind of grouping in a single query without bringing it into memory.

So the best alternative is to minimize the memory. If this doesn't work then you'll need to break it down into several queries.

``````var query1 =
from r in rows
group r.Amount by new
{
r.Date.Year,
r.Date.Month,
r.Type,
} into g
select new
{
g.Key.Year,
g.Key.Month,
g.Key.Type,
Amount = g.Sum(),
};

var query2 =
from r in query1.ToArray()
group r by new
{
r.Year,
r.Month,
} into g
let lookup = g.ToLookup(x => x.Type, x => x.Amount)
let Hours = lookup.Any() ? lookup.Sum() : lookup.Sum()
select new
{
Date = new DateTime(g.Key.Year, g.Key.Month, 1),
Hours,
};
``````

Is this a option a possibility?

score:1

Okay, I think I've got this figured out. I've tested it with some data, and that works.

Testdata and SQL testquery:

``````DECLARE @table TABLE (
Datum DATETIME,
Amount INT,
[Type] INT
)

INSERT INTO @table (Datum, Amount, [Type]) values
('2012-01-01',200,1),
('2012-01-01',100,2),
('2012-01-02',500,1),
('2012-03-01',200,1),
('2012-03-01',100,1),
('2012-03-02',500,2)

SELECT MONTH(Datum), YEAR(Datum), COUNT(*), SUM(Amount)
FROM @table t
INNER JOIN (
SELECT DAY (Datum) AS _day, MONTH(Datum) AS _month, YEAR(Datum) _year,
MAX([Type]) as _type
FROM @table
GROUP BY DAY (Datum), MONTH(Datum), YEAR(Datum)
) X
ON _month = MONTH (T.Datum)
AND _year = YEAR(T.Datum)
AND _day = DAY(T.Datum)
AND _type = T.[Type]
GROUP BY MONTH(Datum), YEAR(Datum)
``````

Result:

``````(No column name)    (No column name)    (No column name)    (No column name)
1   2012    2   600
3   2012    3   800
``````

The translated LINQ query, tested with L2S and a 'real' test-table with test-data.

``````using (DataClasses1DataContext ctx = new DataClasses1DataContext()) {
var rows = ctx.Tests;

var query = rows
.Join(
rows.GroupBy(rr =>
new { rr.Datum.Day, rr.Datum.Month, rr.Datum.Year },
(key, data) => new { Year = key.Year, Month = key.Month, Day = key.Day, MaxType = data.Select(xxx => xxx.Type).Max() }
),
rr => new { Day = rr.Datum.Day, Month = rr.Datum.Month, Year = rr.Datum.Year, Type = rr.Type },
rr => new { Day = rr.Day, Month = rr.Month, Year = rr.Year, Type = rr.MaxType },
(r, r1) => r

)
.GroupBy(r =>
new { Year = r.Datum.Year, Month = r.Datum.Month },
(key, data) => new { Year = key.Year, Month = key.Month, Amount = data.Select(xx => xx.Amount).Sum() }
)
.ToList();
}
``````

This returns the same results.

And, for the fun of it, the SQL-query that L2S generated from the Linq query.

``````SELECT [t7].[value] AS [Year], [t7].[value2] AS [Month], (
SELECT SUM([t8].[Amount])
FROM [dbo].[Test] AS [t8]
INNER JOIN (
SELECT [t11].[value3], [t11].[value2], [t11].[value], (
SELECT MAX([t12].[Type])
FROM [dbo].[Test] AS [t12]
WHERE ((([t11].[value] IS NULL) AND (DATEPART(Day, [t12].[Datum]) IS NULL)) OR (([t11].[value] IS NOT NULL) AND (DATEPART(Day, [t12].[Datum]) IS NOT NULL) AND ((([t11].[value] IS NULL) AND (DATEPART(Day, [t12].[Datum]) IS NULL)) OR (([t11].[value] IS NOT NULL) AND (DATEPART(Day, [t12].[Datum]) IS NOT NULL) AND ([t11].[value] = DATEPART(Day, [t12].[Datum])))))) AND ((([t11].[value2] IS NULL) AND (DATEPART(Month, [t12].[Datum]) IS NULL)) OR (([t11].[value2] IS NOT NULL) AND (DATEPART(Month, [t12].[Datum]) IS NOT NULL) AND ((([t11].[value2] IS NULL) AND (DATEPART(Month, [t12].[Datum]) IS NULL)) OR (([t11].[value2] IS NOT NULL) AND (DATEPART(Month, [t12].[Datum]) IS NOT NULL) AND ([t11].[value2] = DATEPART(Month, [t12].[Datum])))))) AND ((([t11].[value3] IS NULL) AND (DATEPART(Year, [t12].[Datum]) IS NULL)) OR (([t11].[value3] IS NOT NULL) AND (DATEPART(Year, [t12].[Datum]) IS NOT NULL) AND ((([t11].[value3] IS NULL) AND (DATEPART(Year, [t12].[Datum]) IS NULL)) OR (([t11].[value3] IS NOT NULL) AND (DATEPART(Year, [t12].[Datum]) IS NOT NULL) AND ([t11].[value3] = DATEPART(Year, [t12].[Datum]))))))
) AS [value4]
FROM (
SELECT [t10].[value], [t10].[value2], [t10].[value3]
FROM (
SELECT DATEPART(Day, [t9].[Datum]) AS [value], DATEPART(Month, [t9].[Datum]) AS [value2], DATEPART(Year, [t9].[Datum]) AS [value3]
FROM [dbo].[Test] AS [t9]
) AS [t10]
GROUP BY [t10].[value], [t10].[value2], [t10].[value3]
) AS [t11]
) AS [t13] ON (DATEPART(Day, [t8].[Datum]) = [t13].[value]) AND (DATEPART(Month, [t8].[Datum]) = [t13].[value2]) AND (DATEPART(Year, [t8].[Datum]) = [t13].[value3]) AND ([t8].[Type] = [t13].[value4])
WHERE ((([t7].[value] IS NULL) AND (DATEPART(Year, [t8].[Datum]) IS NULL)) OR (([t7].[value] IS NOT NULL) AND (DATEPART(Year, [t8].[Datum]) IS NOT NULL) AND ((([t7].[value] IS NULL) AND (DATEPART(Year, [t8].[Datum]) IS NULL)) OR (([t7].[value] IS NOT NULL) AND (DATEPART(Year, [t8].[Datum]) IS NOT NULL) AND ([t7].[value] = DATEPART(Year, [t8].[Datum])))))) AND ((([t7].[value2] IS NULL) AND (DATEPART(Month, [t8].[Datum]) IS NULL)) OR (([t7].[value2] IS NOT NULL) AND (DATEPART(Month, [t8].[Datum]) IS NOT NULL) AND ((([t7].[value2] IS NULL) AND (DATEPART(Month, [t8].[Datum]) IS NULL)) OR (([t7].[value2] IS NOT NULL) AND (DATEPART(Month, [t8].[Datum]) IS NOT NULL) AND ([t7].[value2] = DATEPART(Month, [t8].[Datum]))))))
) AS [Amount]
FROM (
SELECT [t6].[value], [t6].[value2]
FROM (
SELECT DATEPART(Year, [t0].[Datum]) AS [value], DATEPART(Month, [t0].[Datum]) AS [value2]
FROM [dbo].[Test] AS [t0]
INNER JOIN (
SELECT [t3].[value3], [t3].[value2], [t3].[value], (
SELECT MAX([t4].[Type])
FROM [dbo].[Test] AS [t4]
WHERE ((([t3].[value] IS NULL) AND (DATEPART(Day, [t4].[Datum]) IS NULL)) OR (([t3].[value] IS NOT NULL) AND (DATEPART(Day, [t4].[Datum]) IS NOT NULL) AND ((([t3].[value] IS NULL) AND (DATEPART(Day, [t4].[Datum]) IS NULL)) OR (([t3].[value] IS NOT NULL) AND (DATEPART(Day, [t4].[Datum]) IS NOT NULL) AND ([t3].[value] = DATEPART(Day, [t4].[Datum])))))) AND ((([t3].[value2] IS NULL) AND (DATEPART(Month, [t4].[Datum]) IS NULL)) OR (([t3].[value2] IS NOT NULL) AND (DATEPART(Month, [t4].[Datum]) IS NOT NULL) AND ((([t3].[value2] IS NULL) AND (DATEPART(Month, [t4].[Datum]) IS NULL)) OR (([t3].[value2] IS NOT NULL) AND (DATEPART(Month, [t4].[Datum]) IS NOT NULL) AND ([t3].[value2] = DATEPART(Month, [t4].[Datum])))))) AND ((([t3].[value3] IS NULL) AND (DATEPART(Year, [t4].[Datum]) IS NULL)) OR (([t3].[value3] IS NOT NULL) AND (DATEPART(Year, [t4].[Datum]) IS NOT NULL) AND ((([t3].[value3] IS NULL) AND (DATEPART(Year, [t4].[Datum]) IS NULL)) OR (([t3].[value3] IS NOT NULL) AND (DATEPART(Year, [t4].[Datum]) IS NOT NULL) AND ([t3].[value3] = DATEPART(Year, [t4].[Datum]))))))
) AS [value4]
FROM (
SELECT [t2].[value], [t2].[value2], [t2].[value3]
FROM (
SELECT DATEPART(Day, [t1].[Datum]) AS [value], DATEPART(Month, [t1].[Datum]) AS [value2], DATEPART(Year, [t1].[Datum]) AS [value3]
FROM [dbo].[Test] AS [t1]
) AS [t2]
GROUP BY [t2].[value], [t2].[value2], [t2].[value3]
) AS [t3]
) AS [t5] ON (DATEPART(Day, [t0].[Datum]) = [t5].[value]) AND (DATEPART(Month, [t0].[Datum]) = [t5].[value2]) AND (DATEPART(Year, [t0].[Datum]) = [t5].[value3]) AND ([t0].[Type] = [t5].[value4])
) AS [t6]
GROUP BY [t6].[value], [t6].[value2]
) AS [t7]
``````

I have no idea how efficient this SQL is, you're going to have to try it.

score:0

``````from r in rows
group r by new { r.Date.Year, r.Date.Month }
into g
let type2Days = g.Where( a => a.Type == 2 ).Select( a => a.Date.Day ).Distinct()
let filtered = g.Where( a => a.Type == 2 || type2Days.Contains(a.Date.Day) == false )
select
new
{
Date = new DateTime(g.Key.Year, g.Key.Month, 1),
Hours = filtered.Sum(a => a.Amount)
};
``````