MS SQL Server
  1. MS SQL Server
  3. Requête SQL Server sans utiliser de loops
Intereting Posts
Sql Dates à Colonnes pour Timecard (dynamic) Déclaration de cas avec groupement SQL Server + Sélectionnez WHERE dans une string délimitée par des virgules Comment créer une séquence de lettres? Comment transformer une sous-requête multi-parameters Oracle en sous-requête MS SQL (s) Réplication transactionnelle entre le server Azure DB et les servers de database sur site SQL – sorting des valeurs alphanumériques en anglais et japonais SQL, search de vrai, faux ou les deux Supprimer les duplicates MS-SQL avec date minimum et plusieurs keys Comment faire deux colonne pivot avec le même nom de colonne à l'aide de SQL Server? Sélectionnez la première copy dans le champ aliasé SQL Insérer des valeurs ou ignorer la ligne si une contrainte échoue Join ralenti par la condition WHERE SQL Server – Efficacité de l'index pour le groupe par dans les sous-sélections Ouvrir l'URL du lien de rapport dans une nouvelle window dans SSRS ne fonctionne pas

Requête SQL Server sans utiliser de loops

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;