WHILE Loop to Insert Dynamic Date Values into the Table in SQL Server
WHILE Loop statement is used to loop through the body to execute till the Condition evaluates to False.
Since the WHILE condition is evaluated before entering the loop, it is possible that the loop may not execute even once.
Since the WHILE condition is evaluated before entering the loop, it is possible that the loop may not execute even once.
The following WHILE Loop example inserts the Dates, Month, Quarter, Year values till '12/31/2020' into the table.
USE TAMATAM
GO
CREATE TABLE Tbl_Dates ([Date_] [Date] NOT NULL PRIMARY KEY CLUSTERED,
[Week_Number] [Int] NULL,
[Week_Day_Name] [Varchar](50) NULL,
[Month_Number] [Varchar](50) NULL,
[Month_Name] [Varchar](50) NULL,
[Qtr_Name] [Varchar](50) NULL,
[FY_Name] [Varchar](50) NULL,
[Unique_Id] UniqueIdentifier);
-------------------------------------------------[Week_Number] [Int] NULL,
[Week_Day_Name] [Varchar](50) NULL,
[Month_Number] [Varchar](50) NULL,
[Month_Name] [Varchar](50) NULL,
[Qtr_Name] [Varchar](50) NULL,
[FY_Name] [Varchar](50) NULL,
[Unique_Id] UniqueIdentifier);
WHILE Loop :
-------------------------------------------------
DECLARE @Val DATE
SET @Val=GETDATE()
SET @Val=GETDATE()
PRINT(@Val)
Truncate Table Tbl_Dates
--WHILE Loop Begins here
WHILE @Val <= '12/31/2020'
BEGIN
INSERT INTO Tbl_Dates ( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
[Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
'FY' + DateName(YY,@Val),Newid())
SELECT @Val=DATEADD(DD,1,@Val)
END
INSERT INTO Tbl_Dates ( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
[Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
'FY' + DateName(YY,@Val),Newid())
SELECT @Val=DATEADD(DD,1,@Val)
END
--WHILE Loop Ends here
GO
GO
-------------------------------------------------
Output :
Output :
SELECT*FROM Tbl_Dates Order By 1 Desc
SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1 Desc
FY_Name
FY2017
FY2018
FY2019
FY2020
FY2017
FY2018
FY2019
FY2020
-------------------------------------------------
WHILE Loop with BREAK :
-------------------------------------------------
-------------------------------------------------
The BREAK keyword is used to Break the Loop when a specific Condition is True, explained below :
--WHILE Loop Begins here
WHILE @Val <= '12/31/2020'
BEGIN
INSERT INTO Tbl_Dates ( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
[Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
'FY' + DateName(YY,@Val),Newid())
SELECT @Val=DATEADD(DD,1,@Val)
INSERT INTO Tbl_Dates ( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
[Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
'FY' + DateName(YY,@Val),Newid())
SELECT @Val=DATEADD(DD,1,@Val)
IF YEAR(@Val)='2018'
BREAK; ----WHILE Loop Exits/Stops here when Year=2018
END
BREAK; ----WHILE Loop Exits/Stops here when Year=2018
--WHILE Loop Ends here
SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1
GO
GO
-------------------------------------------------
Output :
SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1
FY_Name
FY2017
-------------------------------------------------
WHILE Loop with CONTINUE :Output :
SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1
FY_Name
FY2017
-------------------------------------------------
-------------------------------------------------
The CONTINUE keyword will stop the Loop to execute Next Lines of Code after it, and make the loop to go to beginning again.
--WHILE Loop Begins here
WHILE @Val <= '12/31/2020'
BEGIN
INSERT INTO Tbl_Dates ( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
[Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
'FY' + DateName(YY,@Val),Newid())
SELECT @Val=DATEADD(DD,1,@Val)
INSERT INTO Tbl_Dates ( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
[Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
'FY' + DateName(YY,@Val),Newid())
SELECT @Val=DATEADD(DD,1,@Val)
CONTINUE --The Loop will go to beginning again, and the next lines(The IF condition section) of Code below in the Loop will not execute.
IF YEAR(@Val)='2018' --This line will not execute
BREAK; --This line will not execute
END
BREAK; --This line will not execute
--WHILE Loop Ends here
SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1
GO
-------------------------------------------------
GO
-------------------------------------------------
Output :
SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1 Desc
FY_Name
FY2017
FY2018
SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1 Desc
FY_Name
FY2017
FY2018
FY2019
FY2020
FY2020
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.