I also show how to obtain the Nth value where I’ve specified N as 4) (The commented out code shows how to obtain the equivalent of the SQL 2012 FIRST_VALUE and LAST_VALUE. ,MAX(CASE WHEN rn=4 THEN balance END) OVER (PARTITION BY customer) AS fourth_value ,MAX(CASE WHEN rn=partitioncount THEN balance END) OVER (PARTITION BY customer) AS last_value ,MAX(CASE WHEN rn=1 THEN balance END) OVER (PARTITION BY customer) AS first_value ,CASE WHEN rn%2=1 THEN MAX(CASE WHEN rn%2=0 THEN balance END) OVER (PARTITION BY customer,rnplus1div2) ELSE MAX(CASE WHEN rn%2=1 THEN balance END) OVER (PARTITION BY customer,rndiv2) ,CASE WHEN rn%2=1 THEN MAX(CASE WHEN rn%2=0 THEN balance END) OVER (PARTITION BY customer,rndiv2) ELSE MAX(CASE WHEN rn%2=1 THEN balance END) OVER (PARTITION BY customer,rnplus1div2) ,COUNT(*) OVER (PARTITION BY customer) partitioncount ,(ROW_NUMBER() OVER (PARTITION BY customer ORDER BY balancedate) + 1)/2 rnplus1div2 ,(ROW_NUMBER() OVER (PARTITION BY customer ORDER BY balancedate))/2 rndiv2 ROW_NUMBER() OVER (PARTITION BY customer ORDER BY balancedate) rn Here’s a solution that references the source table once: Most pre-2012 solutions resort to using self joins to identify the lagging or leading row. Unfortunately, these functions don’t exist prior to the 2012 version. The LAG and LEAD analytical functions in SQL Server 2012 are very useful.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |