Get Reports for next 10 days in single database hit

Question

I want to fetch the count for status for the next 10 days in a single database hit using linq in c#. eg:

Table : Task
Coulmun: Id     Status      DueDate
Value     1     New          Jan 1 2013
Value     2     New          Jan 3 2013
Value     3     In Progress  Jan 1 2014
Value     4     Completed    Jun 21 2016

Now I want to fetch a report for the Next 10 days from today stating : No of records which are in each status. The records count before today date should be part of today date and from tomorrow date it should count of that particular day.

How I can achieve this in a single database hit. I don't want to query DB again n again for 10 times .

Expected result:

Date  Jun 20  Jun 21   Jun22 Jun 23 Jun24   Jun 25......
New    2       0        0       0     0        0
InPrg  1       0        0       0     0        0
Complt 0       1        0       0     0        0

Solution Tried :

sql query:

SELECT count (TaskId) , StatusCode, DueDate  from Task 
where dateadd(day, datediff(day, 0, DueDate), 0) BETWEEN CAST('2013/06/08' as DATE) and cast('2013/06/18' as DATE)
--where CONVERT(DATETIME, DueDate)  >CONVERT(DATETIME, '2013/06/08')  and CONVERT(DATETIME, DueDate) < CONVERT(DATETIME, '2013/06/18')
GROUP by StatusCode, DueDate
 order by 1 desc 

Result :
(No column name)    StatusCode  DueDate
1   IP  2013-06-08 08:13:36.080
1   IP  2013-06-08 09:49:04.263
1   IP  2013-06-08 10:03:26.550
1   NW  2013-06-08 10:14:11.247
1   IP  2013-06-08 10:33:45.760
1   IP  2013-06-08 20:44:27.427
1   NW  2013-06-09 01:13:54.150

Linq Tried :

 DateTime startdate;
             DateTime.TryParse("2013/06/08", out startdate);
            DateTime enddate;
             DateTime.TryParse("2013/06/18", out enddate);

 var query = repository.Data
                            .Where(x => x.StatusCode != null
                                && EntityFunctions.TruncateTime(x.DueDate) < EntityFunctions.TruncateTime(startdate))
                            .GroupBy(p => new
                            {
                                p.StatusCode

                            })
                            .Select(g => new
                            {
                                g.Key.StatusCode,

                                AvailableCpunt = g.Count()
                            }).ToList();


                var result1 = repository.Data
                           .Where(x => x.StatusCode != null
                               && EntityFunctions.TruncateTime(x.DueDate) > EntityFunctions.TruncateTime(startdate) 
                               && EntityFunctions.TruncateTime(x.DueDate) < EntityFunctions.TruncateTime(enddate))
                           .GroupBy(p => new
                           {
                               p.StatusCode,
                               p.DueDate

                           })
                           .Select(g => new
                           {
                               StatusCode = g.Key.StatusCode,
                               DueDate = g.Key.DueDate,
                               AvailableCount = g.Count()
                           }).ToList();

But again it compare with the time also and hence the results are not correct. Could some one please help

Answer 1

You should always break the problem in simpler queries to have a clear perspective of What you want. Here you can first categorize every record as day1, day2, ..., day10. Then count every category with grouping on Status.

I think this would do what you want:

select
    Status,
    SUM(Day1) Day1, SUM(Day2) Day2 , SUM(Day3) Day3 , SUM(Day4) Day4 , SUM(Day5) Day5,
    SUM(Day6) Day6 , SUM(Day7) Day7 , SUM(Day8) Day8 , SUM(Day9) Day9 , SUM(Day10) Day10
from(
    select
        Status,
        case when DueDate <= cast(GETDATE() as date) then 1 else 0 end Day1,
        case when DueDate = dateadd(day, 1, cast(GETDATE() as date)) then 1 else 0 end Day2,
        case when DueDate = dateadd(day, 2, cast(GETDATE() as date)) then 1 else 0 end Day3,
        case when DueDate = dateadd(day, 3, cast(GETDATE() as date)) then 1 else 0 end Day4,
        case when DueDate = dateadd(day, 4, cast(GETDATE() as date)) then 1 else 0 end Day5,
        case when DueDate = dateadd(day, 5, cast(GETDATE() as date)) then 1 else 0 end Day6,
        case when DueDate = dateadd(day, 6, cast(GETDATE() as date)) then 1 else 0 end Day7,
        case when DueDate = dateadd(day, 7, cast(GETDATE() as date)) then 1 else 0 end Day8,
        case when DueDate = dateadd(day, 8, cast(GETDATE() as date)) then 1 else 0 end Day9,
        case when DueDate = dateadd(day, 9, cast(GETDATE() as date)) then 1 else 0 end Day10
    from @task
) t
group by Status

And here is the linq equivalent:

tasks.Select(a => new
{
    Status = a.Status,
    Day1 = a.DueDate <= DateTime.Today ? 1 : 0,
    Day2 = a.DueDate == DateTime.Today.AddDays(1) ? 1 : 0,
    Day3 = a.DueDate == DateTime.Today.AddDays(2) ? 1 : 0,
    Day4 = a.DueDate == DateTime.Today.AddDays(3) ? 1 : 0,
    Day5 = a.DueDate == DateTime.Today.AddDays(4) ? 1 : 0,
    Day6 = a.DueDate == DateTime.Today.AddDays(5) ? 1 : 0,
    Day7 = a.DueDate == DateTime.Today.AddDays(6) ? 1 : 0,
    Day8 = a.DueDate == DateTime.Today.AddDays(7) ? 1 : 0,
    Day9 = a.DueDate == DateTime.Today.AddDays(8) ? 1 : 0,
    Day10 = a.DueDate == DateTime.Today.AddDays(9) ? 1 : 0,
}).GroupBy(a => a.Status).Select(a => new
{
    Status = a.Key,
    Day1 = a.Sum(b => b.Day1),
    Day2 = a.Sum(b => b.Day2),
    Day3 = a.Sum(b => b.Day3),
    Day4 = a.Sum(b => b.Day4),
    Day5 = a.Sum(b => b.Day5),
    Day6 = a.Sum(b => b.Day6),
    Day7 = a.Sum(b => b.Day7),
    Day8 = a.Sum(b => b.Day8),
    Day9 = a.Sum(b => b.Day9),
    Day10 = a.Sum(b => b.Day10)
})