All Forums Database
NiteshM 6 posts Joined 06/16
17 Jun 2016
Recursive Query Vs UNION

Hi Experts,
I have below tables 

Column A    Column B  Column C  Column D
123         001       1/1/2016  60
123         002       1/1/2016  -10
126         003       1/1/2016  -10
124         002       2/1/2016  40
125         002       8/1/2016  60 



table B

columnA    columnB
1/1/2016   WK1
2/1/2016   WK1
8/1/2016   WK2
and so on 

My requirement is to exclude all those values in column A where values in column D is less than 0  
what i am doing right now is making sets of data based on week number and then making a UNION of it 
 
Sel A,B from table A where 
A not IN( 
sel distinct A from table A  INNER JOIN  table B
on column A=Column A
where table B.columnB IN ('WK1','WK2') and table A.Column D<0) 
UNION 
Sel A,B from table A where 
A not IN( 
sel distinct A from table A  INNER JOIN  table B
on column A=Column A
where table B.columnB IN ('WK2','WK3) and table A.Column D<0) 
 
Since i need to do this UNION repetedly for 10 weeks so making these many UNIONS is hampering the performance .
Please let me know if the same can be achieved with Recursive query 

ToddAWalter 316 posts Joined 10/11
17 Jun 2016

Im not totally clear on wht you want but I think it is a list of unique table1.a values where any of the table1.d values is less than zero...
 
SELECT a FROM table1 GROUP BY a HAVING MIN(d)>=0;

NiteshM 6 posts Joined 06/16
17 Jun 2016

Hi Tod,
Actually i want to exclude all those values from colum a where values in column D is less than 0 and then roll it up at week level based on my second table which us B
the final data set should be as below 

Column A  Column B  Column C
124       002         WK1
125       002         WK2   

 I am excluding 123 and 126 from column A because is has one of the negative values in column D of table A
Thanks 

ToddAWalter 316 posts Joined 10/11
17 Jun 2016

Not sure what "roll it up" means since the result doesn't obviously include an aggregate of any of the columns. The previous answer is the beginning but becomes a subquery:
SELECT t1.a, t1.b, t2.b FROM t1, t2
WHERE t1.c=t2.a
AND t1.a IN (SELECT a FROM table1 GROUP BY a HAVING MIN(d)>=0);

NiteshM 6 posts Joined 06/16
19 Jun 2016
Table T1

Column A    Column B  Column C  Column D
123         001       1/1/2016   60
123         002       1/1/2016  -10            
126         003       1/1/2016  -10
124         002       1/1/2016   10         --> This will be included in WK1
124         002       2/1/2016   40         --> This will be included in WK1
124         002       8/1/2016   -10
125         002       8/1/2016   60         --> This will be included in WK2
126         003       17/1/2016  10         --> This will be included in WK3
 
 
table T2
 
columnA    columnB
1/1/2016   WK1
2/1/2016   WK1
8/1/2016   WK2
and so on
-------------------------------------------------------------------------------------------------------------------

Query Prepared for Week 1 and Week 2 and Week 3

sel                         /* Displaying the final result set for week 1 and week 2 */         
T1.a
T1.b
,'W1' as Week Format
from T1
INNER JOIN T2
on T1.C=T2.A
where T1.A not in 
(
sel distinct T1.A from T1  
INNER JOIN  T2             /* Inner query to exclude all values from T1 table where value of D is <= 0 in any of the day for week 1 and Week 2*/
ON T1.C=T2.A
where  T1.D <= 0
and T2.B IN ('WK1','WK2')
)
and T2.B in ('WK1','WK2')
group by 1,2

UNION 

sel                            /* Displaying the final result set for week 1 and week 2 */         
T1.a
T1.b
,'W1' as Week Format
from T1
INNER JOIN T2
on T1.C=T2.A
where T1.A not in 
(
sel distinct T1.A from T1  
INNER JOIN T2               /* Inner query to exclude all values from T1 table where value of D is <= 0 in any of the day for week 1 and Week 2*/
ON T1.C=T2.A
where  T1.D <= 0
and T2.B IN ('WK2','WK3')
)
and T2.B in ('WK2','WK3')
group by 1,2

------------------------------------------------------------------------------------------------------


Final Result SET

Column A  Column B  WeekFormat
124        002       WK1
125        002       WK2
126        003       WK3

Hi Todd,
I think i need to rephrase it a little bit.
So here in table A i have 124 and 002 for date 2/1/2016 and 8/1/2016 so it means it lies in W1 and W2 both and it has value of column D negative for date 8/1/2016 so this should be excluded and this needs to be repeated for all the weeks W1-W2, W2-W3, W3-W4 ....... and then the final result has to be aggregated at week level. Above is the query which i prepared hope this helps in understanding the requirement better.
Since I need to do this for 13 weeks so i am making 13 joins in my query as i did above for WK1, WK2 and WK3.
So is there any other way apart from UNION which i can use .
Thanks
Nitesh
 
 
 
 
 

dnoeth 4628 posts Joined 11/04
20 Jun 2016

Seems like you want to return only the weeks with no negative value in the current and next week.
This should be a simple task using OLAP-functions like this:

select a,b,c
from
 (
   select 
      T1.a,
      T1.b,
      T2.b,
      -- NULL if any negative value
      case when min(d) >= 0 then 1 else 0 end as positiveFlag
   from T1
   INNER JOIN
    (
      select a,b,
         -- need a sequence to check for next week
         dense_rank() over (order by b) as weeknumber
      from  T2
    ) as T2
   on T1.C=T2.A
   group by 1,2,3
 ) as dt
qualify positiveFlag = 1   -- current week not negative 
    and min(weeknumber)    -- next row is from next week
        over (partition by a 
              order by weeknumber
              rows between 1 following and 1 following) = weeknumber +1
    and min(positiveFlag)  -- next week not negative
        over (partition by a
              order by weeknumber
              rows between 1 following and 1 following) = 1

 

Dieter

NiteshM 6 posts Joined 06/16
22 Jun 2016

Hi Dieter,
Thanks a lot , this saved our day :) 
 
Thanks

You must sign in to leave a comment.