RustyHamster - 2 months ago 5

SQL Question

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],
```

Source (Stackoverflow)

Comments