I have been trying to solve the below case on SQL to take it to crystal reports so I can create a dynamic crosstab with the data. Basically I have the below set of data
This shows 1 Order going through different phases, each phase has a date and states (i.e. Phase 1-6 is open and 7-9 is closed). For the sake of testing I only included 1 document but this will be done on thousands of documents. The end result should be this table:
Now using my regular query things are fine until Phase 3, because the document has no data between March and June yet I need to display it in April and May since it's technically still in an open phase (same applies between June and November and after November it has to be removed because it's closed).
The closest I got was to use a separate calendar dimension and use a cross join to duplicate the data but when I tried to turn it into rows again I ran into the issue of not being able to filter out the unnecessary rows for each phase (my solution shows the document in each month for each stage so 12*9 and I can't flag the unneeded rows to remove).
Any help would be appreciated!
EDIT: Query(HANA SQL) below
SELECT
T0."YearMonth",
T1."OrderNum",
T1."Phase 01",
T1."Phase 02",
T1."Phase 03",
T1."Phase 04",
T1."Phase 05",
T1."Phase 06",
T1."Phase 07",
T1."Phase 08",
T1."Phase 09",
CASE WHEN T0."YearMonth" between T1."OpenDate" and ifnull(T1."CloseDate",'20990101') THEN 'O' ELSE 'C' END AS "Status",
T1."Value"
FROM "YearMonthDIM" T0
CROSS JOIN (SELECT * FROM "Orders" WHERE "OrderNum" = 1) T1
WHERE "YEAR" = 2020
ORDER BY T0."YearMonth"
OpenDate is basically the first stage that this order had date, and close is the last date it was closed at.
On the above data this query would correctly show Order 1 as open until it was closed in November correctly, however if I try to switch back to rows in order to get each stage in a separate row it will show 12 lines for each stage regardless and I am yet to figure out how to flag and remove the unnecessary ones.
