Solved

# SQL Server 2008 Sum records by month and create table

Posted on 2009-12-23
375 Views
Last Modified: 2012-05-08
I have a table that has records added to it daily.
Ex.
Rec ID   PC            Date              ProdId      Gallons
1000     123        12/3/2009       1             1000
1001     111        12/4/2009       2             1250

There are about 40 unique PC's and 8 unique ProdId's

I would like to create a new table that has the data as a month summary of each PC's ProdId's

Like...
PC        Year         Month         ProdId        TotalGals
123       2009       December       1               85,250
.
.
.

This table will be updated nightly.

Thanks in advance!
0
Question by:IronDriver
8 Comments

LVL 9

Expert Comment

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

LVL 41

Expert Comment

>> 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
0

LVL 41

Expert Comment

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
0

LVL 6

Expert Comment

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
``````
0

LVL 51

Expert Comment

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.

0

Author Comment

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.
0

LVL 51

Accepted Solution

Might be easiest to think of a view as a shortcut to a more complex select statement. So, instead of having to create an involved select statement every time, we can then more simply select from that view. There can be some performance implications, but generally if constructed right, they should work as per normal selects.

It should be possible to create a linked server and then use a four part table identifier e.g. select * from INFORMIX...MyTable   -- where informix is the name of the linked server.

However there are things that can go bump in the middle of the night. Generally it is fine, but, if it is not needed real time, then a replicated table can mean a certain independance from outer more remote systems, it will almost certainly be faster being a local table (and can tune it to suit the reporting requirement rather than any OLTP bias). Really depends on table sizes, synchronization challenges, accessability, speed, timeliness.

0

Author Closing Comment

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.
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

#### 734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!