(((DATE - Lag(DATE,1) over (partition by ID,FAMILY order by INVOICE))))
DATE
ID
FAMILY
INVOICE
use a DateDIFF to calculate the number of days between the dates!
Agreed on DATEDIFF(). Also, adding a *1 to a field will force it to a number:
Field*1
Field
or
(CASE WHEN Field = 'Cat' THEN ID END)*1
What would be the syntax with datediff?
Since its the same date column
DATEDIFF(DATE, LAG(DATE) -1)
I cant use the window function with datediff