Advertisements
I can generate a table with the following structure – in actuality, this is more than 50,000 rows long – but below example is a simplified representation:
Row Account Number Date
1 1001 2011-01-10
2 1001 2011-02-01
3 1001 2011-02-20
4 1001 2011-02-22
5 2001 2011-04-11
6 2001 2012-01-01
7 2001 2012-01-30
8 2001 2012-02-09
I would like to select the first row and next row for each account number where the date difference is at least 30-days.
So in the example above, I would like to select Rows #1 and #3 for Account Number 1001, Rows #5 and #6 for Account Number 2001.
How to generate this (in snowflake SQL)?
>Solution :
I’m trying to imagine a way of pulling this that doesn’t have an interim result set with a slight cross-join involved, but I’m not sure it’s possible. One option:
SELECT t1.row, t1.accountnumber, t1.date as firstdate, t2.row as 30dayplusrow, t2.date as 30dayplusdate
FROM yourtable t1
INNER JOIN yourtable t2
ON t1.accountnumber = t2.accountnumber
AND DATEADD(day, 2, t1.date) >= t2.date
QUALIFY ROW_NUMBER() OVER (PARTITION BY accountnumber ORDER BY firstdate, 30dayplusdate) = 1