This query takes a long time to run..Can this query be optimized..?
select distinct a.task,a.lgc_id,t.btch_id , cast(a.start_dt as date) from table a inner join (select task,lgc, max(btch_id) btch_id from tableb group by task,lgc) t on a.btch_id=t.btch_id and sys_calendar.calendar.calendar_date - cast(start_dt as date) > 7
Table B has a unique combination of task,lgc and batch id's. And I want to find the record with the max(batchid) for every unique combination of task and lgc. Then I want to find the matching records in table a on batch id and then use the start_date field in table a to find the difference bwetween the run date and the start_date. If this difference is greater than 7, I need the corresponding task and the lgc. I removed the distinct clause but it gives me duplicates with task,a.lgc_id andt.btch_id .
This query is going to run for a long time, so you better shouldn't submit it :-) You're joining to the calendar table without any join condition -> cross join to ∼ 73k rows.
If you check Explain you'll see that product join, this is why you have to use Distinct.
I can't follow all your narrative, but this sounds like a simple join. What do you mean by "run date"? Today? Then it sounds like:
select a.task,a.lgc_id,t.btch_id , cast(a.start_dt as date) from table a inner join (select task,lgc, max(btch_id) btch_id from tableb group by task,lgc) t on a.btch_id=t.btch_id where cast(start_dt as date) > current_date - 7
Could you post some sample data and expected result set?
Table B : I need a unique combinations of Task and Lgc where batch_id is the highest. In this eg the task=29 with btch_id =4369 shd be selected. This has to be joined ti TableA to pull the strt_dt. This is the date when the task was last run. If the diff between today and thsi strt_dt is greater than 7 , we need to select that task and lgc. Task btch_id Lgc grp 29 3219 11 11 29 4367 11 11 320 4367 48 48 329 1761 ? 1150
Sorry I forgot to mention that I dont need the btch_id in teh final Output. My query had it because I wanted to check teh results. I just need a unique combination of task and lgc. I need the batch id just for joining the 2 tables and the start date to calculate the diff in days. So shd I still have the distinct clause? I am trying to run the queries both ways right now and seeing the diff.