SELECT ex.ExpenseTypeID,
COUNT(*) AS ExpenseType,
ext.Description,
CASE
WHEN DATEDIFF(day, ExpenseDate, GETDATE()) >= 90
THEN 90
WHEN(DATEDIFF(day, ExpenseDate, GETDATE()) >= 60
AND DATEDIFF(day, ExpenseDate, GETDATE()) < 90)
THEN 60
WHEN(DATEDIFF(day, ExpenseDate, GETDATE()) >= 30
AND DATEDIFF(day, ExpenseDate, GETDATE()) < 60)
THEN 30
END
FROM tbl_Expenses ex
INNER JOIN tbl_ExpenseType ext ON ex.ExpenseTypeID = ext.ExpenseTypeID
WHERE EmployeeID = 88
AND ExpenseDate <= DATEADD(day, -90, GETDATE())
AND (Approved = 0
OR Paid = 0
AND IsVoided = 0)
GROUP BY ex.ExpenseTypeID,
ext.Description,
ex.ExpenseDate;
SELECT
ex.ExpenseTypeID
, ext.Description
, CASE
WHEN DATEDIFF( DAY, ExpenseDate, GETDATE() ) >= 90 THEN 90
WHEN (DATEDIFF( DAY, ExpenseDate, GETDATE() ) >= 60 AND
DATEDIFF( DAY, ExpenseDate, GETDATE() ) < 90) THEN 60
WHEN (DATEDIFF( DAY, ExpenseDate, GETDATE() ) >= 30 AND
DATEDIFF( DAY, ExpenseDate, GETDATE() ) < 60) THEN 30
END AS Age
, COUNT( * ) AS ExpenseType
FROM tbl_Expenses ex
INNER JOIN tbl_ExpenseType ext ON ex.ExpenseTypeID = ext.ExpenseTypeID
WHERE EmployeeID = 88
AND ExpenseDate <= DATEADD( DAY, -90, GETDATE() )
AND ((Approved = 0 OR Paid = 0)
AND IsVoided = 0)
GROUP BY
ex.ExpenseTypeID
, ext.Description
, CASE
WHEN DATEDIFF( DAY, ExpenseDate, GETDATE() ) >= 90 THEN 90
WHEN (DATEDIFF( DAY, ExpenseDate, GETDATE() ) >= 60 AND
DATEDIFF( DAY, ExpenseDate, GETDATE() ) < 90) THEN 60
WHEN (DATEDIFF( DAY, ExpenseDate, GETDATE() ) >= 30 AND
DATEDIFF( DAY, ExpenseDate, GETDATE() ) < 60) THEN 30
END
+------------+---------------+-----+-----+-----+
| employeeID | expense date | 30 | 60 | 90 |
+------------+---------------+-----+-----+-----+
+------------+---------------+--------+
| employeeID | expense date | alias |
+------------+---------------+--------+
| | | 30 |
| | | 60 |
| | | 90 |
+------------+---------------+--------+
or the second of those?
I'd also add extra parentheses to the WHERE condition just to be very clear about the conditions:
AND ((Approved = 0
OR Paid = 0)
AND IsVoided = 0)