Create table emp(emp_code int,Fromdate DATETIME,Enddate DATETIME,shift INT,shifttype INT)

INSERT INTO emp VALUES(635,'2014-05-01','2014-05-03',2,4)

INSERT INTO emp VALUES(635,'2014-05-07','2014-05-10',2,4)

INSERT INTO emp VALUES(635,'2014-05-14','2014-05-13',2,4)

output:

2015-10-01 00:00:00.0002190DAYSHIFT

2015-10-02 00:00:00.0002190DAYSHIFT

2015-10-03 00:00:00.0002190DAYSHIFT

2015-10-04 00:00:00.0002190DAYSHIFT

2015-10-05 00:00:00.0002190DAYSHIFT

2015-10-06 00:00:00.0002190DAYSHIFT

2015-10-07 00:00:00.0002190DAYSHIFT

2015-10-08 00:00:00.0002190DAYSHIFT

2015-10-09 00:00:00.0002190DAYSHIFT

2015-10-10 00:00:00.0002190DAYSHIFT

2015-10-11 00:00:00.0002190DAYSHIFT

2015-10-12 00:00:00.0002190DAYSHIFT

2015-10-13 00:00:00.0002190DAYSHIFT

2015-10-14 00:00:00.0002190DAYSHIFT

2015-10-15 00:00:00.0002190DAYSHIFT

2015-10-16 00:00:00.0002190DAYSHIFT

2015-10-17 00:00:00.0002190DAYSHIFT

2015-10-18 00:00:00.0002190DAYSHIFT

2015-10-19 00:00:00.0002190DAYSHIFT

2015-10-20 00:00:00.0002190DAYSHIFT

2015-10-21 00:00:00.0002190DAYSHIFT

2015-10-22 00:00:00.0002190DAYSHIFT

2015-10-23 00:00:00.0002190DAYSHIFT

2015-10-24 00:00:00.0002190NIGHTSHIFT

2015-10-25 00:00:00.0002190DAYSHIFT

2015-10-26 00:00:00.0002190DAYSHIFT

2015-10-27 00:00:00.0002190DAYSHIFT

2015-10-28 00:00:00.0002190DAYSHIFT

2015-10-29 00:00:00.0002190DAYSHIFT

2015-10-30...

small bug...it is showing only enddate is nightshift..for example one employee is in night shift for a week(2,4) then this query working for enddate showing nightshift

Thanks a lot

i said coding is parsing...still i have same bug

it is 100% correct it is working

--DDL:

Create table ShiftScheduler(emp_code int,Enddate DATETIME,shift INT,shifttype INT)

INSERT INTO ShiftScheduler VALUES(635,'2014-05-03',2,1)

INSERT INTO ShiftScheduler VALUES(635,'2014-11-22',2,1)

INSERT INTO ShiftScheduler VALUES(635,'2015-10-28',1,1)

Create table emploee(empid INT,shift INT,shifttype INT)

INSERT INTO emploee VALUES(635,1,1)

--CODE:

CREATE PROCEDURE Emp_Shift1

(

@empcode INT,

@FromDate datetime,

@EndDate datetime

)

AS

BEGIN

--DROP...

--DDL:

Create table ShiftScheduler(emp_code int,Enddate DATETIME,shift INT,shifttype INT)

INSERT INTO ShiftScheduler VALUES(635,'2014-05-03',2,1)

INSERT INTO ShiftScheduler VALUES(635,'2014-11-22',2,1)

INSERT INTO ShiftScheduler VALUES(635,'2015-10-28',1,1)

Create table employee(empid INT,shift INT,shifttype INT)

INSERT INTO employee VALUES(635,1,1)

--CODE:

ALTER PROCEDURE Emp_Shift1

(

@empcode INT,

@FromDate datetime,

@EndDate datetime

)

AS

BEGIN

--DROP...

Thanks..for all your effort

Please suggest me an idea for this scenario(this is root cause of the bug)

ex: while the employee joins in the company his shift has been allocated to...

Yes Dates would be easier but 01012016shift 01012016shifttype looks not pretty..for the understanding i need all the d1shift d1shifttype d2shift d2shifttype......d30shift d30shifttype..if i deosn't have value then it should display...

empidD1shiftD1sstypeD2shiftD2SstypeD3shiftD3sstypeD4shiftD4shifttype....Till d30shifttype

3321 2 4 ...

Yes exactly...same format

Empid ...

I done with the output..only issue now is

Empidd1shiftd1shifttyped2shiftd2shifttyped3shiftd3shifttyped4shiftd4shifttype

1255 2 ...

Hi,

ALTER PROCEDURE Emp_Shift1

(

@empcode INT,

@FromDate datetime,

@EndDate datetime

)

AS

BEGIN

--DROP TABLE #t

CREATE TABLE #t(

[Empid] INT,

[Date] date,

[shift] int,

[shifttype] int

)

INSERT INTO #t

SELECT

e.empid,

SS.EndDate,

SS.[Shift],

SS.ShiftType

FROM

dbo.ShiftScheduler SS,Ihsmaster_delop.dbo.Emploee e WHERE e.empcode=SS.Emp_Code

AND

e.empid = @empcode

and FromDate >= @FromDate

and...

Totally this is dummy(created same has source)

CREATE TABLE dbo.ShiftScheduler1(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))

CREATE TABLE dbo.Employee(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))

INSERT INTO dbo.Employee values('1/1/2015',1,1,3321)

INSERT INTO dbo.Employee values('1/7/2015',1,1,3321)

INSERT INTO dbo.ShiftScheduler1 VALUES('1/5/2015',2,4,3321)

INSERT...