?
Solved

SQL Server 2008 Sum records by month and create table

Posted on 2009-12-23
8
Medium Priority
?
425 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
Comment
Question by:IronDriver
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 9

Expert Comment

by:Stephan_Schrandt
ID: 26113530
SELECT PC,Year(Date),Month(Date), ProdID, Sum(Gallons) FROM table1 group by PC,Year(Date),Month(Date), ProdID
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26113534
>> 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

by:ralmada
ID: 26113585
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 6

Expert Comment

by:FVER
ID: 26113592
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

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 26123609
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

by:IronDriver
ID: 26154199
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

by:
Mark Wills earned 2000 total points
ID: 26154712
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

by:IronDriver
ID: 31669474
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

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

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

Join & Ask a Question