在单个数据库命中中获取未来 10 天的报告
本文关键字：报告 未来 获取 单个 数据库 | 更新日期: 2023-09-27 17:57:18
我想使用 c# 中的 linq 在单个数据库命中中获取未来 10 天的状态计数。例如：
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
现在我想从今天开始获取未来 10 天的报告，说明：处于每种状态的记录数。今天日期之前的记录计数应为今天日期的一部分，从明天日期开始，它应计入该特定日期。
我如何在单个数据库命中中实现这一点。我不想再次查询数据库 n 10 次.
预期成果：
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
尝试的解决方案：
SQL 查询：
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
林克尝试过：
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();
但它再次与时间进行比较，因此结果不正确。有人可以帮忙吗
您应该始终在更简单的查询中解决问题，以便清楚地了解您想要什么。在这里，您可以首先将每条记录分类为第 1 天、第 2 天、...、第 10 天。然后按状态分组计算每个类别。
我认为这会做你想要的：
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
这是 linq 等效项：
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)
})