May 12, 2016 at 5:55 am
here is my thoughts on one way I would go about this....it uses PIVOT and takes the date spread as column headers. It concatenates Shift/Shift Type so that there is no need to double up on the columns.
Its not what you have asked for, but maybe give you some ideas on how to solve your problem
CREATE TABLE #ShiftScheduler
(FromDate DATETIME,
ENDDATE DATETIME,
Shift INT,
ShiftType INT,
EmpID INT
);
CREATE TABLE #Employee
(FromDate DATETIME,
ENDDATE DATETIME,
Shift INT,
ShiftType INT,
EmpID INT
);
INSERT INTO #Employee VALUES('1/1/2015','1/4/2015',1,1,3321)
INSERT INTO #Employee VALUES('1/7/2015 ','1/8/2015 ',1,1,3321)
INSERT INTO #ShiftScheduler VALUES('1/5/2015','1/6/2015',2,4,3321)
INSERT INTO #ShiftScheduler VALUES('1/9/2015','1/10/2015',2,4,3321)
INSERT INTO #Employee VALUES('1/3/2015','1/8/2015',1,1,9999)
INSERT INTO #Employee VALUES('1/16/2015 ','1/20/2015 ',1,1,9999)
INSERT INTO #ShiftScheduler VALUES('1/10/2015','1/11/2015',2,4,3321)
INSERT INTO #ShiftScheduler VALUES('1/13/2015','1/13/2015',2,4,3321)
INSERT INTO #Employee VALUES('12/25/2014','12/25/2014',1,1,8888)
INSERT INTO #ShiftScheduler VALUES('12/27/2014','12/27/2014',2,4,7777)
DECLARE @fromdate datetime = '20141225'
DECLARE @enddate datetime = '20150131'
DECLARE @cols NVARCHAR(MAX);
DECLARE @colnames NVARCHAR(MAX);
DECLARE @sql nvarchar(4000);
SELECT *
INTO #shifts
FROM
( SELECT *
FROM #Employee
UNION ALL
SELECT *
FROM #ShiftScheduler
) x;
SELECT s.EmpID
,'Shift ' + CONVERT(varchar(8),s.Shift) + ' Type ' + CONVERT(varchar(8),s.ShiftType) shift_info
,convert(CHAR(10), shift_date, 103) sdate
INTO #allshifts
FROM #shifts s
CROSS APPLY
(
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)
) alldays
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(shift_date)
from
(SELECT TOP (DATEDIFF(dd,@fromdate, @endDate)+1)
shift_date = convert(CHAR(10), DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@fromdate) , 103)
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))x
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SELECT @colnames = STUFF((SELECT ', isnull(' + QUOTENAME(shift_date) +', '' '') as '+ QUOTENAME(shift_date)
from
(SELECT TOP (DATEDIFF(dd,@fromdate, @endDate)+1)
shift_date = convert(CHAR(10), DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@fromdate) , 103)
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))x
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @sql =N'SELECT EmpID, ' + @colnames + ' FROM
( SELECT EmpID, sdate AS [Date] , shift_info FROM #allshifts) p
PIVOT
( Max(shift_info) FOR [Date] IN (' + @cols + ')
)
AS pvt';
EXECUTE sp_executesql @sql
DROP TABLE #allshifts;
DROP TABLE #shifts;
DROP TABLE #ShiftScheduler
DROP TABLE #Employee
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 12, 2016 at 6:12 am
ganapathy.arvindan (5/12/2016)
Yes Dates would be easier but 01012016shift 01012016shifttype looks not pretty..for the understanding i need all the d1shift d1shifttype d2shift d2shifttype......d30shift d30shifttype..if i deosn't have value then it should display has 0Thanks
Data should be just data. Data shouldn't be pretty. Make stuff pretty on the front-end through reports, formatting, whatever. If it's a date, storing it as a date, regardless of how ugly it is, should be how you do things. You're seriously impacting your ability to work with data by trying to make it pretty for storage. It's going to hurt functionality and performance. In short, this is a bad approach.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 12, 2016 at 6:24 am
Thanks..for all your effort
Please suggest me an idea for this scenario(this is root cause of the bug)
ex: while the employee joins in the company his shift has been allocated to 1 at that time date 2/12/2016(joining date)
now the employee moves shift 2 on 2/20/2016 this updation happens only on shiftscheduler...No alter done to employee table...
while i am fetching this one the problem is with union all..if no data found in shift scheduler then it should given output has 1...but it is giving only 4 times since the output of union all statement is 4 records..so only d1 to d4 displays...i want all the d1 to d30
SELECT
e.empid,
SS.EndDate,
SS.[Shift],
SS.ShiftType
FROM
dbo.ShiftScheduler SS,Ihsmaster_delop.dbo.Emploee e WHERE e.empcode=SS.Emp_Code
AND
e.empid = 1255
and FromDate >= '2016-04-01'
and EndDate <= '2016-04-24'
UNION ALL
SELECT
empid,
'2016-04-24', -- this date is not available in employee table
[Shift],
ShiftType
FROM
Ihsmaster_delop.dbo.Emploee
WHERE empid = 1255
Any suggestions to find it
May 12, 2016 at 6:29 am
please provide some sample set up and data scripts .....then provide the code that works with this sample and then provide the expeted results for this sample.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 12, 2016 at 7:11 am
--DDL:
Create table ShiftScheduler(emp_code int,Enddate DATETIME,shift INT,shifttype INT)
INSERT INTO ShiftScheduler VALUES(635,'2014-05-03',2,1)
INSERT INTO ShiftScheduler VALUES(635,'2014-11-22',2,1)
INSERT INTO ShiftScheduler VALUES(635,'2015-10-28',1,1)
Create table employee(empid INT,shift INT,shifttype INT)
INSERT INTO employee VALUES(635,1,1)
--CODE:
ALTER PROCEDURE Emp_Shift1
(
@empcode INT,
@FromDate datetime,
@EndDate datetime
)
AS
BEGIN
--DROP TABLE #t
CREATE TABLE #t(
[Empid] INT,
[Date] date,
[shift] int,
[shifttype] int
)
INSERT INTO #t
SELECT
e.empid,
SS.EndDate,
SS.[Shift],
SS.ShiftType
FROM
dbo.ShiftScheduler SS,Ihsmaster_delop.dbo.Emploee e WHERE e.empcode=SS.Emp_Code
AND
e.empid = @empcode
and FromDate >= @FromDate
and EndDate <= @EndDate
UNION ALL
SELECT
empid,
@EndDate,
[Shift],
ShiftType
FROM
Ihsmaster_delop.dbo.Emploee
WHERE
empid = @empcode
SELECT *, ROW_NUMBER() OVER(PARTITION BY [Empid] ORDER BY [Date]) AS row_num
INTO #tmp
FROM #t
declare @sql varchar(max);
set @sql= STUFF((select ', MAX(case when row_num = ' + CONVERT(varchar, row_num) + ' then [shift] end ) as ' + QUOTENAME('d' + CONVERT(varchar, row_num) + 'shift')
+ ', MAX(case when row_num = ' + CONVERT(varchar, row_num) + ' then [shifttype] end ) as ' + QUOTENAME('d' + CONVERT(varchar, row_num) + 'shifttype')
from #tmp
GROUP BY row_num
ORDER BY row_num
FOR XML PATH('')), 1, 1, '');
set @sql= 'select [Empid]
, ' + @sql + '
from #tmp
Group by [Empid]';
-- print @sql
exec(@sql);
END
EXEC Emp_Shift1 1255,'2016-04-01','2016-04-24'
--OUTPUT
Empid d1shift d1shifttype d2shift d2shifttype d3shift d3shifttype d4shift d4shifttype
1255 2 1 2 1 1 1 2 1
EXPECTED OUTPUT
Empid d1shift d1shifttype d2shift d2shifttype d3shift d3shifttype d4shift d4shifttype d5shift d5shifttype ................d24shift d24shifttype
1255 2 1 2 1 1 1 2 1 1 1 1 1
May 12, 2016 at 7:50 am
sorry...but code wont parse.......cant help you until it does
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 12, 2016 at 8:07 am
ganapathy.arvindan (5/12/2016)
--DDL:Create table ShiftScheduler(emp_code int,Enddate DATETIME,shift INT,shifttype INT)
INSERT INTO ShiftScheduler VALUES(635,'2014-05-03',2,1)
INSERT INTO ShiftScheduler VALUES(635,'2014-11-22',2,1)
INSERT INTO ShiftScheduler VALUES(635,'2015-10-28',1,1)
Create table employee(empid INT,shift INT,shifttype INT)
INSERT INTO employee VALUES(635,1,1)
EXEC Emp_Shift1 1255,'2016-04-01','2016-04-24'
My Dear Friend.......please please please, put the effort into your sample data.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 12, 2016 at 8:11 am
--DDL:
Create table ShiftScheduler(emp_code int,Enddate DATETIME,shift INT,shifttype INT)
INSERT INTO ShiftScheduler VALUES(635,'2014-05-03',2,1)
INSERT INTO ShiftScheduler VALUES(635,'2014-11-22',2,1)
INSERT INTO ShiftScheduler VALUES(635,'2015-10-28',1,1)
Create table emploee(empid INT,shift INT,shifttype INT)
INSERT INTO emploee VALUES(635,1,1)
--CODE:
CREATE PROCEDURE Emp_Shift1
(
@empcode INT,
@FromDate datetime,
@EndDate datetime
)
AS
BEGIN
--DROP TABLE #t
CREATE TABLE #t(
[Empid] INT,
[Date] date,
[shift] int,
[shifttype] int
)
INSERT INTO #t
SELECT
e.empid,
SS.EndDate,
SS.[Shift],
SS.ShiftType
FROM
dbo.ShiftScheduler SS,Emploee e WHERE e.empid=SS.Emp_Code
AND
e.empid = @empcode
and Enddate >= @FromDate
and EndDate <= @EndDate
UNION ALL
SELECT
empid,
@EndDate,
[Shift],
ShiftType
FROM
Emploee
WHERE
empid = @empcode
SELECT *, ROW_NUMBER() OVER(PARTITION BY [Empid] ORDER BY [Date]) AS row_num
INTO #tmp
FROM #t
declare @sql varchar(max);
set @sql= STUFF((select ', MAX(case when row_num = ' + CONVERT(varchar, row_num) + ' then [shift] end ) as ' + QUOTENAME('d' + CONVERT(varchar, row_num) + 'shift')
+ ', MAX(case when row_num = ' + CONVERT(varchar, row_num) + ' then [shifttype] end ) as ' + QUOTENAME('d' + CONVERT(varchar, row_num) + 'shifttype')
from #tmp
GROUP BY row_num
ORDER BY row_num
FOR XML PATH('')), 1, 1, '');
set @sql= 'select [Empid]
, ' + @sql + '
from #tmp
Group by [Empid]';
-- print @sql
exec(@sql);
END
May 12, 2016 at 8:39 am
Create table ShiftScheduler(emp_code int,Enddate DATETIME,shift INT,shifttype INT)
INSERT INTO ShiftScheduler VALUES(635,'2014-05-03',2,1)
INSERT INTO ShiftScheduler VALUES(635,'2014-11-22',2,1)
INSERT INTO ShiftScheduler VALUES(635,'2015-10-28',1,1)
Create table emploee(empid INT,shift INT,shifttype INT)
INSERT INTO emploee VALUES(635,1,1)
well this looks different from what you previously posted.......
ShiftScheduler only has an "Enddate".....no "Fromdate"....is this correct?
emploee (correct spelling?) has no dates at all...is this correct?
are these "tables" actually views from some other table...and that is why there structure is changing?
if so, it maybe prudent to give us sight of the master data.
cheers
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 12, 2016 at 8:42 am
it is 100% correct it is working
May 12, 2016 at 8:44 am
ganapathy.arvindan (5/12/2016)
it is 100% correct it is working
"it is working".....you have solved your problem?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 12, 2016 at 11:26 pm
i said coding is parsing...still i have same bug
May 13, 2016 at 11:53 am
OK, I think you need to be more thorough. You are trying to continually build on something that doesn't work, modifying a bit, but not modifying and understanding in an organized fashion. Really, this is where testing helps, but also you need to proceed methodically.
Don't use "same" bug as that doesn't necessarily help. You want to specifically note issues. I'd suggest you organize, and don't post until you are sure you've got this:
1. DDL for the source table(s). Get this organized and when you paste this into the edit box, make sure you have clicked the "Code=sql" item to the left. Format the code.
2. Sample data. Get a set of specific sample data and use insert statements. There have been some in this thread, but get a consistent set to talk about a problem.
3. Give the query code. Again, formatted, but what code are you running. If you use code from a person here, such as the item in your post a few back, give it a comment (--query 4 to pivot data). That way we can talk about it and understand what code is being referenced.
4. Use a test. Here's an example. Changed the insert into #expected to be the results you need. This test will call your proc, and compare the results returned with what is in the #expected table:
Once you have an issue, you can sort out which columns/rows don't match.
Proceed logically and you can solve this.
EXEC tSQLt.NewTestClass
@ClassName = N'SingleRow';
GO
CREATE PROCEDURE SingleRow.[test get data from single row with one employee]
AS
BEGIN
EXEC tSQLt.FakeTable
'ShiftScheduler';
INSERT INTO ShiftScheduler
VALUES
( 635, '2014-05-03', 2, 1 )
, ( 635, '2014-11-22', 2, 1 )
, ( 635, '2015-10-28', 1, 1 );
EXEC tSQLt.FakeTable
@TableName = N'emploee';
INSERT INTO emploee
VALUES
( 635, 1, 1 );
CREATE TABLE #Expected
(
Empid INT
, d1shift INT
, d1shifttype INT
, d2shift INT
, d2shifttype INT
, d3shift INT
, d3shifttype INT
);
INSERT #Expected
VALUES
( 2424, 2, 4, 2, 4, 2, 4 );
SELECT
*
INTO
#actual
FROM
#Expected AS e
WHERE
1 = 0;
-- act
INSERT #actual
EXEC Emp_Shift1;
-- assert
EXEC tSQLt.AssertEqualsTable
@Expected = N'#expected'
, @Actual = N'#actual'
, @FailMsg = N'The emp_shift1 proc does not work';
END;
GO
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply