CODEWITHSUNDEEP
sqloracle-database
Matt
Matt

Reputation: 174

Historical Backlog Using Oracle SQL

I'm trying to develop a query that will show the historical backlog, month by month for applications my organization processes.

The table I'm querying has each row represent an application.

This is fairly simple for a single month by taking into account when the application was created and when it was closed. I can use the code below.

SELECT COUNT(*)
FROM APPLICATIONS
WHERE TRUNC(CRTE_DTE,'mm') <= TO_DATE('09/01/2016','mm/dd/yyyy')
AND (TRUNC(CLOS_DTE,'mm')> TO_DATE('09/01/2016','mm/dd/yyyy') OR CLOS_DTE IS
NULL)
;

What I would like to do, is create something like this but grouped by month.

i.e. with output that looks similar to:

etc.

For my purposes, I'm defining backlog as any application that hadn't yet been closed by the time that month had ended while excluding any applications opened after that month had ended.

I don't have access to run PL/SQL, but if that's a requirement please let me know.

First time posting here, so if I didn't explain something correctly, let me know!

Upvotes: 0

Views: 370

Answers (1)

Evgeniy Kiskin
Evgeniy Kiskin

Reputation: 199

Try something like this:

SELECT d.dt, COUNT(*)
FROM 
   APPLICATIONS a, 
   (SELECT ADD_MONTHS(:DATE1, ROWNUM-1) DT FROM DUAL CONNECT BY ROWNUM<=MONTHS_BETWEEN(:DATE2, :DATE1)+1) d
WHERE TRUNC(a.CRTE_DTE,'mm') <= d.dt
  AND (TRUNC(a.CLOS_DTE,'mm')> d.dt OR CLOS_DTE IS NULL) 
GROUP BY D.DT

:date1 - start date, for example 08/01/2016, :date2 - end date, for example 10/01/2016, in subquery we get row for each month between :date1 and :date2, and then count backlog for them.

Upvotes: 1

Related Questions