Query to Find First and Last Day of Current Month

Hello friends,
Following is a TSQL script that will find the 'Last day of previous month','First day of current month' and so on...
Hope so this will help you.

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
'Last Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
'Last Day of Current Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
'First Day of Next Month'
GO

You can find main article on this url i.e. http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/


Regards,
Kinjal

XML Path Query

Hello Friends,
Here I provide you one link that will share you how to use xml in sql server.

http://beyondrelational.com/blogs/jacob/archive/2008/08/04/for-xml-path-how-to-remove-the-lt-row-gt-element-from-the-output-of-a-for-xml-path-query.aspx

Hopes it will help for beginners.

Regards,
Kinjal

Generate N Level Product List Using CTE

Hello Friends,
Here I show you example which will generate navigation list for product through N level. Please check below query for generate N Level product list.

Query:-
-----------------------------------------------

DECLARE @Category TABLE
(
CategoryID INT
,Name VARCHAR(100)
,ParentID INT
)

INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 1,'Mobile',0)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 2,'Nokia',1)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 3,'Samsung',1)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 4,'Headphone',0)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 5,'Nokia N70',2)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 6,'Nokia N72',2)
INSERT INTO @Category(CategoryID,Name,ParentID) VALUES ( 7,'IBall',4)

;WITH CTE(CategoryID,Name,ParentID,level,ParentName)
AS
(
SELECT CategoryID,Name,ParentID,0,cast('.' + Name + '.' AS VARCHAR(MAX)) FROM @Category WHERE ParentID=0
UNION ALL
SELECT C.CategoryID,C.Name,CT.ParentID,level + 1,CAST (CT.ParentName + '.' + C.Name AS VARCHAR(MAX)) FROM @Category C
INNER JOIN CTE CT ON CT.CategoryID=C.ParentID
)
SELECT SPACE(level * 4) + Name AS Products FROM CTE ORDER BY ParentName

Output
-------------------

Common Table Expression Example

Hello Friends,
Here I put the sample code example using CTE.

--Generate Fibonacii Series using CTE


;WITH FibonaciiSeries(N,F1,F2,F3)
AS
(
SELECT CAST(1 AS BIGINT), CAST(1 AS BIGINT),CAST(0 AS BIGINT),CAST(1+0 AS BIGINT)
UNION ALL
SELECT N+1, F2,F3,F2+F3 FROM FibonaciiSeries WHERE N<92
)
SELECT * FROM FibonaciiSeries

--OUTPUT
-------------------------



--Find Factorial

;WITH Factorial(N,Number)
AS
(
SELECT CAST (1 AS BIGINT), CAST (1 AS BIGINT)
UNION ALL
SELECT N+1,CAST ((N+1)*(Number) AS BIGINT) FROM Factorial WHERE N<10
)

SELECT * FROM Factorial

--OUTPUT
-------------------------

Find the second highest salary for each department

Hello Friends,
Here I post one TSQL Challenge. I hopes it will help you.

TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

http://beyondrelational.com/blogs/tcb/archive/2009/10/13/tsql-beginner-s-challenge-1-find-the-second-highest-salary-for-each-department.aspx

TSQL Challenge Solved-

TSQL:-
SET NOCOUNT ON
DECLARE @Employees TABLE(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Adams','Finance', 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('M Williams','IT', 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Jones','IT', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Miller','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('L Lewis','IT', 50000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Clerk','Back-Office', 10000)


SELECT * FROM @Employees

;WITH SecondHighestSalary
AS
(
SELECT EmployeeID,EmployeeName,Department,SALARY AS SALARY,RANK() over (partition by Department order by salary) AS SalaryNumber FROM @Employees
)
SELECT EmployeeID,EmployeeName,Department,SALARY FROM SecondHighestSalary WHERE SalaryNumber=2



OUTPUT:-