Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
This old blog post, Canonical Date, by @hic could also be a good read in your situation.
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.
This old blog post, Canonical Date, by @hic could also be a good read in your situation.
Thank you very much for providing the link to the solution. It is very well explained there.