Count records

• abhi-307868

  SSC-Addicted

  Points: 415

  More actions

  February 19, 2008 at 8:12 pm

  #186833

  Hi All,

  I have 1 table that contains the paymnet plan. Sample data being:

  ID QuoteID DateTypeID DueDate Amount TypeID

  12367 70460 1 2007-10-19 75.0 1

  12369 70460 2 2008-02-11 268.0 1

  12368 70460 3 2008-02-15 100.0 2

  as per this customer is supposed to pay initial deposit of $75on 19th Oct. Then Installment of $268 on 11th Feb and then 100% of the balance due on 15th of feb.

  Then there is a view that contains all the customers and the totalcost and balancedue information and the quoteid. I need to count the number of customers who have paid in full, who are overdue and who are up to date as per the payment plan. only condition is that if the final payment date has passed todays date then all the customer are overdue except the once who have paid in full.

  Conditions:

  Paid in full := balancedue <=0

  Customers Overdue:= (payments-Amountdue) <0 --amountdue is from pmtplan

  Customers Up to Date:=(payments-Amountdue)>=0 --amountdue is from pmtplan

  Any suggestion to do this would be appreciated.

  Thanks

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  February 20, 2008 at 8:39 am

  #780228

  I'll assume that you know how to get your 'raw' data, and you just want to count/sum up the different numbers...

  You can do something like this for the different counts;

  SELECT ...

  SUM(CASE WHEN balancedue <= 0 then 1 else 0 END) as PaidInFull,

  SUM(CASE WHEN (payments - amountdue) < 0 THEN 1 ELSE 0 END) as overdue,

  SUM(CASE WHEN (payments - amountdue >= 0 THEN 1 ELSE 0 END) as UpToDate

  FROM ...

  /Kenneth