I need help with dates in the where clause I need to get data from 5pm to 5am

Question

I need help with dates in the where clause I need to get data from 5pm to 5am

kat35601

SSC Eights!

Points: 825
More actions
December 29, 2020 at 2:32 pm

#3825149
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.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101097 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

kat35601 SSC Eights! Points: 825 More actions · Answer 2

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.

ScottPletcher SSC Guru Points: 101097 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kat35601 SSC Eights! Points: 825 More actions · Answer 6

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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.

This reply was modified 3 years, 10 months ago by Lynn Pettis.

kat35601 SSC Eights! Points: 825 More actions · Answer 8

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

This reply was modified 3 years, 10 months ago by kat35601.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

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.

kat35601 SSC Eights! Points: 825 More actions · Answer 10

Correct no day shift data wanted the hard coded date I am using to get if you will today's information I don't want to have to change the date manually.

This reply was modified 3 years, 10 months ago by kat35601.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

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.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 12

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs