Selecting the current week of data and reseting from week to week

Question

I want to just select data for the current week. So... If the current date is a Monday just select Monday If the current date is a Tuesday select Monday and Tuesday's data If the current date is Wednesday select Monday, Tuesday and Wednesday ...and so on. I want it to reset on Sunday and I believe it's some kind of "where" clause just don't know what. As you can see below I'm just counting the number of pieces into the oven and want it to accumulate as the week goes on and then reset on Sunday.

select count(*) as PiecesIntoOven from ovenfeederfloat where...??

Thanks for the help.

Please post some sample data as well – kkuilla Apr 29 '15 at 11:37 — kkuilla, Apr 29 '15 at 11:37

score 0 · Answer 1 · edited May 23 '17 at 12:22

If you're looking to do this in Sql Server, see below. Essentially this converts the current date to its numeric (0-6) value, then finds the 0th date for that week and uses it to set the lower bound of the where clause.

select sum(numberofpieces)
from Test
where dateofwork <= getdate()
and dateofwork >= (DATEADD(DAY, DATEPART(WEEKDAY,getdate()) * -1, getdate()) + 1)

Note that the '0' value is impacted by DATEFIRST. https://stackoverflow.com/a/1113891/4824030

I'm not certain how to do this in Oracle. Something like the below should work, but it's being finicky in sqlfiddle.

select sum(numberofpieces)
from Test
where dateofwork <= current_timestamp
and dateofwork >= (((to_char(level+trunc(current_timestamp,'D'),'Day') * -1) + current_timestamp) + 1)

Selecting the current week of data and reseting from week to week

1 Answers1