Prev: Updating column values only if value is not null
Next: restricting access to sp_table_statistics2_rowset
From: LN on 26 Feb 2010 15:50
Thank you again.
L

"Plamen Ratchev" wrote:

> LN wrote:
> > I have to 2 questions for you.
> >
> > 1. what is A.rn = B.rn + 1 does?
>
> This predicate is used to join the current row to the prior date row.
>
> > 2. Is there a way to provide Total count for each LoanId. See desire
> > results below.
> >
>
> ;WITH Ranked
> AS (SELECT LoanId, EnterDate, ROW_NUMBER () OVER (PARTITION BY LoanId ORDER BY EnterDate) AS rn
> FROM #Temp
> )
> SELECT A.LoanId
> ,A.EnterDate
> ,CASE WHEN B.LoanId IS NULL
> THEN COUNT(*) OVER(PARTITION BY A.LoanId)
> END AS TotalCount
> ,COALESCE(DATEDIFF(HH, B.EnterDate, A.EnterDate), 0) AS diff
> ,AVG(DATEDIFF(HOUR, B.EnterDate, A.EnterDate)) OVER(PARTITION BY A.LoanId) AS avg_diff
> FROM Ranked AS A
> LEFT JOIN Ranked AS B
> ON A.LoanId = B.LoanId
> AND A.rn = B.rn + 1;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Updating column values only if value is not null
Next: restricting access to sp_table_statistics2_rowset