RustyHamster RustyHamster - 2 months ago 5
SQL Question

Sum amount on Date Range Quarterly

I am trying to produce a report that shows the amount of cases we have per quarter and there monetary value.
The problem is I can work out the amount of cases per quarter however the loan Amount is a struggle.

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


Thanks

Update here is what the Output should be with correct The correct loan amount.

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

Answer

Just use the same CASE you have in COUNT for 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],
Comments