Query die dagen tellen tussen records a.h.v. datum

[Scheepers.A]

Ik wil graag hulp bij een query in SQL Server 2017.
Voorbeeld:

code:

Datum       Dagen        Tijdstip   Uitslag
14-5-2012   0            1           5.38
14-10-2013  518          2           5.39
15-6-2017   1858         3           3.68
30-9-2019   2695         4           7.50

Voorbeeld query tot nu toe:

SQL:

SELECT        A.PrikDatum, U.UitslagWaarde
FROM            Aanvraag AS A INNER JOIN
                         Uitslag AS U ON A.MonsterNummer = U.MonsterNummer
WHERE        (A.PatientNummer = '001001001') AND (U.TestCode = 'atrop')

Wat ik niet voor elkaar krijg is de dagen tellen.
De eerste datum begint altijd met 0 en de volgende datum wordt het verschil van datum opgeteld en zo verder met de volgende datums.
Daarnaast wil ik nog een kolom met tijdstip beginnend met 1, etc.

[ Voor 2% gewijzigd door RobIII op 26-11-2019 15:15 . Reden: Code tags toegevoegd ]

[n9iels]

Ik denk dat je op zoek bent naar DATEDIFF. Deze StackOverflow vraag is volgensmij vergelijkbaar met wat jij wilt: https://stackoverflow.com...-between-consecutive-rows

Daarnaast kun je met PARTITION_BY/OVER het volgensmij nog wat makkelijker/mooier maken. Daarmee zou je de INNER_JOIN weg kunnen halen als het goed is.

[ Voor 44% gewijzigd door n9iels op 26-11-2019 15:23 ]

[Ric00w]

Ik denk dat je op zoek bent naar iets als het volgende:

SQL:

SELECT        
Datum = A.PrikDatum
, Dagen = ISNULL(DATEDIFF(DAY, A.Prikdatum, LAG(A.Prikdatum) OVER (PARTITION BY A.Patientnummer ORDER BY A.Prikdatum),0)
, Tijdstip = Row_number() OVER (PARTITION BY A.Patientnummer ORDER BY A.Prikdatum)
, Uitslag = U.UitslagWaarde
FROM            Aanvraag AS A INNER JOIN
                         Uitslag AS U ON A.MonsterNummer = U.MonsterNummer
WHERE        (A.PatientNummer = '001001001') AND (U.TestCode = 'atrop')

Wellicht dat je 'U.Testcode' ook nog in de Partition moet overnemen, maar dat kan ik niet beoordelen.

[Scheepers.A]

Bovenstaande query was niet correct. Deze aangepast.

SQL:

SELECT  A.PrikDatum as Datum ,
ISNULL( DATEDIFF(DAY, A.Prikdatum, LAG(A.Prikdatum) OVER (PARTITION BY A.Patientnummer ORDER BY A.Prikdatum asc)),0) as Dagen ,
Row_number() OVER (PARTITION BY A.Patientnummer ORDER BY A.Prikdatum asc) as Tijdstip ,
 U.UitslagWaarde as Uitslag 
FROM            Aanvraag AS A INNER JOIN
                         Uitslag AS U ON A.MonsterNummer = U.MonsterNummer
WHERE        (A.PatientNummer = '001001001') AND (U.TestCode = 'atrop')

Alleen de dagen is niet correct.
Datum Dagen Tijdstip Uitslag
14-10-2013 0 1 5.39
15-6-2017 -1340 2 3.68
30-9-2019 -837 3 7.50

Moet zijn :
Datum Dagen Tijdstip Uitslag
14-10-2013 0 1 5.39
15-6-2017 1340 2 3.68
30-9-2019 2127 3 7.50

[ Voor 0% gewijzigd door Creepy op 27-11-2019 20:42 . Reden: Code tags toegevoegd ]

[Creepy]

Dus wat heb je zelf dan geprobeerd om dat nog goed te krijgen?

[Scheepers.A]

De regel van dagen heb ik aangepast, maar daar zit ook het probleem.
ISNULL( DATEDIFF(DAY, A.Prikdatum,MIN(A.Prikdatum) OVER (PARTITION BY A.Patientnummer ORDER BY A.Prikdatum asc)),0) as deltadagen ,

Alleen worden de deltadagen vanaf het 2e record als negatief(-) aangegeven.

[ Voor 53% gewijzigd door Scheepers.A op 28-11-2019 09:46 ]

[Luit]

Twee mogelijke oplossingen kan ik zo snel verzinnen:

Kijk eens naar het uitbreiden van je OVER()-functie met ROWS UNBOUNDED PRECEDING:
- https://docs.microsoft.co...pecifying-the-rows-clause

Of benader het probleem in twee stappen:
- bepaal de eerste datum binnen je patient met een MIN(prikdatum) OVER()
- bereken voor alle rows het verschil met die datum met een DATEDIFF

[ Voor 37% gewijzigd door Luit op 28-11-2019 08:14 ]

[Scheepers.A]

De MIN heb ik gedaan en dat werkt, maar de laatste problem is de negatieve dagen.

[Luit]

Als je het verschil wilt weten tussen 1 en 8 dan doe je: 8 - 1 = 7. Omgedraaid levert het op: 1 - 8 = -7.
De DATEDIFF-functie neemt het verschil tussen twee waarden (twee dates). Hier geldt dus hetzelfde. Verwissel de input en je zult het juiste resultaat krijgen.

[vdws]

My 2 cents...

SQL:

select t2.PrikDatum
,      t2.tijdstip
,      DATEEDIFF(t2.PrikDatum,(select min(t1.PrikDatum) from Aanvraag t1 where t1.Patientnummer = t2.Patientnummer)) dagen
from Aanvraag t2
where t2.Patientnummer = '001001001'
order by t2.PrikDatum
;

[ Voor 5% gewijzigd door vdws op 03-12-2019 16:26 ]

[Scheepers.A]

Ik krijg toch nog een foutmelding als ik de query draai.

De query ziet er als volgt uit:
SELECT DISTINCT FORMAT(A.PrikDatum, 'dd-MM-yyyy') as Datum ,
ISNULL( DATEDIFF(DAY, MIN(A.Prikdatum) OVER (PARTITION BY A.Patientnummer ORDER BY A.Prikdatum ),A.Prikdatum),0) as deltadagen ,
Row_number() OVER (PARTITION BY A.Patientnummer ORDER BY A.Prikdatum asc) as datapunt ,
U.UitslagWaarde
FROM Aanvraag AS A INNER JOIN
Uitslag AS U ON A.MonsterNummer = U.MonsterNummer
WHERE A.PatientNummer = '001001001' AND U.TestCode = 'atryp'

De foutmelding is:
SQL text cannot be represented in the grid pane and diagram pane.

Wat is er fout in de query?

[gekkie]

Klinkt meer als een fout van de (gui) tool die je gebruikt om de query uit te voeren en het resultaat er van te bekijken, dan een fout in de query zelf.

[doogster]

dit is ook een mogelijkheid:

Voorbeeld tabel

patient	datum	uitslag
1	2012-05-14	5.38
1	2013-10-14	5.39
1	2017-06-15	3.68
1	2019-09-30	7.50
2	2011-01-03	1.34
2	2012-01-03	1.56

Query

SQL:

SELECT patient, 
       datum, 
       SUM(ISNULL(DATEDIFF(day, vorige_datum, datum), 0)) OVER(PARTITION BY patient
       ORDER BY datum) AS dagen, 
       tijdstip, 
       uitslag
FROM
(
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY patient
           ORDER BY datum) AS tijdstip, 
           LAG(datum, 1) OVER(PARTITION BY patient
           ORDER BY datum) AS vorige_datum
    FROM dbo.uitslagen
) AS dataset;

Resultaat

patient	datum	dagen	tijdstip	uitslag
1	2012-05-14	0	1	5.38
1	2013-10-14	518	2	5.39
1	2017-06-15	1858	3	3.68
1	2019-09-30	2695	4	7.50
2	2011-01-03	0	1	1.34
2	2012-01-03	365	2	1.56