Skip to content
Advertisement

Count distinct per fiscal year and display all dates in query result

DB-Fiddle:

Expected Result:


In the above result I want to list all order dates and count the number of customers distinct per fiscal year.
The fiscal year starts two months after the calender year and therefore goes from March to February.
(e.g. from 2020-03 til 2021-02).

For example Customer_01 appears the first time on 2020-03-18 within the fiscal year 2020-03 til 2021-02.
Therefore, this order_date gets assigned 1 to it.
If the customer appears again within the fiscal year the next order_date will get assigned 0 to it.


So far I have used this query:

It works perfectly for the calender year.
However, I have no clue how I can apply the two months shift from the fiscal year to it.
Do you have any idea?

Advertisement

Answer

I have updated your query as below:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement