Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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;
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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)
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)
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
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.
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.
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?
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.
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 |
+------------+---------------+--------+
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.
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
Expected Output
Expense Type Total Days Past
Mileage 15 30
Activities 7 60
Tolls 10 90
I have removed the date restriction from the where clause, otherwise very similar to my previous post:
[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.
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
[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.
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.
:)
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial