SQL syntax for average

I am trying to calculate the average days to pay for a customer. The query below returns the diffenence between the invoice and the last payment. This query works OK. Dates are stored as BigInt, 20081112.

SELECT     DATEDIFF(d, SUBSTRING(CAST(MIN(doc_dt) AS char), 5, 2) + '/' + SUBSTRING(CAST(MIN(doc_dt) AS char), 7, 2) + '/' + SUBSTRING(CAST(MIN(doc_dt)
                      AS char), 1, 4), SUBSTRING(CAST(MAX(doc_dt) AS char), 5, 2) + '/' + SUBSTRING(CAST(MAX(doc_dt) AS char), 7, 2) + '/' + SUBSTRING(CAST(MAX(doc_dt)
                      AS char), 1, 4))
FROM         AROPNHST_SQL
WHERE     (apply_to_no IN
                          (SELECT     Apply_To_No
                            FROM          AROpnHst_SQL
                            WHERE      Doc_Type = 'I' AND Doc_Dt >= 19941201 AND Doc_Dt <= 19941231 AND Cus_no LIKE ('%100')))
GROUP BY apply_to_no

The above query gives me the total days to pay for each invoice. What I want is the average for all the invoices.
When I try
Select Avg(the above query) from AROpnHst_SQL
  I receive the following error:
Incorrect error near the keyword Select.
Any suggestions?
thanks,
pat
mpdillonAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
select totalaverage = avg(daysval)
from
(
SELECT     apply_to_no, daysval = DATEDIFF(d, SUBSTRING(CAST(MIN(doc_dt) AS char), 5, 2) + '/' + SUBSTRING(CAST(MIN(doc_dt) AS char), 7, 2) + '/' + SUBSTRING(CAST(MIN(doc_dt)
                      AS char), 1, 4), SUBSTRING(CAST(MAX(doc_dt) AS char), 5, 2) + '/' + SUBSTRING(CAST(MAX(doc_dt) AS char), 7, 2) + '/' + SUBSTRING(CAST(MAX(doc_dt)
                      AS char), 1, 4)), counter = count(*)
FROM         AROPNHST_SQL
WHERE     (apply_to_no IN
                          (SELECT     Apply_To_No
                            FROM          AROpnHst_SQL
                            WHERE      Doc_Type = 'I' AND Doc_Dt >= 19941201 AND Doc_Dt <= 19941231 AND Cus_no LIKE ('%100')))
GROUP BY apply_to_no
) a
0
 
hardikbeitCommented:
You can also do this by creating "Temporary table" Or "Table variable".
1. SELECT     DATEDIFF(d, SUBSTRING(CAST(MIN(doc_dt) AS char), 5, 2) + '/' + SUBSTRING(CAST(MIN(doc_dt) AS char), 7, 2) + '/' + SUBSTRING(CAST(MIN(doc_dt)
                      AS char), 1, 4), SUBSTRING(CAST(MAX(doc_dt) AS char), 5, 2) + '/' + SUBSTRING(CAST(MAX(doc_dt) AS char), 7, 2) + '/' + SUBSTRING(CAST(MAX(doc_dt)
                      AS char), 1, 4)) as CalculatedSalary
INTO #salary
FROM         AROPNHST_SQL
WHERE     (apply_to_no IN
                          (SELECT     Apply_To_No
                            FROM          AROpnHst_SQL
                            WHERE      Doc_Type = 'I' AND Doc_Dt >= 19941201 AND Doc_Dt <= 19941231 AND Cus_no LIKE ('%100')))
GROUP BY apply_to_no
Then do the average like:  Select Avg(CalculatedSalary) from #salary
2.
Create table @Salary ( CalculatedSalary  Bigint)
Insert into @Salary ( CalculatedSalary  )
SELECT     DATEDIFF(d, SUBSTRING(CAST(MIN(doc_dt) AS char), 5, 2) + '/' + SUBSTRING(CAST(MIN(doc_dt) AS char), 7, 2) + '/' + SUBSTRING(CAST(MIN(doc_dt)
                      AS char), 1, 4), SUBSTRING(CAST(MAX(doc_dt) AS char), 5, 2) + '/' + SUBSTRING(CAST(MAX(doc_dt) AS char), 7, 2) + '/' + SUBSTRING(CAST(MAX(doc_dt)
                      AS char), 1, 4)) as CalculatedSalary
FROM         AROPNHST_SQL
WHERE     (apply_to_no IN
                          (SELECT     Apply_To_No
                            FROM          AROpnHst_SQL
                            WHERE      Doc_Type = 'I' AND Doc_Dt >= 19941201 AND Doc_Dt <= 19941231 AND Cus_no LIKE ('%100')))
GROUP BY apply_to_no
Then do the average: Select Avg(CalculatedSalary) from @Salary
0
 
mpdillonAuthor Commented:
Thanks, just what I was looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.