score:1

Accepted answer
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[2].Any() ? lookup[2].Sum() : lookup[1].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[2].Any() ? lookup[2].Sum() : lookup[1].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

How about this?

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)
        };

Related Articles