Generating a Date Range With a Tally Table

Another way you can generate a range of dates is by utilizing a Tally Table to create the dates between the range:

Declare @FromDate Date = '2014-04-21',
        @ToDate Date = '2014-09-02'
;With
    E1(N) As (Select 1 From (Values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) DT(N)),
    E2(N) As (Select 1 From E1 A Cross Join E1 B),
    E4(N) As (Select 1 From E2 A Cross Join E2 B),
    E6(N) As (Select 1 From E4 A Cross Join E2 B),
    Tally(N) As
    (
        Select Row_Number() Over (Order By (Select Null))
        From E6
    )
    Select DateAdd(Day, N - 1, @FromDate) Date
    From Tally
    Where N <= DateDiff(Day, @FromDate, @ToDate) + 1

 Related Article: Generating Date Range With Recursive CTE

Generating Date Range With Recursive CTE

 Using a Recursive CTE, you can generate an inclusive range of dates:

Declare @FromDate Date = '2014-04-21',
        @ToDate Date = '2014-09-02'
;With DateCte (Date) As
(
    Select @FromDate Union All
    Select DateAdd(Day, 1, Date)
    From DateCte
        Where Date < @ToDate
)
Select Date
    From DateCte
Option (MaxRecursion 0)

The default MaxRecursion setting is 100. Generating more than 100 dates using this method will require the Option. Otherwise it will throw below error.

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

(MaxRecursion N) segment of the query, where N is the desired MaxRecursion setting. Setting this to 0 will remove the MaxRecursion limitation altogether.

Related Article : Generating a Date Range With a Tally Table

Find Column Name From All Tables of Database

Find the column name present in all tables in the database. You use below query to find out.

For Example Column Name: Id present in list of tables in database

SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
        where c.name like 'Column Name%'
    ORDER BY schema_name, table_name;

Output:

How to search through Stored procedures in SQL Server

 You can use below query to search through Stored procedures in SQL Server. It will help to find part of string present in stored procedure.

SELECT o.type_desc AS ROUTINE_TYPE,o.[name] AS ROUTINE_NAME,
m.definition AS ROUTINE_DEFINITION
    FROM sys.sql_modules AS m INNER JOIN sys.objects AS o
        ON m.object_id = o.object_id WHERE m.definition LIKE '%RK From SP Modified%'
    order by ROUTINE_NAME

Output:

Create Employee and Manager Hierarchy using Common Table Expression

We will see how to get the Employee-Manager hierarchy from the same table.

Let's setup the table first.

CREATE TABLE dbo.Employee
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
ManagerID INT NULL
)

insert values into the table

INSERT INTO Employee VALUES (101, 'Ken', 'Sánchez', NULL)
INSERT INTO Employee VALUES (102, 'Keith', 'Hall', 101)
INSERT INTO Employee VALUES (103, 'Fred', 'Bloggs', 101)
INSERT INTO Employee VALUES (104, 'Joseph', 'Walker', 102)
INSERT INTO Employee VALUES (105, 'Zydr', 'Klyb', 101)
INSERT INTO Employee VALUES (106, 'Sam', 'Jackson', 105)
INSERT INTO Employee VALUES (107, 'Peter', 'Miller', 103)
INSERT INTO Employee VALUES (108, 'Chloe', 'Samuels', 105)
INSERT INTO Employee VALUES (109, 'George', 'Weasley', 105)
INSERT INTO Employee VALUES (110, 'Michael', 'Kensington', 106)

See the values below.

EmployeeID	FirstName	LastName	ManagerID
101	Ken	Sánchez	NULL
102	Keith	Hall	101
103	Fred	Bloggs	101
104	Joseph	Walker	102
105	Zydr	Klyb	101
106	Sam	Jackson	105
107	Peter	Miller	103
108	Chloe	Samuels	105
109	George	Weasley	105
110	Michael	Kensington	106

Below SQL Code to demonstrate the Employee and Manager relationship.

;WITH cteReports (EmpID, FirstName, LastName, SupervisorID, EmpLevel) AS
(
	SELECT EmployeeID, FirstName, LastName, ManagerID, 1
	FROM Employee
	WHERE ManagerID IS NULL
	UNION ALL
	SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1
	FROM Employee AS e
	INNER JOIN cteReports AS r ON e.ManagerID = r.EmpID
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employee WHERE EmployeeID = cteReports.SupervisorID)
AS ManagerName
FROM cteReports
ORDER BY EmpLevel, SupervisorID

Output:

FullName	EmpLevel	ManagerName
Ken Sánchez	1	NULL
Keith Hall	2	Ken Sánchez
Fred Bloggs	2	Ken Sánchez
Zydr Klyb	2	Ken Sánchez
Joseph Walker	3	Keith Hall
Peter Miller	3	Fred Bloggs
Sam Jackson	3	Zydr Klyb
Chloe Samuels	3	Zydr Klyb
George Weasley	3	Zydr Klyb
Michael Kensington	4	Sam Jackson

Update statement using join in SQL server

Join clause can also be used in update statement. Let's see the below example. It can be used in different way is mentioned below.

Update using simple join statement:

Create table Users and Preferences.

CREATE TABLE Users (
UserId int NOT NULL,
AccountId int NOT NULL,
RealName nvarchar(200) NOT NULL
)

Create the second table.

CREATE TABLE Preferences (
UserId int NOT NULL,
SomeSetting bit NOT NULL
)

Update the SomeSetting column of the Preferences table filtering by a predicate on the Users table as follows:

UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId= p.UserId
WHERE u.AccountId = 1234

p is an alias for Preferences defined in the FROM clause of the statement. Only rows with a matching AccountId from the Users table will be updated.

Update with left outer join statements

(Demonstrated the update statement format only)

Update t
SET t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2
ON t2.ID=t.ID

Update tables with inner join and aggregate function

UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (select field3, sum(field2) as field2Sum
from table2
group by field3) as t2
on t2.field3 = t1.field3

PIVOT and UNPIVOT in SQL Server

There are several ways to transpose a dataset from rows to columns and columns to rows. SQL Pivot is one of the techniques that allows transposing of rows to columns and performs possible aggregations along the way. SQL PIVOT and SQL UNPIVOT relational operators transpose a table-valued two-dimensional data into another form of data. 

SQL PIVOT transposes a table-valued expression from a unique set of values from one column into multiple columns in the output and performs aggregations. SQL UNPIVOT performs the opposite operation of SQL PIVOT by transforming the columns of a table-valued expression into column values.

PIVOT relational operator converts data from row level to column level.

UNPIVOT relational operator is reverse process of PIVOT relational operator. UNPIVOT relational operator convert data from column level to row level.

Let's see the below example to understand.

Example:

PIVOT

Below is a simple example which shows average item's price of each item per weekday.

First, suppose we have a table which keeps daily records of all items' prices.

CREATE TABLE tbl_stock(item NVARCHAR(10), weekday NVARCHAR(10), price INT);

insert values into the table:

INSERT INTO tbl_stock VALUES
('Item1', 'Mon', 110), ('Item2', 'Mon', 230), ('Item3', 'Mon', 150),
('Item1', 'Tue', 115), ('Item2', 'Tue', 231), ('Item3', 'Tue', 162),
('Item1', 'Wed', 110), ('Item2', 'Wed', 240), ('Item3', 'Wed', 162),
('Item1', 'Thu', 109), ('Item2', 'Thu', 228), ('Item3', 'Thu', 145),
('Item1', 'Fri', 120), ('Item2', 'Fri', 210), ('Item3', 'Fri', 125),
('Item1', 'Mon', 122), ('Item2', 'Mon', 225), ('Item3', 'Mon', 140),
('Item1', 'Tue', 110), ('Item2', 'Tue', 235), ('Item3', 'Tue', 154),
('Item1', 'Wed', 125), ('Item2', 'Wed', 220), ('Item3', 'Wed', 142);

In order to perform aggregation which is to find the average price per item for each week day, we are going to use the relational operator PIVOT to rotate the column weekday of table-valued expression into aggregated row values as below:

SELECT * FROM tbl_stock
PIVOT (
AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt;

Output:

item	Mon	Tue	Wed	Thu	Fri
Item1	116	112	117	109	120
Item2	227	233	230	228	210
Item3	145	158	152	145	125

UNPIVOT

Lastly, in order to perform the reverse operation of PIVOT, we can use the relational operator UNPIVOT to rotate

columns into rows as below:

SELECT * FROM tbl_stock
PIVOT (
AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt
UNPIVOT (
price FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) unpvt;

Output:

item	price	weekday
Item1	116	Mon
Item1	112	Tue
Item1	117	Wed
Item1	109	Thu
Item1	120	Fri
Item2	227	Mon
Item2	233	Tue
Item2	230	Wed
Item2	228	Thu
Item2	210	Fri
Item3	145	Mon
Item3	158	Tue
Item3	152	Wed
Item3	145	Thu
Item3	125	Fri