#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2012
    Posts
    6
    Rep Power
    0

    Question 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.. (MS SQL)

    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. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2012
    Posts
    6
    Rep Power
    0
    Any?
  4. #3
  5. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,608
    Rep Power
    278
    something like that
    Code:
    SELECT cus.CustomerStartDate, 
    	   COUNT(cus.CustomerNo)AS NumberOfWeb
    FROM Company.dbo.Customer cus
    inner join (SELECT ROW_NUMBER() over(PARTITION by CustomerNo order by PaymentDate) as row,
    		PaymentDate, CustomerNo
    	FROM Company.dbo.Payment 
    	WHERE PaymentAmount > 0
    	AND py.PaymentDate BETWEEN '2013-01-01' AND '2013-02-01') py
    on cus.CustomerNo = py.CustomerNo	
    WHERE cus.SourceCode = 'Web'
    AND cus.customerStartDate > '2012-01-01'
    and py.row = 1
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree

Similar Threads

  1. Distinct, Count & Count by Date
    By duval in forum SQL Development
    Replies: 14
    Last Post: November 29th, 2011, 11:09 PM
  2. Dividing a Distinct Count by a count
    By jerelp in forum Microsoft Access Help
    Replies: 1
    Last Post: February 17th, 2010, 06:22 AM
  3. Online payments
    By princecharles in forum ASP Development
    Replies: 1
    Last Post: November 19th, 2008, 09:53 PM
  4. How to reduce Scan count and Logical count in the Query
    By sqluser in forum SQL Development
    Replies: 0
    Last Post: September 20th, 2008, 04:59 AM
  5. Advice with payments
    By jessie2477 in forum .NET Development
    Replies: 4
    Last Post: July 25th, 2006, 02:36 PM

IMN logo majestic logo threadwatch logo seochat tools logo