Início / dba / Perguntas / 270142
Accepted
Juan Velez
Asked: 2020-07-01 07:43:21 +0800 CST

Derivar períodos de datas de datas de início e término na tabela do SQL Server

  • 772

Estou usando o SQL Server 2016

Eu tenho uma tabela que contém 1 linha por mês que um paciente é atribuído a um determinado provedor.

Um paciente pode ser atribuído a vários provedores durante o ano.

Como posso derivar intervalos de datas (data de início e data de término) para representar a hora em que um paciente foi atribuído a cada provedor.

Minha tabela está assim:

+----------+---------------+------------+-----------+
| Provider | Patient       | StartDate  | EndDate  | 
+----------+---------------+------------+-----------+
| 1922157  | 12345         | 20191201  | 20191231 | 
| 1904176  | 12345         | 20191101  | 20191201 |
| 1904176  | 12345         | 20191001  | 20191101 |
| 1904176  | 12345         | 20190901  | 20191001 | 
| 1904176  | 12345         | 20190801  | 20190901 |
| 1904176  | 12345         | 20190701  | 20190801 |
| 1904176  | 12345         | 20190601  | 20190701 |
| 1904176  | 12345         | 20190501  | 20190601 |
| 1904176  | 12345         | 20190401  | 20190501 |
| 1904176  | 12345         | 20190301  | 20190401 |
| 1904176  | 12345         | 20190201  | 20190301 |
| 1922157  | 12345         | 20190101  | 20190201 |
| 1922157  | 56789         | 20190101  | 20190201 |
+----------+---------------+------------+-----------+

Neste caso, o paciente 12345 foi atribuído a 2 provedores diferentes. Um para 2 meses, janeiro e dezembro e outro para o resto do ano (10 meses) de fevereiro a novembro. O paciente 56789 foi atribuído apenas a 1 provedor (1922157) por 1 mês (em dezembro).

Estou tentando fazer com que minha saída se pareça com a tabela abaixo, mas estou tendo problemas, acho que porque o paciente é atribuído ao mesmo pcp durante 2 épocas diferentes do ano. Tentei usar a função lag, mas só obtenho os resultados corretos em alguns casos, mas não em todos, como neste caso em particular.

+----------+---------------+------------+-----------+
| Provider | Patient       | StartDate  | EndDate  | 
+----------+---------------+------------+-----------+
| 1922157  | 12345         | 20190101  | 20190201  | 
| 1904176  | 12345         | 20190201  | 20191201  | 
| 1922157  | 12345         | 20191201  | 20191231  | 
| 1922157  | 56789         | 20191201  | 20191231  |
+----------+---------------+------------+-----------+

Atualização: estava fazendo mais algumas pesquisas e me deparei com o seguinte post:

https://stackoverflow.com/questions/35900765/ms-sql-combine-date-rows-into-start-end-date

Acabei de encaixar minha tabela no código na resposta para a pergunta acima e testei alguns dos meus casos e parece que pode fazer o trabalho. Infelizmente, minha tabela base tem 140 mil linhas de datas que precisarão ser calculadas, então não tenho certeza de quanto tempo levará para ser executada. Está funcionando agora por 6 minutos, vou postar de volta com os resultados.

sql-server date-math

1 respostas

  1. Best Answer

    Acho que entendi o que você está tentando fazer. Você está tentando obter a data de início e a data de término de um paciente em um provedor, desde que não haja intervalo entre as datas de início e término dos períodos. Criei uma tabela de teste com os dados amostrados.

    Create table test (Provider int, Patient int, startdate date, enddate date)
insert into test (Provider, Patient, StartDate, EndDate)

SELECT * FROM 
(SELECT 1922157 as Provider  , 12345 as Patient         , '2019-12-01' as StartDate , '2019-12-31' as EndDate
union all SELECT 1904176  , 12345         , '2019-11-01'  , '2019-12-01' 
union all SELECT 1904176  , 12345         , '2019-10-01'  , '2019-11-01' 
union all SELECT 1904176  , 12345         , '2019-09-01'  , '2019-10-01' 
union all SELECT 1904176  , 12345         , '2019-08-01'  , '2019-09-01' 
union all SELECT 1904176  , 12345         , '2019-07-01'  , '2019-08-01' 
union all SELECT 1904176  , 12345         , '2019-06-01'  , '2019-07-01' 
union all SELECT 1904176  , 12345         , '2019-05-01'  , '2019-06-01' 
union all SELECT 1904176  , 12345         , '2019-04-01'  , '2019-05-01' 
union all SELECT 1904176  , 12345         , '2019-03-01'  , '2019-04-01' 
union all SELECT 1904176  , 12345         , '2019-02-01'  , '2019-03-01' 
union all SELECT 1922157  , 12345         , '2019-01-01'  , '2019-02-01' 
union all SELECT 1922157  , 56789         , '2019-01-01'  , '2019-02-01' )t

    A ideia é começar por ordenar os dados e tentar obter os que coincidem com as datas de início e de fim, de forma a detectar um furo nas datas. Eu faço isso com a função "ROW_NUMBER". Em seguida, encontro todas as linhas que correspondem e pego o primeiro StartDate e max EndDate para aqueles que correspondem e, em seguida, adiciono todas as linhas que estão "sozinhas" e não têm correspondência.

    Eu acho que funciona com os dados que você forneceu. Não consegui testar com outros dados. A recursividade é outra opção para encontrar as datas Min/Max de valores diferentes, mas não usei recursividade neste caso. (fique à vontade para dar nomes melhores, fui um pouco rápido)

    ;With RowsWithNum AS
(
SELECT Provider, Patient, StartDate, EndDate, ROW_NUMBER() OVER (ORDER BY Provider, patient, StartDate) as RowNum
FROM test
)
,BeforeAndAfterDates AS
(
SELECT a.Provider, a.Patient, a.StartDate, a.RowNum, a.EndDate, b.StartDate EndStartDate, DATEPART(DAYOFYEAR, b.StartDate)-DATEPART(DAYOFYEAR,a.EndDate) as DateDiffInDays, b.EndDate as EndEndDate, b.RowNum as EndRowNum
FROM RowsWithNum a
LEFT JOIN RowsWithNum b ON b.Provider=a.Provider and b.Patient=a.Patient and b.StartDate=a.EndDate
)
SELECT Provider, Patient, Min(StartDate) as StartDate, Max(EndEndDate) as EndDate, Min(RowNum) as RowNum
FROM BeforeAndAfterDates
WHERE DateDiffInDays=0
GROUP BY Provider, Patient
UNION
SELECT a.Provider, a.Patient, a.StartDate, a.EndDate, a.RowNum
FROM BeforeAndAfterDates a
LEFT JOIN BeforeAndAfterDates b ON b.EndEndDate=a.enddate
WHERE a.DateDiffInDays IS NULL AND b.RowNum IS NULL

    E aqui está o meu resultado.

    resultado

    • 0

relate perguntas