Best table structure for Monthly Data

• vbwrangler

  SSC Journeyman

  Points: 93

  More actions

  September 27, 2012 at 11:45 am

  #393270

  I have the need to create a table for imports. The Table will have monthly totals coming from another system. The structure I am getting from the other system would be like CustomerNumber, CustomerName, Jan-2012Sales, Feb-2012Sales, Mar-2012Sales,.. etc. Rather than hard coding monthly columns i.e. Jan-2012, Feb-2012,... I was looking for a structure that would be more flexible and not need the maintenance of adding columns when you run out.

  Btw this database may be moved into SSAS so a date dimension or star schema may be in the right direction.

  vbwrangler

• cafescott

  SSCarpal Tunnel

  Points: 4724

  More actions

  September 27, 2012 at 11:53 am

  #1543030

  If you are using SSIS, you can have a script task that dynamically adds the new columns that you need. It can determine what the current month and year are and then run an "alter table add column" statement. If you do this at the top of the package you can be ready to get the data in subsequent steps.

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  September 27, 2012 at 2:57 pm

  #1543112

  How about something like this?

  CREATE TABLE dbo.CustomerSales

  (

  CustomerNumber INT,

  CustomerName VARCHAR(100),

  SalesYear INT CONSTRAINT [ck_dbo.CustomerSales.SalesYear] CHECK (SalesYear BETWEEN 1990 AND 2100),

  JanuarySales DECIMAL(20, 4),

  FebruarySales DECIMAL(20, 4),

  MarchSales DECIMAL(20, 4),

  AprilSales DECIMAL(20, 4),

  MaySales DECIMAL(20, 4),

  JuneSales DECIMAL(20, 4),

  JulySales DECIMAL(20, 4),

  AugustSales DECIMAL(20, 4),

  SeptemberSales DECIMAL(20, 4),

  OctoberSales DECIMAL(20, 4),

  NovemberSales DECIMAL(20, 4),

  DecemberSales DECIMAL(20, 4)

  );

  GO

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• vbwrangler

  SSC Journeyman

  Points: 93

  More actions

  September 27, 2012 at 3:03 pm

  #1543114

  SSCarpal Tunnel

  Thanks yes, I had thought of this one. I am just not familiar enough with SSAS to say that would be a smooth transition.