September 28, 2012 at 3:40 pm
Hi guys,
I'm working on this query and don't know how to find the average for this. The data table looks like this
Corp_address paid_date_interval credit_limit ...
10008 21 10000
10008 32 10000
10008 20 10000
Is there a way that I can have the table look like this
Corp_address paid_date_interval credit_limit
10008 24.33 10000
The paid date interval = average as ( (21+32+22)/3) per corp_address
I have use the function SUM ( paid_date_interval) / COUNT( corp_address) but it was not working. This is my query looks like :
WITH invoice AS(
SELECT
address.corp_address_id
,address.name
,invoice_hdr.invoice_no
,invoice_hdr .invoice_date
,ar.payment_date
,invoice_hdr.net_due_date
,DATEDIFF (DAY, invoice_hdr.net_due_date ,ar.payment_date) pay_date_executed_interval
--,customer.credit_limit
--,customer.credit_limit_used
-- ,customer.credit_limit - customer.credit_limit_used as credit_available
FROM p21_view_invoice_hdr invoice_hdr
LEFT OUTER JOIN p21_view_address address ON address.id = invoice_hdr.customer_id
LEFT OUTER JOIN p21_view_invoice_line invoice_line ON invoice_line.invoice_no = invoice_hdr.invoice_no
LEFT OUTER JOIN p21_ar_receipts_view ar
INNER JOIN p21_ar_view ON p21_ar_view.invoice_no = ar.invoice_no
ON ar.invoice_no = invoice_hdr.invoice_no
--LEFT OUTER JOIN customer ON customer.customer_id = address.id
WHERE
invoice_hdr.paid_in_full_flag <> 'N'
AND invoice_hdr.year_for_period IN (2011,2012)
AND ar.payment_date IS NOT NULL
GROUP BY
address.corp_address_id
,address.name
,invoice_hdr .invoice_date
, ar.payment_date
,invoice_hdr.invoice_no
,invoice_hdr.net_due_date
),customer_1 AS(
SELECT
customer.customer_id
,customer.credit_limit
,customer.credit_limit_used
,customer.credit_limit - customer.credit_limit_used as credit_available
FROM customer
LEFT OUTER JOIN invoice ON invoice.corp_address_id = customer.customer_id
), ar_status AS(
SELECT
invoice.corp_address_id
,invoice.name
,invoice.invoice_no
,invoice.invoice_date
,invoice.payment_date
,invoice.net_due_date
,invoice.pay_date_executed_interval
,CASE WHEN pay_date_executed_interval < 0 THEN 'Acceptable' ELSE 'Not Acceptable' END status
,customer_1 .credit_limit
,customer_1 .credit_limit_used
,customer_1.credit_available
,CASE WHEN customer_1.credit_limit <> 0 THEN (1- (customer_1.credit_available / customer_1.credit_limit )) ELSE 0 END percentage_limit_used
FROM invoice
LEFT OUTER JOIN customer_1 ON customer_1.customer_id = invoice.corp_address_id
GROUP BY
invoice.corp_address_id
,invoice.name
,invoice.invoice_no
,invoice.invoice_date
,invoice.payment_date
,invoice.net_due_date
,customer_1 .credit_limit
,customer_1 .credit_limit_used
,customer_1.credit_available
,pay_date_executed_interval
) SELECT
corp_address_id
,name
,SUM (ar_status.pay_date_executed_interval ) / COUNT (ar_status.name) average
,status
,credit_limit
,credit_limit_used
,credit_available
FROM ar_status
GROUP BY
corp_address_id
,name
,status
,credit_limit
,credit_limit_used
,credit_available
,pay_date_executed_interval
Please let me know. Thanks
September 28, 2012 at 3:46 pm
Have a look at the average function http://msdn.microsoft.com/en-us/library/ms177677.aspx
Beyond that if you would like some more help can you please read the first link in my signature about how to post DDL and Sample data in order to have your question answered faster?
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
September 28, 2012 at 3:54 pm
--would be appreciate if next time you post sample data like this, either that or post your credit card number we can charge to 🙂
declare @temp table(Corp_address int, paid_date_interval numeric(9,2), credit_limit int)
insert into @temp(Corp_address, paid_date_interval, credit_limit)
select 10008, 21, 10000 union all
select 10008, 32, 10000 union all
select 10008, 20, 10000 union all
select 10009, 20, 10000 union all
select 10009, 10, 10000
--solution 1
select
Corp_address, SUM(paid_date_interval)/COUNT(*) as total, credit_limit
from @temp
group by Corp_address,credit_limit
--solution 2
select
Corp_address, avg(paid_date_interval) as total, credit_limit
from @temp
group by Corp_address,credit_limit
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply