Pulling the average data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pulling the average data

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-09-28 : 17:40:53

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-28 : 19:09:28

AVG(paid_date_interval)

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources