June 21, 2016 at 7:07 am
GA_SQL (6/21/2016)
i almost done ..created another SP called effreportday to calculate efficencyreport which will give the daywise efficiency of the employee like thisempidd1 d2d3d4d5d6d7d8d9d10d11d12d13d14d15d16d17d18d19d20d21d22d23d24d25d26d27d28d29d30d31WorkingDaysTotalEfficiencyAveragei want to include this code below once it displays shift like this509700000000000000000008890000000000001780
empidd1d1effd2d2effd3d3effd4d4effd5d5effd6d6effd7d7effd8d8effd9 d9eff ...d31 d31effd1eff,d2eff is got from above SP output effreportday
1. exec effreportday sp
2. add the output of effreportday sp to the end pivot
this is my complete source code
ALTER PROCEDURE Empshifteffreport @sd DATETIME,@emp INT,@fromdate DATETIME,@enddate DATETIME
AS
BEGIN
--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 ((rn ) as varchar(2)) AS DN
FROM
( SELECT rn = ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM sys.objects a CROSS JOIN sys.objects b
) x
WHERE x.rn <= DATEDIFF(dd, @sd, DATEADD(day, 1, @ed))
)
,cte1 as (
SELECT
c.DN,
CASE
WHEN h.date1 > 0 THEN 'H'
WHEN DATEPART(dw, c.thedate) IN (1,7) THEN 'WO' ELSE 'W'
END hdL
FROM yourcalendar AS c
LEFT OUTER JOIN HolidayMaster AS h ON c.thedate = h.date1
)
SELECT * INTO #temp1 FROM (
SELECT * FROM cte1
UNION ALL
SELECT 'NightshiftIHWdays', CAST(COUNT(hdL) as varchar(3))
FROM cte1
WHERE (hdL = 'w')
)x
DECLARE @cols1 AS NVARCHAR(MAX), @sql AS NVARCHAR(MAX);
select @cols1 = STUFF((SELECT ',' + (DN)
from #temp1
group by DN, hdL
order by LEFT(DN,1), CASE WHEN LEFT(DN,1) = 'D' THEN convert(int,substring(DN,2,len(DN))) END
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @sql = 'SELECT ' + @cols1 + ' from
(select hdL,DN from #temp1
) x
pivot
( max(hdl)
for DN in (' + @cols1 + ')
) p '
EXECUTE sp_executesql @sql
DROP TABLE #temp1
DECLARE @ed1 DATEtime = dateadd(mm, datediff(mm, 0, @sd) + 1, -1);
WITH yourcalendar2 as (
SELECT DATEADD(dd, rn - 1, @sd) thedate, 'D' + CAST ((rn ) as varchar(2)) AS DN
FROM
( SELECT rn = ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM sys.objects a CROSS JOIN sys.objects b
) x
WHERE x.rn <= DATEDIFF(dd, @sd, DATEADD(day, 1, @ed1))
)
,cte2 as (
SELECT
c.DN,
CASE
WHEN h.date1 > 0 THEN 'HL'
WHEN DATEPART(dw, c.thedate) IN (2,3,4,5,6) THEN 'W'
WHEN DATEPART(dw, c.thedate) = 7 and ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) IN (2,3) THEN 'W'
ELSE 'WO'
END hdL
FROM yourcalendar2 AS c
LEFT OUTER JOIN HolidayMaster AS h ON c.thedate = h.date1
)
SELECT * INTO #temp2 FROM (
SELECT * FROM cte2
UNION ALL
SELECT 'Wdays', CAST(COUNT(hdL) as varchar(3))
FROM cte2
WHERE (hdL = 'w')
)x
DECLARE @cols2 AS NVARCHAR(MAX), @sql2 AS NVARCHAR(MAX);
select @cols2 = STUFF((SELECT ',' + (DN)
from #temp2
group by DN, hdL
order by LEFT(DN,1), CASE WHEN LEFT(DN,1) = 'D' THEN convert(int,substring(DN,2,len(DN))) END
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @sql2 = 'SELECT ' + @cols2 + ' from
(select hdL,DN from #temp2
) x
pivot
( max(hdl)
for DN in (' + @cols2 + ')
) p '
EXECUTE sp_executesql @sql2
DROP TABLE #temp2
DECLARE @ed3 DATEtime = dateadd(mm, datediff(mm, 0, @sd) + 1, -1);
WITH yourcalendar3 as (
SELECT DATEADD(dd, rn - 1, @sd) thedate, 'D' + CAST ((rn ) as varchar(2)) AS DN
FROM
( SELECT rn = ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM sys.objects a CROSS JOIN sys.objects b
) x
WHERE x.rn <= DATEDIFF(dd, @sd, DATEADD(day, 1, @ed3))
)
,cte3 as (
SELECT
c.DN,
CASE
WHEN h.date1 > 0 THEN 'H'
WHEN DATEPART(dw, c.thedate) IN (1,7) THEN 'WO' ELSE 'W'
END hdL
FROM yourcalendar3 AS c
LEFT OUTER JOIN HolidayMaster AS h ON c.thedate = h.date1
)
SELECT * INTO #temp3 FROM (
SELECT * FROM cte3
UNION ALL
SELECT 'NightshiftWdays', CAST(COUNT(hdL) as varchar(3))
FROM cte3
WHERE (hdL = 'w')
)x
DECLARE @cols3 AS NVARCHAR(MAX), @sql3 AS NVARCHAR(MAX);
select @cols3 = STUFF((SELECT ',' + (DN)
from #temp3
group by DN, hdL
order by LEFT(DN,1), CASE WHEN LEFT(DN,1) = 'D' THEN convert(int,substring(DN,2,len(DN))) END
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @sql3 = 'SELECT ' + @cols3 + ' from
(select hdL,DN from #temp1
) x
pivot
( max(hdl)
for DN in (' + @cols3 + ')
) p '
EXECUTE sp_executesql @sql3
DROP TABLE #temp3
DECLARE @SQL4 nvarchar(MAX);
WITH alldates as (
SELECT TOP (DATEDIFF(dd,@fromdate, @endDate)+1)
shift_date = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@fromdate)
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))n1(n)
CROSS JOIN(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))n2(n)
)
SELECT @SQL4 = N'WITH Shifts AS(' + NCHAR(10)
+ N' SELECT emp1.empid,' + NCHAR(10)
+ N' emp.fromdate,' + NCHAR(10)
+ N' emp.EndDate,' + NCHAR(10)
+ N' CASE WHEN emp.shifttype = 4 THEN ''NIGHTSHIFT'' END ShiftType' + NCHAR(10)
+ N' FROM emploee emp1' + NCHAR(10)
+ N' LEFT JOIN dbo.ShiftScheduler emp ON emp.emp_code = emp1.empcode ' + NCHAR(10)
+ N' AND fromdate <= @enddate' + NCHAR(10)
+ N' AND enddate >= @fromdate' + NCHAR(10)
+ N' WHERE emp1.empid = @emp' + NCHAR(10)
+ N')' + NCHAR(10)
+ N'SELECT empid' + NCHAR(10)
+ ( SELECT CHAR(9) + ',MAX(CASE WHEN ''' + CONVERT(char(8), shift_date, 112) + ''' BETWEEN fromdate AND enddate THEN shifttype ELSE ''DAYSHIFT''END) AS ' + QUOTENAME(CONVERT(char(10), shift_date, 120)) + CHAR(10)
FROM alldates
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)')
+ N'FROM Shifts ' + NCHAR(10)
+ N'GROUP BY empid; ' + NCHAR(10);
PRINT @SQL4;
EXECUTE sp_executesql @SQL4, N'@emp int, @fromdate DATETIME, @enddate DATETIME', @emp, @fromdate, @enddate;
END
where is the code for SP called effreportday?
details and sample data for shiftscedluer please
and your expect results for all of this based on your sample data please
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 21, 2016 at 7:28 am
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 d31d31eff Totaleff workingdaysAverage
490DAYSHIFT80DAYSHIFT81DAYSHIFT83DAYSHIFT85DAYSHIFT90DAYSHIFT ....... DAYSHIFT85DAYSHIFT90DAYSHIFT911050Totaleff/workingdays
Workingdays column comes from the totalworkingdays column in our code, please help me
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply