troubleshooting Question

Obtaining Transactions Greater Than 90 Days T-SQL

Avatar of Trent Adams
Trent Adams asked on
* T-SQLMicrosoft SQL Server
9 Comments1 Solution78 ViewsLast Modified:
I need to get expenses that are 90 days past due and have not been approved or paid. I can get the first part, however, when I attempt to perform a case when...the aggregation seems to get destroyed.

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;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros