Some Useful Sql Server Queries

Some Useful Sql Server Queries

Get Number Of Days in a Month:

SQL Query to get No of Days in a month:-

SELECT DAY(DATEADD(MONTH, 1, GETDATE()) - DAY(DATEADD(MONTH, 1, GETDATE())))

Reading XMl from Sql Server

DECLARE @FileName varchar(255)

DECLARE @ExecCmd VARCHAR(255)

DECLARE @y INT

DECLARE @x INT

DECLARE @FileContents VARCHAR(8000)

CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

SET @FileName = Full Path To Xml \General.xml'

SET @ExecCmd = 'type ' + @FileName

SET @FileContents = ''

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd

SELECT @y = count(*) from #tempXML

SET @x = 0

WHILE @x <> @y

BEGIN

SET @x = @x + 1

SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK = @x

END

SELECT @FileContents as FileContents

DROP TABLE #tempXML

Grouping Records in Sql Server 2005

declare @NO_OF_PARTITION int

set @NO_OF_PARTITION = 10

SELECT c.First_Name, c.Last_Name , C.DIVISION_ID

,NTILE(@NO_OF_PARTITION)

OVER(PARTITION BY DIVISION_ID ORDER BY DIVISION_ID ASC) AS 'GROUP_ID'

FROM EMPLOYEE C

WHERE division_id = 2

ORDER BY DIVISION_ID ASC

Replacement to IN and NOT IN from INTERSECT and EXCEPT

SELECT EMPLOYEE_ID FROM EMPLOYEE

INTERSECT

SELECT EMPLOYEE_ID FROM LEAVE_DETAILS

SELECT EMPLOYEE_ID FROM EMPLOYEE

EXCEPT

SELECT EMPLOYEE_ID FROM LEAVE_DETAILS

Using NTILE to tile (group) your records

SELECT c.First_Name, c.Last_Name , C.DIVISION_ID

,NTILE(2)

OVER(PARTITION BY DIVISION_ID ORDER BY DIVISION_ID ASC) AS 'GROUP_ID'

FROM EMPLOYEE C

WHERE EMPLOYEE_ID < 13

ORDER BY DIVISION_ID ASC

Get Serial No. with the query result (ROW_NUMBER function SQL Server 2005)

SELECT ROW_NUMBER() OVER (ORDER BY DIVISION_ID ASC) AS ROWID, * FROM EMPLOYEE

Using Pivot Keyword SQL Server 2005

CREATE TABLE dbo.SalesByQuarter
(
Y INT,
Q INT,
sales INT,
PRIMARY KEY (Y,Q)
)
GO

INSERT dbo.SalesByQuarter(Y,Q,Sales)
SELECT 2003, 2, 479000
UNION SELECT 2003, 3, 321000
UNION SELECT 2003, 4, 324000
UNION SELECT 2004, 1, 612000
UNION SELECT 2004, 2, 524000
UNION SELECT 2004, 3, 342000
UNION SELECT 2004, 4, 357000
UNION SELECT 2005, 1, 734000
GO

SELECT Y,
[1] AS Q1,
[2] AS Q2,
[3] AS Q3,
[4] AS Q4
FROM
(SELECT Y, Q, Sales
FROM SalesByQuarter) s
PIVOT
(
SUM(Sales)
FOR Q IN ([1],[2],[3],[4])
) p
ORDER BY [Y]
GO

DROP TABLE dbo.SalesByQuarter
GO