June 13, 2016 at 2:00 am
Thank you so much it is working as expected...
June 13, 2016 at 10:03 am
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' + 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))
)
SELECT
--c.thedate,
c.DN,
-- h.date1,
-- DATEPART(dw, c.thedate) dw,
-- DATENAME(dw, c.thedate),
--ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) dwrn,
CASE
WHEN h.date1 > 0 THEN 'H'
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 into #temp
FROM yourcalendar AS c
LEFT OUTER JOIN Holidaymaster AS h ON c.thedate = h.Date1
ORDER BY c.thedate
select * from #temp
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + (DN)
from #temp
group by DN, hdL
order by convert(int,substring(DN,2,len(DN)))
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @cols
set @query = 'SELECT ' + @cols + ' from
(
select hdL, DN
from #temp
) x
pivot
(
max(hdl)
for DN in (' + @cols + ')
) p '
execute(@query)
June 13, 2016 at 10:19 am
I am still concerned about your insistence on having indeterminate column names (D1----D31).
what is your reasoning to do this instead of having actual dates as column names in your pivot?
as far as I can fathom....D1 could be any date you choose...it doesnt have to be the start of a month/year/week......how do your end users decipher what is "D1"?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 15, 2016 at 12:19 pm
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))
)
SELECT
--c.thedate,
c.DN,
-- h.date1,
-- DATEPART(dw, c.thedate) dw,
-- DATENAME(dw, c.thedate),
--ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) dwrn,
CASE
WHEN h.date1 > 0 THEN 'H'
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 into #temp
FROM yourcalendar AS c
LEFT OUTER JOIN Holidaymaster AS h ON c.thedate = h.Date1
ORDER BY c.thedate
--select * from #temp
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + (DN)
from #temp
group by DN, hdL
order by convert(int,substring(DN,2,len(DN)))
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--select @cols
set @query = 'SELECT ' + @cols + ' from
(
select hdL, DN
from #temp
) x
pivot
(
max(hdl)
for DN in (' + @cols + ')
) p '
execute(@query)
In the above code it generates D1 to D31 , i need total of W's in new column TotalW like below
DROP TABLE mytable
CREATE TABLE mytable(
D1 VARCHAR NOT NULL
,D2 VARCHAR NOT NULL
,D3 VARCHAR NOT NULL
,D4 VARCHAR NOT NULL
,D5 VARCHAR NOT NULL
,D6 VARCHAR NOT NULL
,D7 VARCHAR NOT NULL
,D8 VARCHAR NOT NULL
,D9 VARCHAR NOT NULL
,D10 VARCHAR NOT NULL
,D11 VARCHAR NOT NULL
,D12 VARCHAR NOT NULL
,D13 VARCHAR NOT NULL
,D14 VARCHAR NOT NULL
,D15 VARCHAR NOT NULL
,D16 VARCHAR NOT NULL
,D17 VARCHAR NOT NULL
,D18 VARCHAR NOT NULL
,D19 VARCHAR NOT NULL
,D20 VARCHAR NOT NULL
,D21 VARCHAR NOT NULL
,D22 VARCHAR NOT NULL
,D23 VARCHAR NOT NULL
,D24 VARCHAR NOT NULL
,D25 VARCHAR NOT NULL
,D26 VARCHAR NOT NULL
,D27 VARCHAR NOT NULL
,D28 VARCHAR NOT NULL
,D29 VARCHAR NOT NULL
,D30 VARCHAR NOT NULL
,D31 VARCHAR NOT NULL
,TotalW INTEGER
)
INSERT INTO mytable(D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31,TotalW) VALUES ('H','WO','WO','W','H','W','W','W','W','WO','W','W','W','W','W','W','WO','W','W','W','W','W','WO','WO','W','H','W','W','W','WO','WO',20)
June 15, 2016 at 12:28 pm
try looking at this part of your code
select * from #temp and maybe count from there?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 17, 2016 at 7:49 am
did you manage to solve this successfully ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 19, 2016 at 12:06 am
Still not able to add w's
June 19, 2016 at 7:58 am
as a start
SELECT COUNT(hdL)
FROM #temp
WHERE (hdL = 'w')
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 20, 2016 at 1:40 am
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 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))
)
SELECT
--c.thedate,
c.DN,
-- h.date1,
-- DATEPART(dw, c.thedate) dw,
-- DATENAME(dw, c.thedate),
--ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) dwrn,
CASE
WHEN h.Holiday_date > 0 THEN 'H'
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'
WHEN DATEPART(dw, c.thedate) = 5 and ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) IN (1,2,3,4,5) THEN 'W'
ELSE 'WO'
END hdL into #temp1
FROM yourcalendar1 AS c
LEFT OUTER JOIN ihsmaster.dbo.Holidaymaster AS h ON c.thedate = h.Holiday_date
ORDER BY c.thedate
DECLARE @cols1 AS NVARCHAR(MAX),
@query1 AS NVARCHAR(MAX)
CREATE TABLE #temp2([Count1] INT)
INSERT into #temp2 SELECT COUNT(hdL) FROM #temp1 WHERE (hdL = 'w')
select @cols1 = STUFF((SELECT ',' + (DN)
from #temp1
group by DN, hdL
order by convert(int,substring(DN,2,len(DN)))
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--select @cols
set @query1 = 'SELECT ' + @cols1 + ' from
(
select hdL,DN
from #temp1
) x
pivot
(
max(hdl)
for DN in (' + @cols1 + ')
) p '
execute(@query1)
June 20, 2016 at 7:43 am
ganapathy.arvindan (6/20/2016)
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 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))
)
SELECT
--c.thedate,
c.DN,
-- h.date1,
-- DATEPART(dw, c.thedate) dw,
-- DATENAME(dw, c.thedate),
--ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) dwrn,
CASE
WHEN h.Holiday_date > 0 THEN 'H'
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'
WHEN DATEPART(dw, c.thedate) = 5 and ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) IN (1,2,3,4,5) THEN 'W'
ELSE 'WO'
END hdL into #temp1
FROM yourcalendar1 AS c
LEFT OUTER JOIN ihsmaster.dbo.Holidaymaster AS h ON c.thedate = h.Holiday_date
ORDER BY c.thedate
DECLARE @cols1 AS NVARCHAR(MAX),
@query1 AS NVARCHAR(MAX)
CREATE TABLE #temp2([Count1] INT)
INSERT into #temp2 SELECT COUNT(hdL) FROM #temp1 WHERE (hdL = 'w')
select @cols1 = STUFF((SELECT ',' + (DN)
from #temp1
group by DN, hdL
order by convert(int,substring(DN,2,len(DN)))
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--select @cols
set @query1 = 'SELECT ' + @cols1 + ' from
(
select hdL,DN
from #temp1
) x
pivot
(
max(hdl)
for DN in (' + @cols1 + ')
) p '
execute(@query1)
you have created a second temp table
CREATE TABLE #temp2([Count1] INT)
INSERT into #temp2 SELECT COUNT(hdL) FROM #temp1 WHERE (hdL = 'w')
but your pivot is only based on #temp1....you need to get both sets of results into one data set ......(maybe use UNION)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 20, 2016 at 7:49 am
I Tried with union as well, but while adding the column name count(hdL) it shows error..Please reply
June 20, 2016 at 7:57 am
ganapathy.arvindan (6/20/2016)
I Tried with union as well, but while adding the column name count(hdL) it shows error..Please reply
show what code you tried (I cant see your screen :-)) and the full error message.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 20, 2016 at 8:10 am
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 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))
)
--SELECT * FROM yourcalendar1
SELECT
--c.thedate,
c.DN,
-- h.date1,
-- DATEPART(dw, c.thedate) dw,
-- DATENAME(dw, c.thedate),
--ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) dwrn,
CASE
WHEN h.Holiday_date > 0 THEN 'H'
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'
WHEN DATEPART(dw, c.thedate) = 5 and ROW_NUMBER() OVER (PARTITION BY DATEPART(dw, c.thedate) ORDER BY c.thedate) IN (1,2,3,4,5) THEN 'W'
ELSE 'WO'
END hdL into #temp1
FROM yourcalendar1 AS c
LEFT OUTER JOIN Holidaymaster AS h ON c.thedate = h.Holiday_date
ORDER BY c.thedate
SELECT * FROM #temp1
INSERT into #temp2 SELECT COUNT(hdL) FROM #temp1 WHERE (hdL = 'w')
DECLARE @cols1 AS NVARCHAR(MAX),
@query1 AS NVARCHAR(MAX)
select @cols1 = STUFF((SELECT ',' + (DN)+','+ COUNT(CAST(hdL as NVARCHAR(MAX)))
from #temp1 union #temp2
WHERE hdL='w'
group by DN
order by convert(int,substring(DN,2,len(DN)))
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,'')
--select @cols
set @query1 = 'SELECT ' + @cols1 + ' from
(
select hdL,DN
from #temp1
) x
pivot
(
max(hdl)
for DN in (' + @cols1 + ')
) p '
execute(@query1)
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'yourcalendar1'.
Msg 102, Level 15, State 1, Line 40
Incorrect syntax near '#temp2'.
June 20, 2016 at 8:26 am
USE [tempdb]
CREATE TABLE [dbo].[#Holidaymaster](
[Holiday_ID] [int] NULL,
[Holiday] [varchar](50) NULL,
[Date1] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[#Holidaymaster] ([Holiday_ID], [Holiday], [Date1]) VALUES (1, N'New Year', '2016-01-01' )
INSERT [dbo].[#Holidaymaster] ([Holiday_ID], [Holiday], [Date1]) VALUES (2, N'Pongal', '2016-01-16' )
INSERT [dbo].[#Holidaymaster] ([Holiday_ID], [Holiday], [Date1]) VALUES (3, N'Labour day', '2016-05-01')
INSERT [dbo].[#Holidaymaster] ([Holiday_ID], [Holiday], [Date1]) VALUES (4, N'Christmas', '2016-12-26')
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 'TotalW', 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
DROP TABLE #Holidaymaster
________________________________________________________________
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 6:34 am
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 include this code below once it displays shift like this
empidd1d1effd2d2effd3d3effd4d4effd5d5effd6d6effd7d7effd8d8effd9 d9eff ...d31 d31eff
d1eff,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
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply