总额日期范围每季度

问题描述：

我正在尝试生成一份报告，显示我们每个季度的案件数量和存在的货币价值。问题是我可以计算每个季度的案件数量，但贷款金额是一个挣扎。这里总额日期范围每季度

SELECT 
COUNT(case when CONVERT(DATE,p.ProjectEndEstimate,103) BETWEEN GETDATE() AND DATEADD(Day , +91,GETDATE()) then 1 end) AS [1st Quater], 
SUM(c.LoanAmount) AS [1st Quater LoanAmount] , 
COUNT(case when CONVERT(DATE,p.ProjectEndEstimate,103) BETWEEN DATEADD(Day , +92,GETDATE()) AND DATEADD(Day , +183,GETDATE()) then 1 end) AS [2nd Quater], 
SUM(c.LoanAmount) AS [2nd Quater LoanAmount] , 
COUNT(case when CONVERT(DATE,p.ProjectEndEstimate,103) BETWEEN DATEADD(Day , +184,GETDATE()) AND DATEADD(Day , +275,GETDATE()) then 1 end) AS [3rd Quater], 
SUM(c.LoanAmount) AS [3rd Quater LoanAmount] , 
COUNT(case when CONVERT(DATE,p.ProjectEndEstimate,103) BETWEEN DATEADD(Day , +276,GETDATE()) AND DATEADD(Day , +366,GETDATE()) then 1 end) AS [4th Quater], 
SUM(c.LoanAmount) AS [4th Quater LoanAmount] 
FROM [dbo].[tbl_Projects] AS p 
INNER JOIN tbl_CaseDetails AS c 
ON p.PK_ProjectID = c.FK_ProjectID 
INNER JOIN [dbo].[tbl_Lenders] AS l 
ON l.PK_CompanyID = c.LenderID 
WHERE l.PK_CompanyID = @Lender

感谢

更新是输出应该是具有正确正确的贷款额度是什么。

1st Quater 1st Quater LoanAmount 2nd Quater 2nd Quater LoanAmount 3rd Quater 3rd Quater LoanAmount 4th Quater 4th Quater LoanAmount 
309   2068822879.50   223   2068822879.50   64   2068822879.50   21   2068822879.50

我们展示了一些示例数据和预期的结果。请阅读[** How-to-Ask **]（http://*.com/help/how-to-ask） \t \t这里是[** START **]（http ：//spaghettidba.com/2015/04/24/how-to-post-at-sql-question-on-a-public-forum/），了解如何提高您的问题质量并获得更好的答案。 –

答

只要使用相同CASE你COUNT有SUM

COUNT(case WHEN CONVERT(DATE,p.ProjectEndEstimate,103) 
       BETWEEN GETDATE() 
       AND DATEADD(Day , +91,GETDATE()) 
      THEN 1 
     end) AS [1st Quater], 
SUM(case WHEN CONVERT(DATE,p.ProjectEndEstimate,103) 
       BETWEEN GETDATE() 
       AND DATEADD(Day , +91,GETDATE()) 
     THEN c.LoanAmount 
     ELSE 0 
     end) AS [1st Quater],

作品感谢分配 – RustyHamster

总额日期范围每季度

相关推荐