Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post May 31, 2012 03:46 PM by capjlp
May 31, 2012 01:34 PM|LINK
I am building a Grid View using some data. I have records for each customer of there energy usage for 13 to 14 months I want to get a yearly usage for each customer. So i only want to sum the first 12 records for each customer. I have two tables i am
pulling from my customer table and my energy usage table. I have the sum working on all records but not sure how to make the sum only sum up the first 12 records for each customer.
Here is what i have.
SELECT dbo_tblEnergyHistory.CoopCode, dbo_tblCustomer.CustomerName, dbo_tblEnergyHistory.LocationID, dbo_tblCustomer.LMI, dbo_tblCustomer.Income, dbo_tblCustomer.Zip, dbo_tblCustomer.County, Sum(dbo_tblEnergyHistory.kWh_Usage) AS SumOfkWh_Usage, dbo_tblEnergyHistory.DataStatus
FROM dbo_tblCustomer INNER JOIN dbo_tblEnergyHistory ON (dbo_tblCustomer.CoopCode = dbo_tblEnergyHistory.CoopCode) AND (dbo_tblCustomer.LocationID = dbo_tblEnergyHistory.LocationID)
GROUP BY dbo_tblEnergyHistory.CoopCode, dbo_tblCustomer.CustomerName, dbo_tblEnergyHistory.LocationID, dbo_tblCustomer.LMI, dbo_tblCustomer.Income, dbo_tblCustomer.Zip, dbo_tblCustomer.County, dbo_tblEnergyHistory.DataStatus
ORDER BY dbo_tblEnergyHistory.CoopCode, dbo_tblCustomer.CustomerName;
Appreciate the help...
May 31, 2012 01:38 PM|LINK
it think u may use select top (12) rows.
Just try it.
May 31, 2012 01:40 PM|LINK
I thought of that.. But if i do that it only gets teh top 12 customers
I have thousands of customers and they all have 12 to 14 months of usage in the history table so i need to sum up the first 12 records for all customers.
I think i am going to have to do 2 selects perhaps...
May 31, 2012 03:18 PM|LINK
with CTE as
ROW_NUMBER() OVER(PARTITION BY CoopCode, LocationID order by Data) AS RowNum
where DataStatus = 'br'
Sum(e.kWh_Usage) AS SumOfkWh_Usage,
FROM dbo_tblCustomer as c
INNER JOIN CTE as e ON (e.CoopCode = c.CoopCode) AND (e.LocationID = c.LocationID)
where e.RowNum <= 12
Hope this helps.
May 31, 2012 03:33 PM|LINK
Hey that did the trick... Thank you!
What does CTE stand for?
May 31, 2012 03:45 PM|LINK
it stands for Common Table Expression...
May 31, 2012 03:46 PM|LINK
Ok Thank you. I had seen it used before and wondered.