Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
detlion1643
Yak Posting Veteran
67 Posts |
 Posted - 2010-04-14 : 09:54:48
|
| We have about 75+ excel files right now (we do all our work in excel). Just recently, I started backing up our work in sql express, just on a local machine, for the time being (it's a start). Well, I seem to be stuck in thinking about a design for a table.The excel files use 5 columns, let's say a,b,c,d,e.A contains an id, b contains a decimal/double (can be blank), c contains a decimal/double, d contains a date, and e contains an integer (can be blank). Only columns a,c, and d matter.These files will only be 'backed up' into sql express if the file changes. So, some days we won't put anything in sql, and most days we'll put a lot of info in. I can't just update the rows where the id's exist - we need to keep the running data for a months time.This is how I was thinking a report would look:id , date1 , date2 , date3 , date3 , date4 , date5, etc....22 , 300.00, 200.00, 300.00, 290.00, 280.00, 270.00 etc...I just can't seem to think of a good design for a table, as all dates will not be pre-defined and only exist for a month, then be backed up and start over the next month.EDIT - We can also have multiple entries per date |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-14 : 10:28:28
|
| How many columns would this report contain? Would the dates in the column headings be dynamic or static? It sounds like you need to use some sort of dynamic pivot. ie. Import the data as is, and then pivot it for the report.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-04-14 : 10:38:32
|
| Well, the report would contain a different amount of columns. Basically, if the excel files were only changed once a day for a month, then the report would contain 1 column for each day + the id column. The dates would be the dates that the data was input into sql.Ex) If I ran the report for the first day of this month:id , 4/1/201022 , 300.00On the 2nd day:id , 4/1/2010, 4/2/201022 , 300.00, 200.00I've never used pivot's before, so kind of a new concept to me. I'll muck around the net for pivot examples and read up on them. |
|
|
|
|
|
|
|
|
variable table (suggestions)