Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Query

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-03-31 : 07:17:22

i have 2 tables


Table_A
Row     Date          Qnty-Out
1    01/01/2015        20
2    01/02/2015        100
3    01/03/2015        100
 
Table_B
Row     Date          Qnty-Out
1    01/02/2015        6
2    01/02/2015        4

i want to get the join between A and B whe a.Date<=b.Date
i mean


A.Row     A.Date          A.Qnty-Out  B.Row   B.Date   B.Qnty-Out
1       01/01/2015          20          1    01/02/2015        6
1       01/01/2015          20          1    01/02/2015        4
2       01/02/2015         100          1    01/02/2015        6
2       01/02/2015         100          1    01/02/2015        4
3       01/03/2015         100          NULL   NULL             NULL

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 08:57:52

OK -- so post your query so far. It's just a simple join.

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-03-31 : 14:23:02

quote:
Originally posted by gbritton

OK -- so post your query so far. It's just a simple join.

what do you mean simple join?

you see ,in the first table the date (01/01/2015) - it doesn't exist in the second table. so i make the join with the next date(01/02/2015)

another example


Table_B
Row     Date          Qnty-Out
1    01/03/2015        6
2    01/03/2015        4


so i want to get
A.Row     A.Date          A.Qnty-Out  B.Row   B.Date   B.Qnty-Out
1       01/01/2015          20          1    01/03/2015        6
1       01/01/2015          20          1    01/03/2015        4
2       01/02/2015         100          1    01/03/2015        6
2       01/02/2015         100          1    01/03/2015        4
3       01/03/2015         100          1    01/03/2015        6
3       01/03/2015         100          1    01/03/2015        4

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 14:40:01

you asked:

quote:
i want to get the join between A and B whe a.Date<=b.Date

select ...
join ...
on a.Date<=b.Date

pretty simple!

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-04-01 : 01:13:39

i try to explain with another example:


Table_A
Row     Date          Qnty-Out
1    01/01/2015        20
2    01/02/2015        100
3    01/03/2015        100
 
Table_B
Row     Date          Qnty-Out
1    01/02/2015        6
2    01/02/2015        4
3    01/03/2015        5


A.Row     A.Date          A.Qnty-Out  B.Row   B.Date   B.Qnty-Out
1       01/01/2015          20          1    01/02/2015        6
1       01/01/2015          20          2    01/02/2015        4
2       01/02/2015         100          1    01/02/2015        6
2       01/02/2015         100          2    01/02/2015        4
3       01/03/2015         100          3    01/03/2015            5

so if i make a.date<=b.date i will join between dates that i do not need ,for example: A.row=1 with b.row=3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources