Consolidate 2 queries into 1 in Oracle Sql, Dynamic query based on Day of Week

I have two very similar queries that I'd like to consolidate into one.
I have to run these 5 days a week.
I need to alter join based on Day in a week.

Tuesdays - Thurs I need my join to be:  
date_referenced = to_date(sysdate)

Open in new window

.

If the sysdate day is a Monday then I need to pull in data from Sunday - Monday to be joined:
date_referenced = to_date(sysdate) OR              --Monday
date_referenced = to_date(sysdate-1). OR          --Sunday
date_referenced = to_date(sysdate-2)                  --Saturday

Open in new window

Fin VangelisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
sysdate is a date so there is no need to use TO_DATE.

Please post more of the problem.

Getting day of the week is easy and making a decision on that should also be easy but you didn't provide enough on the actual requirements.

I'm thinking it will go like:
where ...
(
  (to_char(sysdate,'Dy')='Mon' and date_referenced >= trunc(sysdate)-1 and date_referenced < trunc(sysdate)+1)
  or
  (to_char(sysdate,'Dy') in ('Tue','Wed','Thu') and date_referenced >= trunc(sysdate) and date_referenced < trunc(sysdate)+1)
  or
  (--the next set of requirements)
  or
  (--the next set of requirements)
)

Open in new window

0
slightwv (䄆 Netminder) Commented:
This should be the same but I used a CASE and removed all the OR checks:
where ...
(
	date_referenced >=
  		case
			when to_char(sysdate,'Dy')='Mon' then trunc(sysdate)-1
  			when to_char(sysdate,'Dy') in ('Tue','Wed','Thu') then trunc(sysdate)
		end
	and date_referenced < trunc(sysdate)+1
)

Open in new window

1
Bill PrewCommented:
No points for me, but I'd tweak the good work slightwv has done to get the following (assuming it runs the same on Friday, and doesn't run Sat and Sun).

where ...
(
	date_referenced >=
  		case
			when to_char(sysdate,'Dy')='Mon' then trunc(sysdate)-2
  			else trunc(sysdate)
		end
	and date_referenced < trunc(sysdate)+1)
)

Open in new window


»bp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
@Bill,

hehehe.....

Saw the cleaner way after I had already posted the first one.

I did miss the ELSE in the case though!
0
Bill PrewCommented:
And perhaps a slightly different approach, not sure if there is any performance to be gains with just the = compare on 4 of the 5 days, see what you think...

where
((to_char(sysdate,'Dy')='Mon' and date_referenced in (trunc(sysdate)-2, trunc(sysdate)-1, trunc(sysdate)) OR 
 to_char(sysdate,'Dy')<>'Mon' and date_referenced = trunc(sysdate)))

Open in new window


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
My guess is the execution plans would be fairly similar if not exact.  The Optimizer is getting really decent about rewriting SQL to protect us from ourselves.

>>just the = compare on 4 of the 5 days

If date_referenced has the time portion populated it won't work.  If the time is ALWAYS zero'd, then sure.
0
Fin VangelisAuthor Commented:
I should have included that I can only run Tues-Fri code or Mon. code.
I am consolidating the queries but I need logic in my query to run a set based on day of week.
If it's Monday then run set of code shown above.
Tues-Friday then run other set of code.
0
Bill PrewCommented:
Right, that's the assumption I made in my last suggestion.  As mentioned, if date_referenced has a time component then you will need to TRUNC() in my code, since you didn't have it in yours I assumed it didn't...


»bp
0
slightwv (䄆 Netminder) Commented:
>>I am consolidating the queries but I need logic in my query to run a set based on day of week

I believe we have provided that.  Just add 'Fri' where appropriate to the SQL you decide to use.
0
slightwv (䄆 Netminder) Commented:
>>then you will need to TRUNC()

Be careful.  If date_referenced has an index using TRUNC not allow index use.
1
Fin VangelisAuthor Commented:
Thanks. I was over-complicating the problem thinking I would need a CTE or using variables.
0
Fin VangelisAuthor Commented:
Thanks for the solutions.
0
Fin VangelisAuthor Commented:
anyway i can accomplish my task without using trunc()? There's a performance hit but dataset is still small so not too long of  a wait. Any way to build query while keeping the index on date_referenced?
0
Bill PrewCommented:
I think you would use #a42470945 for that.  The TRUNC(SYSDATE) is not a problem, won't affect index usage.


»bp
0
slightwv (䄆 Netminder) Commented:
Even if the wait is acceptable, why would you purposely write SQL to ignore a perfectly good index?

Use the >= and < syntax.  It is a good habit to get into because some day, you'll need index usage for performance.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
toad

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.