September 24, 2015 at 1:48 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 a query which 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 🙂
September 24, 2015 at 2:29 am
Here's how you do it:
SELECT
s.*,
[ForecastDate] = DATEADD(DAY, x.n, s.ForecastStartDate)
FROM #STG s
CROSS APPLY (
SELECT TOP(1 + DATEDIFF(DAY, s.ForecastStartDate, s.ForecastEndDate))
n = -1 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (SELECT x = 0 FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a (x),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b (x),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c (x)
) d
) x
The CROSS APPLY block is an inline tally table restricted to n rows where n is the difference in days between the startdate and the enddate, plus one.
Holler if you need further help.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 24, 2015 at 5:24 am
Hi Used below query to get the desired output.
;with cte AS (SELECT [ProgramId]
,[ProgramVersionId]
,[WRINPrefix]
,[CoOpId]
,[DCId]
,[ForecastStartDate]
,[ForecastEndDate]
,[TotalUnits]
,[RestCounts]
,[ADU]
FROM ForecastNonStandard_STG
UNION ALL
SELECT [ProgramId]
,[ProgramVersionId]
,[WRINPrefix]
,[CoOpId]
,[DCId]
,DATEADD(day,1,ForecastStartDate)
,[ForecastEndDate],[TotalUnits]
,[RestCounts]
,[ADU]
FROM cte
WHERE ForecastStartDate < [ForecastEndDate])
SELECT [ProgramId]
,[ProgramVersionId]
,[WRINPrefix]
,[CoOpId]
,[DCId], ForecastStartDate ForecastDate, [TotalUnits]
,[RestCounts]
,[ADU]
FROM cte
ORDER BY [ProgramId]
,[ProgramVersionId],ForecastStartDate
Thank you so much to all of you
September 24, 2015 at 5:30 am
vipin_jha123 (9/24/2015)
Hi Used below query to get the desired output.;with cte AS (SELECT [ProgramId]
,[ProgramVersionId]
,[WRINPrefix]
,[CoOpId]
,[DCId]
,[ForecastStartDate]
,[ForecastEndDate]
,[TotalUnits]
,[RestCounts]
,[ADU]
FROM ForecastNonStandard_STG
UNION ALL
SELECT [ProgramId]
,[ProgramVersionId]
,[WRINPrefix]
,[CoOpId]
,[DCId]
,DATEADD(day,1,ForecastStartDate)
,[ForecastEndDate],[TotalUnits]
,[RestCounts]
,[ADU]
FROM cte
WHERE ForecastStartDate < [ForecastEndDate])
SELECT [ProgramId]
,[ProgramVersionId]
,[WRINPrefix]
,[CoOpId]
,[DCId], ForecastStartDate ForecastDate, [TotalUnits]
,[RestCounts]
,[ADU]
FROM cte
ORDER BY [ProgramId]
,[ProgramVersionId],ForecastStartDate
Thank you so much to all of you
Using a recursive CTE for row generation is a very expensive choice compared to the tally table version I posted above.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply