SQL: Count payments for a given period
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.. (MS SQL)
It only counts who has had a payment during the period, but not who have had their first payment during this periode.
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)
Any suggestions on how I can fix this?