May 9, 2016 at 4:26 am
Hi..There are two tables employee and Shiftscheduler which to include where...since yourtable i am not get it exactly
May 11, 2016 at 2:13 am
Hi,
Almost done with it..please check some bugs still
ALTER PROCEDURE Emp_Shift1
(
@empcode INT,
@FromDate datetime,
@EndDate datetime
)
AS
BEGIN
--DROP TABLE #t
CREATE TABLE #t(
[Date] date,
[Empid] INT,
[shift] int,
[shifttype] int
)
INSERT INTO #t
SELECT
SS.EndDate,
SS.[Shift],
SS.ShiftType,
e.empid
FROM
dbo.ShiftScheduler SS,dbo.Emploee e WHERE e.empcode=SS.Emp_Code
AND
e.empid = @empcode
and FromDate >= @FromDate
and EndDate <= @EndDate
UNION ALL
SELECT
@EndDate,
[Shift],
ShiftType,
empid
FROM
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
output is
Empidd1shiftd1shifttype
1 1 2190
2 4 2190
Problem here is empid coming in d1shifttype column.. also i want all the shift details in single column like d1shift,d1shiftype,d2shift,d2shifttype,d3shidt,d3shifttype
Please help
For your reference added some DDL
CREATE TABLE dbo.ShiftScheduler(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))
CREATE TABLE dbo.Employee(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))
INSERT INTO dbo.Employee values('1/1/2015','1/4/2015',1,1,3321)
INSERT INTO dbo.Employee values('1/7/2015 ','1/8/2015 ',1,1,3321)
INSERT INTO dbo.ShiftScheduler VALUES('1/5/2015','1/6/2015',2,4,3321)
INSERT INTO dbo.ShiftScheduler VALUES('1/9/2015','1/10/2015',2,4,3321)
May 11, 2016 at 2:23 am
your sample data set up doesnt work......plus I would suggest you extend it to include all possible variants ,
CREATE TABLE dbo.ShiftScheduler(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))
CREATE TABLE dbo.Employee(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))
INSERT INTO dbo.Employee values('1/1/2015','1/4/2015',1,1,3321)
INSERT INTO dbo.Employee values('1/7/2015 ','1/8/2015 ',1,1,3321)
INSERT INTO dbo.ShiftScheduler VALUES('1/5/2015','1/6/2015',2,4,3321)
INSERT INTO dbo.ShiftScheduler VALUES('1/9/2015','1/10/2015',2,4,3321)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 11, 2016 at 2:39 am
CREATE TABLE dbo.ShiftScheduler(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))
CREATE TABLE dbo.Emploee(FromDate DATETIME,Shift INT,ShiftType INT ,empid VARCHAR(50))
INSERT INTO dbo.Emploee values('1/1/2015','1/4/2015',1,1,3321)
INSERT INTO dbo.Emploee values('1/7/2015 ','1/8/2015 ',1,1,3321)
INSERT INTO dbo.ShiftScheduler VALUES('1/5/2015','1/6/2015',2,4,3321)
INSERT INTO dbo.ShiftScheduler VALUES('1/9/2015','1/10/2015',2,4,3321)
May 11, 2016 at 3:04 am
ganapathy.arvindan (5/11/2016)
CREATE TABLE dbo.ShiftScheduler(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))CREATE TABLE dbo.Emploee(FromDate DATETIME,Shift INT,ShiftType INT ,empid VARCHAR(50))
INSERT INTO dbo.Emploee values('1/1/2015','1/4/2015',1,1,3321)
INSERT INTO dbo.Emploee values('1/7/2015 ','1/8/2015 ',1,1,3321)
INSERT INTO dbo.ShiftScheduler VALUES('1/5/2015','1/6/2015',2,4,3321)
INSERT INTO dbo.ShiftScheduler VALUES('1/9/2015','1/10/2015',2,4,3321)
did you run this code....I get
Msg 213, Level 16, State 1, Line 3
Column name or number of supplied values does not match table definition.
you are trying to insert two dates where only one date (Fromdate) is in the tables.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 11, 2016 at 5:20 am
Totally this is dummy(created same has source)
CREATE TABLE dbo.ShiftScheduler1(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))
CREATE TABLE dbo.Employee(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))
INSERT INTO dbo.Employee values('1/1/2015',1,1,3321)
INSERT INTO dbo.Employee values('1/7/2015',1,1,3321)
INSERT INTO dbo.ShiftScheduler1 VALUES('1/5/2015',2,4,3321)
INSERT INTO dbo.ShiftScheduler1 VALUES('1/9/2015',2,4,3321)
May 11, 2016 at 5:23 am
Hi,
Add the necessary column in the create statement.
Change the order of the insert and then use the below statement.
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);
Im getting the o/p as below.
Empidd1shiftd1shifttyped2shiftd2shifttyped3shiftd3shifttyped4shiftd4shifttyped5shiftd5shifttyped6shiftd6shifttype
3321112424112424
May 11, 2016 at 5:50 am
Hi,
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 empid,shift,shifttype, 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
getting output like this....
Empidd1shiftd1shifttyped2shiftd2shifttype
2190 2 4 1 1
Actually only one record is from 2 and 4 type From date and End Date
Emp_Code ShiftShiftType FromDate EndDate
2190 2 4 2016-04-01 00:00:00.0002016-04-03 00:00:00.000
So the output should be like this
Empidd1shiftd1shifttyped2shiftd2shifttype d3shiftd3shifttype d4shiftd4shifttype d5shiftd5shifttype
2190 2 4 2 4 2 4 1 1 1 1
Please help
May 11, 2016 at 6:41 am
Empidd1shiftd1shifttyped2shiftd2shifttype d3shiftd3shifttype d4shiftd4shifttype d5shiftd5shifttype
21902 4 2 4 2 4 1 1 1 1
is this expected output to be in separate columns for each result...or is it just one single column?
if its one single column what is this being used for, becasue I cannot see how it is going to be useful if someone has to read it?
also, it would really make it a whole lot easier if you could tie your sample data names with the procedure you keep posting...I for one am not going to spend time altering column and tables names for you.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 11, 2016 at 6:57 am
Empid d1shiftd1shifttyped2shiftd2shifttyped3shiftd3shifttyped4shiftd4shifttype
1255 21 2 1 1 1 2 1
for the remaining days it should display Null or 0..starting from d5shift d5shifttype d6shift d6shifttype ..................d30shift d30shifftype
May 11, 2016 at 7:11 am
ganapathy.arvindan (5/11/2016)
Empid d1shiftd1shifttyped2shiftd2shifttyped3shiftd3shifttyped4shiftd4shifttype1255 21 2 1 1 1 2 1
for the remaining days it should display Null or 0..starting from d5shift d5shifttype d6shift d6shifttype ..................d30shift d30shifftype
is this one column output...or is empid, d1shift,d1shiftype etc in separate columns...
eg
+---------------------------------------------------------------------------------------------------------------------+
¦ ¦ Empid ¦ d1shift ¦ d1shifttype ¦ d2shift ¦ d2shifttype ¦ d3shift ¦ d3shifttype ¦ d4shift ¦ d4shifttype ¦
¦------+-------+---------+-------------------+---------+-------------+----------+-------------+---------+-------------¦
¦ 1255 ¦ 2 ¦ 1 ¦ 2 ¦ 1 ¦ 1 ¦ 1 ¦ 2 ¦ 1 ¦ ¦
+---------------------------------------------------------------------------------------------------------------------+
by not formatting your expected results it is not easy to determine
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 11, 2016 at 7:54 am
Yes exactly...same format
May 11, 2016 at 8:46 am
ganapathy.arvindan (5/11/2016)
Yes exactly...same format
I can't believe that this thread is still going strong. Had you taken the time to provide source data, DDL and expected results, as described in the link in my signature, this problem would have been solved by now.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 12, 2016 at 4:15 am
ganapathy.arvindan (5/11/2016)
Empid d1shiftd1shifttyped2shiftd2shifttyped3shiftd3shifttyped4shiftd4shifttype1255 21 2 1 1 1 2 1
for the remaining days it should display Null or 0..starting from d5shift d5shifttype d6shift d6shifttype ..................d30shift d30shifftype
I think that if step thro your code and run this part
SELECT *, ROW_NUMBER() OVER(PARTITION BY [Empid] ORDER BY [Date]) AS row_num
--INTO #tmp
FROM #t
you will start to understand why you are not getting the remaining days you are looking for.
Can I ask why you are using 'D1, D2 etc' rather than actual dates...i would have thought that it would be far easier for end user.....possibly???
________________________________________________________________
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 4:32 am
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 0
Thanks
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply