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
DECLARE @inpStartDate DATE=DATEADD(YY,-2,GETDATE()),

--- 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
DECLARE @inpStartDate DATE=DATEADD(YY,-2,GETDATE()),

--- 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
SELECT DayDate=DateADD(D,1,DayDate),
NameOfDay=DateName(DW,DateADD(D,1,DayDate)),
NameOfMonth=DateName(M,DateADD(D,1,DayDate)),
YearOf=YEAR(DateADD(D,1,DayDate))
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.

Tuesday, November 17, 2015

SQL - Sequence Object

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
    START WITH 201501
    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.

Popular Posts