Solved

dates - loop

Posted on 2016-11-22
12
90 Views
Last Modified: 2016-11-27
Hi
I have a query (query 1)where a specific date_time is used to return a single value (1 row )
e.g
select max(t1.amount)
from table1 t1
inner join table2 t2 on t1.id = t2.id
where t1.date_time = TO_DATE('2015/05/15 8:30:25', 'YYYY/MM/DD HH:MI:SS')
and t2.date_time = TO_DATE('2015/05/15 8:30:25', 'YYYY/MM/DD HH:MI:SS') - 7;

I also have another query (query 2) which returns say 10 rows of different date_time values. Here is my problem - I want to use these date_times (query 2) in the above query (query 1) which would give me 10 different amounts (10 rows). Is there a way to do this without using PLSQL?


Regards
0
Comment
Question by:PHIL Sawyer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 41898078
I only see one query in your question.


please post sample data and expected results
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41898083
Something like this:

select max(t1.amount)
from table1 t1
inner join table2 t2 on t1.id = t2.id
cross join (query 2) t3
where t1.date_time = t3.date_field
and t2.date_time = t3.date_field - 7;
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 41898087
just guessing based on the description, it might look something like this...


SELECT q2.date1, q2.date2, MAX(t1.amount)
  FROM table1 t1
       INNER JOIN table2 t2 ON t1.id = t2.id
       CROSS JOIN (your query2) q2
 WHERE t1.date_time = q2.date1 AND t2.date_time = q2.date2
group by q2.date1, q2.date2;
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:PHIL Sawyer
ID: 41898153
FYI
The query 2 would return say .....
2015/05/15 8:30:25
2015/05/15 9:30:25
2015/05/15 10:30:25
2015/05/15 11:30:25 ..... etc
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41898198
ok, still guessing

SELECT q2.your_date, MAX(t1.amount)
   FROM table1 t1
        INNER JOIN table2 t2 ON t1.id = t2.id
        CROSS JOIN (your query2) q2
  WHERE t1.date_time = q2.your_date AND t2.date_time = q2.your_date
group by q2.your_date
0
 

Author Comment

by:PHIL Sawyer
ID: 41898804
Hi
The query below returns a single value can I use the same logic to replace the hard coded dates with the dates from query2?

Select case when To_Date('22/NOV/2016 23:30:00', 'dd/mon/yyyy HH24:MI:SS') < sysdate then 1 else 0 end as amount
FROM Table1 m
WHERE (
  CASE
    WHEN (sysdate BETWEEN next_day(last_day(to_date('01-03','DD-MM'))-7,'Sunday') AND next_day(last_day(to_date('01-10','DD-MM'))-7,'Sunday'))
    THEN M.START_TIME + to_dsinterval('0 01:00:00')
    Else M.Start_Time
  END) >= To_Date('22/NOV/2016 23:30:00', 'dd/mon/yyyy HH24:MI:SS')
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41899018
You can use a query that returns any number of dates in the samples that have been provided.  I'm confused by what you posted as it does not return a date.  It returns either 0 or 1.

If you provide the real queries, we can put them together for you.  Otherwise we are all guessing.  You gave us parts of sample queries, so you have been given enough samples that you should be able to put it together.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41899023
probably  

I can't give anything more definitive because you're not giving complete information.

Provide sample input data and expected output.  Providing only half, or a word-description leads to ambiguity.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41899398
>>The query 2 would return say .....<<
Can you post that full query?
0
 

Author Comment

by:PHIL Sawyer
ID: 41899790
Sorry if I have not made this clear. I'll give it another go.

The following query1 below has one hard coded date throughout and in this case it happens to be "To_Date('22/NOV/2016 23:30:00', 'dd/mon/yyyy HH24:MI:SS')" and the query always returns one value. I have another query2 which returns say 10 date times of which one of the date times happens to be 22/11/2016 23:30:00 - so I would like to use the other nine date times from query2 to return a further 9 different amounts. I could hard code dates in 9 other queries by hard coding each date time and then Union them all. Looking for alternative.
Hope this now makes sense.
eg H
Amount
24

query1
Select case when To_Date('22/NOV/2016 23:30:00', 'dd/mon/yyyy HH24:MI:SS') < sysdate then 1 else 0 end as amount
FROM Table1 m
WHERE (
  CASE
    WHEN (sysdate BETWEEN next_day(last_day(to_date('01-03','DD-MM'))-7,'Sunday') AND next_day(last_day(to_date('01-10','DD-MM'))-7,'Sunday'))
    THEN M.START_TIME + to_dsinterval('0 01:00:00')
    Else M.Start_Time
  END) >= To_Date('22/NOV/2016 23:30:00', 'dd/mon/yyyy HH24:MI:SS')


query2
select date_time from dates

So - my final output might look like ..

Amount
24
34
56
67 etc

Regards
0
 
LVL 35

Accepted Solution

by:
johnsone earned 250 total points
ID: 41899806
Like it was shown.  Put the date query as an in-line view and just replace the hard coded values with the date from the query:

Select case when d.date_time < sysdate then 1 else 0 end as amount
FROM Table1 m, (select date_time from dates) d
WHERE (
  CASE
    WHEN (sysdate BETWEEN next_day(last_day(to_date('01-03','DD-MM'))-7,'Sunday') AND next_day(last_day(to_date('01-10','DD-MM'))-7,'Sunday'))
    THEN M.START_TIME + to_dsinterval('0 01:00:00')
    Else M.Start_Time
  END) >= d.date_time
0
 

Author Closing Comment

by:PHIL Sawyer
ID: 41903283
Thanks for the help - much appreciated and set me on my way
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question