CODEWITHSUNDEEP
oracle-databasedatecount
cmomah
cmomah

Reputation: 315

How can I get the Year to Date (YTD) count of data using Oracle?

How can I get the Year to Date (YTD) count of a certain data using Oracle query?

Assume that we are interested in summing the total number of vouchers filed since the beginning of the current year.

This is the query I came up with

WITH cteDAYSCOUNT AS (SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'DDD')) 
FROM dual)
SELECT COUNT(VOUCHER_FILED_DATE), SYSDATE AS "AS OF" FROM CLAIMS 
WHERE VOUCHER_FILED_DATE > sysdate - cteDAYSCOUNT;

This part of it returns the number of days since the beginning of the year

WITH cteDAYSCOUNT AS (SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'DDD')) FROM dual)

And this part attempts to use the sysdate - {number of days} to calculate the count

SELECT COUNT(VOUCHER_FILED_DATE), SYSDATE AS "AS OF" 
FROM CONTINUED_CLAIMS WHERE VOUCHER_FILED_DATE > sysdate - cteDAYSCOUNT;

But the problem is the although cteDAYSCOUNT holds the number of days since the year starts, it is not being recognized as a number, so it's throws an error

Is there a better query for calculating YTD count or a fix of the above query?

Upvotes: 0

Views: 325

Answers (1)

Littlefoot
Littlefoot

Reputation: 143163

I'm not sure I'm following you.

Query you posted is incomplete; CTE lacks in column name, while FROM clause misses join with the CTE. Therefore, your query can't work at all.

If it is fixed, then:

SQL> WITH
  2  ctedayscount (ctedayscount) AS
  3    -- this is yours
  4    (SELECT TO_NUMBER (TO_CHAR (SYSDATE, 'DDD'))  FROM DUAL),
  5  claims (voucher_filed_date) AS
  6    -- this is my CTE, so that query would actually return something
  7    (SELECT DATE '2021-01-15' FROM DUAL)
  8    -- final SELECT; you're missing JOIN with CTEDAYSCOUNT
  9  SELECT COUNT (voucher_filed_date), SYSDATE AS "AS OF"
 10    FROM claims CROSS JOIN ctedayscount
 11   WHERE voucher_filed_date > SYSDATE - ctedayscount;

COUNT(VOUCHER_FILED_DATE) AS OF
------------------------- ----------
                        1 09.02.2021

SQL>

So, it works.

Furthermore, you said:

But the problem is the although cteDAYSCOUNT holds the number of days since the year starts, it is not being recognized as a number, so it's throws an error

How do you know it isn't a NUMBER? Which error is it? It is difficult to debug an unknown error. Could it, perhaps, be that CLAIMS table's voucher_filed_date datatype is something different than DATE (such as VARCHAR2) and contains data which Oracle can't implicitly convert to DATE so WHERE clause (my line #11) fails?

Or is the main problem the fact that you just missed to join CTEDAYSCOUNT with CLAIMS (which I already mentioned)?

Upvotes: 1

Related Questions