Sample
Input (Choose it from date to to date)
Date Empid Shift Shifttype
1/1/2014 1424 2 4
1/2/2014 1424 2 4
1/3/2014 1424 2 4
1/4/2014 1424 1 2
Output like
Empid d1shift d1shifttype d2shift d2shifttype d3shift d3shifttype d4shift d4shifttype
2424 2 4 2 4 2 4 1 2
It should process on daily basis..please suggest me any loop will do or any better sugesstion
USE [process]
GO
/****** Object: StoredProcedure [dbo].[Emp_Shift2] Script Date: 05/02/2016 15:01:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Emp_Shift3]
@FromDate datetime,
@EndDate datetime
AS
BEGIN
CREATE TABLE #WorkDays ( EmpId int, EmpName varchar(20), MainTeamId int, WorkDate date )
INSERT INTO #WorkDays ( EmpId, EmpName, MainTeamId, WorkDate )
SELECT [Emp_Code],Shift,ShiftType,CreatedOn FROM ShiftScheduler WHERE EndDate BETWEEN @FromDate and @EndDate;
SELECT *
FROM
(
SELECT
FROM #WorkDays T
CROSS APPLY ( ) AS Calc
) D
PIVOT ( COUNT( WorkDateDay ) FOR WorkDateDay IN ( [d1eff],[d1shifttype] [d2eff],[d2shift],[d2shifttype], [d3Eff],[d3shift],[d3shifttype], [d4eff],[d4shift],
[d4shifttype], [d5Eff],[d5shift],[d5shifttype], [d6Eff],[d6shift],[d6shifttype],[d7],[d7shift],[d7shifttype],[d8Eff],[d8shift],[d8shifttype],
[d9Eff],[d9shift],[d9shifttype], [d10Eff],[d10shift],[d10shifttype],[d11Eff],[d11shift],[d11shifttype], [d12Eff],[d12shift],[d12shifttype],
[d13Eff],[d13shift],[d13shifttype],[d14Eff],[d14shift],[d14shifttype],[d15Eff],[d15shift],[d15shifttype],[d16Eff],[d16shift],[d16shifttype],
[d17Eff],[d17shift],[d17shifttype],[d18Eff],[d18shift],[d18shifttype] ,[d19Eff],[d19shift],[d19shifttype] ,[d20Eff],[d20shift],[d20shifttype],
[d21Eff],[d21shift],[d21shifttype],[d22Eff],[d22shift],[d22shifttype],[d23Eff],[d23shift],[d23shifttype],[d24Eff],[d24shift],[d24shifttype],[d25Eff],
[d25shift],[d25shifttype],[d26Eff],[d26shift],[d26shifttype] ,[d27Eff],[d27shift],[d27shifttype],[d28Eff],[d28shift],[d28shifttype],[d29Eff],[d29shift],
[d29shifttype],[d30Eff],[d30shift],[d30shifttype],[d31Eff],[d31shift],[d31shifttype] )
) PVT
END
Please help me in getting this pivot done