Currently end_tran_date is a date with 00:00:00.000 for the time
Same with the end_tran_time also goes for a 1900-01-01 with the actual time behind it.
(I'm not sure the data types just because I started really basic coding a year ago and haven't gotten much time to actually look at it and I've not gotten the proper training to understand all of code that's why I've been experimenting on my own I'd like to take a crash course in it just to understand it better)
I'll keep the >= at the lower boundary from now on out, I saw some code that used >= as the lower function but didn't want to use it because it would seem to me that it would then be pulling yesterdays date and todays date, I used your suggestion of using
AND ttl.end_tran_date >= CAST(GETDATE() AS DATE)
This was pretty easy but now I have another supervisor wanting the same kind of information but his shift falls between days, is there an easy way to do that? I've tried to do his shift times but I'm always getting the wrong numbers.
This is how I'm trying right now
from t_tran_log ttl with(nolock)
inner join t_employee emp with(nolock) on ttl.employee_id = emp.id
where ttl.tran_type in ('343','372')
and emp.dept = '2nd'
and ttl.employee_id = 'ONEALL'
and ttl.end_tran_date >= dateadd(d,datediff(d,0, getdate()),-1)
and ttl.end_tran_date < dateadd(d,datediff(d,0, getdate()),+1)
--and ttl.end_tran_time > '1900-01-01 05:00:00.000'
--and ttl.end_tran_time < '1900-01-01 17:30:00.000'
and ttl.end_tran_time < '1900-01-01 02:00:00.000'
and ttl.end_tran_time > '1900-01-01 15:30:00.000'
--group by ttl.employee_id order by ttl.employee_id
The shift begins at 15:30 but the shift starts would be opposite of the other shift, 2 AM would be the end time so I tried to use the same kind of query for it but flipping things around I'll take out the commented sections once I feel confident that this code is running right. Currently I'm getting no results but the user last night when I look through all their transactions have transactions last night that should have been picked up by this query or at least in theory it should've.