SQL in SSIS

• vipin_jha123

  Hall of Fame

  Points: 3085

  More actions

  September 24, 2015 at 1:56 am

  #399080

  I have 2 table

  STG is my source table and Finaltest is my target table

  I have 2 column in source table.Forecaststartdate and forecastenddate.

  I am looking for ETL will be creating a row for each day starting with the Forecaststartdate ending with the forecastenddate with the same value for the following fields in Finaltest table.

  ProgramId

  ProgramVersionId

  WRINPrefix

  CoOpId

  DCId

  Units

  StoreCount

  AverageDailyUnits

  I have attached the sample output of finaltest table

  Below script help to create both table with data.

  CREATE TABLE [dbo].[Finaltest](

  [ProgramId] [int] NOT NULL,

  [ProgramVersionId] [int] NULL,

  [WRINPrefix] [nvarchar](50) NULL,

  [CoOpId] [bigint] NULL,

  [DCId] [bigint] NULL,

  [ForecastDate] [date] NULL,

  [Units] [int] NULL,

  [StoreCount] [int] NULL,

  [AverageDailyUnits] [int] NULL

  ) ON [PRIMARY]

  CREATE TABLE [dbo].[STG](

  [ProgramId] [int] NOT NULL,

  [ProgramVersionId] [int] NULL,

  [WRINPrefix] [nvarchar](50) NULL,

  [CoOpId] [bigint] NULL,

  [DCId] [bigint] NULL,

  [ForecastStartDate] [date] NULL,

  [ForecastEndDate] [date] NULL,

  [TotalUnits] [int] NULL,

  [RestCounts] [int] NULL,

  [ADU] [int] NULL

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[STG] ([ProgramId], [ProgramVersionId], [WRINPrefix], [CoOpId], [DCId], [ForecastStartDate], [ForecastEndDate], [TotalUnits], [RestCounts], [ADU]) VALUES (7, 130, N'3', 600000000000, 88, CAST(N'2015-09-02' AS Date), CAST(N'2015-09-6' AS Date), NULL, 255, 88560)

  INSERT [dbo].[STG] ([ProgramId], [ProgramVersionId], [WRINPrefix], [CoOpId], [DCId], [ForecastStartDate], [ForecastEndDate], [TotalUnits], [RestCounts], [ADU]) VALUES (104, 128, N'3', 600000000000, 600, CAST(N'2015-09-23' AS Date), CAST(N'2015-09-25' AS Date), NULL, 2, 695)

  ALTER TABLE [dbo].[Finaltest] ADD DEFAULT ((0)) FOR [Units]

  GO

  Thanks in Advance 🙂

• Phil Parkin

  SSC Guru

  Points: 245813

  More actions

  September 24, 2015 at 2:01 am

  #1829045

  You've been here long enough to know not to cross-post. Replies here please.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.