#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0

    Question MS SQL: Count payments for a given periode


    Hi,
    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..


    Code:
    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 (
    	SELECT 'x'
    	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?

    Thank you!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Code:
    SELECT cus.CustomerStartDate
         , COUNT(*) AS NumberOfWeb
      FROM Company.dbo.Customer cus
    INNER
      JOIN ( SELECT CustomerNo
                  , MIN(PaymentDate) AS first_payment
               FROM Company.dbo.Payment
              WHERE PaymentAmount > 0
             GROUP
                 BY CustomerNo ) AS pay
        on pay.CustomerNo = cus.CustomerNo
     WHERE cus.SourceCode = 'Web'
       AND cus.customerStartDate >= '2012-01-01'
       AND pay.first_payment >= '2013-01-01' 
       AND pay.first_payment  < '2013-02-01'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0
    Thank you! That's it

    What about this case:
    Every customer that have had two payments og more, at any time the two last years (after 01-01-2011)?

IMN logo majestic logo threadwatch logo seochat tools logo