Getting specific result in subquery of a query

[chc5286]

So I am trying to get the sum of all "scriptamount" for each "practice" for the previous month in one field, and then add two more fields. One for 2 months ago and then one for 3 months ago. This will allow me to see the growth for each "practice" over the past 3 months. However, I am stuck on the first step. The below subquery returns the the sum of "scriptamount" for the entire month. How can I have this query return the sum for each practice instead of for the entire month? Thanks in advance!


SELECT SourceScriptsByMonth.Practices,
(SELECT sum(scriptamount)
FROM sourcescriptsbymonth
WHERE month(datefilled) = month(date())-1
AND year(datefilled)=year(date()))
AS [Last month]
FROM SourceScriptsByMonth
GROUP BY practices;

 

[vbaInet]

Have you thought about using a Crosstab query?

 

[Guus2005]

So I am trying to get the sum of all "scriptamount" for each "practice" for the previous month in one field, and then add two more fields. One for 2 months ago and then one for 3 months ago. This will allow me to see the growth for each "practice" over the past 3 months. However, I am stuck on the first step. The below subquery returns the the sum of "scriptamount" for the entire month. How can I have this query return the sum for each practice instead of for the entire month? Thanks in advance!

SELECT SourceScriptsByMonth.Practices, 
          (SELECT sum(scriptamount) 
             FROM sourcescriptsbymonth 
             WHERE month(datefilled) = month(date())-1 
                AND year(datefilled)=year(date())
                [COLOR="Red"]AND practice = A.practice)[/COLOR] 
     AS [Last month]
FROM SourceScriptsByMonth[COLOR="red"] A[/COLOR]
GROUP BY practices;

You have to connect to outer query to the inner query. See my comment in Red.

 

[chc5286]

Thanks Guus. This did work but the query took an extremely long time to load and was freezing the program.

A crosstab won't work for me because I want to put in growth % next to each month. I think this type of report is better served by Excel.. unless anyone has a different idea?

 

[vbaInet]

A crosstab won't work for me because I want to put in growth % next to each month.

That's new information!

In a spreadsheet show show us what records you currently have and below that what you would like it to be transposed into.

 

[Guus2005]

For each record in SourceScriptsByMonth the sum(scriptamount) was calculated for the product in that record.
If you have 5 records for a certain product the sum was calculated five times, slowing done the performance.

To speed things up create a temp table for each product for the year and month you need.
Join this temp table to the original query and you will see that it is lot faster. Calculations are stored in the temp table once for each product/month/year.

If you need a different month, empty the temp table and start over.

HTH

 

[vbaInet]

Temp table is one way but it's not really need. It can all be done in one query but we just need to see exactly the OP's desired output.

 

[Guus2005]

As the op pointed out running a single query is quite slow. You can speed things up by using a temp table.

HTH;D

 

[vbaInet]

It's the subquery that's making it slow. Also, the fields we're joining against is making it slow too.