this task so huge..

Step 1 : Calculating the efficiency of the employee on daily basis, scenario calculate efficiency based on night and dayshift

Step 2: Getting this report

Month - generate...

1000+ line coding..lot of tables used in that sp

shiftscheduler sample

Emp_CodeShiftShiftTypeFromDateEndDate

3039242013-10-07 00:00:00.0002013-10-15 00:00:00.000

sample output expected

empidd1d1effd2d2effd3d3effd4 d4effd5 d5effd6 .... d29d29eff d30d305eff...

i almost done ..created another SP called effreportday to calculate efficencyreport which will give the daywise efficiency of the employee like this empidd1 d2d3d4d5d6d7d8d9d10d11d12d13d14d15d16d17d18d19d20d21d22d23d24d25d26d27d28d29d30d31WorkingDaysTotalEfficiencyAverage

509700000000000000000008890000000000001780

i want to...

Working has Expected

So many combinations i tried but still got the error

DECLARE @sd DATEtime = '20160201'

DECLARE @ed DATEtime = dateadd(mm, datediff(mm, 0, @sd) + 1, -1);

DROP TABLE #temp1;

CREATE TABLE #temp2([Count1] INT)

WITH yourcalendar1...

I Tried with union as well, but while adding the column name count(hdL) it shows error..Please reply

create table ShiftScheduler(emp_code VARCHAR(10),fromdate DATETIME,enddate DATETIME,shifttype INT)

INSERT INTO ShiftScheduler VALUES('I2486','2014-08-25 00:00:00.000','2014-08-30 00:00:00.000',4)

create table emploee(empid INT,empcode VARCHAR(10),shifttype INT)

INSERT INTO emploee VALUES(469,'I2486',1)

Same way i also did, but including has column in this pivot is not working..WITH yourcalendar1 as (

SELECT DATEADD(dd, rn - 1, @sd) thedate, 'D' + CAST ((rn ) as...

Still not able to add w's

DECLARE @sd DATEtime = '20160101'

DECLARE @ed DATEtime = dateadd(mm, datediff(mm, 0, @sd) + 1, -1);

WITH yourcalendar as (

SELECT DATEADD(dd, rn - 1, @sd) thedate, 'D' + CAST...

Added with pivot

DECLARE @sd DATEtime = '20160101'

DECLARE @ed DATEtime = dateadd(mm, datediff(mm, 0, @sd) + 1, -1);

WITH yourcalendar as (

SELECT DATEADD(dd, rn - 1, @sd) thedate, 'D' ...

Thank you so much it is working as expected...

Done with the Nightshift

DECLARE @sd DATEtime = '20161201'

DECLARE @ed DATEtime = dateadd(mm, datediff(mm, 0, @sd) + 1, -1);

WITH yourcalendar as (

SELECT DATEADD(dd, rn - 1, @sd) thedate, 'D' ...

Thanks, i am trying for it, just i asked for it to make the changes, Sorry and thanks

Any updates reg to Nightshift tables