Learn how to a build a cloud-first strategyRegister Now

x
Solved

# SQL syntax for average

Posted on 2008-11-12
Medium Priority
309 Views
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
Incorrect error near the keyword Select.
Any suggestions?
thanks,
pat
0
Question by:mpdillon

LVL 60

Accepted Solution

chapmandew earned 2000 total points
ID: 22947128
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

LVL 3

Expert Comment

ID: 22947852
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

Author Closing Comment

ID: 31516257
Thanks, just what I was looking for.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
###### Suggested Courses
Course of the Month21 days, 1 hour left to enroll