Find second last max date

Transact-SQL
1

i have this table - end of month

Date ProductKey CustomerKey
...
...

31/08/2015 1 2
31/10/2015 1 2
30/11/2015 1 2

i want to get for all month the last max month

i mean
Date ProductKey CustomerKey Date_Last_month
31/08/2015 1 2 NULL
31/10/2015 1 2 31/08/2015
30/11/2015 1 2 31/10/2015

2

if you are using SQL Server 2012 or later you can use the LAG() or LEAD() function

https://msdn.microsoft.com/en-us/library/hh231256.aspx
https://msdn.microsoft.com/en-us/library/hh213125.aspx

3

thanks it's help.

but if i want find the last row with some conditions ,so how can i do it in the query?
i mean find the last row when column_a>0 or b.coulmn_b>0

this is the basic query:

SELECT a.* ,LAG(LoadingDate) OVER (PARTITION BY ProductKey,GrandCustomerKey ORDER BY LoadingDate)
FROM My_Table

4

Don't quite get what you want here. Example ?

5 
Date                         ProductKey CustomerKey Column_A Column_B
...
...

31/08/2015                    1                  2               1             1
31/09/2015                    1                  2               0             0
31/10/2015                    1                  2               0             0
30/11/2015                    1                  2               0             0


Date                         ProductKey CustomerKey Column_A Column_B  Date_Last
31/08/2015                    1                  2               1             0              NULL
31/09/2015                    1                  2               0             0            31/08/2015
31/10/2015                    1                  2               1             1           31/08/2015
30/11/2015                    1                  2               0             0           31/10/2015

we can see that at 31/10/2015 the Date_Last is 31/08/2015 because his Column_A >0 or Column_B>0
and at 31/09/2015 it is not like that

is it understood?

6

There might be a clever way to use the order by clause in the LAG function to get the result you want, but the light bulb is not going on in my mind. You can always use a subquery like shown below which will always work, but is not as clever and not as efficient as using the LAG function.

SELECT a.* 
	,b.LoadingDate
FROM
	My_Table a
	OUTER APPLY
	(
		SELECT TOP (1)
			b.LoadingDate
		FROM
			My_Table b
		WHERE
			a.ProductKey = b.ProductKey
			AND a.GrandCustomerKey = b.GrandCustomerKey
			AND ( a.column_a > 0 OR a.column_b > 0 )
			AND a.Date < b.Date
		ORDER BY
			b.Date DESC
	) b;