This functionis used for finding a date for a particular year,month and n(th) week day

CREATE FUNCTION [dbo].[findDate]
(
@month SMALLINT, -- Month of Date
@year INT, -- Year of Date
@day SMALLINT, -- Day of Date
@weekday SMALLINT -- (n)th Week of Date
)
RETURNS DATETIME
AS
BEGIN

/*
@day variable can be :
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday
*/

DECLARE @start_date DATETIME, @end_date DATETIME, @retrunDate DATETIME, @total_days TINYINT,@counter TINYINT

SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-01' As SmallDateTime)

SET @total_days = DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@start_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@start_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))

SET @end_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-'+ CAST(@total_days AS VARCHAR(4)) As SmallDateTime)

SET @counter =1

WHILE @start_date <= @end_date
BEGIN

IF(DATEPART(dw , @start_date )= @day) AND @counter <= @weekday
BEGIN
--SELECT CAST(@start_date as VARCHAR(50)),DATEPART(dw , @start_date )

SET @retrunDate = @start_date
SET @counter = ISNULL(@counter,0)+1
END
-- SELECT @counter, @retDate
SET @start_date = DATEADD(DAY,1,@start_date)

END

--SELECT @retrunDate

RETURN @retrunDate

END

This is used to find all dates of a year mentioned along with (n)th week day

DECLARE @month int
DECLARE @year INT
DECLARE @weekday int
DECLARE @whichday int
DECLARE @dt datetime

SET @year=2012
SET @month=1
SET @weekday=5
SET @whichday=5


WHILE @month <= 12
BEGIN

SET @dt=CAST(@year AS CHAR(4))+'-'+CAST(@month AS CHAR(2))+'-01'

IF(MONTH(DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt))=@month)
BEGIN
IF(MONTH(DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)))=@month)
SELECT DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),1
ELSE
SELECT DATEADD(WEEK,@whichday-2,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),2
END
ELSE
BEGIN
IF(MONTH(DATEADD(WEEK,@whichday,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)))=@month)
SELECT DATEADD(WEEK,@whichday,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),3
ELSE
SELECT DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),4
END

SET @month = @month + 1
END

Calculate no days in a particular month

DECLARE @today_date DATETIME
SET @today_date = '2009/02/01'

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@today_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@today_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@today_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@today_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))

Use of CTE along with PIVOTING

;with cte
as
(
select cb.name as Branch, cee.entity_fk as entity, cee.id, cee.emp_code, cee.first_name +' '+ cee.last_name as Employee,
es.salary_component_fk as component, es.amount
from employee cee
inner join entity ee on ee.id = cee.entity_fk
inner join branch cb on cb.id = ee.branch_fk
inner join salary es on es.employee_fk= cee.id
where --cee.entity_fk = 3 and
cee.emp_code is not null
and cee.dol is null
and cee.active = 1
and es.active = 1
and es.amount > 0
)
select Branch,emp_code, Employee, Isnull([1],0) as [Basic],Isnull([2],0) as HRA,Isnull([3],0 ) as [TPL All],Isnull([4],0) as [SPL All],
(Isnull([1],0) + Isnull([2],0) + Isnull([3],0) + Isnull([4],0)) as Total

from (select Branch, entity, id, emp_code,Employee, component, amount from cte) AS vs
PIVOT (sum(amount) FOR component in([1], [2], [3], [4])) AS p

order by Branch, entity, CAST(SUBSTRING(emp_code,5,len(emp_code)) AS INT)

This is the way to insert OPENXML data into table without using view

INSERT into TableName
SELECT *
FROM OPENXML(@hdoc, @nodeName)
WITH
(
id INT '@id',
component_fk int '@component_fk',
amount float '@amount',
active bit '@active'
)

XML inSQL Query

We can use New way of selecting Nodes from XMl by using following:
Declare @xmlData xml
SET @xmlData='';
Declare @temp as table (id int, Name varchar(50))
Insert into @temp
SELECT
T.item.value('@id', 'int') as [id],
T.item.value('@first_name', 'varchar(50)') as [Name]
FROM @xmlData.nodes('/employee/user') AS T(item)
where T.item.value('@id', 'int')=57
So there is no need of using OPENXML() method.

OpenXML and xml Nodes

DECLARE @xmlData XMLIF (@xmldata.exist('//sd/member')=1)BEGIN
DECLARE @hdoc int Declare @nodeName as varchar(100)
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmldata Set @nodeName = '//sd/member'-- Declare a temp table and insert from open xml having auto increment ID column-- This ID column is used to avoid cursor--INSERT into @tempSponsorNew SELECT * FROM OPENXML( @hdoc, @nodeName, 8 ) WITH ( id INT '@id' ,[sp_fk] INT '@sponsor_fk' ,[name] varchar(100) '@name')
EXEC sp_xml_removedocument @hDoc
DECLARE @sponserCounter INT
DECLARE @totalRowSponser INT -- avoid using cursorSET @sponserCounter=0 SELECT @totalRowSponser=count(1) FROM @tempSponsorNew While(@sponserCounter < @totalRowSponser) BEGIN SET @sponserCounter=@sponserCounter + 1 -- Do what ever you want to do with this temp table dataselect *FROM @tempSponsorNew ts WHERE ts.id < 0 and ts.active=1 and ts.[primary_id]=@sponserCounter END
END