Solved

dates - loop

Posted on 2016-11-22
12
57 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
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 73

Expert Comment

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


please post sample data and expected results
0
 
LVL 34

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 73

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
 

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 73

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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now