I've a Table with 600 millons of rows (Account, Ammount, DateBegin and DateStart)
I need check what the table rows was in full sequence ( row n ->DateEnd + 1 = row n+1 ->DateStart )
Acc1 100.00 2012-01-01 2012-01-05
Acc2 200.00 2012-01-06 2012-01-08
My query is :
FROM MyDB.MyTable T1
LEFT OUTER JOIN MyDB.MyTable T2
ON T1.Account = T2.Account
AND T1.DateEnd +1 = T2.DateStart
WHERE T1.DateEnd <> 2001231
AND T2.Account IS NULL
It run very slow. Any idea for a best performance ?
You want to return the rows with gaps?
SELECT Account, DateStart, DateEnd,
over (partition by Account
order by DateStart
rows between 1 following and following) - DateEnd as Gap
qualify Gap > 1
In TD 13.0 i need add Unbounded in line 5
rows between 1 following and unbounded following) - DateEnd as Gap
The runtime was 20 minutes instead of 6 hours !!!
Thaks a lot.