May 12, 2016 at 11:35 pm
i have 2 tables one is emp and another is emp1
Step 1 : IN Stored procedure pass input empid,from_date and end_date
Ex: EXEC shift1 9999,'2016-05-01','2016-05-20'
First date it has to check for the entry of this employee in emp table(i.e) 9999,'2016-05-01'
if record found then it is 'NIGHT SHIFT'
if record not found then i should check with emp1 table if it is there then it is DAY SHIFT
second date it has to check for the entry of this employee in emp table(i.e) 9999,'2016-05-02'
if record found then it is 'NIGHT SHIFT'
if record not found then i should check with emp1 table if it is there then it is DAY SHIFT
Third date it has to check for the entry of this employee in emp table(i.e) 9999,'2016-05-03'
if record found then it is 'NIGHT SHIFT'
if record not found then i should check with emp1 table if it is there then it is DAY SHIFT
Like that it has to continue for all 20 days(as per the exec statement above)
Some sample data:
Create table emp(emp_code int,Enddate DATETIME,shift INT,shifttype INT)
INSERT INTO emp VALUES(635,'2014-05-03',2,1)
INSERT INTO emp VALUES(635,'2014-11-22',2,1)
INSERT INTO emp VALUES(635,'2015-10-28',1,1)
Create table emp1 (empid INT,shift INT,shifttype INT)
INSERT INTO emp1 VALUES(635,1,1)
May 13, 2016 at 5:36 am
possibly ???
note i altered your sample data to get relevant results
Create table emp(emp_code int,Enddate DATETIME,shift INT,shifttype INT)
INSERT INTO emp VALUES(635,'20160503',2,1)
INSERT INTO emp VALUES(635,'20160508',2,1)
INSERT INTO emp VALUES(635,'20160514',1,1)
Create table emp1 (empid INT,shift INT,shifttype INT)
INSERT INTO emp1 VALUES(635,1,1)
--=====================================================
declare @fromdate datetime = '20160501'
declare @enddate datetime = '20160520'
declare @emp INT = 635;
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)
)
,
valid_emp as (
SELECT emp.emp_code,
emp.Enddate
FROM emp
INNER JOIN emp1 ON emp.emp_code = emp1.empid
WHERE(emp1.empid = @emp)
)
SELECT ad.shift_date,
COALESCE(ve.emp_code, @emp) emp_code,
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;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2016 at 6:21 am
small bug...it is showing only enddate is nightshift..for example one employee is in night shift for a week(2,4) then this query working for enddate showing nightshift
Thanks a lot
May 13, 2016 at 6:26 am
ganapathy.arvindan (5/13/2016)
small bug...it is showing only enddate is nightshift..for example one employee is in night shift for a week(2,4) then this query working for enddate showing nightshiftThanks a lot
can you please post the results (as a table please) you want based on the sample data.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2016 at 6:41 am
output:
2015-10-01 00:00:00.0002190DAYSHIFT
2015-10-02 00:00:00.0002190DAYSHIFT
2015-10-03 00:00:00.0002190DAYSHIFT
2015-10-04 00:00:00.0002190DAYSHIFT
2015-10-05 00:00:00.0002190DAYSHIFT
2015-10-06 00:00:00.0002190DAYSHIFT
2015-10-07 00:00:00.0002190DAYSHIFT
2015-10-08 00:00:00.0002190DAYSHIFT
2015-10-09 00:00:00.0002190DAYSHIFT
2015-10-10 00:00:00.0002190DAYSHIFT
2015-10-11 00:00:00.0002190DAYSHIFT
2015-10-12 00:00:00.0002190DAYSHIFT
2015-10-13 00:00:00.0002190DAYSHIFT
2015-10-14 00:00:00.0002190DAYSHIFT
2015-10-15 00:00:00.0002190DAYSHIFT
2015-10-16 00:00:00.0002190DAYSHIFT
2015-10-17 00:00:00.0002190DAYSHIFT
2015-10-18 00:00:00.0002190DAYSHIFT
2015-10-19 00:00:00.0002190DAYSHIFT
2015-10-20 00:00:00.0002190DAYSHIFT
2015-10-21 00:00:00.0002190DAYSHIFT
2015-10-22 00:00:00.0002190DAYSHIFT
2015-10-23 00:00:00.0002190DAYSHIFT
2015-10-24 00:00:00.0002190NIGHTSHIFT
2015-10-25 00:00:00.0002190DAYSHIFT
2015-10-26 00:00:00.0002190DAYSHIFT
2015-10-27 00:00:00.0002190DAYSHIFT
2015-10-28 00:00:00.0002190DAYSHIFT
2015-10-29 00:00:00.0002190DAYSHIFT
2015-10-30 00:00:00.0002190DAYSHIFT
2015-10-31 00:00:00.0002190NIGHTSHIFT
Expected is
2015-10-01 00:00:00.0002190DAYSHIFT
2015-10-02 00:00:00.0002190DAYSHIFT
2015-10-03 00:00:00.0002190DAYSHIFT
2015-10-04 00:00:00.0002190DAYSHIFT
2015-10-05 00:00:00.0002190DAYSHIFT
2015-10-06 00:00:00.0002190DAYSHIFT
2015-10-07 00:00:00.0002190DAYSHIFT
2015-10-08 00:00:00.0002190DAYSHIFT
2015-10-09 00:00:00.0002190DAYSHIFT
2015-10-10 00:00:00.0002190DAYSHIFT
2015-10-11 00:00:00.0002190DAYSHIFT
2015-10-12 00:00:00.0002190DAYSHIFT
2015-10-13 00:00:00.0002190DAYSHIFT
2015-10-14 00:00:00.0002190DAYSHIFT
2015-10-15 00:00:00.0002190DAYSHIFT
2015-10-16 00:00:00.0002190DAYSHIFT
2015-10-17 00:00:00.0002190DAYSHIFT
2015-10-18 00:00:00.0002190DAYSHIFT
2015-10-19 00:00:00.0002190NIGHTSHIFT
2015-10-20 00:00:00.0002190NIGHTSHIFT
2015-10-21 00:00:00.0002190NIGHTSHIFT
2015-10-22 00:00:00.0002190NIGHTSHIFT
2015-10-23 00:00:00.0002190NIGHTSHIFT
2015-10-24 00:00:00.0002190NIGHTSHIFT
2015-10-25 00:00:00.0002190DAYSHIFT
2015-10-26 00:00:00.0002190NIGHTSHIFT
2015-10-27 00:00:00.0002190NIGHTSHIFT
2015-10-28 00:00:00.0002190NIGHTSHIFT
2015-10-29 00:00:00.0002190NIGHTSHIFT
2015-10-30 00:00:00.0002190NIGHTSHIFT
2015-10-31 00:00:00.0002190NIGHTSHIFT
From emp table:(represents 2,4 is night shift)
ShiftShiftTypeFromDate EndDate
24 2015-10-19 00:00:00.0002015-10-24 00:00:00.000
24 2015-10-26 00:00:00.0002015-10-31 00:00:00.000
May 13, 2016 at 6:46 am
Create table emp(emp_code int,Fromdate DATETIME,Enddate DATETIME,shift INT,shifttype INT)
INSERT INTO emp VALUES(635,'2014-05-01','2014-05-03',2,4)
INSERT INTO emp VALUES(635,'2014-05-07','2014-05-10',2,4)
INSERT INTO emp VALUES(635,'2014-05-14','2014-05-13',2,4)
May 13, 2016 at 7:46 am
ganapathy.arvindan (5/13/2016)
Create table emp(emp_code int,Fromdate DATETIME,Enddate DATETIME,shift INT,shifttype INT)INSERT INTO emp VALUES(635,'2014-05-01','2014-05-03',2,4)
INSERT INTO emp VALUES(635,'2014-05-07','2014-05-10',2,4)
INSERT INTO emp VALUES(635,'2014-05-14','2014-05-13',2,4)
It apppears that you are going around in circles here and in your other threads.
Where did the "FROM DATE" suddenly appear from?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2016 at 7:49 am
In another thread I asked you about the table structure
http://www.sqlservercentral.com/Forums/FindPost1785784.aspx
and you replied to say that it was correct........!!
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2016 at 8:20 am
Sir..Please sir..That is different requirement this one is different..sorry for bothering you
May 13, 2016 at 9:09 am
ganapathy.arvindan (5/13/2016)
Sir..Please sir..That is different requirement this one is different..sorry for bothering you
😉
so you have now changed table "emp" structure....
is table "emp1" still the same as you have previously posted, or are you intending to change that one as well?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2016 at 12:48 pm
-- CAVEAT.....
--I think that you need to clearly understand the structure of your data
--from your posts so far your sample tables and data keep changing
--if you find that the following does not work for you then...
--PLEASE provide sample data in the format that I have given you below
--AND PLEASE dont give feedback based on data that is IS NOT in the sample data
CREATE TABLE #emp -- from my understanding so far...if there are records in this tbale they are 'NIGHTSHIFT'
(emp_code INT,
Fromdate DATETIME,
Enddate DATETIME,
shift INT,
shifttype INT
);
INSERT INTO #emp VALUES(635,'20160501','20160503',2,4)
INSERT INTO #emp VALUES(635,'20160507','20160510',2,4)
INSERT INTO #emp VALUES(635,'20160513','20160514',2,4)
CREATE TABLE #emp1
(empid INT,
shift INT,
shifttype INT
);
INSERT INTO #emp1 VALUES(635,1,1)
--=====================================================
declare @fromdate datetime = '20160501'
declare @enddate datetime = '20160520'
declare @emp INT = 635
-- the following validates thats @emp is valid......if not valid then an empty set is returned
-- you may wish to break out at this point in your proc
declare @emp_select INT = (SELECT empid FROM #emp1 WHERE (empid = @emp));
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)
)
,
-- the alldates cte delivers a list of all dates between @fromdate and @enddate
--shift_date
--2016-05-01
--2016-05-02
--2016-05-03
--thro to....
--2016-05-18
--2016-05-19
--2016-05-20
emp_shifts as (
SELECT e.emp_code,
shift_date
FROM #emp e
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)
) x
)
-- the emp_shifts cte delivers a list of all dates from #emp (ie Nightshift dates)
--emp_codeshift_date
--6352016-05-01 00:00:00.000
--6352016-05-02 00:00:00.000
--6352016-05-03 00:00:00.000
--6352016-05-07 00:00:00.000
--6352016-05-08 00:00:00.000
--6352016-05-09 00:00:00.000
--6352016-05-10 00:00:00.000
--6352016-05-13 00:00:00.000
--6352016-05-14 00:00:00.000
-- we then select use the two previous ctes with a left outer join to get all dates as requested
SELECT ad.shift_date,
COALESCE(es.emp_code, @emp) empcode,
CASE
WHEN es.shift_date IS NULL
THEN 'DAYSHIFT'
ELSE 'NIGHTSHIFT'
END shift_type
FROM alldates AS ad
LEFT OUTER JOIN emp_shifts AS es ON ad.shift_date = es.shift_date
WHERE @emp_select > 0;
--============================================
DROP TABLE #emp
DROP TABLE #emp1
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2016 at 11:27 pm
THanks a lot..it is working...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply