Either remain between same day or next day.

Mehram
Mehram used Ask the Experts™
on
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


;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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
to make it simple

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'

Reza RadConsultant, Trainer

Commented:
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])   )

Open in new window

Consultant, Trainer
Commented:
put my code instead of these portions of yours:
e2.[datetime]
                            between e.[datetime] and dateadd(d,1,e.datetime)) --the code should be modified only at this very line
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
RitesShah has correctly identified my point at
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_25096665.html#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

Open in new window

look at line # 38 to 42 in above solution.
or may be this one:



;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 cast(CONVERT(varchar,e.[datetime],111) as datetime) and 
                                        case when dayadd>0 then
											dateadd(d,1,e.datetime)
										else
											cast(CONVERT(varchar,e.[datetime],111)+' 23:59:59' as 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

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial