Is there an easy way to calculate a datediff of date/time field(effective_date based on the change of another field (client_id) changes by bed_id ?
AKA when status_code_id is 17 for a row, then the next row is a different client_id, calculate the datediff of effective date from these 2 rows, but only when the bed_id is the same.
This sql gives me the complete dataset.
(
select client_id,effective_date,bed_id,status_code_id from census_item where bed_id is not null and deleted = 'n'
)
union
(
select client_id,effective_date,bed_id,status_code_id from census_item
where status_code_id = '17' and deleted = 'n'
)
And here is an example of a small dataset:
AKA when status_code_id is 17 for a row, then the next row is a different client_id, calculate the datediff of effective date from these 2 rows, but only when the bed_id is the same.
This sql gives me the complete dataset.
(
select client_id,effective_date,bed_id,status_code_id from census_item where bed_id is not null and deleted = 'n'
)
union
(
select client_id,effective_date,bed_id,status_code_id from census_item
where status_code_id = '17' and deleted = 'n'
)
And here is an example of a small dataset: