Delete duplicate records using CTE, Sql Server

You may use following query :

WITH CTE(ORDER_ID,REQUEST_ID,ROW_No)
as
(
SELECT ORDER_ID,REQUEST_ID,ROW_NUMBER() OVER(PARTITION BY ORDER_ID ORDER BY ORDER_ID) AS ROW_No
FROM CRM.ORDER_COMPLIANCE WHERE ORDER_ID=397
)
SELECT ORDER_ID,REQUEST_ID,ROW_No FROM CTE
–DELETE FROM CTE WHERE ROW_NO>1

There are alternate ways also to achieve this.Share your ideas.

Reference: Narendra Singh (http://blog.sikarnarender.com)

How to get difference in year month and days from 2 date values ?

Following query will give result as (X Years X Months X Days) :

Declare @DOB DateTime
DECLARE @yy INT
DECLARE @mm INT
DECLARE @dd INT

SET @DOB=’2011-10-11 00:00:00.000′
SET @yy = DATEDIFF(mm, @DOB, GETDATE())/12
SET @mm = DATEDIFF(mm, @DOB, GETDATE())%12 – 1
SET @dd = ABS(DATEDIFF(dd, DATEADD(mm,@mm , DATEADD(yy, @yy, @DOB)), GETDATE()))
SELECT Convert(varchar(10),@yy) + ‘ Years ‘ + Convert(varchar(10),@mm) + ‘ Months ‘ + Convert(varchar(10),@dd) + ‘ Days ‘

There are alternate ways also to achieve this.Share your ideas.

Reference: Narendra Singh (http://blog.sikarnarender.com)