Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

dates - loop

Posted on 2016-11-22
12
Medium Priority
?
101 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
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.

 

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

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.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

772 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