Either remain between same day or next day.
The query given in the code snippet is partial,
Just consider line no 8 which is
dayadd = schedule.dayadd
meaning that cte_week.dayadd will have value either 0 or 1
and line number 38 is
between e.[datetime] and dateadd(d,1,e.datetime))
this line needs to be modified
if cte_week.dayadd has value of 0 it should check within same day
if cte_week.dayadd has value of 1 it should check between the day involved and next day upto that time
to make it clear
if e.dateime is 2010/01/29 08:12:34 and cte_week.dayadd is 0 then it should check between
2010/01/29 08:12:34 and 2010/01/29 23:59:59
if e.dateime is 2010/01/29 08:12:34 and cte_week.dayadd is 1 then it should check between
2010/01/29 08:12:34 and 2010/01/30 08:12:33
Just consider line no 8 which is
dayadd = schedule.dayadd
meaning that cte_week.dayadd will have value either 0 or 1
and line number 38 is
between e.[datetime] and dateadd(d,1,e.datetime))
this line needs to be modified
if cte_week.dayadd has value of 0 it should check within same day
if cte_week.dayadd has value of 1 it should check between the day involved and next day upto that time
to make it clear
if e.dateime is 2010/01/29 08:12:34 and cte_week.dayadd is 0 then it should check between
2010/01/29 08:12:34 and 2010/01/29 23:59:59
if e.dateime is 2010/01/29 08:12:34 and cte_week.dayadd is 1 then it should check between
2010/01/29 08:12:34 and 2010/01/30 08:12:33
;with cte_week as
( select shift = schedule.shift
, Schedule_code = schedule.schedule_code
, timein = schedule.timein
, timeout = schedule.timeout
, dayno = wk.dayno
, off_day = schedule.off_day
, dayadd = schedule.dayadd
, card_code = schedule.card_code
from weeks wk
left join
( select
distinct e.card_Code, e.company_code,s.shift, s.schedule_code
, s.timein, s.timeout,es.days_from, esd.off_day
, dayadd = case when s.range_from > s.range_to then 1 else 0 end
from employee e
join emp_schedule_det esd on (e.company_code = esd.company_code and e.card_code = esd.card_code)
join emp_schedule es on (esd.trans_no = es.trans_no and esd.company_code = es.company_code)
join schedules s on (s.schedule_code = es.schedule_code)
where ( e.company_code = @company_code)
and ( e.card_code = @emp_code)
) schedule on wk.weekno = schedule.days_from
)
,cte1 as
(
select e.emp_code,
e.company_code,
trans_date,
timein = time_portion ,
timeout =convert(varchar,( select min(e2.datetime)
from attendance.dbo.events e2
where e2.emp_code = e.emp_code
and e2.company_code = e.company_code
and status = '02'
and e.leave_type <> 'L'
and e2.[datetime]
between e.[datetime] and dateadd(d,1,e.datetime)) --the code should be modified only at this very line
,108),
dateout =( select convert(datetime,convert(varchar(10),min(e2.datetime),111))
from attendance.dbo.events e2
where e2.emp_code = e.emp_code
and e2.company_code = e.company_code
and status = '02'
and e.leave_type <> 'L'
and e2.[datetime] between e.[datetime] and dateadd(d,1,e.datetime) ),
e.remarks,
leave_type,
ot_tag = y.ot_tag,
holiday_ot = y.holiday_ot,
emp_type = y.emp_type,
left_date = y.left_date
from attendance.dbo.events e
join employee y on ( e.emp_code = y.card_code and e.company_code = y.company_code)
where e.emp_code = @emp_code
and status = '01'
and e.company_code = @company_code
and ( [datetime] between @dt1 and dateadd(s,86399,@dt2) )
) ,
cte2 as
try this one:
(cte_week.dayadd=0 and e2.[datetime] between e.[datetime] and dateadd(ss,-1,dateadd(Day,1,e.[datetime])) )
or
(cte_week.dayadd=0 and e2.[datetime] between e.[datetime] and dateadd(Day,1,e.[datetime]) )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
RitesShah has correctly identified my point at
https://www.experts-exchange.com/questions/25096665/Simple-Question.html?anchorAnswerId=26435473#a26435473
<I guess, this is the one you are searching for. if there is 0 in dayAdd, you want same date but with 23:59:59 time and if you have 1 in dayAdd, you want same time but the next day of the date.>>
Here in this question line no 38 needs to be modifed accordingly.
the valud in dayadd will be (select dayadd from cte_week sw where sw.card_code = y.emp_code)
https://www.experts-exchange.com/questions/25096665/Simple-Question.html?anchorAnswerId=26435473#a26435473
<I guess, this is the one you are searching for. if there is 0 in dayAdd, you want same date but with 23:59:59 time and if you have 1 in dayAdd, you want same time but the next day of the date.>>
Here in this question line no 38 needs to be modifed accordingly.
the valud in dayadd will be (select dayadd from cte_week sw where sw.card_code = y.emp_code)
look at this:
;with cte_week as
( select shift = schedule.shift
, Schedule_code = schedule.schedule_code
, timein = schedule.timein
, timeout = schedule.timeout
, dayno = wk.dayno
, off_day = schedule.off_day
, dayadd = schedule.dayadd
, card_code = schedule.card_code
from weeks wk
left join
( select
distinct e.card_Code, e.company_code,s.shift, s.schedule_code
, s.timein, s.timeout,es.days_from, esd.off_day
, dayadd = case when s.range_from > s.range_to then 1 else 0 end
from employee e
join emp_schedule_det esd on (e.company_code = esd.company_code and e.card_code = esd.card_code)
join emp_schedule es on (esd.trans_no = es.trans_no and esd.company_code = es.company_code)
join schedules s on (s.schedule_code = es.schedule_code)
where ( e.company_code = @company_code)
and ( e.card_code = @emp_code)
) schedule on wk.weekno = schedule.days_from
)
,cte1 as
(
select e.emp_code,
e.company_code,
trans_date,
timein = time_portion ,
timeout =convert(varchar,( select min(e2.datetime)
from attendance.dbo.events e2
where e2.emp_code = e.emp_code
and e2.company_code = e.company_code
and status = '02'
and e.leave_type <> 'L'
and e2.[datetime] between e.[datetime] and
case when dayadd>0 then
dateadd(d,1,e.datetime)
else
e.datetime
end
)
,108),
dateout =( select convert(datetime,convert(varchar(10),min(e2.datetime),111))
from attendance.dbo.events e2
where e2.emp_code = e.emp_code
and e2.company_code = e.company_code
and status = '02'
and e.leave_type <> 'L'
and e2.[datetime] between e.[datetime] and dateadd(d,1,e.datetime) ),
e.remarks,
leave_type,
ot_tag = y.ot_tag,
holiday_ot = y.holiday_ot,
emp_type = y.emp_type,
left_date = y.left_date
from attendance.dbo.events e
join employee y on ( e.emp_code = y.card_code and e.company_code = y.company_code)
where e.emp_code = @emp_code
and status = '01'
and e.company_code = @company_code
and ( [datetime] between @dt1 and dateadd(s,86399,@dt2) )
),
cte2 as
look at line # 38 to 42 in above solution.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
declare @dayadd int
set @dayadd = 0
declare @datetime smalldatetime
set @datetime = '2010/01/29 08:12:34'
select newdatetime = '2010/01/29 23:59:59'
set @dayadd = 1
select newdatetime = '2010/01/30 08:12:33'