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;

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

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);

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

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

Hi Experts,

I have below tables

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