September 24, 2015 at 1:56 am
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 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply