I'm working on a way to average elapsed days between two dates on a customer level in SQL Server.
SELECT
CUSTOMER,
VISIT_ID,
DAYIN_DATE,
DAYOUT_DATE,
RANK () OVER (PARTITION BY CUSTOMER ORDER BY VISIT_ID DESC) AS RANKING,
LAG(DAYOUT_DATE, 1)
OVER (PARTITION BY CUSTOMER ORDER BY VISIT_ID DESC) AS DATEOUT_DT,
LEAD(DAYIN_DATE, 1)
OVER (PARTITION BY CUSTOMER ORDER BY VISIT_ID DESC) AS DATEIN_DT,
(DATEIN_DT - DATEOUT_DT) AS LATENCY
FROM VISIT_TABLE
GROUP BY 1,2,3,4;
This code takes the customer and visit(unique id), then ranks them by the DAYIN_DATE/DAYOUT_DATE. I've written a Lag and Lead function to help calculate the latency but, when averaged it doesn't average on a customer level.
What am I missing?
Aucun commentaire:
Enregistrer un commentaire