May 10, 2016 at 11:35 pm
hi,
Trying to find the below condition
If he is shift 1 shifttype 1 then the record comes from employee table
if he is shift 2 shifttype 4 then the record comes from shiftscheduler table
So consider in the SP i am giving three inputs fromdate todate and empid then according to the shift it should select the table...shift 1 means from employee table and shift 2 means from shiftscheduler table..please help
THanks in advance
May 10, 2016 at 11:56 pm
With the little information you've provided based on that, if there is only Shift1 or Shift2 as options to keep it simple you can use an IF.
Firstly, prior to the IF... you need to workout the value of the Shift (1 or 2). Store it into a local variable then you can do the below
DECLARE @ShiftID INT = (your logic...)
IF <something> = @ShiftID
BEGIN
-- logic for employee table
END
ELSE
BEGIN
-- logic for shiftscheduler table
END
The more detail you put into your question will provide a better answer that what's above. There can be many ways to target this but based on your simple requirement this should be sufficient...
May 11, 2016 at 12:24 am
If he is shift 1 shifttype 1then the record comes from employee table
if he is shift 2 shifttype 4 then the record comes from shiftscheduler table
For example :
I want to know the details empid : 3321 From date 1/1/2015 to 1/10/2015
1/1/2015 -- shift 1 shifttype 1
1/2/2015 -- shift 1 shifttype 1
1/3/2015 -- shift 1 shifttype 1
1/4/2015 -- shift 1 shifttype 1
Still this time record comes from employee table
now shift changes
1/5/2015 --shift 2 shifttype 4
1/6/2015 --shift 2 shifttype 4
Above 2 records comes from shiftscheduler table
Again shift changes
1/7/2015 -- shift 1 shifttype 1
1/8/2015 -- shift 1 shifttype 1
Above 2 records comes from employee table
Again Shift changes
1/9/2015 --shift 2 shifttype 4
1/10/2015 --shift 2 shifttype 4
Above 2 records comes from shiftscheduler table
My Exact output reqd is if i giving date range then it should all the dates shift and shift type..
Thanks for ur reply please help
May 11, 2016 at 12:43 am
With what you've just provided, then the above I mentioned earlier isn't suited in this case and a bit more work will need to happen.
What have you done so far to test your query? can you post it so I can see and modify if needed.... also if you could provide sample DDL and Data so its easier for me without needing to create.
May 11, 2016 at 1:08 am
ALTER PROCEDURE Emp_Shift1
@empcode varchar(50),
@FromDate datetime,
@EndDate datetime
AS
BEGIN
DECLARE @TotDays INT
DECLARE @CNT INT
SET @TotDays = DATEDIFF(DD,@FromDate,@EndDate)-- [NO OF DAYS between two dates]
PRINT @TotDays
SET @CNT = 0
WHILE @TotDays >= @CNT -- repeat for all days
BEGIN
IF EXISTS (SELECT Emp_Code,ShiftType=2 FROM dbo.ShiftScheduler )
BEGIN
SELECT FromDate,enddate,Shift,ShiftType,Emp_Code FROM dbo.ShiftScheduler WHERE EndDate between @FromDate and @EndDate and Emp_Code=@empcode
END
ELSE
IF EXISTS (SELECT ShiftType=1 FROM emploee )
BEGIN
SELECT empcode,Shift,ShiftType FROM dbo.emploee WHERE empcode=@empcode
END
SET @CNT = @CNT + 1
END
END
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 1:27 am
You can use a UNION to combine your result set based on your 3 values, this is one way.
I've ignored the shift type for the time being as I don't see a relevance if everything in the table Employee is 1 and ShiftScheduler is 2 ... if this is needed then you can modify SP.
Is the outcome what you expected?
ALTER PROCEDURE Emp_Shift1
(
@empcode varchar(50),
@FromDate datetime,
@EndDate datetime
)
AS
BEGIN
/*
-- test variables
DECLARE @empcode varchar(50) = 3321
DECLARE @FromDate datetime = '2015-01-01'
DECLARE @EndDate datetime = '2015-01-10'
*/
SELECT
FromDate,
EndDate,
[Shift],
ShiftType,
Emp_Code
FROM
dbo.ShiftScheduler
WHERE
Emp_code = @empcode
and FromDate >= @FromDate
and EndDate <= @EndDate
UNION ALL
SELECT
FromDate,
EndDate,
[Shift],
ShiftType,
Emp_Code
FROM
dbo.Employee
WHERE
Emp_code = @empcode
and FromDate >= @FromDate
and EndDate <= @EndDate
END
May 11, 2016 at 1:38 am
Thank you so much it is working has expected...Thanks a lot
May 11, 2016 at 2:19 am
ganapathy.arvindan (5/11/2016)
Thank you so much it is working has expected...Thanks a lot
No problem, just as an FYI... not sure if its an assignment or actual work related but if its an assignment and you've designed the database, you can improve the design which will then also improve your stored procedure.
If you can't alter the design then stick with this way.
May 11, 2016 at 4:19 am
Can you help me in this...
Same source table
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
EXEC Emp_Shift1 2190,'2016-04-01','2016-04-24'
Empidd1shiftd1shifttype
112190
242190
Two issues here one is i need all the shift and shifttype columns in single row...d1shift d1shifttype d2shift d2shifttype d3shift d3shifttype...d1 represents day 1 d2 represents day 2
second is in the above output instead of d1shifttype column it displays empid...PLEASE HELP
Thanks for the code earlier
May 11, 2016 at 6:39 am
I'll need to look at this tomorrow when infront of a computer. To make it easier, with the output can you format it better for first part.
May 11, 2016 at 6:55 am
I done with the output..only issue now is
Empidd1shiftd1shifttyped2shiftd2shifttyped3shiftd3shifttyped4shiftd4shifttype
1255 2 1 2 1 1 1 2 1
But the expected is
for the remaining days it should display Null or 0..starting from d5shift d5shifttype d6shift d6shifttype ..................d30shift d30shifftype
Code which is altered
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
EXEC Emp_Shift1 1255,'2016-04-01','2016-04-24'
May 11, 2016 at 11:35 pm
empidD1shiftD1sstypeD2shiftD2SstypeD3shiftD3sstypeD4shiftD4shifttype....Till d30shifttype
3321 2 4 1 1 2 4 1 1 Till 30th day
May 12, 2016 at 12:13 am
There are syntax errors in your code. Since the tables you provided yesterday,columns names have changed and some others have been added.
May 12, 2016 at 12:20 am
You don't really have a new column for each day of the month, do you? If you do, that's the source of your problem.
A little normalization is a beautiful thing.
May 12, 2016 at 12:31 am
pietlinden (5/12/2016)
You don't really have a new column for each day of the month, do you? If you do, that's the source of your problem.A little normalization is a beautiful thing.
raised this yesterday with the OP. Wasn't sure if its actual company work or assignment/homework. There's duplication of data everywhere.
Design should change, but maybe the OP doesn't have the ability too... so just solving it on what is provided, albeit not being best way forward.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply