December 29, 2020 at 2:32 pm
I need help with dates in the where clause I need to get data from 5pm to 5am each day of the week
WHERE tdate >= '12-28-2020 17:00' and tdate <= DATEADD(HH, 5, CONVERT(DATETIME, CONVERT(date, GETDATE()+1)))
this does one shift but I need to be able to do it for the whole week so when I run the script it pulls only data form night shift not day shift for the week.
December 29, 2020 at 3:43 pm
WHERE tdate >= '12-28-2020 17:00' and tdate < DATEADD(DAY, 7, '12-28-2020 05:00') and
(DATEPART(HOUR, tdate) BETWEEN 17 AND 23 or DATEPART(HOUR, tdate) BETWEEN 0 AND 4)
If you need to include all entries for 05:00 to 05:59, change "05:00" to "06:00" and "BETWEEN 0 AND 4" to "BETWEEN 0 AND 5"
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 29, 2020 at 4:11 pm
I am not following I need to include for 12 hours 5pm to 5am the next day and then not include data from 5am till 5pm the same day so it is two 12 hour shifts but I only want data from the second shift that spans two day dates.
December 29, 2020 at 4:28 pm
I don't know your data. In the original q, you stated "each day of the week". So I guessed one week.
If you only need two days, then change the ending date range.
WHERE tdate >= '12-28-2020 17:00' and tdate < '12-30-2020 05:00' AND
If there's a proper index, that's the most efficient way to get only that range of tdates.
Then, the HOUR checks I did should give you only times between 5PM and 5AM, because the hour of day must be between 5PM and 11PM or between midnight and 4AM.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 29, 2020 at 4:47 pm
So, you are pulling data for an entire week, and just want the data for the second shift (17:00 to 05:00). Is this query only run once a week and is it run on the same day each week for the same time frame (Sunday through Saturday for instance)? The more information you provide the better the answers you will get instead of what amounts to shots i the dark.
December 29, 2020 at 4:57 pm
Just to add fuel to the fire, what do you want done if something overlaps the shift boundaries? Or is that simply not possible because you're truly only using a single date and not a start/end date combination?
--Jeff Moden
December 29, 2020 at 5:19 pm
The query will be run each day and what was produced each day for the entire week would show for example day one we built 400 of part a1 and 50 of part b1 day two we built 100 of part c1 and 300 of part d1 etc.....and then the query would start over the next monday.
December 29, 2020 at 5:25 pm
Could be that I need more sleep (doubt that as I slept almost 12 hours last night) so I am still confused. You really aren't providing the information needed to really help. Now It looks like you run this daily and that the date span resets each Monday. Okay, but what would really help is if you provided the query you are running instead of a snippet. There is probably a simple way to accomplish your task but without more information it is simply alluding me.
December 29, 2020 at 7:18 pm
SELECT
ISNULL(Transactions.item, Snumbers.item) AS item
,count(serial) AS qty
,tuser
,sum(ISNULL(M1_KF.dbo.PartUnitSalePrices.imhUnitSalePrice,0)) as TotalPrice
FROM Orbedata.dbo.SNumbers
LEFT OUTER JOIN OrbeData.dbo.Transactions ON snum = serial
left JOIN M1_KF.dbo.PartUnitSalePrices ON Orbedata.dbo.transactions.item = M1_KF.dbo.PartUnitSalePrices.imhPartID
WHERE tdate >= '12-28-2020 17:00' and tdate <= DATEADD(HH, 5, CONVERT(DATETIME, CONVERT(date, GETDATE()+1)))
AND trans = 'fpr'
GROUP BY
ISNULL(Transactions.item, Snumbers.item)
,tuser
,Orbedata.dbo.transactions.qty
order by tuser,item
December 29, 2020 at 7:38 pm
So, every Monday you start at that Monday at 17:00. This means that you get nothing when run during the first shift on Monday. Is this correct or do you change the hard coded date on Tuesday to the previous Monday. You are still leaving things in an ambiguous state.
December 29, 2020 at 8:19 pm
Try the following as your WHERE clause:
WHERE
tdate >= dateadd(hour,17,dateadd(week,datediff(week,0,getdate()),0)) and tdate < dateadd(hour,17,dateadd(week,datediff(week,0,getdate()) + 1,0))
and cast(dateadd(hour,7,tdate) as time) >= cast('00:00' as time) and cast(dateadd(hour,7,tdate) as time) < cast('12:00' as time)
Pretty sure others may come along with a better way, but this should work.
December 29, 2020 at 10:26 pm
Maybe this will get you started...
Declare @startDate datetime = '2020-12-21'
, @endDate datetime = getdate();
With t(n)
As (
Select t.n
From (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, dateRange (ShiftStart)
As (
Select Top(datediff(day, @startDate, @endDate) + 1)
dateadd(hour, 17, dateadd(day, checksum(row_number() over(Order By @@spid)), @startDate))
From t t1, t t2 --100 days
)
Select *
From dateRange dr
Left Join ...
Where tdate >= dr.ShiftStart
And tdate < dateadd(hour, 12, dr.ShiftStart);
Generate a table of ShiftStart dates - now you can query looking for 'tdate' that occurs anywhere between your ShiftStart and 12 hours later (ShiftEnd).
You should use aliases for your tables - and use those aliases when referencing your columns - it helps identify what table the column comes from and in this case we have no idea where tdate exists. If it exists in Transactions or PartUnitSalePrices - then using an outer join is meaningless as it works the same as an inner join.
You should also consider using synonyms instead of 3-part naming. Using synonyms allows you to change the referenced database without having to modify all of your code - if that database happens to change name or is moved.
It seems you are looking for a single transaction date (tdate) - but not sure. If you need to look for when something started or ended based upon a transaction start and end date then your where/on clause would need to change so it looks at the ShiftStart less than the end range or the ShiftEnd greater than start range.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply