how to get Date and DayName based on row number in SQL Server

Hi amitj,

Will you please expalin on which basis you are assigning date to the employee.

I am using Sql System Table 

table empName

ID       Name

1        amit

2        sumit

for eg. pass two parameeter Month and Year.

go to system table get the data.

afterthat assign 

1 day first Employee name 

than 2 day second Employee name.

you have any other logic please apply here.

thank you.

Refer the below sql test query for your reference.You can use it as store procedure and call it to bind your grid view.

TestQuery

DECLARE @Month INT
DECLARE @Year INT
DECLARE @Date DATE

SET @Month = 03
SET @Year  = 2016 

SET @Date = CAST(RIGHT('0' + CAST(@Month as VARCHAR(2)), 2)+'-'+'01-'+ CAST(@Year as VARCHAR(4)) As DATE)

DECLARE @Table AS TABLE(EmployeeId INT,EmployeeName VARCHAR(20))
INSERT INTO @Table VALUES(1,'David')
INSERT INTO @Table VALUES(2,'Kevin')
INSERT INTO @Table VALUES(3,'Peter')
INSERT INTO @Table VALUES(4,'Jhon')
INSERT INTO @Table VALUES(5,'Andrea')
SELECT REPLACE(CONVERT(VARCHAR(24),CAST(DATEADD(DD,ROW_NUMBER() OVER (ORDER BY EmployeeId) - 1,@Date) AS DATE),103) ,'/','-')[Date]
      ,DATENAME(dw,DATEADD(DD,ROW_NUMBER() OVER ( ORDER BY EmployeeId) - 1,@Date)) [DayName] 
      ,EmployeeName
FROM @Table

Output