June 20, 2016 at 7:04 am
Please help me in adding dynamic pivot dates based on @fromdate and @enddate
Declare @temp TABLE(shift_date1 datetime,empid1 int,shift_type VARCHAR(30))
declare @fromdate datetime = '2014-02-01'
declare @enddate datetime = '2014-02-20'
declare @emp INT=469;
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 * FROM alldates
,
valid_emp as (
SELECT emp.Emp_Code,
emp.EndDate,emp1.empid
FROM dbo.ShiftScheduler emp
INNER JOIN emploee emp1 ON emp.emp_code = emp1.empcode
WHERE(emp1.empid = @emp)
)
--SELECT * FROM valid_emp
insert into @temp(shift_date1,empid1,shift_type)
SELECT ad.shift_date,
COALESCE(ve.empid, @emp) empid,
CASE
WHEN ve.EndDate IS NULL
THEN 'DAYSHIFT'
ELSE 'NIGHTSHIFT'
END shift_type
FROM alldates AS ad
LEFT OUTER JOIN valid_emp AS ve ON ad.shift_date = ve.EndDate;
--SELECT * INTO #temp1 FROM @temp
--SELECT * FROM #temp1
--DROP TABLE #temp1
Update q set shift_type=case when b.ShiftType=4 then 'NIGHTSHIFT' else 'DAYSHIFT' end
from emploee a inner join @temp q on a.empid =q.empid1
Inner join dbo.ShiftScheduler b on a.empcode =b.Emp_Code and q.shift_date1 between b.Fromdate and b.EndDate
--DROP TABLE #temp1
SELECT * INTO #temp1 FROM @temp
SELECT * FROM #temp1
select * from ( SELECT shift_type ,shift_date1, empid1 FROM #temp1 ) as sourceTable
Pivot (MAX(shift_type) for shift_date1 in ([2015-10-01],[2015-10-02],[2015-10-03],[2015-10-04],[2015-10-05],[2015-10-06],[2015-10-07],[2015-10-08],[2015-10-09],[2015-10-10],[2015-10-11],[2015-10-12],[2015-10-13],[2015-10-14],[2015-10-15],[2015-10-16],[2015-10-17],[2015-10-18],[2015-10-19],[2015-10-20],[2015-10-21],[2015-10-22],[2015-10-23],[2015-10-24],[2015-10-25],[2015-10-26],[2015-10-27],[2015-10-28],[2015-10-29],[2015-10-30],[2015-10-31]) )
June 20, 2016 at 7:08 am
Please, post DDL and sample data. To know how to do it, read the articles from my signature.
June 20, 2016 at 7:41 am
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)
June 20, 2016 at 9:20 am
This is what I got from your code, you might need to tweak it.
To understand how it works, you can read the following articles:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
http://www.sqlservercentral.com/articles/Crosstab/65048/
DECLARE @fromdate datetime = '2014-08-01',
@enddate datetime = '2014-08-30',
@emp int = 469;
DECLARE @SQL 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 @SQL = 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 dbo.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 @SQL;
EXECUTE sp_executesql @SQL, N'@emp int, @fromdate DATETIME, @enddate DATETIME', @emp, @fromdate, @enddate;
Ask any questions that you might have.
June 21, 2016 at 1:35 am
Working has Expected
June 21, 2016 at 8:44 am
GA_SQL (6/21/2016)
Working has Expected
Do you understand how it works? Remember that you'll be the one supporting it.
June 21, 2016 at 8:52 am
Luis Cazares (6/21/2016)
GA_SQL (6/21/2016)
Working has ExpectedDo you understand how it works? Remember that you'll be the one supporting it.
Hi Luis
have you seen this other thread from OP?
http://www.sqlservercentral.com/Forums/FindPost1796115.aspx
this has been ongoing from OP for last two months nearly......
________________________________________________________________
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 9:01 am
J Livingston SQL (6/21/2016)
Luis Cazares (6/21/2016)
GA_SQL (6/21/2016)
Working has ExpectedDo you understand how it works? Remember that you'll be the one supporting it.
Hi Luis
have you seen this other thread from OP?
http://www.sqlservercentral.com/Forums/FindPost1796115.aspx
this has been ongoing from OP for last two months nearly......
I didn't see it before, but it seems that the OP copied the code provided here.
June 21, 2016 at 9:42 am
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 dayshift,nightshift,nightshiftIH with count workingdays
Step 2a: calculate DS and NSIH for each day for th
Step 2b: merge efficiency and shift
June 21, 2016 at 11:35 am
GA_SQL (6/21/2016)
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 dayshift,nightshift,nightshiftIH with count workingdays
Step 2a: calculate DS and NSIH for each day for th
Step 2b: merge efficiency and shift
ok...this is my view on your situation.....get a consultant.
sorry if this seem harsh...but for nearly two months now, you have been drip feeding additional requests in separate threads...and, it would appear, trying your hardest to mash all these together to get a result.
maybe, just maybe...........if you can post the relevant DDL, sample data and your FINAL expected results ...then someone may be able to help you.
fyi......as posted on other threads....pivots are the final result.....so lets have raw data and not some misintrepted DDL that pertains to be a table when its actually the result of some previous pivot code you a have already written.
as already said...apologies and I hope you reach a solution.
best wishes JLS
________________________________________________________________
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 8:02 pm
thanks..almost done..i am in my last step merge of efficiency and shift...i am trying it for past 24 hrs without break i will complete it.Thanks once again
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply