J'essaye de supprimer les inputs dupliquées qui sont provoquées par les valeurs de date étant différentes. J'ai essayé d'utiliser min (date) en groupe mais cela n'est pas autorisé
Par exemple en récupérant les 2 lignes suivantes quand tout ce dont j'ai besoin est le 1er
MasterCustomerId NewClubKeyId DateAssigned 000000201535 K18752 2014-08-13 20:25:18.717 000000201535 K18752 2015-01-08 00:41:03.037
Voici ma requête. Des idées? Merci
SELECT nc.CreatorMasterCustomerId MasterCustomerId,nc.NewClubKeyId,MIN(nc.DateCreated) DateAssigned FROM NewClub nc WHERE nc.IsActive = 1 AND nc.NewClubKeyId IS NOT NULL AND nc.DateCreated IS NOT NULL AND nc.DateCreated >='2013-10-10' GROUP BY nc.CreatorMasterCustomerId,nc.NewClubKeyId,nc.DateCreated UNION SELECT ncb.MasterCustomerId,nc.NewClubKeyId,MIN(ncb.DateCreated) DateAssigned FROM NewClubBuilder ncb JOIN NewClub nc ON nc.Id = ncb.NewClubId WHERE nc.IsActive = 1 AND nc.NewClubKeyId IS NOT NULL AND ncb.DateCreated IS NOT NULL AND ncb.DateCreated >='2013-10-10' GROUP BY ncb.MasterCustomerId,nc.NewClubKeyId,ncb.DateCreated
Selon la suggestion de @suslov ci-dessous, j'ai implémenté la requête comme décrit et cela fonctionne bien. C'est ici:
select t.MasterCustomerId, t.NewClubKeyId, MIN(t.DateCreated)DateAssigned FROM ( SELECT DISTINCT nc.CreatorMasterCustomerId MasterCustomerId,nc.NewClubKeyId,nc.DateCreated FROM NewClub nc WHERE nc.IsActive = 1 AND nc.NewClubKeyId IS NOT NULL AND nc.DateCreated IS NOT NULL AND nc.DateCreated >='2013-10-10' UNION SELECT DISTINCT ncb.MasterCustomerId,nc.NewClubKeyId,ncb.DateCreated FROM NewClubBuilder ncb JOIN NewClub nc ON nc.Id = ncb.NewClubId WHERE nc.IsActive = 1 AND nc.NewClubKeyId IS NOT NULL AND ncb.DateCreated IS NOT NULL AND ncb.DateCreated >='2013-10-10' )t GROUP BY t.MasterCustomerId,t.NewClubKeyId
Vous pouvez utiliser votre select
avec union
comme table temporaire, puis en select
et faire un group by
passé sans le champ DateCreated
.
select t.CreatorMasterCustomerId as MasterCustomerId , t..NewClubKeyId , min(t.DateCreated) as DateAssigned from (<...>) t group by t.MasterCustomerId , t.NewClubKeyId