J'ai une table de Payment
qui ressemble un peu à ceci:
Id (int identity) CustomerId (int) PaymentDate (SmallDateTime)
Maintenant, je veux écrire une requête qui va find les clients qui ont fait trois paiements dans une période de trois mois. Compte tenu des données suivantes:
Id CustomerId PaymentDate (YYYY-MM-DD) ------------------------------------------ 1 1 2010-01-01 2 1 2010-02-01 3 1 2010-03-01 4 1 2010-06-01 5 2 2010-04-01 6 2 2010-05-01 7 2 2010-06-01 8 2 2010-07-01
Je voudrais produire le résultat suivant:
CustomerId LastPaymentDateInPeriod ------------------------------------- 1 2010-03-01 2 2010-07-01
Où LastPaymentDateInPeriod
correspond au PaymentDate
avec la valeur la plus élevée sur une période de trois mois. S'il y a plus d'une période de trois mois pour un client donné, il devra returnner la valeur la plus élevée de la période la plus récente (c'est ce que j'ai essayé d'illustrer pour le client 2 dans l'exemple ci-dessus). Notez que trois paiements sur trois jours consécutifs répondraient également aux critères. Les paiements doivent simplement tomber dans une période de trois mois.
Je sais comment le faire avec un slider et beaucoup de petites requêtes mais c'est lent (et, j'ai compris, ne devrait être qu'un dernier recours). Alors, est-ce que l'un de vos génies SqlServer sait comment faire cela avec une requête?
Merci d'avance.
Cela devrait faire le travail:
select CustomerID, max(LastPaymentDateInPeriod) as LastPaymentDateInPeriod from ( select LastPaymentInPeriod.CustomerID, LastPaymentInPeriod.PaymentDate as LastPaymentDateInPeriod from Payment LastPaymentInPeriod inner join Payment RelatedPayment on LastPaymentInPeriod.CustomerID = RelatedPayment.CustomerID and LastPaymentInPeriod.PaymentDate > RelatedPayment.PaymentDate and datediff(m, RelatedPayment.PaymentDate, LastPaymentInPeriod.PaymentDate) < 3 group by LastPaymentInPeriod.CustomerID, LastPaymentInPeriod.PaymentDate having count(*) > 1 ) as PaymentPeriods group by CustomerID
mise à jour: j'ai testé cela maintenant et cela semble fonctionner pour datatables de @ Martin
update2: S'il est requirejs que Jan 31 et Apr 1 soient considérés comme étant à less de 3 mois d'intervalle, l'appel de la fonction DATEDIFF peut être remplacé par quelque chose comme ceci:
create function fn_monthspan ( @startdate datetime, @enddate datetime ) returns int as begin return datediff(m, @startdate, @enddate) - case when datepart(d, @startdate) > datepart(d, @enddate) then 1 else 0 end end
Un peu d'un travail précipité comme je suis hors.
declare @T TABLE ( Id int, CustomerId int, PaymentDate SmallDateTime ) insert into @T SELECT 1, 1,'2010-01-01' UNION ALL SELECT 2, 1,'2010-02-01' UNION ALL SELECT 3, 1,'2010-03-01' UNION ALL SELECT 4, 1,'2010-06-01' UNION ALL SELECT 5, 2,'2010-04-01' UNION ALL SELECT 6, 2,'2010-05-01' UNION ALL SELECT 7, 2,'2010-06-01' UNION ALL SELECT 8, 2,'2010-07-01' ;with CTE1 AS ( SELECT Id, CustomerId, PaymentDate, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY PaymentDate) RN FROM @T ), CTE2 AS ( SELECT C1.Id, C1.CustomerId, MAX(C2.PaymentDate) AS LastPaymentDateInPeriod FROM CTE1 C1 LEFT JOIN CTE1 C2 ON C1.CustomerId = C2.CustomerId AND C2.RN BETWEEN C1.RN AND C1.RN + 2 and C2.PaymentDate <=DATEADD(MONTH,3,C1.PaymentDate) GROUP BY C1.Id, C1.CustomerId HAVING COUNT(*)=3 ) SELECT CustomerId, MAX(LastPaymentDateInPeriod) LastPaymentDateInPeriod FROM CTE2 GROUP BY CustomerId
Cela vous donne tous les trois paiements dans un timeout de 3 mois.
; WITH CustomerPayments AS ( SELECT 1 Id, 1 CustomerId, Convert (DateTime, '2010-01-01') PaymentDate UNION SELECT 2, 1, '2010-02-01' UNION SELECT 3, 1, '2010-03-01' UNION SELECT 4, 1, '2010-06-01' UNION SELECT 5, 2, '2010-04-01' UNION SELECT 6, 2, '2010-05-01' UNION SELECT 7, 2, '2010-06-01' UNION SELECT 8, 2, '2010-07-01' UNION SELECT 9, 3, '2010-07-01' UNION SELECT 10, 3, '2010-07-01' ), FirstPayment AS ( SELECT Id, CustomerId, PaymentDate FROM CustomerPayments where Id IN ( SELECT Min (Id) Id FROM CustomerPayments Group by CustomerId ) ), SecondPayment AS ( SELECT Id, CustomerId, PaymentDate FROM CustomerPayments where Id IN ( SELECT Min (Id) Id FROM CustomerPayments WHERE ID NOT IN ( SELECT ID from FirstPayment ) Group by CustomerId ) ), ThirdPayment AS ( SELECT Id, CustomerId, PaymentDate FROM CustomerPayments where Id IN ( SELECT Min (Id) Id FROM CustomerPayments WHERE ID NOT IN ( SELECT ID from FirstPayment UNION SELECT ID from SecondPayment ) Group by CustomerId ) ) SELECT * FROM FirstPayment FP Left JOIN SecondPayment SP ON FP.CustomerId = SP.CustomerId Left JOIN ThirdPayment TP ON SP.CustomerId = TP.CustomerId WHERE 1=1 AND SP.PaymentDate IS NOT NULL AND TP.PaymentDate IS NOT NULL AND ABS (DATEDIFF (mm, SP.PaymentDate, TP.PaymentDate)) <3
J'ai pensé à:
select customerId,max(PaymentDate) from payment where customerId in
(select case when count(*)<3 then null else customerId end as customerId from payment where paymentdate>dateadd(month,-3,getdate()) group by customerId)
group by customerId;