Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-17-2016
09:10 AM

06-17-2016
09:10 AM

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

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-22-2016
06:55 AM

1 ACCEPTED SOLUTION

6 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-17-2016
12:05 PM

06-17-2016
12:05 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-17-2016
12:13 PM

06-17-2016
12:13 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-17-2016
12:21 PM

06-17-2016
12:21 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-19-2016
11:56 PM

06-19-2016
11:56 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-20-2016
11:37 PM

06-20-2016
11:37 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-22-2016
06:55 AM