Thursday, September 28, 2017

Get top X from each category SQL SERVER 2005 +



Get top 5 from each category

simple logic use Row_Number function with partition by the category for which you want to create group.
 ROW_NUMBER() OVER (PARTITION BY CategoryID order by CategoryID  )

Step 2: Now Row_Number will add numbers on each category, kept the result in temp table or CTE.

Step 3: Now use select statement to get records where rowNum greater than X.


;with Rohi  as
(
 select
 TestID,CategoryID,
 ROW_NUMBER() OVER (PARTITION BY CategoryID order by CategoryID  ) AS  'RowNum'

  from Test
  )
  select * from Rohi where RowNum <=5
 

Tuesday, May 23, 2017

All Dates or Number series between start and end position - SQL SERVER Months between two dates, Numbers series between to number


Example 1: Date Series
DECLARE @StartDate  DATETIME ='20110501' ,
        @EndDate    DATETIME= '20110801'

SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'      
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

Example 1: Int Series
DECLARE @StartRange  int ='2' ,
        @EndRange     int= '10'

SELECT  x.number AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'      
AND     x.number between @StartRange and  @EndRange