RustyHamster - 1 year ago 64
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
``````

Just use the same `CASE` you have in `COUNT` for `SUM`

``````COUNT(case WHEN CONVERT(DATE,p.ProjectEndEstimate,103)
BETWEEN GETDATE ()
THEN 1
end) AS [1st Quater],
SUM(case WHEN CONVERT(DATE,p.ProjectEndEstimate,103)
BETWEEN GETDATE ()
THEN c.LoanAmount
ELSE 0
end) AS [1st Quater],
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download