Showing Open and closed work in each month

[Abhi_WP]

Hi,

I have a request from user to show a  graph which will have Year Month and for each Year Month I need to show how many work orders were opened and also show how many were closed (irrespective of when they were opened). The field I have in my table is Raised Date, Closed Date and Work order Status for Closed and Open. How can I achieve it ?

select
wod.word_no,
wod.rsd_dt,
wod.clsd_dt,
wod.word_stat_cde,
wod.word_cmptd_cde,
from
ndwu.work_order_dim wod,

[Vegar]

What if you make a date link table to your master calendar?

date_link:

Load

wod.word_no,

wod.clsd_dt as dt,

'Closed' as dt_type

Resident data;

Comcatenate Load

wod.word_no,

wod.rsd_dt as dt,

'Raised' as dt_type

Resident data;

Create a master calendar linked to the new dt field.

Then you can use yearmonth from your master calendar and  dt_type as a dimension to distinguish between raised and closed dates. 

[Vegar · Accepted solution]

This old blog post, Canonical Date, by @hic could also be a good read in your situation.

[Abhi_WP]

Thank you very much for providing the link to the solution. It is very well explained there.