CODEWITHSUNDEEP
sql-server-2012linq-to-entitiesgroupingentity-framework-6dbcontext
kjv
kjv

Reputation: 11327

Most efficient way to retrieve daily data records grouped by month using EF 6

I have a table with the following structure:

| Column Name | Type           | 
|-------------|--------------- |
| ProjectId   | INT            |     
| StartDate   | INT            |   
| Amount      | DECIMAL(12, 2) |

The StartDate is an INT, but it actually represents a day, in the format YYYYMMDD.

The table stores daily numeric values for a project (only working days are stored, no Sunday or Saturday), as follows:

| ProjectId | StartDate | Amount |
|-----------|-----------|--------|
| 1         | 20140926  | 0.5    |
| 1         | 20140929  | 0.5    |
| 1         | 20140930  | 0.8    |
| 1         | 20141001  | 1      |     
| 1         | 20141002  | 1      |  
| 1         | 20141003  | 0.5    |
| 2         | 20141001  | 0.6    |
| 2         | 20141002  | 0.5    |

What would be the most efficient way to get the average monthly values for each project? I am using EF 6 (the DbContext approach) and SQL Server 2012? I am looking for a solution using Linq-to-Entities.

The ideal result would be something like this:

| ProjectId | StartDate | Amount |
|-----------|-----------|--------|
| 1         | 20140926  | 0.6    |
| 1         | 20141001  | 0.83   |
| 2         | 20141001  | 0.55   |

I have a solution, but I'm not sure it's optimal:

var results = dbContext.ProjectValues
                .GroupBy(pv =>
                    new
                    {
                        ProjectId = pv.ProjectId,
                        Year = pv.StartDate.Year,
                        Month = pv.StartDate.Month,
                    })
                .Select(g =>
                    new
                    {
                        ProjectId = g.Key.ProjectId,
                        StartDate = g.Min(v => v.StartDate),
                        Amount = g.Average(v => v.Amount)
                    })
                .ToList();

Upvotes: 0

Views: 98

Answers (1)

radar
radar

Reputation: 13425

You can do GROUP BY project id and month As date is stored as int, converting into date using cast

SELECT ProjectID, 
       AVG(Amount) as AverageAmount, 
       DATEADD(MONTH, DATEDIFF(MONTH,0,CAST(CAST(StartDate AS VARCHAR(12)) AS DATE)), 0)  
FROM Table1
GROUP BY ProjectID, DATEADD(MONTH, DATEDIFF(MONTH,0,CAST(CAST(StartDate AS VARCHAR(12)) AS DATE)), 0)

Upvotes: 1

Related Questions