Get count of historical values based on current value

Question

I want to identify how long the users were already in their CURRENT rank... I worked over subqueries and having but it always gave me back all ranks counted which the user were in before the parameter.

     DECLARE @T As Table
(
    [User] char(1),
    [Date] date,
    [Rank] varchar(10)
)

INSERT INTO @T VALUES
('A', '2016-10-01', 'Chairman'),
('A', '2016-11-01', 'Bee'),
('A', '2016-12-01', 'Chairman'),
('A', '2017-01-01', 'Chairman'),
('B', '2016-10-01', 'Artist'),
('B', '2016-11-01', 'Artist'),
('B', '2016-12-01', 'Artist'),
('B', '2017-01-01', 'Artist')

Which SQL statement delivers me for the parameter 2016.12.01 the following result:

UserId - CountCurrentRankLength
A - 1 
B - 3

-- Explanation of A: because the sequence is broken on november (when parameter is december)

2 new issues arose (i have changed the original question):

1. It seems to solve my original issue and will work in 98% of the cases, but as mentioned by Gordon Linoff the perfect solution would be to only trace back the time which the user was before he could have been already in a rank, then beeing upgraded and downgraded.

2. Now i have tried to put it into my table function but it seems as it doesnt like the WITH statement:

But the server doesnt like it as it seems not to be allowed to be used within the function:

Msg 156, Level 15, State 1, Procedure fn_getRankUserAffiliation, Line 14 [Batch Start Line 7] Incorrect syntax near the keyword 'WITH'. Msg 319, Level 15, State 1, Procedure fn_getRankUserAffiliation, Line 14 [Batch Start Line 7] Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

ALTER function [dbo].[fn_getRankUserAffiliation](@StartDate date)
            returns @RankUserAffiliation table(
                UserID NVARCHAR(50),
                MonthsInRank int
                    )
                    as
                    begin
                        declare @CurrentMonth date
                        select @CurrentMonth = dbo.fn_getFirstOfMonth(@StartDate)
                        insert into @RankUserAffiliation
                    ;WITH CTE
                        SELECT *, ROW_NUMBER() OVER(Partition BY [userid], [Rank] ORDER BY DateofValidity) CountCurrentRankAffiliation
                    FROM PDATA 

                    SELECT UserID, CountCurrentRankAffiliation
                    FROM CTE
                    WHERE [Dateofvalidity] = @CurrentMonth
                    return
                end

Answer 1

Update 2

Using Row number I couldn't count only the current sequence of ranks, so I had to change the query completely, but I did manage to get the desired results:

SELECT  [User], 
        (
        SELECT COUNT(*)
        FROM @T i
        WHERE o.[User] = i.[User]
        AND o.[Rank] = i.[Rank]
        AND o.[Date] >= i.[Date]
        AND i.[Date] >
        COALESCE 
        (
            (
            SELECT TOP 1 [Date]
            FROM @T ii
            WHERE i.[User] = ii.[User]
            AND i.[Rank] <> ii.[Rank]
            AND ii.[Date] < o.[Date]
            ORDER BY [Date] DESC
            )
        , '1900-01-01')
        ) As CountCurrentRankLength
FROM @T o
WHERE [Date] = '2016-12-01'
ORDER BY [User], [Date]

The first inner query (the count(*)) basically does the same as row number, However, using the second inner query it only counts the number of rows where the rank was sequential - this is something I couldn't do with the regular row number function.

Update

If the CTE is causing you problems, you can write it as a derived table instead:

SELECT [User], CountCurrwntRankLength
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [User], [Rank] ORDER BY [Date]) CountCurrwntRankLength
    FROM @T
) CTE
WHERE [Date] = '2016-12-01'

This will give you the same results as the CTE

First version

Well, let's play the assumption game. I assume you are using sql server 2008 or higher:

Create and populate sample table (Please, save us this step in your future questions)

DECLARE @T As Table
(
    [User] char(1),
    [Date] date,
    [Rank] varchar(10)
)

INSERT INTO @T VALUES
('A', '2016-10-01', 'Bee'),
('A', '2016-11-01', 'Bee'),
('A', '2016-12-01', 'Chairman'),
('A', '2017-01-01', 'Chairman'),
('B', '2016-10-01', 'Artist'),
('B', '2016-11-01', 'Artist'),
('B', '2016-12-01', 'Artist'),
('B', '2017-01-01', 'Artist')

The query:

;WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [User], [Rank] ORDER BY [Date]) CountCurrwntRankLength
    FROM @T
)

SELECT [User], CountCurrwntRankLength
FROM CTE
WHERE [Date] = '2016-12-01'

Results:

User    CountCurrwntRankLength
A       1
B       3

Explanation:
The CTE will add the number of times the same user has the same rank by using the ROW_NUMBER window function with the PARTITION BY clause.

Then all you have to do is query the CTE for a particular date.

Answer 2

If we assume that users do not switch back to the current rank, you can do this with aggregation and window functions:

select t.*
from (select t.user, t.rank, count(*) as cnt,
             row_number() over (partition by t.user order by max(t.date) desc) as seqnum
      from t
      group by t.user, t.rank
     ) t
where seqnum = 1;

This actually returns "2" and "4" for the two users, rather than "1" and "3". I find the "2" and "4" to be a better answer to your question. Subtract 1, if you prefer starting to count from zero.

Answer 3

Lot of reserved keywords, but here we go:

select t1.user, count(distinct t1.date)
from
(select 
    user, date, rank
from t t1
where date <= '2016.12.01') t1
inner join
(select
    user, rank
from t t2
where date = '2016.12.01') t2
on t1.user = t2.user
and t1.rank = t2.rank
group by t1.user;