Solved

dates - loop

Posted on 2016-11-22
12
39 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
Comment Utility
I only see one query in your question.


please post sample data and expected results
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Expert Comment

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

Author Comment

by:PHIL Sawyer
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the help - much appreciated and set me on my way
0

Featured Post

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.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

771 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

12 Experts available now in Live!

Get 1:1 Help Now