SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system.

Common Table Expressions

--CTE, common table expression: temporary named result set that one can reference within a select, insert...
CREATE TABLE Employees
(
  EmployeeID int NOT NULL PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL,
  ManagerID int NULL
)

SELECT * FROM Employees;

INSERT INTO Employees VALUES (1, 'Ken', 'Thompson', NULL)
INSERT INTO Employees VALUES (2, 'Terri', 'Ryan', 1)
INSERT INTO Employees VALUES (3, 'Robert', 'Durello', 1)
INSERT INTO Employees VALUES (4, 'Rob', 'Bailey', 2)
INSERT INTO Employees VALUES (5, 'Kent', 'Erickson', 2)
INSERT INTO Employees VALUES (6, 'Bill', 'Goldberg', 3)
INSERT INTO Employees VALUES (7, 'Ryan', 'Miller', 3)
INSERT INTO Employees VALUES (8, 'Dane', 'Mark', 5)
INSERT INTO Employees VALUES (9, 'Charles', 'Matthew', 6)
INSERT INTO Employees VALUES (10, 'Michael', 'Jhonson', 6) 

--You can use a recursive CTE to display the hierarchy of employee data.
WITH
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, 
      r.EmpLevel + 1
    FROM Employees e
      INNER JOIN cteReports r
        ON e.ManagerID = r.EmpID
  )
SELECT
  FirstName + ' ' + LastName AS FullName, 
  EmpLevel,
  (SELECT FirstName + ' ' + LastName FROM Employees 
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports 
ORDER BY EmpLevel, MgrID 

CREATE TABLE ItemDetails    
(    
Item_ID int identity(1,1),    
Item_Name VARCHAR(100) NOT NULL,    
Item_Price int NOT NULL,    
Date VARCHAR(100) NOT NULL ,
CONSTRAINT [PK_ItemDetails] PRIMARY KEY CLUSTERED     
(     
[Item_ID] ASC     
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]     
) ON [PRIMARY]     

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Access Point',950,'2017-02-10')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('CD',350,'2017-02-13')     
Insert into ItemDetails(Item_Name,Item_Price,Date) values('Desktop Computer',1400,'2017-02-16')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('DVD',1390,'2017-03-05')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('DVD Player',450,'2017-05-07')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('Floppy',1250,'2017-05-07')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('HDD',950,'2017-07-10')       
Insert into ItemDetails(Item_Name,Item_Price,Date) values('MobilePhone',1150,'2017-07-10')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('Mouse',399,'2017-08-12')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('MP3 Player ',897,'2017-08-14')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('Notebook',750,'2017-08-16')     
Insert into ItemDetails(Item_Name,Item_Price, Date) values('Printer',675,'2017-07-18')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('RAM',1950,'2017-09-23')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('Smart Phone',679,'2017-09-10')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('USB',950,'2017-02-26')    
    
select * from ItemDetails

--In the CTE query we display all item details with the year.

;WITH itemCTE (Item_ID, Item_Name, Item_Price,SalesYear)
AS
(
  SELECT Item_ID, Item_Name, Item_Price ,YEAR(Date) SalesYear
  FROM ItemDetails   
)
 
Select * from itemCTE
 
-- CTE using Union ALL

--Let’s consider there is a below two scenarios to display the result.

--The first scenario is to display each Item Price of current Year.
--The second scenario is to increment 10% to each Item Price for next year.
--For this we use the above CTE Query. 
-- In this query, we add the UNION ALL and in UNION ALL 
-- Query we do calculation to add 10% to each item 
-- Price and show in next row with adding one year.

;WITH itemCTE2 (Item_ID, Item_Name, Item_Price,MarketRate,SalesYear)
AS
(
    SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,YEAR(Date) as SalesYear 
    FROM ItemDetails  
    UNION ALL
     SELECT Item_ID as Item_ID, Item_Name,
            (Item_Price + (Item_Price *10 )/100) as Item_Price,
            'Future Price' as MarketRate,  YEAR(dateadd(YEAR, 1, Date))  as SalesYear
    FROM ItemDetails
    
) 
SELECT * from itemCTE2 Order by Item_Name,SalesYear

CREATE TABLE ItemHistory    
(    
ID int identity(1,1),
oldITEMID    int, 
Item_Name VARCHAR(100) NOT NULL,    
Item_Price int NOT NULL,   
MarketRate  VARCHAR(100) NOT NULL,    
Date VARCHAR(100) NOT NULL )

--Here we use above same CTE query Insert the result in 
-- to the Item History table. 
--From this query we insert both item details of present 
-- year Item price along with the next year Item prices added as 10% more.
;WITH itemCTE (Item_ID, Item_Name, Item_Price,MarketRate,Date)
AS
(
    SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,Date  
    FROM ItemDetails  
    UNION ALL
     SELECT Item_ID as Item_ID, Item_Name,(Item_Price + (Item_Price *10 )/100) as Item_Price,
     'Future Price' as MarketRate,  dateadd(YEAR, 1, Date) as Date
    FROM ItemDetails
    
)
-- Define the outer query referencing the CTE name.
Insert into ItemHistory(oldITEMID ,Item_Name,Item_Price,MarketRate,Date)  
SELECT Item_ID, Item_Name, Item_Price,MarketRate,year(Date) from itemCTE Order by Item_Name,Date

select * from ItemHistory

Lead and Lag functions

--We use a Lag() function to access previous rows 
-- data as per defined offset value. 
--In the lead function, we access subsequent rows, 
-- but in lag function, we access previous rows. 
DECLARE   @Employee TABLE
  (
       EmpCode VARCHAR(10),
       EmpName   VARCHAR(10),
       JoiningDate  DATE
    )
INSERT INTO @Employee VALUES ('1', 'Rajendra', '1-Sep-2018')
INSERT INTO @Employee VALUES ('2', 'Manoj', '1-Oct-2018')
INSERT INTO @Employee VALUES ('3', 'Sonu', '10-Mar-2018')
INSERT INTO @Employee VALUES ('4', 'Kashish', '25-Oct-2018')
INSERT INTO @Employee VALUES ('5', 'Tim', '1-Dec-2018')
INSERT INTO @Employee VALUES ('6', 'Akshita', '1-Nov-2018')

--SELECT * FROM   @Employee;



--SQL Lag and Lead function without a default value

SELECT *, 
       Lag(JoiningDate, 1) OVER(
       ORDER BY JoiningDate ASC) AS EndDate
FROM @Employee;

SELECT *, 
       LEAD(JoiningDate, 1) OVER(
       ORDER BY JoiningDate ASC) AS EndDate
FROM @Employee;

--SQL Lag and Lead function with a default value

SELECT *, 
       Lag(JoiningDate, 1,'1999-09-01') OVER(
       ORDER BY JoiningDate ASC) AS EndDate
FROM @Employee;

SELECT *, 
       LEAD(JoiningDate, 1,0) OVER(
       ORDER BY JoiningDate ASC) AS EndDate
FROM @Employee;

--SQL Lag function with OFFSET value 2

SELECT *, 
       Lag(JoiningDate, 2,'1999-09-01') OVER(
       ORDER BY JoiningDate ASC) AS EndDate
FROM @Employee;

SELECT *, 
       LEAD(JoiningDate, 1,'2018-01-01') OVER(
       ORDER BY JoiningDate ASC) AS EndDate
FROM @Employee;


CREATE TABLE dbo.ProductSales
(
  [Year]  INT
 ,[Quarter] TINYINT
 ,Sales     DECIMAL(9,2)
);
 
INSERT INTO dbo.ProductSales VALUES 
 (2017, 1, 55000.00)
,(2017, 2, 78000.00)
,(2017, 3, 49000.00)
,(2017, 4, 32000.00)
,(2018, 1, 41000.00)
,(2018, 2,  8965.00)
,(2018, 3, 69874.00)
,(2018, 4,  32562.00)
,(2019, 1, 87456.00)
,(2019, 2, 75000.00)
,(2019, 3, 96500.00)
,(2019, 4, 85236.00)

--SQL Lag and lead function with PARTITION BY clause

SELECT [Year], 
       [Quarter], 
       Sales, 
       LAG(Sales, 1, 0) OVER(
       ORDER BY [Year], 
                [Quarter] ASC) AS [NextQuarterSales]
FROM dbo.ProductSales;

SELECT [Year], 
       [Quarter], 
       Sales, 
       LEAD(Sales, 1, 0) OVER(
       ORDER BY [Year], 
                [Quarter] ASC) AS [NextQuarterSales]
FROM dbo.ProductSales;

SELECT [Year], 
       [Quarter], 
       Sales, 
       LAG(Sales, 1, 0) OVER(PARTITION BY [Year]
       ORDER BY [Year], 
                [Quarter] ASC) AS [NextQuarterSales]
FROM dbo.ProductSales;

SELECT [Year], 
       [Quarter], 
       Sales, 
       LEAD(Sales, 1, 0) OVER(PARTITION BY [Year]
       ORDER BY [Year], 
                [Quarter] ASC) AS [NextQuarterSales]
FROM dbo.ProductSales;

--Example 4: SQL Server Lead function and Common Table Expressions ( CTE)
--We can use the lead function in combination with the CTE 
-- for writing complex queries. In the following query, we do the following tasks.

--Apply Lead function on the CTE to get the required values
;WITH cte_netsales_2018
     AS (SELECT [Quarter], 
                SUM(Sales) net_sales
         FROM dbo.ProductSales
         WHERE year = 2018
         GROUP BY [Quarter])
     SELECT [Quarter], 
            net_sales, 
            LEAD(net_sales, 1, 0) OVER(
            ORDER BY [Quarter]) sales
     FROM cte_netsales_2018;

--Example 5: SQL Server Lead function and specify OFFSET argument value
--In the previous examples, we used default 
--offset value 1 to return the subsequent values.
-- Let’s specify the offset value other than the default value and view the output.

SELECT [Year], 
       [Quarter], 
       Sales, 
       LEAD(Sales, 2, 0) OVER(
       ORDER BY [Year], 
                [Quarter] ASC) AS [NextQuarterSales]
FROM dbo.ProductSales;

--Example 6: SQL Server Lead function with expressions
--We can use expressions as well in the lead function. 
-- In the following query, we specified the following expressions.

--Scalar_expression: To double the sales figure using the 2*sales
--Offset: we use the expression to calculate the offset value as well

SELECT [Year], 
       [Quarter], 
       Sales, 
       LEAD(2*sales, 1+(select min(quarter) from productsales), 0) OVER(
       ORDER BY [Year], 
                [Quarter] ASC) AS [NextQuarterSales]
FROM dbo.ProductSales;