how to loop through table2 with column value in table 1

Question

how to loop through table2 with column value in table 1

hegdesuchi

Hall of Fame

Points: 3047
More actions
October 8, 2018 at 8:58 pm

#408711

Hi All,
I have two tables table 1 and table 2.
Table1 :
ID Type StartDate EndDate Units AAA 1  4/3/2018 4/7/2018 1 AAA 1  4/8/2018 4/21/2018 1 AAA 1  1/8/2017 2/6/2017 2 AAA 1  1/1/2017 1/7/2017 2 BBB 2  7/16/2017 7/22/2017 1 BBB 2  8/1/2017 8/1/2017 1
Table 2: ID Type StartDate EndDate AAA 1  01/02/2017 01/17/2017 AAA 1  01/02/2017 01/17/2017 AAA 1  01/02/2017 01/17/2017 AAA 1  01/02/2017 01/17/2017 AAA 1  02/01/2017 02/28/2017 AAA 1  02/01/2017 02/28/2017 AAA 1  02/01/2017 02/28/2017 AAA 1  02/01/2017 02/28/2017 AAA 1  02/01/2017 02/28/2017 AAA 1  04/03/2018 04/03/2018 AAA 1  04/10/2018 04/10/2018 BBB 2  07/20/2017 07/21/2017 BBB 2  08/01/2017 09/01/2017
I have to look on (table2. Startdate between Table1 start and end dates) Or (table2 enddate between table1 start date and end date) and loop and flag the Table2 =Y for number of Units in table1 in that range.
My Expected output is :
ID Type StartDate EndDate   Flag AAA 1  01/02/2017 01/17/2017 Y AAA 1  01/02/2017 01/17/2017 Y AAA 1  01/02/2017 01/17/2017 Y AAA 1  01/02/2017 01/17/2017 Y AAA 1  02/01/2017 02/28/2017 N AAA 1  02/01/2017 02/28/2017 N AAA 1  02/01/2017 02/28/2017 N AAA 1  02/01/2017 02/28/2017 N AAA 1  02/01/2017 02/28/2017 N AAA 1  04/03/2018 04/03/2018 Y AAA 1  04/10/2018 04/10/2018 Y BBB 2  07/20/2017 07/21/2017 Y BBB 2  08/01/2017 09/01/2017 Y
I have to consider ID, Type and Date ranges for these two tables. I have to check table2 against table1 for Table2 start date and Table2 end date.
Can anyone please help me with this looping?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

hegdesuchi - Monday, October 8, 2018 8:58 PM
Hi All,
I have two tables table 1 and table 2.
Table1 :
ID Type StartDate EndDate Units AAA 1  4/3/2018 4/7/2018 1 AAA 1  4/8/2018 4/21/2018 1 AAA 1  1/8/2017 2/6/2017 2 AAA 1  1/1/2017 1/7/2017 2 BBB 2  7/16/2017 7/22/2017 1 BBB 2  8/1/2017 8/1/2017 1
Table 2: ID Type StartDate EndDate AAA 1  01/02/2017 01/17/2017 AAA 1  01/02/2017 01/17/2017 AAA 1  01/02/2017 01/17/2017 AAA 1  01/02/2017 01/17/2017 AAA 1  02/01/2017 02/28/2017 AAA 1  02/01/2017 02/28/2017 AAA 1  02/01/2017 02/28/2017 AAA 1  02/01/2017 02/28/2017 AAA 1  02/01/2017 02/28/2017 AAA 1  04/03/2018 04/03/2018 AAA 1  04/10/2018 04/10/2018 BBB 2  07/20/2017 07/21/2017 BBB 2  08/01/2017 09/01/2017
I have to look on (table2. Startdate between Table1 start and end dates) Or (table2 enddate between table1 start date and end date) and loop and flag the Table2 =Y for number of Units in table1 in that range.
My Expected output is :
ID Type StartDate EndDate   Flag AAA 1  01/02/2017 01/17/2017 Y AAA 1  01/02/2017 01/17/2017 Y AAA 1  01/02/2017 01/17/2017 N --only two units in this range AAA 1  01/02/2017 01/17/2017 N AAA 1  02/01/2017 02/28/2017 Y AAA 1  02/01/2017 02/28/2017 Y AAA 1  02/01/2017 02/28/2017 N --only two units in this range AAA 1  02/01/2017 02/28/2017 N AAA 1  02/01/2017 02/28/2017 N AAA 1  04/03/2018 04/03/2018 Y -- this satisfies AAA 1  04/10/2018 04/10/2018 Y -- this satisfies BBB 2  07/20/2017 07/21/2017 Y BBB 2  08/01/2017 09/01/2017 Y
I have to consider ID, Type and Date ranges for these two tables. I have to check table2 against table1 for Table2 start date and Table2 end date.
Can anyone please help me with this looping?

So why do you think a loop is necessary? Writing a query provides an inherent looping mechanism. However, I'm not sure I understand the nature of the relationship between your data and the expected results. You have 4 identical rows in Table 2 for which two of them get a Y to indicate they qualify. What I don't understand is why the other two rows don't get the exact same result. That scenario is then repeated, as the following 4 rows are also identical to each other, and yet only the first two qualify for a Y. You're going to need to be able to explain why an identical row get's a different result. This suggests you are doing some kind of matching of qualifying rows in Table 1, and then those Table 1 rows get "used up" and are presumably not used for any other matching. Please be very detailed in your explanation.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 2

Hi,
Basically, I have to deny the rows in table2 > number of units in table 1 for that date range.

sgmunson SSC Guru Points: 110639 More actions · Answer 3

hegdesuchi - Tuesday, October 9, 2018 8:38 AM
Hi,
Basically, I have to deny the rows in table2 > number of units in table 1 for that date range.

Okay, you've changed your expected output. Compare my post to what your post now contains and you'll see the difference, as will everyone else. From your comment, are you summing the value of Table 1's Units column for qualifying rows in Table 1, given a row in Table 2 ? And then only that many duplicated rows from table 2 would get a Y ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)