February 21st, 2013, 05:57 AM
Join Date: Feb 2013
Time spent in forums: 13 m 50 sec
Reputation Power: 0
MS SQL: Count payments for a given periode
I am trying to solve a problem..
I got two tabels, one customer table where customerStartDate is stored.And one payment table where several paymentDates can be stored.
The customers have a customerStartDate that is not equal to the first paymentDate. The periode between the customerStartDate and the paymentDates varies from customer to customer. I would like to be able to count the numbers of first payments for a given month/periode.
This is what I got this far..
SELECT cus.CustomerStartDate, COUNT(cus.CustomerNo)AS NumberOfWeb
FROM Company.dbo.Customer cus
WHERE cus.SourceCode = 'Web'
AND cus.customerStartDate > '2012-01-01'
AND EXISTS (
FROM Company.dbo.Payment py
WHERE cus.CustomerNo = py.CustomerNo
AND py.PaymentAmount > 0
AND py.PaymentDate BETWEEN '2013-01-01' AND '2013-02-01'
having count(*) = 1)
It only counts who has had a payment during the period, but not who have had their first payment during this periode. Any suggestions on how I can fix this?