SQL Server 2008 Sum records by month and create table

SELECT PC,Year(Date),Month(Date), ProdID, Sum(Gallons) FROM table1 group by PC,Year(Date),Month(Date), ProdID

>> This table will be updated nightly.<<
You mean once a month? If so then create your table and use the following query (create a job to run it)

insert newtable
select pc, year(date), month(date), prodid, sum(gallons)
from yourtable
group by pc, year(date), month(date), prodid

It the job runs daily I guess you will have to update the existing values
update a
set a.totalgals = b.totalgal
from newtable a
inner join (select pc, year(date) as yr, month(date) as mth, prodid, sum(gallons) as totalgal
 from yourtable
 group by pc, year(date), month(date), prodid
 ) b on a.pc = b.pc and a.prodid = b.prodid and a.year = b.yr and a.month = b.mth
Of course you will reach a point that you will have to create new rows to accomodate new values so I think this should do it
insert newtable
select pc, year(date), month(date), prodid, sum(gallons)
from yourtable b
where not exists(select 1 from newtable a where a.pc = b.pc and a.prodid = b.prodid and a.year = year(b.date) and a.month = month(b.date))
group by pc, year(date), month(date), prodid  

The query to retreive the aggregated data would look like the following, but I would suggest to create a view rather than a table.

SELECT PC,
       datepart(year,Date) Year,
       datepart(month,Date) Month,
       ProdId,
       sum(Gallons) TotalGals
  FROM YourTable
 GROUP BY PC,
          datepart(year,Date) Year,
          datepart(month,Date) Month,
          ProdId

Select allOpen in new window

If you want month names like "December" then you need to use the datename function e.g. DATENAME(month,mydate)

However, would highly recommend that you keep that date column as a datetime (or date in SQL2008) datatype so you can do date chronology type things rather than string fields. Views / Reports etc will be able to easily show in a variety of formats.

Easiest way to retain as a datetime is to use the datefunctions e.g. select dateadd(month,datediff(month,0,getdate()),0) will be the first of the month.

Now, you dont really need a different table if you don't want. You can create a "view" of the data. A View is simply a stored select statement (for the ease of a definition), but you can use it like any other table in select statements and so on. Once defined, it is there to use at your leisure, the definition become part of the database and the real data is in the table... Avoids having to rebuild tables, resynch, keeping the "one truth of data" reconciled etc...

create view vw_pc_details as
select PC
, dateadd(month,datediff(month,0,[date]),0) as Period
, Prodid
, Sum(Gallons) as TotalGals
From pc_details
Group by PC,dateadd(month,datediff(month,0,[date]),0), prodid
go


Now, that gives us a queriable data source e.g.

select * from vw_pc_details

And all the group by and everything else is done for you as part of the view definition.

Then to get it exactly the way you want, can then do:

select PC, year(period) as [year], datename(month,period) as [month], prodid, totalgals from vw_pc_details

and you can use "where" clauses, sort, do more group by, export etc and it is all based on the one data table.

If you still want a seperate table, please let me know... in the meantime, please consider the above.

Sorry for the delay in responding.

Mark is it possible to do what you describe if the original source of the data is a Linked Informix RISC server. I have been grabbing the data from the RISC and basically creating a mirror table of the data that I need for the applications that use it. I like what you have done but I have never worked with a view before.

Thanks for all the input. Mark your response got me headed more in the direction I needed to go. I ended up creating a job that uses an open query on the linked server to create the new table within my application db. The job runs once nightly and the the performance between the application and the SQL table is excellent.