Matching multiple fields into time interval

[Anonymous]

Hello,

Our lab has several procedures that we follow before billing a test. I need to report on the volume flowing through each step weekly.

The data model is simple (although the dates below may be datetimes):

load * inline [

CaseID,kit_received, test_reported, data_entry_start, data_entry_end, ptp_start, ptp_end, auth_start, auth_end, billed_date, claim_submit_date

1,'01-01-2018', '01-15-2018', '01-01-2018', '01-02-2018', '01-02-2018', '01-06-2018', '01-06-2018', '01-11-2018', '01-15-2018', '01-15-2018'

2,'01-02-2018', '01-15-2018', '01-02-2018', '01-02-2018', '01-02-2018', '01-09-2018', '01-10-2018', '01-11-2018', '01-15-2018', '01-15-2018'

3,'01-02-2018', '01-09-2018', '01-03-2018', '01-03-2018', '01-04-2018', '01-06-2018', '01-06-2018', '01-13-2018', '01-14-2018', '01-15-2018'

4,'01-03-2018', '01-09-2018', '01-03-2018', '01-04-2018', '01-05-2018', '01-08-2018', '01-10-2018', '01-12-2018', '01-12-2018', '01-12-2018'

]

What I would want to show is a table like this:

or this:

However, I don't know how to create an independent time dimension and to match / interval match different fields to it. Any advice would be appreciated.

[eduardo_dimperio]

Hi, Maybe using a crosstable:

BASE:

load * inline [

CaseID,kit_received, test_reported, data_entry_start, data_entry_end, ptp_start, ptp_end, auth_start, auth_end, billed_date, claim_submit_date

1,'01-01-2018', '01-15-2018', '01-01-2018', '01-02-2018', '01-02-2018', '01-06-2018', '01-06-2018', '01-11-2018', '01-15-2018', '01-15-2018'

2,'01-02-2018', '01-15-2018', '01-02-2018', '01-02-2018', '01-02-2018', '01-09-2018', '01-10-2018', '01-11-2018', '01-15-2018', '01-15-2018'

3,'01-02-2018', '01-09-2018', '01-03-2018', '01-03-2018', '01-04-2018', '01-06-2018', '01-06-2018', '01-13-2018', '01-14-2018', '01-15-2018'

4,'01-03-2018', '01-09-2018', '01-03-2018', '01-04-2018', '01-05-2018', '01-08-2018', '01-10-2018', '01-12-2018', '01-12-2018', '01-12-2018'

];

Crosstable (FIELDS, DATE) TEST: LOAD * Resident BASE;

DROP TABLE BASE;

[MarcoWedel]

Hi,

maybe helpful:

Canonical Date

regards

Marco