Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL syntax for average

Posted on 2008-11-12
3
Medium Priority
?
309 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:mpdillon
3 Comments
 
LVL 60

Accepted Solution

by:
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

by:hardikbeit
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

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question