Skip to main content
Database Administrators

Return to Answer

Question has been corrected.
Source Link
ypercubeᵀᴹ
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

A combination of OUTER APPLY and ROW_NUMBER seems to do the trick:

WITH CTE AS
(
    SELECT  *,
            Tot_Cnt = ROW_NUMBER() OVER(ORDER BY dt)
    FROM #x
)
SELECT  A.dt,
        A.[Name],
        B.Unq_Cnt,
        A.Tot_Cnt,
        Ratio_of_Cnts = CONVERT(NUMERIC(10,4),B.Unq_Cnt)/A.Tot_Cnt 
FROM CTE A
OUTER APPLY (SELECT Unq_Cnt = COUNT(DISTINCT [Name]) 
             FROM CTE
             WHERE dt <= A.dt) B
;

Here is a demo of this.

The last 2 rows of your sample seems to be getting wrong results though, since it should be 5 Unq_Cnt

A combination of OUTER APPLY and ROW_NUMBER seems to do the trick:

WITH CTE AS
(
    SELECT  *,
            Tot_Cnt = ROW_NUMBER() OVER(ORDER BY dt)
    FROM #x
)
SELECT  A.dt,
        A.[Name],
        B.Unq_Cnt,
        A.Tot_Cnt,
        Ratio_of_Cnts = CONVERT(NUMERIC(10,4),B.Unq_Cnt)/A.Tot_Cnt 
FROM CTE A
OUTER APPLY (SELECT Unq_Cnt = COUNT(DISTINCT [Name]) 
             FROM CTE
             WHERE dt <= A.dt) B
;

Here is a demo of this.

The last 2 rows of your sample seems to be getting wrong results though, since it should be 5 Unq_Cnt

A combination of OUTER APPLY and ROW_NUMBER seems to do the trick:

WITH CTE AS
(
    SELECT  *,
            Tot_Cnt = ROW_NUMBER() OVER(ORDER BY dt)
    FROM #x
)
SELECT  A.dt,
        A.[Name],
        B.Unq_Cnt,
        A.Tot_Cnt,
        Ratio_of_Cnts = CONVERT(NUMERIC(10,4),B.Unq_Cnt)/A.Tot_Cnt 
FROM CTE A
OUTER APPLY (SELECT Unq_Cnt = COUNT(DISTINCT [Name]) 
             FROM CTE
             WHERE dt <= A.dt) B
;

Here is a demo of this.

added 6 characters in body
Source Link
Lamak
Lamak
  • 2.6k
  • 1
  • 24
  • 30

A combination of OUTER APPLY and ROW_NUMBER seems to do the trick:

WITH CTE AS
(
    SELECT  *,
            Tot_Cnt = ROW_NUMBER() OVER(ORDER BY dt)
    FROM #x
)
SELECT  A.dt,
        A.[Name],
        B.Unq_Cnt,
        A.Tot_Cnt,
        Ratio_of_Cnts = CONVERT(NUMERIC(10,4),B.Unq_Cnt)/A.Tot_Cnt Ratio_of_Cnts
FROM CTE A
OUTER APPLY (SELECT Unq_Cnt = COUNT(DISTINCT [Name]) Unq_Cnt
             FROM CTE
             WHERE dt <= A.dt) B
;

Here is a demo of this.

The last 2 rows of your sample seems to be getting wrong results though, since it should be 5 Unq_Cnt

A combination of OUTER APPLY and ROW_NUMBER seems to do the trick:

WITH CTE AS
(
    SELECT  *,
            Tot_Cnt = ROW_NUMBER() OVER(ORDER BY dt)
    FROM #x
)
SELECT  A.dt,
        A.[Name],
        B.Unq_Cnt,
        A.Tot_Cnt,
        CONVERT(NUMERIC(10,4),B.Unq_Cnt)/A.Tot_Cnt Ratio_of_Cnts
FROM CTE A
OUTER APPLY (SELECT COUNT(DISTINCT [Name]) Unq_Cnt
             FROM CTE
             WHERE dt <= A.dt) B
;

Here is a demo of this.

The last 2 rows of your sample seems to be getting wrong results though, since it should be 5 Unq_Cnt

A combination of OUTER APPLY and ROW_NUMBER seems to do the trick:

WITH CTE AS
(
    SELECT  *,
            Tot_Cnt = ROW_NUMBER() OVER(ORDER BY dt)
    FROM #x
)
SELECT  A.dt,
        A.[Name],
        B.Unq_Cnt,
        A.Tot_Cnt,
        Ratio_of_Cnts = CONVERT(NUMERIC(10,4),B.Unq_Cnt)/A.Tot_Cnt 
FROM CTE A
OUTER APPLY (SELECT Unq_Cnt = COUNT(DISTINCT [Name]) 
             FROM CTE
             WHERE dt <= A.dt) B
;

Here is a demo of this.

The last 2 rows of your sample seems to be getting wrong results though, since it should be 5 Unq_Cnt

Source Link
Lamak
Lamak
  • 2.6k
  • 1
  • 24
  • 30

A combination of OUTER APPLY and ROW_NUMBER seems to do the trick:

WITH CTE AS
(
    SELECT  *,
            Tot_Cnt = ROW_NUMBER() OVER(ORDER BY dt)
    FROM #x
)
SELECT  A.dt,
        A.[Name],
        B.Unq_Cnt,
        A.Tot_Cnt,
        CONVERT(NUMERIC(10,4),B.Unq_Cnt)/A.Tot_Cnt Ratio_of_Cnts
FROM CTE A
OUTER APPLY (SELECT COUNT(DISTINCT [Name]) Unq_Cnt
             FROM CTE
             WHERE dt <= A.dt) B
;

Here is a demo of this.

The last 2 rows of your sample seems to be getting wrong results though, since it should be 5 Unq_Cnt