May 28, 2008 at 9:17 am
I have a query that works in the Microsoft SQL Server Management Studio, but not as a database query in Excel, with an ODBC connection.
First, it builds a table in memory with the TABLE datatype, that contains the daily summary of materials used from our inventory transaction table.
Then, a SELECT query follows that shows a list of those materials, with the average of the daily summaries just built (see above).
My report needs to show the average of the daily total usage of a given material, not just the average of each issue over the course of a day.
Why does this not work as a database query in Excel, and are there different ways of approaching this problem that would work?
May 28, 2008 at 11:16 am
Believe it or not - you should be able to do all of that in a single query statement these days, using an AVERAGE() OVER on the summed expressions.
Something like:
;with SumCTE as (
select dayvalue, itemID, sum(itemvalue) as dayitemTot
from myTable
group by davalue,itemID)
select SumCTE,*, average(dayitemTot) over (Partition by DayValue)
from SumCTE.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply