## Sunday, November 29, 2015

### SQL – Recursive Date with CTE

In the daily analytical process, we need to verify the daily prices for the particular stack. It’s become very difficult if we need to get the last two or more year price performance because we never know that when price is missing. To get the price missing date, we need to create the mirror calendar date for that date range. With the help of this mirror date calendar, we can easily find out the price missing date. To generate the Calendar Date, CTE (Common Table Expression) would be very useful due to its recursive nature.

To get the recursive date, we need two date parameters such as start date to end date where start date will introduced the starting date of the calendar and end date will introduced the last date of the calendar as given below:

--- Input parameters
--- Start date of the calendar

--- End Date of the calendar
@inpEndDate DATE=GETDATE()

SELECT StartDate=@inpStartDate, EndDate=@inpEndDate

 StartDate EndDate 11/29/2013 11/29/2015

In our example, we are going to get the calendar date having DayDate, NameOfDay, NameOfMonth, YearOf as given below:

--- Input parameters
--- Start date of the calendar

--- End Date of the calendar
@inpEndDate DATE=GETDATE()

SELECT StartDate=@inpStartDate, EndDate=@inpEndDate

---- Common Table Expression
;WITH GetCalander (DayDate, NameOfDay, NameOfMonth, YearOf)
AS
(
---- Block 1
---- This block will create the base of table to generate the
---- First record of the calendar
SELECT DayDate=@inpStartDate,
NameOfDay=DateName(DW,@inpStartDate),
NameOfMonth=DateName(M,@inpStartDate),
YearOf=YEAR(@inpStartDate)

UNION ALL
---- Block 2
---- This block will use the first block and
---- will add the 1 day in the current date of the GetCalander
---- This will produce a resultset till the end of the @inpEndDate
FROM GetCalander
WHERE DayDate<=@inpEndDate
)

---- Pull the calendar data
SELECT * FROM GetCalander
---- Set to get all data
OPTION (MAXRECURSION 0)

---- Calendar should have the folloing data till the end of the end date parameter

 DayDate NameOfDay NameOfMonth YearOf 11/29/2013 Friday November 2013 11/30/2013 Saturday November 2013 12/1/2013 Sunday December 2013 12/2/2013 Monday December 2013 12/3/2013 Tuesday December 2013 12/4/2013 Wednesday December 2013 12/5/2013 Thursday December 2013 12/6/2013 Friday December 2013 12/7/2013 Saturday December 2013 12/8/2013 Sunday December 2013 12/9/2013 Monday December 2013 12/10/2013 Tuesday December 2013 12/11/2013 Wednesday December 2013 12/12/2013 Thursday December 2013 12/13/2013 Friday December 2013 12/14/2013 Saturday December 2013 12/15/2013 Sunday December 2013

So, CTE is the best way to get the this kind of the data based on the some condition where we need to recursive functionality.

# This beautiful feature of the SQL Server was introduced within SQL Server 2012 released. A sequence object is nothing but it’s a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. Sequences, unlike identity columns, are not associated with specific tables.

Sequences generated by the Sequence object are similar to identity property values; however, it is not tied to one table. A sequence can be defined as any integer data type. If the data type is not specified, a sequence defaults to bigint.
Remember, a SEQUENCE is not an IDENTITY. The best things is that we can use the sequence object to generate the numeric values as in an ascending or descending order at a defined intervals and can be configured to restart (cycle) when exhausted. Applications refer to a sequence object to retrieve its next value.
The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.
CREATE SEQUENCE Statement
As we stated that it’s not an Identity, a sequence is created independently of the tables by using the CREATE SEQUENCE statement. These Options enable us to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance of the application. Sequence object can be referenced by applications directly and can also be shared by many rows or many tables.
To understand the sequences, we can create use them as the following types-
• Single Sequences
• Multiple Sequences
• Recycling Sequences
Single Sequences - In this scenario, we are using a sequence number in a single table. To this we need to create a sequence object and target table to use it as showing below:
 USE SQLDemo Go ---- Create Sequence CREATE SEQUENCE dbo.EmployeeCode as int START WITH 2015 INCREMENT BY 5; GO ---- Create Employee Table CREATE TABLE dbo.EmployeeMaster ( EmpCode Int,  EmpName Varchar(10),  EmpAge Int  )  Go
After creating the sequence object and target table, we need to fill the data into the employee master table with the help of the sequence object as given below:
USE SQLDemo
Go

--- Call Sequence Object
SELECT NEXT VALUE FOR dbo.EmployeeCode
 No Column Name 2015

---- Call Sequence Object with column name
SELECT NEXT VALUE FOR dbo.EmployeeCode
Go
 EmpCode 2020

---- Insert Values into Employee Master table with sequence object
INSERT INTO dbo.EmployeeMaster (EmpCode, EmpName, EmpAge) Values
(NEXT VALUE FOR dbo.EmployeeCode, 'Ryan Arjun', 25),
(NEXT VALUE FOR dbo.EmployeeCode, 'Will Smith', 35),
(NEXT VALUE FOR dbo.EmployeeCode, 'Lucy Gray', 18)

---- Select records from the table
SELECT EmpCode, EmpName, EmpAge FROM dbo.EmployeeMaster
 EmpCode EmpName EmpAge 2025 Ryan Arjun 25 2030 Will Smith 35 2035 Lucy Gray 18

Multiple Sequences: We have already known that sequence object can be referenced by applications directly and can also be shared by many rows or many tables. To understand a sequence number in multiple tables, we are going to take a student class master example where registration number is common for every class as shown below:

Use SQLDemo
GO

---- Create Reg Number Sequence Object
CREATE SEQUENCE dbo.RegNumber
AS int
INCREMENT BY 1;
GO

--- Create BCA Student Class Table
CREATE TABLE dbo.StudentBCA
(
RegNumber Int Primary Key
default(NEXT VALUE FOR dbo.RegNumber),
RollNumber Int Identity(1,1),
StudentName Varchar(25)
)
--- Create BBA Student Class Table
CREATE TABLE dbo.StudentBBA
(
RegNumber Int Primary Key
default(NEXT VALUE FOR dbo.RegNumber),
RollNumber Int Identity(1,1),
StudentName Varchar(25)
)
--- Create MCA Student Class Table
CREATE TABLE dbo.StudentMCA
(
RegNumber Int Primary Key
default(NEXT VALUE FOR dbo.RegNumber),
RollNumber Int Identity(1,1),
StudentName Varchar(25)
)
--- Create MBA Student Class Table
CREATE TABLE dbo.StudentMBA
(
RegNumber Int Primary Key
default(NEXT VALUE FOR dbo.RegNumber),
RollNumber Int Identity(1,1),
StudentName Varchar(25)
)

---- Insert Values into the respective tables
INSERT INTO dbo.StudentBBA (StudentName) Values
('Ryan Arjun'),('Will Smith'),('Lucy Grey')
INSERT INTO dbo.StudentBCA (StudentName) Values
('Arjun Singh'),('Smith Blue'),('Grey White')
INSERT INTO dbo.StudentMBA (StudentName) Values
('Alice Wolf'),('Will Johnson'),('Apple Grey')
INSERT INTO dbo.StudentMCA (StudentName) Values
('Anil Arjun'),('Gerry Smith'),('Neil Grey')

---- Fetch records from the respective tables
SELECT RegNumber,RollNumber, StudentName, Class='BBA'
FROM dbo.StudentBBA
UNION
SELECT RegNumber,RollNumber, StudentName, Class='BCA'
FROM dbo.StudentBCA
UNION
SELECT RegNumber,RollNumber, StudentName, Class='MBA'
FROM dbo.StudentMBA
UNION
SELECT RegNumber,RollNumber, StudentName, Class='MCA'
FROM dbo.StudentMCA
 RegNumber RollNumber StudentName Class 201501 1 Ryan Arjun BBA 201502 2 Will Smith BBA 201503 3 Lucy Grey BBA 201504 1 Arjun Singh BCA 201505 2 Smith Blue BCA 201506 3 Grey White BCA 201507 1 Alice Wolf MBA 201508 2 Will Johnson MBA 201509 3 Apple Grey MBA 201510 1 Anil Arjun MCA 201511 2 Gerry Smith MCA 201512 3 Neil Grey MCA

Recycling Sequences- Generating repeating sequence numbers in a result set is another very useful feature of the sequence objects where we can set the recycling limit of the sequences after reaching the maximum value of the sequence as given below:
 Use SQLDemo Go ---- Create Sequence with CYCLE CREATE SEQUENCE dbo.RecyclingSequence AS TINYINT START WITH 2 ---- Start Position INCREMENT BY 1 ---- Increment By MINVALUE  0 ---- minimum value MAXVALUE  5 CYCLE ---- Set recycle again ---- Select value from the sequence object SELECT NEXT VALUE FOR dbo.RecyclingSequence as RecycleNumber, Getdate() as RunDate SELECT NEXT VALUE FOR dbo.RecyclingSequence as RecycleNumber, Getdate() as RunDate SELECT NEXT VALUE FOR dbo.RecyclingSequence as RecycleNumber, Getdate() as RunDate SELECT NEXT VALUE FOR dbo.RecyclingSequence as RecycleNumber, Getdate() as RunDate SELECT NEXT VALUE FOR dbo.RecyclingSequence as RecycleNumber, Getdate() as RunDate
Alter Sequence Object: we can alter the existing sequence objects with the help of the alter command as given below:
 Use SQLDemo Go ---- Alter Sequence with CYCLE ALTER SEQUENCE dbo.RecyclingSequence RESTART WITH 3 INCREMENT BY 2 MINVALUE  2 MAXVALUE  10

Drop Sequence Objects: we can drop the existing sequence objects with the help of the drop command as given below:
 Use SQLDemo Go ---- drop Sequence Objects Drop Sequence dbo.RecyclingSequence
Benefits of Using Sequences
In the real world, there are lots of scenarios where we can use sequences instead of identity columns in the following:
1. If the application requires a number before the insert into the table is made.
2. The business application requires the sharing a single series of numbers for multiple tables or multiple columns within a table.
3. The application must restart the number series when a specified number is reached.
4. The application requires sequence values to be sorted by another field.
5. An application requires multiple numbers to be assigned at the same time.
6. We need to change the specification of the sequence, such as the increment value.
Limitations of Sequence Objects
1. Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. To prevent sequence values from being changed, use an update trigger on the table to roll back changes.
2. Uniqueness is not automatically enforced for sequence values. The ability to reuse sequence values is by design.
3. The sequence object generates numbers according to its definition, but the sequence object does not control how the numbers are used.
4. If there are multiple instances of the NEXT VALUE FOR function specifying the same sequence generator within a single Transact-SQL statement, all those instances return the same value for a given row processed by that Transact-SQL statement.
5. This behaviour is consistent with the ANSI standard.