SQL Server 2014 – Utiliser la valeur précédente lorsque la date n'est pas présente

J'ai posé une question similaire hier mais je n'étais pas très bon dans ma description de ce que je voulais. Ce sera beaucoup plus clair.

Lead / Lag ne me procure pas ce dont j'ai besoin. C'est proche, mais pas assez. Utilisation de SQL Server 2014 pour le client, server réel basé sur SQL 2012.

Voici mon code: Créer une table d'équipe

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');

Maintenant, pour créer un tableau de données, je dois me joindre à

 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);

Voici une sélection de base pour montrer ce qui se passe:

 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

Cela me donne un set de résultats qui ressemble à ceci:

Cliquez pour la sortie de données

Ce dont j'ai besoin, c'est de l'endroit où j'ai des zéros, c'est-à-dire que ce serait tirer sur le nom de l'équipe précédente qui n'est pas nul. Donc, dans le cas User1, ces 4 nulls pour les mois de Juillet et Août diraient Team1 puisque c'était l'équipe où il était dernier. Pareil pour les nulls après Team2, ceux-ci devraient dire Team2.

Lead / Lag est proche ou je ne l'utilise pas correctement. Avec un peu de chance, avec tout ce code, cela facilite grandement les tâches de quelqu'un.

UPDATE: Lag / Lead donne les mêmes résultats. Encore besoin des zéros à remplir

 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

(Déplacer les notes de mise à jour à la fin)

Je pense que la solution la plus simple (conceptuellement) est de se joindre à tous les mois jusqu'à month_sk puis de filterr pour get seulement le dernier match. Cela "se sent" potentiellement inefficace, donc vous voudriez le tester avec un volume de données réalist et s'il y a un problème, alors cherchez quelque chose de mieux. (Mais "quelque chose de mieux" peut impliquer des changements au model physique de données …)

Alors:

 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

Notez que dans les versions précédentes, j'ai utilisé row_number() over() au lieu de rank() over() … et vous pouvez le faire, mais si vous le faites, vous devez inclure dans la key de partitionnement datatables de la table b pourrait provoquer une duplication d'une ligne de la table lors de la jointure. L'utilisation du rank garantit que tous ces duplicates partagent leur rang comme ils le devraient.

MISE À JOUR – Après avoir d'abord écrit ceci, je l'ai supprimé parce que je pensais que j'avais mal interprété votre question; mais comme j'écrivais un rlocation réalisé je l'ai peut-être eu droit en premier lieu. Alors voilà, avec une mise en garde:

Cela suppose que la seule raison pour laquelle vous obtenez la valeur NULL est la jointure externe. Si jamais la table "main droite" a une ligne et juste une valeur pour une colonne dans celle-ci est NULL, alors get la valeur précédente pour cette colonne nécessiterait un travail supplémentaire avec des sous-requêtes ou des fonctions analytiques. Mais même alors, le lead / lag peut ne pas fonctionner, car ils sont basés sur la position. (Je pense que quelque chose avec LAST_VALUE pourrait être plus approprié, mais laissera les détails de cela à less que ce soit nécessaire.)

UPDATE 2 – basé sur votre description du model de données dans les commentaires ci-dessous, je modifie la requête pour montrer une jointure interne comme cela semble fonctionner (une fois que vous élargissez les critères de jointure) et devrait être plus efficace.

MISE À JOUR 3 – J'ai mal interprété vos données d'échantillon et j'ai obtenu l'expression de partitionnement pour calculer n mauvais. Devrait être fixé en supposant que les valeurs de la table b sont uniques. Sinon c'est encore réparable mais nécessite plus de supercherie …

Vous pouvez le faire avec une application et une sous-requête comme celle-ci.

 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

Dans cette version, j'identifie d'abord le mois de "binding" approprié dans le CTE, puis je l'utilise comme search dans la jointure finale. (Cela a été beaucoup plus facile une fois que j'ai réalisé que Media et NCO ne jouaient aucun rôle dans la jointure.)

 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