So I've been messing around with getdate functions and my boss wanted me to get together a function that is able to be ran every day at a specified time that I wouldn't necessarily know. So I put this together for what he's asking for.
select distinct (ttl.employee_id),count(distinct ttl.hu_id) [Cartons Picked]
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 ('312','377','330','321')
and emp.dept = '1st'
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'
group by ttl.employee_id order by ttl.employee_id
It runs just fine but the
and ttl.end_tran_date > dateadd(d,datediff(d,0, getdate()),-1)
and ttl.end_tran_date < dateadd(d,datediff(d,0, getdate()),+1)
Lines look sloppy to me, is there an easier, cleaner way to write this?
I prefer the standard method, without the "short-cut" -1/+1 on the end. That is, the truly standard method is:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
But, you can shorten it to just day on both sides of the calc if you prefer:
= CAST(GETDATE() AS date)
< CAST(GETDATE() + 1 AS date))
Again, I do not recommend this method, but it work fine in SQL Server.
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
select *
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.
You should not do this. Either you should change the first condition on end_tran_date to today - i.e., ttl.end_tran_date >= dateadd(d,datediff(d,0, getdate()),0)
or
Use strictly greater than ttl.end_tran_date > dateadd(d,datediff(d,0, getdate()),-1)
If you think of date that includes time portion as being represented by floating point numbers internally, with the date boundaries i.e., midnight, falling on integer numbers, then the reasoning becomes clearer. If you assume that midnight on July 16, 2019 is represented internally as, for example 43660 then midnight July 17 is 43661, and everything in between will be between those two numbers. So for example, noon on July 16 would be 43660.5, 10:30 AM would be 43660.4375 and so on. So to filter in any date+time that is on July 16, you would filter for anything GREATER THAN OR EQUAL TO than 43660 and LESS THAN 43661.
Even though SQL Server does store datetime as something akin to floating point numbers, the above description is only for illustration purposes. It is not good practice to treat datetime values as floating point numbers.
As a technical point, SQL stores datetime as two (4-byte) integer values, not floating point in any way. Floating point would be too imprecise to store datetime values.
I just want to make sure that I got this right and have it in the same format that you have suggested.
This is what I came up with though I think it might not pick up on the midnight values but I'm hoping that it does.
The data looks good I checked a few outputs based on the employee info but I just wanted to get the formatting correct.
SELECT DISTINCT (ttl.employee_id),COUNT(distinct ttl.hu_id) [Cartons Loaded]
FROM t_tran_log ttl with(nolock)
LEFT OUTER 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.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 15:30:00.000'
AND ttl.location_id_2 LIKE 'D%'
OR ttl.tran_type IN ('343','372')
AND ttl.employee_id = 'WCS'
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 15:30:00.000'
AND ttl.location_id_2 LIKE 'D%'
OR ttl.tran_type IN ('343','372')
AND emp.dept = '2nd'
AND ttl.end_tran_date = CAST(GETDATE() AS DATE)
AND ttl.end_tran_date < DATEADD(d,DATEDIFF(d,0, GETDATE()),+1)
AND ttl.end_tran_time < '1900-01-01 02:30:00.000'
AND ttl.location_id_2 LIKE 'D%'
OR ttl.tran_type IN ('343','372')
AND ttl.employee_id = 'WCS'
AND ttl.end_tran_date = CAST(GETDATE() AS DATE)
AND ttl.end_tran_date < DATEADD(d,DATEDIFF(d,0, GETDATE()),+1)
AND ttl.end_tran_time < '1900-01-01 02:30:00.000'
AND ttl.location_id_2 LIKE 'D%'
GROUP BY ttl.employee_id ORDER BY ttl.employee_id
I don't think this is quite correct. If you evaluate the right-side of the two expressions I quoted from your post they show July 16, 2019 and July 18, 2019.
So the expression is saying that ttl.end_tran_date should be exactly equal to 7/16/2019 and that it should be less than 7/18/2019. The first condition makes the second one superfluous.
Also, just make sure that your ANDs and OR's are evaluated in the right order. AND has higher precedence than OR. Usually I insert brackets both for readability and to make sure that the evaluation order is correct.
Thanks, yeah I realized that after I posted the query that the second
AND ttl.end_tran_date
Is a waste of space.
The or is only there to grab the next day and for our conveyor system that doesn't have an employee_id on t_employee. I only used the AND's for the conditions that I wanted per day, there wasn't really an easier way to write the code unfortunately.