Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Formula to count the # of uncompleted issues at the end of each month.

NicoLavioJul 5 2019 — edited Jun 2 2020

Hello all,

I am stuck on the coding of formula and I need your talent.

Objective is to count the number of “Issues” that are in status “open” at the end of each month:

I have the following subject areas:

About issues:

“Issue_ID”

“Issue Open Date”

“Issue Completed date”

“Issue Status” (to make my example easier I will consider only closed issues)

I also have subject areas dedicated to time (named “all propose dates”)

“Date”

“Week”

“Year and Month”

My first idea was to develop a formula looking like

CASE

WHEN "Year and Month" = '2017 / 01' THEN

COUNT (DISTINCT

CASE WHEN “Issue Open Date” < timestamp '2017-02-01 00:00:01' AND “Issue Completed date” >= timestamp '2017-02-01 00:00:01' THEN “Issue_ID” ELSE NULL END)

WHEN "Year and Month" = '2017 / 02' THEN

                        …

but:

- it leaves to a very complicate formula (reviewing month by month).

- it’s not working at all. One if the issues is that each “Issue_ID” is counted only one time. In example, an Issue opened before Jan 2017 and closed in 2018 will appear only in the count of Jan 2017, but not in the next months.

I tried a second method with a formula looking like:

(CASE WHEN "Issue Open Date" <= TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH("Year and Month") * -(1) + 1, "Year and Month"))   /* Formula for last day of the month */

AND "Issue Completed date" >= TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH("Year and Month") * -(1) + 1, "Year and Month")))

THEN COUNT(DISTINCT "Issue_ID") END)

Here I have an error message because “Function Calendar Extract is called with an incompatible type”

Does one of you have any idea about a successful way to code this count?

Many thanks in advance for any suggestion or comment?

Nico

This post has been answered by Gianni Ceresa on Jul 5 2019
Jump to Answer

Comments