I am trying to write a query that will bring back a result set for the
last business day before the query was run
I decided it would be fine to select .... where entry_date =
max(entry_date) since there will always be entries on every business
day, the only problem with this is if this query is run today after an
entry has already been made today, then it will return a result set
for today and not the last business day. If we worked everyday it
would be easy to do: where entry_date = (sysdate - 1) but since we do
not work weekends or holidays this would not work
It seems like I need to write something like this sudo code but I am
not sure how
select * from mytable where entry_date is the max entry_date other
than sysdate
This will be embedded within an application which will not take user
input so a date cannot be provided.
Any thoughts?
Thanks
Problematic coder says...
> I decided it would be fine to select .... where entry_date =
> max(entry_date) since there will always be entries on every business
> day, the only problem with this is if this query is run today after an
> entry has already been made today, then it will return a result set
> for today and not the last business day. If we worked everyday it
> would be easy to do: where entry_date = (sysdate - 1) but since we do
> not work weekends or holidays this would not work
> It seems like I need to write something like this sudo code but I am
> not sure how
> select * from mytable where entry_date is the max entry_date other
> than sysdate
For that pseudo-code, a simple subquery.
select * from mytable where entry_date = (select max(entry_date) from
mytable where entry_date <> sysdate)
Depending on the storage format of entry_date you may need to use
trunc(entry_date) <> trunc(sysdate)
to ignore time components.
GM
-----------------------------------------------Reply-----------------------------------------------
hmm if you want to ensure it only returns the previous day i would
write it like this:
select * from mytable where entry_date between trunc(sysdate-1) and
trunc(sysdate)
-----------------------------------------------Reply-----------------------------------------------
On May 7, 5:02 pm, Geoff Muldoon <geoff.muld
@trap.gmail.com> wrote:
> Problematic coder says...
> > I decided it would be fine to select .... where entry_date =
> > max(entry_date) since there will always be entries on every business
> > day, the only problem with this is if this query is run today after an
> > entry has already been made today, then it will return a result set
> > for today and not the last business day. If we worked everyday it
> > would be easy to do: where entry_date = (sysdate - 1) but since we do
> > not work weekends or holidays this would not work
> > It seems like I need to write something like this sudo code but I am
> > not sure how
> > select * from mytable where entry_date is the max entry_date other
> > than sysdate
> For that pseudo-code, a simple subquery.
> select * from mytable where entry_date = (select max(entry_date) from
> mytable where entry_date <> sysdate)
> Depending on the storage format of entry_date you may need to use
> trunc(entry_date) <> trunc(sysdate)
> to ignore time components.
> GM
Thank you, exactly what I needed
-----------------------------------------------Reply-----------------------------------------------
On May 8, 8:24 am, "knightconsult
@gmail.com"
<knightconsult
@gmail.com> wrote:
> hmm if you want to ensure it only returns the previous day i would
> write it like this:
> select * from mytable where entry_date between trunc(sysdate-1) and
> trunc(sysdate)
You are correct sir, however I was looking for the last business day
which if the query is run on a Monday would be Friday, holidays also
to be taken into account.
Geoff gave me just what I was looking for but I do appreciate your
feedback and time - thank you
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
