我遇到了这个错误.我看到原因是因为从数据的角度来看,有时返回的平均值是0.00.这个SQL查询工作正常,但这是因为它自动输入0.00.
LINQ抱怨,所以我尝试使用DefaultIfEmpty(),但它说它期待我的viewmodel.
Dim ticketCounts = From t In queue _
Where _
(t.StatusId = 2) And _
(t.CloseDate.Year = Convert.ToDateTime(DateTime.Now).Year) And _
(t.ResolutionDays > 0)
Group t By _
Column1 = CType(t.CloseDate.Month,Integer),_
Column2 = CType(t.CloseDate.ToString("MMMM"),String) _
Into g = Group _
Order By Column1 _
Select _
Id = Column1,_
Month = Column2,_
Critical = g.Where(Function(t) t.PriorityId = 1).DefaultIfEmpty().Average(Function(t) t.ResolutionDays),_
High = g.Where(Function(t) t.PriorityId = 2).DefaultIfEmpty().Average(Function(t) t.ResolutionDays),_
Normal = g.Where(Function(t) t.PriorityId = 3).DefaultIfEmpty().Average(Function(t) t.ResolutionDays),_
Low = g.Where(Function(t) t.PriorityId = 4).DefaultIfEmpty().Average(Function(t) t.ResolutionDays),_
Total = g.Where(Function(t) t.Id <> Nothing).DefaultIfEmpty().Average(Function(t) t.ResolutionDays)
更新！
这是SQL查询做我需要VB做的事情.
SELECT
DATENAME(MONTH,t.CloseDate) AS 'Month',AVG(CASE WHEN (t.PriorityId = 1) THEN CAST(t.ResolutionDays AS Decimal(18,2)) ELSE 0 END) AS 'Critical',AVG(CASE WHEN (t.PriorityId = 2) THEN CAST(t.ResolutionDays AS Decimal(18,2)) ELSE 0 END) AS 'High',AVG(CASE WHEN (t.PriorityId = 3) THEN CAST(t.ResolutionDays AS Decimal(18,2)) ELSE 0 END) AS 'Normal',AVG(CASE WHEN (t.PriorityId = 4) THEN CAST(t.ResolutionDays AS Decimal(18,2)) ELSE 0 END) AS 'Low',AVG(CAST(t.ResolutionDays AS Decimal(18,2))) AS 'Monthly Average'
FROM
tblMaintenanceTicket t
WHERE
t.StatusId = 2
AND YEAR(t.CloseDate) = year(getdate())
GROUP BY
MONTH(t.CloseDate),DATENAME(MONTH,t.CloseDate)
ORDER BY
MONTH(t.CloseDate)
