[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

dates - loop

Posted on 2016-11-22
12
Medium Priority
?
104 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 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 1000 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month10 days, 7 hours left to enroll

612 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