SQL Server 2014 - use previous value if date is missing

Question

I asked a similar question yesterday, but I was not very well versed in my description of what I wanted. It will be much clearer.

Lead / Lag doesn't get me what I need. Its close, but not enough. Using SQL Server 2014 for the client, the actual server built on SQL 2012.

Here is my code: Creating a command table

CREATE TABLE ##TeamTable
    ([UserID] varchar(50), [CurrentTeam] varchar(5), [ChangeDate] datetime)
;

INSERT INTO ##TeamTable
    ([UserID], [CurrentTeam], [ChangeDate])
VALUES
    ('User1', 'Team1', '6/1/2016'),
    ('User1', 'Team2', '9/1/2016'),
    ('User1', 'Team3', '12/1/2016'),
    ('User2', 'Team1', '4/1/2016'),
    ('User2', 'Team2', '10/1/2016'),
    ('User2', 'Team3', '11/1/2016');

Now to create a data table I need to join

CREATE TABLE ##DataTable
    ([UserID] varchar(50), Month_sk datetime, Media varchar(50), NCO int)
INSERT INTO ##DataTable
    ([UserID] , Month_sk , Media , NCO )
VALUES
    ('User1', '2016-06-01 00:00:00', 'Fax', 100),
    ('User1', '2016-06-01 00:00:00', 'Voice', 120),
    ('User1', '2016-07-01 00:00:00', 'Voice', 90),
    ('User1', '2016-07-01 00:00:00', 'Email', 100),
    ('User1', '2016-08-01 00:00:00', 'Voice', 150),
    ('User1', '2016-08-01 00:00:00', 'Email', 100),
    ('User1', '2016-09-01 00:00:00', 'Voice', 100),
    ('User1', '2016-09-01 00:00:00', 'Email', 120),
    ('User1', '2016-10-01 00:00:00', 'Voice', 90),
    ('User1', '2016-10-01 00:00:00', 'Email', 100),
    ('User1', '2016-11-01 00:00:00', 'Voice', 150),
    ('User1', '2016-11-01 00:00:00', 'Email', 100),
    ('User1', '2016-12-01 00:00:00', 'Voice', 150),
    ('User1', '2016-12-01 00:00:00', 'Email', 100),
    ('User2', '2016-04-01 00:00:00', 'Fax', 100),
    ('User2', '2016-04-01 00:00:00', 'Voice', 120),
    ('User2', '2016-05-01 00:00:00', 'Fax', 100),
    ('User2', '2016-05-01 00:00:00', 'Voice', 120),
    ('User2', '2016-06-01 00:00:00', 'Fax', 100),
    ('User2', '2016-06-01 00:00:00', 'Voice', 120),
    ('User2', '2016-07-01 00:00:00', 'Voice', 90),
    ('User2', '2016-07-01 00:00:00', 'Email', 100),
    ('User2', '2016-08-01 00:00:00', 'Voice', 150),
    ('User2', '2016-08-01 00:00:00', 'Email', 100),
    ('User2', '2016-09-01 00:00:00', 'Voice', 100),
    ('User2', '2016-09-01 00:00:00', 'Email', 120),
    ('User2', '2016-10-01 00:00:00', 'Voice', 90),
    ('User2', '2016-10-01 00:00:00', 'Email', 100),
    ('User2', '2016-11-01 00:00:00', 'Voice', 150),
    ('User2', '2016-11-01 00:00:00', 'Email', 100),
    ('User2', '2016-12-01 00:00:00', 'Voice', 150),
    ('User2', '2016-12-01 00:00:00', 'Email', 100);

Here's a basic Select to show what happens:

SELECT  b.UserID
        ,b.Media
        ,b.NCO
        ,Month_sk
        ,CurrentTeam

FROM    ##DataTable b

LEFT OUTER JOIN ##TeamTable a on b.UserID = a.UserID and b.Month_sk = a.ChangeDate

order by UserID, Month_sk, media

This gives me a result that looks like this:

, , , null. , User1 4 Team1, , . Team2, Team2.

Lead/Lag , . , -.

UPDATE: Lag/Lead .

SELECT  b.UserID
        ,b.Media
        ,b.NCO
        ,Month_sk
        ,CurrentTeam
        ,LAG(CurrentTeam,1, currentteam) OVER(PARTITION BY a.userid, changedate ORDER BY ChangeDate) as Lag

FROM    ##DataTable b

LEFT OUTER JOIN ##TeamTable a on b.UserID = a.UserID and b.Month_sk = a.ChangeDate

order by UserID, Month_sk, media

Answer 1

( )

, () month_sk, , . "" , , , - . ( "- " ...)

:

select userid, media, nco, month_sk, currentteam
  from (SELECT b.UserID
             , b.Media
             , b.NCO
             , Month_sk
             , CurrentTeam
             , rank() over(partition by b.userID
                               order by a.changeDate desc) n
        FROM            ##DataTable b
             INNER JOIN ##TeamTable a
                     on b.UserID = a.UserID
                    and b.Month_sk >= a.ChangeDate
       ) x
 where n = 1
 order by UserID, Month_sk, media

, row_number() over() rank() over()... , , b, a . rank , , .

. , , , , ; , , , , , . , , :

, , NULL, . - "" NULL, . / , . ( , - LAST_VALUE , , .)

2. , , , , ( ) .

3. n. , b . , ...

Answer 2

APPLY -, .

SELECT 
    userid, 
    media,
    nco,
    month_sk,
    currentteam
FROM
    ##DataTable td
    OUTER APPLY (
        SELECT TOP (1) 
            CurrentTeam,
            ChangeDate
        FROM 
            ##TeamTable tt
        WHERE 
            tt.UserID = td.UserID
            and tt.ChangeDate <= td.Month_sk
        ORDER BY
            tt.ChangeDate desc
    ) dataTableWithTeam
ORDER BY
    td.UserID,
    td.Month_sk,
    td.media

Answer 3

"" CTE, . ( , , Media NCO .)

WITH cteDateLookup
 as (
    --  Get the ChangeDate for this User/Month
    SELECT
       b.UserID
      ,b.Month_sk
      ,max(a.ChangeDate) ChangeDate
     from ##DataTable b
      left outer join ##TeamTable a
       on b.UserID = a.UserID
        and b.Month_sk >= a.ChangeDate
    group by 
       b.UserID
      ,b.Month_sk
  )
--  Use the cte as a "lookup" for the appropriate date
SELECT
   b.UserID
  ,b.Media
  ,b.NCO
  ,b.Month_sk
  ,a.CurrentTeam
 from ##DataTable  b
  left outer join cteDateLookup  cte
   on cte.UserId = b.UserId
    and b.Month_sk = cte.Month_sk
  left outer join ##TeamTable  a
   on a.UserId = cte.UserId
    and a.ChangeDate = cte.ChangeDate
 order by
   b.UserID
  ,b.Month_sk
  ,b.media