Link to home
Start Free TrialLog in
Avatar of tips54
tips54

asked on

capture AR balance for two years

Hi.
I have a table name (customer_credit_history) which has columns CustomerID, month_invoiced, year_invoiced, Invoice_sales,  Amt_paid.  I need to capture AR balance for the lat couple years by periods and year. my biggest problem with this query is caputering the value for the previous period and adding it to the next without getting another column.
The calculation is simply (Invoice_sales -  Amt_paid).  I keep getting the in multiple columns when I need it all in one row.  
customer 12345 bought on a 1000 and paid 200 ...
then buy: 900  Paid  500:   buy for 500  Paid 500.  Buy for 2000  Paid: 2200.....
 Expected results:
Customer_id,            Year              Month            AR_Bal
12345                      2008               12                  800
12345                      2009               1                    1200
12345                      2009               3                    1200
12345                      2009               4                    1000

Thanks in advance.
ASKER CERTIFIED SOLUTION
Avatar of JohnSingleton
JohnSingleton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tips54
tips54

ASKER

John,
this is awesome.  would I do the same if I wanted sum of amount paid in the very first 4 month than keep adding the next month.
customer 12345 bought for 1000 and paid 200 ...
then buy: 900  Paid  500:  
 buy for 500  Paid 500.  
Buy for 200  Paid: 300,
 Buy 200 Paid 100.
Buy 400  paid: 100
Example of expected results:
CustomerID       Amt-PD-4Mnths
12345              1500   (sum of first month payments)        
12345              1600
12345              1700
Yeah, you could do stuff like that with the above methedology, i'd just create a new column as a subquery like i'd done before.

the date criteria would be the only changes.  Note however that i changed the dates to YYYYMM format so they could be accurately evaluated in a greater than/less than way.  

i'm not sure i understand your last post and the 4 month thing, but i'm pretty sure you could do something like this.

i will caution however, over alot of data it won't be very effecient, for prolonged, scalable use, i'd recommend creating a scalar function and feeding the customer ID and the current month/year into it to get the value back out you want.

-John
Avatar of tips54

ASKER

Can you give me an example what the syntax would look like in changing those dates. basically I want if they paid 400 in the 100's in the last 4 months to read amounth paid 400. If they paid 200 next month, for next period to read 600 amt paid.
Avatar of tips54

ASKER

for example I'm getting the first value by doing this:

select
,sum (case when datediff( "d", P21_view_customer_credit_history.date_created , getdate()) between 0 and 120
            then  amount_paid
            else 0
      end)  as [AR_4Mnths_AmtPd]
from P21_view_customer_credit_history
I'm not sure how I would add the next period

so you'd like to consolidate the first month as actually 4 months rolled into one? then go from there one month at a time?  is that correct?

ok......
let me know if that's correct before i start.

-John


You could also easily apply a pivot query, or a matrix report in order to get the desired results.  by pivoting on the Customer,

If you are going to use a Matrix report in SSRS you would have the Customers on the rows, and the Period / Balances on the columns
Avatar of tips54

ASKER

"so you'd like to consolidate the first month as actually 4 months rolled into one? then go from there one month at a time?  is that correct?

ok......
let me know if that's correct before i start. "
you are correct .

Auric,
I'm on sql 2000
Darn :( that's too bad
Alright, no test data, i'm taking a swing.

this has to change the ar_balance column to adhere to the first 4 month rollup as well, can't have it both ways because of grouping.  to support both, we'd need to group the date columns (month & year) 2 different ways simultaneously, which we cannot. this hopefully, if i've done it right, will roll both ar_balance and payments up for first 4 months, then each month thereafter on their own.

hope this helps

-John

select cchist.customer_id
, case when cchist.yrMn in 
          (select top 4 (cast(c1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), c1.month_invoiced), 2)) as yrmn4
          from customer_credit_history as c1
          where c1.customer_id = cchist.customer_id
          order by yrmn4)
       then (select top 1 (cast(c1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), c1.month_invoiced), 2)) as yrmn4
          from customer_credit_history as c1
          where c1.customer_id = cchist.customer_id
          order by yrmn4 desc)
       else cchist.yrMn
       end as YrMnAdj
, sum(cchist.ar_balance) as [amt_paid_adj]
, sum(cchist.amt_paid) as [amt_paid_adj]
from
(select t1.customer_id
, t1.year_invoiced
, t1.month_invoiced
, (cast(t1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), t1.month_invoiced), 2)) as YrMn
, (select sum(ch.invoice_sales - ch.amt_paid) as [ar_balance]
from customer_credit_history as ch
where t1.customer_id = ch.customer_id
and (cast(t1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), t1.month_invoiced), 2))
>=  (cast(ch.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), ch.month_invoided), 2))
         group by t1.customer_id 
         ,(cast(t1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), t1.month_invoiced), 2))) as [ar_balance]
, (select sum(ch.amt_paid) as [amt_paid]
from customer_credit_history as ch
where t1.customer_id = ch.customer_id
and (cast(t1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), t1.month_invoiced), 2))
>=  (cast(ch.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), ch.month_invoided), 2))
         group by t1.customer_id 
         ,(cast(t1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), t1.month_invoiced), 2))) as [amt_paid]
from customer_credit_history as t1) as cchist
group by
cchist.customer_id
, case when cchist.yrMn in 
          (select top 4 (cast(c1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), c1.month_invoiced), 2)) as yrmn4
          from customer_credit_history as c1
          where c1.customer_id = cchist.customer_id
          order by yrmn4)
       then (select top 1 (cast(c1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), c1.month_invoiced), 2)) as yrmn4
          from customer_credit_history as c1
          where c1.customer_id = cchist.customer_id
          order by yrmn4 desc)
       else cchist.yrMn
       end

Open in new window

Avatar of tips54

ASKER

Error:
Server: Msg 144, Level 15, State 1, Line 47
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
Avatar of tips54

ASKER

I just noticed you're combining the AR_balance in this script. I'm currious if it would be simpler to just get customer_id, period, year,  sum cash_receipt in the last 4 month and add the next period as you go along.
Avatar of tips54

ASKER

I'm assigning points before I forget.
thanks a bunch JohnSingleton
Ok, i went back to the drawing board and re-designed because the way we were getting sumed ammounts out through subqueries were causing problems when trying to do a fragmentted grouping like that.  Again, i've no test data, but this should be close.
i'm basically grouping the first 4 months together right off the bat and considering them 1 month.  I'm unioning that group to all other months (not in the first 4), which are coming out through at face value.

once i have the summed first 4 unioned with the rest individually, the subqueries for the subtotals are injected into the top-level of the query.  I've also put a counter on it to see how many detail records are comprising the subtotal row (to validate the first 4 months are indeed rolled up into one.)


hope this helps
-John
       select u1.customer_id
            , u1.yrMn
            , u1.RecordCount
            , u1.invoice_sales
            , u1.amt_paid
            , (select sum(ch.invoice_sales) as [inv_sales]
              from customer_credit_history as ch
              where u1.customer_id = ch.customer_id
                and (cast(u1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), u1.month_invoiced), 2))
                >=  (cast(ch.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), ch.month_invoided), 2))
              group by u1.customer_id 
                   ,(cast(u1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), u1.month_invoiced), 2))) as [subtotal_Invoice_sales]
            , (select sum(ch.amt_paid) as [amt_paid]
              from customer_credit_history as ch
              where u1.customer_id = ch.customer_id
                and (cast(u1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), u1.month_invoiced), 2))
                >=  (cast(ch.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), ch.month_invoided), 2))
              group by u1.customer_id 
                   ,(cast(u1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), u1.month_invoiced), 2))) as [subtotal_amt_paid]
            , (select sum(ch.invoice_sales - ch.amt_paid) as [ar_balance]
              from customer_credit_history as ch
              where u1.customer_id = ch.customer_id
                and (cast(u1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), u1.month_invoiced), 2))
                >=  (cast(ch.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), ch.month_invoided), 2))
              group by u1.customer_id 
                   ,(cast(u1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), u1.month_invoiced), 2))) as [subtotal_ar_balance]
       from
              (select f4_2.*
              from
                    (select f4.customer_id
                      , max(f4.firstYrMn) as [YrMn]
                      , sum(f4.invoice_sales) as [invoice_sales]
                      , SUM(f4.amt_paid) as [amt_paid]
                      , count(*) as [RecordCount]
                    from
                        (select
                            t1.customer_id
                          , (cast(t1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), t1.month_invoiced), 2)) as [firstYrMn]
                          , t1.invoice_sales
                          , t1.amt_paid
                        from customer_credit_history as t1
                        where (cast(t1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), t1.month_invoiced), 2)) in
                                (select top 4 (cast(cch.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), cch.month_invoiced), 2)) as [yrmn]
                                from customer_credit_history as cch
                                where cch.customer_id = t1.customer_id
                              order by [yrmn])) as f4
                    group by f4.customer_id) as f4_2
                union select
                        t1.customer_id
                      , (cast(t1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), t1.month_invoiced), 2)) as [YrMn]
                      , t1.invoice_sales
                      , t1.amt_paid
                      , 1 as [RecordCount]
                    from customer_credit_history as t1
                    where (cast(t1.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), t1.month_invoiced), 2)) NOT IN
                            (select top 4 (cast(cch.year_invoiced as varchar(4)) + RIGHT('0' + convert(varchar(2), cch.month_invoiced), 2)) as [yrmn]
                            from customer_credit_history as cch
                            where cch.customer_id = t1.customer_id
                          order by [yrmn])) as u1

Open in new window