Obtaining Transactions Greater Than 90 Days T-SQL

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;

Open in new window

Trent AdamsDevOps LeadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
All expenses must be at least 90 days old, so the current CASE statement would only ever return "90" anyway.

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)
PortletPaulEE Topic AdvisorCommented:
Not sure what you are attempting to achieve, but in addition to the avove the group by clause needs to use the same case expression
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

Open in new window

Trent AdamsDevOps LeadAuthor Commented:
The goal here is to return results which will be used for analytics on a dashboard.
I’m trying to get expenses for a user based on the employeeID and expense date. Criteria is expenses which are not approved with the last 90 days and expenses that haven’t been paid or voided.
These will be displayed with an alias column which show if they are 30,60 or 90 days plus.
This will be used to help users see if they have any expenses which haven’t been approved and paid within a 99 day period.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
Trent,
the very best way to define your required output is to use a mock-up.
Words are notoriously bad at describing requirements, for example I don't really know what you mean by:
"These will be displayed with an alias column"

Is that 3 columns like this?
+------------+---------------+-----+-----+-----+
| employeeID |  expense date |  30 |  60 |  90 |
+------------+---------------+-----+-----+-----+

+------------+---------------+--------+
| employeeID |  expense date |  alias |
+------------+---------------+--------+
|            |               |     30 |
|            |               |     60 |
|            |               |     90 |
+------------+---------------+--------+

Open in new window

or the second of those?

Is expense description required in the output?

Experience shows that if you provide sample data and expected result you will get an accurate answer in the minimum time.
Trent AdamsDevOps LeadAuthor Commented:
My apologies. The second is what I’m looking for. The description will be used as an X axis label. So, total the expenses which are 30, 60 and 90 days past due. Then list by expense type with the total days past due (approximation of past due days).

Expected Output
Expense Type  Total   Days Past
Mileage              15       30
Activities             7        60
Tolls                    10       90
PortletPaulEE Topic AdvisorCommented:
I have removed the date restriction from the where clause, otherwise very similar to my previous post:
SELECT
    ext.Description As [Expense]
  , COUNT( * ) AS Total
  , 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 [Days_Past]
FROM tbl_Expenses ex
INNER JOIN tbl_ExpenseType ext ON ex.ExpenseTypeID = ext.ExpenseTypeID
WHERE EmployeeID = 88
AND ((Approved = 0 OR Paid = 0)
AND IsVoided = 0)
GROUP BY
    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

Open in new window


[edit]You may want to use this in the where clause

ExpenseDate <= DATEADD( DAY, -30, GETDATE() )

OR

and an else condition in the case expression for expenses within 30 days.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Trent AdamsDevOps LeadAuthor Commented:
Thanks. So there were a couple things I needed to change, otherwise close but no cigar (I should’ve went through my syntax from the bottom up). I greatly appreciate all the help and advice.
PortletPaulEE Topic AdvisorCommented:
:)

This is what i commonly do when forming a group by query, I COPY the select list under the group by
Then, remove any aliasing

(nb. this is one of the reasons why I list all non-aggregating columns before any aggregations in the select list)

step1: copy

select
      non_aggregating1 as x
    , non_aggregating2 as y
    , case when ... end as z
    , max(x)
    , count(*)
from ...
where ...
group by
      non_aggregating1 as x
    , non_aggregating2 as y
    , case when ... end as z


step2: remove alises

select
      non_aggregating1 as x
    , non_aggregating2 as y
    , case when ... end as z
    , max(x)
    , count(*)
from ...
where ...
group by
      non_aggregating1
    , non_aggregating2
    , case when ... end
Trent AdamsDevOps LeadAuthor Commented:
@PortletPaul
Your break down made a lot of sense. Not to mention it organizes the structure of the query a lot better and makes it more readable. I just keep forgetting that SQL does things in reverse logic (or reversed compared to average human thought).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
T-SQL

From novice to tech pro — start learning today.