[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

I have a query that I run monthly, how do I modify it to work for a quarterly report?

Posted on 2005-04-19
22
Medium Priority
?
210 Views
Last Modified: 2006-11-18
The query I use now for the monthly report involves four tables that all have equal joins.  This is what the query looks like:

field 1:  InvoiceGroup from table Invoice
filed 2:  BillingGroup from table BillingGroup
field 3:  CustomerAccountNumber from table Customer
field 4:  CustomerName from table Customer
field 5:  InvoiceDate from table Invoice
field 6:  DetailTypeID from table InvoiceDetail
filed 7: Amount from table InvoiceDetail

Basically, I put in a date range in InvoiceDate to find the invoice date for the month I want and it pulls up the top 25 customers based on Amount.  How can I modify this query to create a quarterly query (or report) that will sum three months of Amount for each customer and then give me the top 25?

Because this seems like a difficult question to me, and I need it fairly soon, I'm going to make it worth 400 points!
0
Comment
Question by:lhedgecoth
  • 11
  • 10
22 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 13817912
You can format the date like this:

field 5: format([invoiceDate],"q")

That will give you a quarter number

For top 25, use

SELECT TOP 25

at the start of your SQL statement
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13817949
So your first sql line will look something like this:

SELECT TOP 25 InvoiceGroup, BillingGroup, CustomerAccountNumber, CustomerName, format([invoiceDate],"q") as inviocedate, DetailTypeID, Amount
0
 

Author Comment

by:lhedgecoth
ID: 13818122
Okay, here is the first line of my SQL statement (from Access) without the format:

SELECT TOP 25 dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName AS [Billing Group], dbo_Customer.CustomerAcctNumber AS [Acct #], dbo_Customer.CustomerName AS [Customer Name], Sum(dbo_InvoiceDetail.Amount) AS [Total Revenue], dbo_InvoiceDetail.DetailTypeID, dbo_Invoice.InvoiceDate

So do I just paste it at the beginning of the dbo_Inoivce.InvoiceDate, or does it replace that?

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 26

Expert Comment

by:dannywareham
ID: 13818266
Change to this:

SELECT TOP 25 dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName AS [Billing Group], dbo_Customer.CustomerAcctNumber AS [Acct #], dbo_Customer.CustomerName AS [Customer Name], Sum(dbo_InvoiceDetail.Amount) AS [Total Revenue], dbo_InvoiceDetail.DetailTypeID, Format([dbo_Invoice].[InvoiceDate],"Q") AS myDate



0
 

Author Comment

by:lhedgecoth
ID: 13818419
I cut and pasted the code from above, but I'm getting the following Access error message when I try to run it:

You tried to execute a query that does not include the specified expression 'Format([dbo_Invoice].[InvoiceDate],"q")' as part of an aggregate function.

0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13818425
What is your full SQL, including the new TOP 25 bit...?
0
 

Author Comment

by:lhedgecoth
ID: 13818464
SELECT TOP 25 dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName AS [Billing Group], dbo_Customer.CustomerAcctNumber AS [Acct #], dbo_Customer.CustomerName AS [Customer Name], Sum(dbo_InvoiceDetail.Amount) AS [Total Revenue], Format([dbo_Invoice].[InvoiceDate],"q") AS myDate

FROM (dbo_Customer INNER JOIN ((dbo_Invoice INNER JOIN dbo_InvoiceDetail ON dbo_Invoice.InvoiceNumber = dbo_InvoiceDetail.InvoiceNumber) INNER JOIN dbo_InvoiceDetailType ON dbo_InvoiceDetail.DetailTypeID = dbo_InvoiceDetailType.DetailTypeID) ON dbo_Customer.CustID = dbo_Invoice.CustID) INNER JOIN dbo_BillingGroup ON dbo_Customer.BillingGroupID = dbo_BillingGroup.BillingGroupID

WHERE (((dbo_Customer.InvoiceResponsible)=1) AND ((dbo_Customer.AffinityID)<>23))
GROUP BY dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName, dbo_Customer.CustomerAcctNumber, dbo_Customer.CustomerName, dbo_InvoiceDetail.DetailTypeID

HAVING (((dbo_InvoiceDetail.DetailTypeID)=5 Or (dbo_InvoiceDetail.DetailTypeID)=6 Or (dbo_InvoiceDetail.DetailTypeID)=7 Or (dbo_InvoiceDetail.DetailTypeID)=10 Or (dbo_InvoiceDetail.DetailTypeID)=105 Or (dbo_InvoiceDetail.DetailTypeID)=106 Or (dbo_InvoiceDetail.DetailTypeID)=107 Or (dbo_InvoiceDetail.DetailTypeID)=110))
ORDER BY Sum(dbo_InvoiceDetail.Amount) DESC;
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13818542
Lose the GROUP BY:

SELECT TOP 25 dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName AS [Billing Group], dbo_Customer.CustomerAcctNumber AS [Acct #], dbo_Customer.CustomerName AS [Customer Name], Sum(dbo_InvoiceDetail.Amount) AS [Total Revenue], Format([dbo_Invoice].[InvoiceDate],"q") AS myDate
FROM (dbo_Customer INNER JOIN ((dbo_Invoice INNER JOIN dbo_InvoiceDetail ON dbo_Invoice.InvoiceNumber = dbo_InvoiceDetail.InvoiceNumber) INNER JOIN dbo_InvoiceDetailType ON dbo_InvoiceDetail.DetailTypeID = dbo_InvoiceDetailType.DetailTypeID) ON dbo_Customer.CustID = dbo_Invoice.CustID) INNER JOIN dbo_BillingGroup ON dbo_Customer.BillingGroupID = dbo_BillingGroup.BillingGroupID
WHERE (((dbo_Customer.InvoiceResponsible)=1) AND ((dbo_Customer.AffinityID)<>23))
HAVING (((dbo_InvoiceDetail.DetailTypeID)=5 Or (dbo_InvoiceDetail.DetailTypeID)=6 Or (dbo_InvoiceDetail.DetailTypeID)=7 Or (dbo_InvoiceDetail.DetailTypeID)=10 Or (dbo_InvoiceDetail.DetailTypeID)=105 Or (dbo_InvoiceDetail.DetailTypeID)=106 Or (dbo_InvoiceDetail.DetailTypeID)=107 Or (dbo_InvoiceDetail.DetailTypeID)=110))
ORDER BY Sum(dbo_InvoiceDetail.Amount) DESC;
0
 

Author Comment

by:lhedgecoth
ID: 13819027
Still getting error message:

You tried to execute a query that does not include the specified expression 'InvoiceGroup' as part of an aggregate function.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13822548
Try this - I've guessed at your table structures:

SELECT TOP 25 dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName AS [Billing Group], dbo_Customer.CustomerAcctNumber AS [Acct #], dbo_Customer.CustomerName AS [Customer Name], Sum(dbo_InvoiceDetail.Amount) AS [Total Revenue], Format([dbo_Invoice].[InvoiceDate],"q") AS myDate
FROM (dbo_Customer INNER JOIN ((dbo_Invoice INNER JOIN dbo_InvoiceDetail ON dbo_Invoice.InvoiceNumber = dbo_InvoiceDetail.InvoiceNumber) INNER JOIN dbo_InvoiceDetailType ON dbo_InvoiceDetail.DetailTypeID = dbo_InvoiceDetailType.DetailTypeID) ON dbo_Customer.CustID = dbo_Invoice.CustID) INNER JOIN dbo_BillingGroup ON dbo_Customer.BillingGroupID = dbo_BillingGroup.BillingGroupID
WHERE (((dbo_Customer.InvoiceResponsible)=1) AND ((dbo_Customer.AffinityID)<>23))
GROUP BY dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName, dbo_Customer.CustomerAcctNumber, dbo_Customer.CustomerName, Format([dbo_Invoice].[InvoiceDate],"q"), dbo_InvoiceDetail.DetailTypeID
HAVING (((dbo_InvoiceDetail.DetailTypeID)=5 Or (dbo_InvoiceDetail.DetailTypeID)=6 Or (dbo_InvoiceDetail.DetailTypeID)=7 Or (dbo_InvoiceDetail.DetailTypeID)=10 Or (dbo_InvoiceDetail.DetailTypeID)=105 Or (dbo_InvoiceDetail.DetailTypeID)=106 Or (dbo_InvoiceDetail.DetailTypeID)=107 Or (dbo_InvoiceDetail.DetailTypeID)=110))
ORDER BY Sum(dbo_InvoiceDetail.Amount) DESC;
0
 
LVL 44

Expert Comment

by:GRayL
ID: 13824755
How can you have top 25 of a table and the sum of the top 25 in the same query?  Whenever you specify a sum, you will get one record for what ever it is you want to sum, or am I not reading you correctly?
0
 

Author Comment

by:lhedgecoth
ID: 13825542
What the original query does is sum the specific parts of the invoice I want (does not include taxes or late fees) and then gives me the top 25 customers based on the sum [amount].  What I need to do is modify that query to do the same thing for a quarterly report.  
0
 

Author Comment

by:lhedgecoth
ID: 13825609
dannywareham

That one ran (progress!) But it's not choosing data from a quarter, it's choosing data for misc. dates from 2000 - 2005!  Any way to get a little better control over the dates?
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13826313
The misc dates from 2000 - 2005 are because the format will not differenciate between years.
his lets you add a year

SELECT TOP 25 dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName AS [Billing Group], dbo_Customer.CustomerAcctNumber AS [Acct #], dbo_Customer.CustomerName AS [Customer Name], Sum(dbo_InvoiceDetail.Amount) AS [Total Revenue], Format([dbo_Invoice].[InvoiceDate],"q") AS myDate, Format([dbo_Invoice].[InvoiceDate],"YYYY") AS myYear
FROM (dbo_Customer INNER JOIN ((dbo_Invoice INNER JOIN dbo_InvoiceDetail ON dbo_Invoice.InvoiceNumber = dbo_InvoiceDetail.InvoiceNumber) INNER JOIN dbo_InvoiceDetailType ON dbo_InvoiceDetail.DetailTypeID = dbo_InvoiceDetailType.DetailTypeID) ON dbo_Customer.CustID = dbo_Invoice.CustID) INNER JOIN dbo_BillingGroup ON dbo_Customer.BillingGroupID = dbo_BillingGroup.BillingGroupID
WHERE (((dbo_Customer.InvoiceResponsible)=1) AND ((dbo_Customer.AffinityID)<>23))
GROUP BY dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName, dbo_Customer.CustomerAcctNumber, dbo_Customer.CustomerName, Format([dbo_Invoice].[InvoiceDate],"q"), dbo_InvoiceDetail.DetailTypeID
HAVING ((((dbo_InvoiceDetail.DetailTypeID)=5 Or (dbo_InvoiceDetail.DetailTypeID)=6 Or (dbo_InvoiceDetail.DetailTypeID)=7 Or (dbo_InvoiceDetail.DetailTypeID)=10 Or (dbo_InvoiceDetail.DetailTypeID)=105 Or (dbo_InvoiceDetail.DetailTypeID)=106 Or (dbo_InvoiceDetail.DetailTypeID)=107 Or (dbo_InvoiceDetail.DetailTypeID)=110)) AND (Format([dbo_Invoice].[InvoiceDate],"YYYY") = [Enter Year])
ORDER BY Sum(dbo_InvoiceDetail.Amount) DESC;
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13826332
Sorry - typo:


SELECT TOP 25 dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName AS [Billing Group], dbo_Customer.CustomerAcctNumber AS [Acct #], dbo_Customer.CustomerName AS [Customer Name], Sum(dbo_InvoiceDetail.Amount) AS [Total Revenue], Format([dbo_Invoice].[InvoiceDate],"q") AS myDate
FROM (dbo_Customer INNER JOIN ((dbo_Invoice INNER JOIN dbo_InvoiceDetail ON dbo_Invoice.InvoiceNumber = dbo_InvoiceDetail.InvoiceNumber) INNER JOIN dbo_InvoiceDetailType ON dbo_InvoiceDetail.DetailTypeID = dbo_InvoiceDetailType.DetailTypeID) ON dbo_Customer.CustID = dbo_Invoice.CustID) INNER JOIN dbo_BillingGroup ON dbo_Customer.BillingGroupID = dbo_BillingGroup.BillingGroupID
WHERE (((dbo_Customer.InvoiceResponsible)=1) AND ((dbo_Customer.AffinityID)<>23))
GROUP BY dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName, dbo_Customer.CustomerAcctNumber, dbo_Customer.CustomerName, Format([dbo_Invoice].[InvoiceDate],"q"), dbo_InvoiceDetail.DetailTypeID, Format([dbo_Invoice]![InvoiceDate],"yyyy")
HAVING (((dbo_InvoiceDetail.DetailTypeID)=5 Or (dbo_InvoiceDetail.DetailTypeID)=6 Or (dbo_InvoiceDetail.DetailTypeID)=7 Or (dbo_InvoiceDetail.DetailTypeID)=10 Or (dbo_InvoiceDetail.DetailTypeID)=105 Or (dbo_InvoiceDetail.DetailTypeID)=106 Or (dbo_InvoiceDetail.DetailTypeID)=107 Or (dbo_InvoiceDetail.DetailTypeID)=110) AND ((Format([dbo_Invoice]![InvoiceDate],"yyyy"))=[Enter Year]))
ORDER BY Sum(dbo_InvoiceDetail.Amount) DESC;
0
 

Author Comment

by:lhedgecoth
ID: 13827305
This is what it ran:
InvoiceGroup Billing Group Acct # Cust Name  Total Revenue myDate
11050115      Dedicated      10      Customer D      $      1
11050215      Dedicated      10      Customer D      $      1
11050215      Dedicated      41      Customer C      $      1
11050415      Dedicated      41      Customer C      $      2
11050315      Dedicated      41      Customer C      $      1
11050115      Dedicated      41      Customer C      $      1
11050215      Dedicated      31      Customer G      $      1
11050415      Dedicated      31      Customer G      $      2
11050415      Dedicated      4      Customer A      $      2
11050315      Dedicated      4      Customer A      $      1
11050215      Dedicated      4      Customer A      $      1
11050115      Dedicated      4      Customer A      $      1
11050415      Dedicated      24      Customer P      $      2
11050215      Dedicated      24      Customer P      $      1
11050215      Dedicated      85      Customer I      $      1
11050415      Dedicated      85      Customer S      $      2
11050315      Dedicated      85      Customer S      $      1
11050415      Dedicated      55      Customer B      $      2
21050215      Dedicated      74      Customer T      $      1
21050315      Dedicated      74      Customer T      $      1
21050115      Dedicated      74      Customer T      $      1
11050415      Dedicated      42      Customer U      $      2
11050415      Dedicated      42      Customer U      $      2
11050115      Dedicated      42      Customer U      $      1
11050315      Dedicated      42      Customer U      $      1
11050215      Dedicated      42      Customer U      $      1

So I more or less ended up with the top 10 customers for 1/2005 - 4/2005 (the last 6 digits of the Invoice Group are yymmdd).  Not quite what I need.  Do I need a separate query to calculate the total revenue of all customers and then another one to grab the top 25 for the quarter?  
0
 
LVL 26

Assisted Solution

by:dannywareham
dannywareham earned 1600 total points
ID: 13831577
Do you want the first 25 records for a quarter?
What classes as the first 25? The amount?

This query pulls the top 25 from the year that you select, sorts them by amount and sows the quarter they were in.
If you need to show just a single quarter, then we can change the filter so that you can select quarter and year:

SELECT TOP 25 dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName AS [Billing Group], dbo_Customer.CustomerAcctNumber AS [Acct #], dbo_Customer.CustomerName AS [Customer Name], Sum(dbo_InvoiceDetail.Amount) AS [Total Revenue], Format(dbo_Invoice.InvoiceDate,"q") AS myDate
FROM (dbo_Customer INNER JOIN ((dbo_Invoice INNER JOIN dbo_InvoiceDetail ON dbo_Invoice.InvoiceNumber = dbo_InvoiceDetail.InvoiceNumber) INNER JOIN dbo_InvoiceDetailType ON dbo_InvoiceDetail.DetailTypeID = dbo_InvoiceDetailType.DetailTypeID) ON dbo_Customer.CustID = dbo_Invoice.CustID) INNER JOIN dbo_BillingGroup ON dbo_Customer.BillingGroupID = dbo_BillingGroup.BillingGroupID
WHERE (((dbo_Customer.InvoiceResponsible)=1) AND ((dbo_Customer.AffinityID)<>23))
GROUP BY dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName, dbo_Customer.CustomerAcctNumber, dbo_Customer.CustomerName, Format(dbo_Invoice.InvoiceDate,"q"), dbo_InvoiceDetail.DetailTypeID, Format(dbo_Invoice!InvoiceDate,"yyyy")
HAVING (((Format(dbo_Invoice.InvoiceDate,"q"))=[Enter quarter number]) AND ((dbo_InvoiceDetail.DetailTypeID)=5 Or (dbo_InvoiceDetail.DetailTypeID)=6 Or (dbo_InvoiceDetail.DetailTypeID)=7 Or (dbo_InvoiceDetail.DetailTypeID)=10 Or (dbo_InvoiceDetail.DetailTypeID)=105 Or (dbo_InvoiceDetail.DetailTypeID)=106 Or (dbo_InvoiceDetail.DetailTypeID)=107 Or (dbo_InvoiceDetail.DetailTypeID)=110) AND ((Format([dbo_Invoice]![InvoiceDate],"yyyy"))=[Enter Year]))
ORDER BY Sum(dbo_InvoiceDetail.Amount) DESC;
0
 

Author Comment

by:lhedgecoth
ID: 13835000
I need the top 25 customers for the quarter based on the amount, however, I need the results to show each customer only once.  So, each of the top 25 customers should be shown once with their grand total amount for the quarter.  If the same customer shows up 3 or four times, I only get the top 8 or 9 customers for the quarter.  Does that make more sense?
0
 
LVL 26

Accepted Solution

by:
dannywareham earned 1600 total points
ID: 13835286
I don't think that can be done in one query.

Try this - let's see how it works with your data:

SELECT TOP 25 dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName AS [Billing Group], dbo_Customer.CustomerAcctNumber AS [Acct #], dbo_Customer.CustomerName AS [Customer Name], Sum(dbo_InvoiceDetail.Amount) AS [Total Revenue], Format(dbo_Invoice.InvoiceDate,"q") AS myDate
FROM (dbo_Customer INNER JOIN ((dbo_Invoice INNER JOIN dbo_InvoiceDetail ON dbo_Invoice.InvoiceNumber = dbo_InvoiceDetail.InvoiceNumber) INNER JOIN dbo_InvoiceDetailType ON dbo_InvoiceDetail.DetailTypeID = dbo_InvoiceDetailType.DetailTypeID) ON dbo_Customer.CustID = dbo_Invoice.CustID) INNER JOIN dbo_BillingGroup ON dbo_Customer.BillingGroupID = dbo_BillingGroup.BillingGroupID
WHERE (((dbo_Customer.InvoiceResponsible)=1) AND ((dbo_Customer.AffinityID)<>23))
GROUP BY dbo_Invoice.InvoiceGroup, dbo_BillingGroup.CompanyName, dbo_Customer.CustomerAcctNumber, dbo_Customer.CustomerName, Format(dbo_Invoice.InvoiceDate,"q"), dbo_InvoiceDetail.DetailTypeID, Format(dbo_Invoice!InvoiceDate,"yyyy")
HAVING (((dbo_InvoiceDetail.DetailTypeID)=5 Or (dbo_InvoiceDetail.DetailTypeID)=6 Or (dbo_InvoiceDetail.DetailTypeID)=7 Or (dbo_InvoiceDetail.DetailTypeID)=10 Or (dbo_InvoiceDetail.DetailTypeID)=105 Or (dbo_InvoiceDetail.DetailTypeID)=106 Or (dbo_InvoiceDetail.DetailTypeID)=107 Or (dbo_InvoiceDetail.DetailTypeID)=110) AND ((Format([dbo_Invoice].[InvoiceDate],"q"))=[Enter quarter number]) AND ((Format([dbo_Invoice]![InvoiceDate],"yyyy"))=[Enter Year]) AND ((Count(dbo_Customer.CustomerName))=1))
ORDER BY Sum(dbo_InvoiceDetail.Amount) DESC;
0
 

Author Comment

by:lhedgecoth
ID: 13836978
The code is working, and I know I can change it to get the top 75 and then just move it into Excel and create a pivot table to get what I need.  But, is it possible to create a second query or report that can do that for me?
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13837146
Change the first line

SELECT TOP 75

As for exporting it, that's a different question...

:-)
0
 

Author Comment

by:lhedgecoth
ID: 13837185
Got ya!  Thanks for your help, I will study the SQL code so I understand!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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