SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pulling the average data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tantcu
Yak Posting Veteran

USA
58 Posts

Posted - 09/28/2012 :  17:40:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 09/28/2012 :  19:09:28  Show Profile  Visit webfred's Homepage  Reply with Quote
AVG(paid_date_interval)


Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000