SQL Server plus grande n par requête de groupe: informations client avec date la plus récente

Introduction: J'ai regardé plusieurs exemples de dépassement de stack et beaucoup m'ont approché de ce dont j'avais besoin, mais pas exactement de ce dont j'avais besoin. S'il vous plaît nus avec et aussi la seule chose que j'ai trouvé inutile est que beaucoup de réponses ont jeté une solution sans explication à la façon dont cela fonctionne et pourquoi les pièces sont nécessaires. Donc, si la réponse peut inclure une explication et une ventilation de la requête, ce serait génial. Je vous remercie.

Problème: J'ai une table Client comme indiqué ci-dessous.

Id Name Account Active 1 Bob ABC True 2 Jenny BED True 3 Tony POT False 4 David DON False

J'ai aussi une table de command. Voir ci-dessous

 OrderId CustomerId OrderDate Status 1 2 2016-04-01 3 2 2 2016-09-05 7 3 2 2017-02-20 5 4 3 2015-02-20 8 5 4 2017-04-16 3

Ce dont j'ai besoin: Je dois donc avoir une table qui a le Customer.Id., Customer.Name, Customer.Account, Customer.Active, Order.Status ET le dernier OrderDate qui a un statut de 3 ou 7. Le tableau devrait afficher tous les clients et une valeur nulle pour Order.Status et LatestDate où le client n'a jamais commandé OU sa command ne correspond pas aux critères d'état. Voir ci-dessous pour un exemple.

 CustomerId Name Account Active Status LatestDate 1 Bob ABC True NULL NULL 2 Jenny BED True 7 2016-09-05 3 Tony POT False NULL NULL 4 David DON False 3 2017-04-16

Donc, comme vous pouvez le voir, Bob et Tony ont tous les deux un status et lastDate de null parce qu'ils n'ont pas fait de transaction ou qu'ils ne remplissent pas les critères du statut de l'Ordre à 3 ou 7. David fait et n'a qu'une seule command donc son dernier est simple. Jenny a plusieurs commands et plusieurs qui correspondent aux critères et donc il prend la command avec la dernière date et ajoute cela à notre table (en prenant toujours en count si le statut est 3 ou 7).

Maintenant, dans les tentatives précédentes à ce problème, j'ai fini avec toutes les transactions sockets en count (pas seulement 3 et 7), j'ai eu des clients en double montré (ne pouvait pas utiliser distinct en raison de la date différente), et dans une autre tentative, il a obtenu toutes datatables mais ignoré tout ce qui avait une valeur nulle. Comme je l'ai déjà dit, j'ai vu plein d'exemples sur Stack Overflow, mais ils semblent tous donner un problème ou un problème à mon cas et n'offrent pas assez d'explication ou de panne pour que je puisse le modifier pour ma cause.

Donc, si quelqu'un peut offrir une solution à ce problème ainsi qu'une ventilation de la SQL juste pour ma compréhension claire (et celle des autres). Merci d'avance.

Ma dernière tentative était:

 Select Distinct c.Id, c.[Name], c.Account, c.Active, o.Status, LatestDates.LatestDate From Customer as c Left Outer Join (Select CustomerId, Max(OrderDate) LatestDate From [Order] Group By CustomerId) LatestDates On c.Id = LatestDates.CustomerId Left Join [Order] o on LatestDates.CustomerId = o.CustomerId and LatestDates.LatestDate = o.OrderDate Where c.Active is not null Order by c.Active

Cependant, cette solution n'a pas pris en count les chèques 3 et 7 et je ne l'ai pas suffisamment comprise pour confirmer que datatables que je reçois sont correctes. Cela a été pris à partir d'une question de dépassement de stack différente. Faites-moi savoir si vous avez besoin de plus de détails.

Utiliser top with ties avec row_number()

 select top 1 with ties c.Id, c.[Name], c.Account, c.Active, o.Status, o.OrderDate from Customer as c left join [Order] o on c.Id= o.CustomerId and o.Status in (3,7) where c.Active is not null order by row_number() over (partition by c.Id order by o.OrderDate desc)

ou avec outer apply()

 select c.Id, c.[Name], c.Account, c.Active, o.Status, o.OrderDate from Customer as c outer apply ( select top 1 i.Status, i.OrderDate from [Order] i where i.CustomerId = c.Id and i.Status in (3,7) order by i.OrderDate desc ) as o

ou en ajustant votre tentative de requête en incluant les critères d' status dans la requête de table dérivée:

 Select Distinct c.Id, c.[Name], c.Account, c.Active, o.Status, LatestDates.LatestDate From Customer as c Left Outer Join ( Select CustomerId, Max(OrderDate) as LatestDate From [Order] i where i.Status in (3,7) Group By CustomerId ) LatestDates On c.Id = LatestDates.CustomerId Left Join [Order] o on LatestDates.CustomerId = o.CustomerId and LatestDates.LatestDate = o.OrderDate and o.Status in (3,7) Where c.Active is not null Order by c.Active

Rextester démo pour tous les 3: http://rextester.com/WNVKL52324

tous les 3 returns:

 +----+-------+---------+--------+--------+------------+ | Id | Name | Account | Active | Status | LatestDate | +----+-------+---------+--------+--------+------------+ | 1 | Bob | ABC | True | NULL | NULL | | 2 | Jenny | BED | True | 7 | 2016-09-05 | | 3 | Tony | POT | False | NULL | NULL | | 4 | David | DON | False | 3 | 2017-04-16 | +----+-------+---------+--------+--------+------------+

Utilisez simplement row_number() :

 select c.*, o.status, o.latestdate from Customer c left join (select o.*, row_number() over (partition by o.customerId order by o.orderDate desc) as seqnum from orders o ) o on o.customerId = c.customerId where seqnum = 1 and c.Active is not null order by c.Active;