Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: SQL: Count payments for a given periode

Results 1 to 3 of 3
Share This Thread →
  1. #1
    RadioX is offline Registered User
    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
    RadioX is offline Registered User
    Join Date
    Mar 2012
    Posts
    6
    Rep Power
    0
    Any?

  3. #3
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,538
    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

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

Similar Threads

  1. Distinct, Count & Count by Date
    By duval in forum SQL Development
    Replies: 14
    Last Post: November 29th, 2011, 10:09 PM
  2. Dividing a Distinct Count by a count
    By jerelp in forum Microsoft Access Help
    Replies: 1
    Last Post: February 17th, 2010, 05:22 AM
  3. Online payments
    By princecharles in forum ASP Development
    Replies: 1
    Last Post: November 19th, 2008, 08: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, 03:59 AM
  5. Advice with payments
    By jessie2477 in forum .NET Development
    Replies: 4
    Last Post: July 25th, 2006, 01:36 PM

ASP Free Advertisers and Affiliates