SQL join on a row number

I have a weird join condition that I'm having some problems with. I want to join the two tables below using the following code, but am getting a problem.  
select 
	cte.*,
	drb.PaymentsUpperBound,
	drb.RefundTypeId,
	drb.EarnedDiscount
from 
	cteCombo cte
	join DiscountRefundBound drb on drb.PaymentsUpperBound = (cte.num + (select max(installmentnumber) from InstallmentSchedule where CustomerAccountId = @PartyId and DueDate <= @CurrentBusinessDate))

where 
	drb.DiscountRefundInstallmentBoundId = @DiscountRefundInstallmentBoundId

Open in new window

* Subquery returns the number 7:
cteCombo table
PartyId    PeriodStart                            PeriodEnd                            num
143774      2014-08-01 00:00:00.000      2014-08-31 23:59:59.000      0
143774      2014-09-01 00:00:00.000      2014-09-30 23:59:59.000      1
143774      2014-10-01 00:00:00.000      2014-10-31 23:59:59.000      2
143774      2014-11-01 00:00:00.000      2014-11-30 23:59:59.000      3
143774      2014-12-01 00:00:00.000      2014-12-31 23:59:59.000      4
143774      2015-01-01 00:00:00.000      2015-01-31 23:59:59.000      5
143774      2015-02-01 00:00:00.000      2015-02-28 23:59:59.000      6
143774      2015-03-01 00:00:00.000      2015-03-31 23:59:59.000      7
143774      2015-04-01 00:00:00.000      2015-04-30 23:59:59.000      8
143774      2015-05-01 00:00:00.000      2015-05-31 23:59:59.000      9
143774      2015-06-01 00:00:00.000      2015-06-30 23:59:59.000      10
143774      2015-07-01 00:00:00.000      2015-07-31 23:59:59.000      11

DiscountRefundBound Table
DiscountRefundInstallmentBoundId     PaymentsUpperBound     RefundTypeId     EarnedDiscount
3                                                                    0                                           1                            20.00
3                                                                    1                                           1                            21.00
3                                                                    2                                           1                            22.00
3                                                                    3                                           1                            23.00
3                                                                    4                                           1                            24.00
3                                                                    5                                           1                            25.00
3                                                                    6                                           1                            26.00
3                                                                    7                                           1                            27.00
3                                                                    8                                           1                            28.00
3                                                                    9                                           1                            29.00
3                                                                    10                                         1                            30.00
3                                                                    11                                         1                            31.00
3                                                                    12                                         1                            32.00

Desired Results:
PartyId    PeriodStart                            PeriodEnd                            num  PaymentsUpperBound  EarnedDiscount
143774      2014-08-01 00:00:00.000      2014-08-31 23:59:59.000      0        7                                         27.00
143774      2014-09-01 00:00:00.000      2014-09-30 23:59:59.000      1        8                                         28.00
143774      2014-10-01 00:00:00.000      2014-10-31 23:59:59.000      2        9                                         29.00
143774      2014-11-01 00:00:00.000      2014-11-30 23:59:59.000      3        10                                       30.00
143774      2014-12-01 00:00:00.000      2014-12-31 23:59:59.000      4        11                                       31.00
143774      2015-01-01 00:00:00.000      2015-01-31 23:59:59.000      5        12                                       32.00
143774      2015-02-01 00:00:00.000      2015-02-28 23:59:59.000      6        NULL                                  NULL
143774      2015-03-01 00:00:00.000      2015-03-31 23:59:59.000      7        NULL                                  NULL
143774      2015-04-01 00:00:00.000      2015-04-30 23:59:59.000      8        NULL                                  NULL
143774      2015-05-01 00:00:00.000      2015-05-31 23:59:59.000      9        NULL                                  NULL
143774      2015-06-01 00:00:00.000      2015-06-30 23:59:59.000      10      NULL                                  NULL
143774      2015-07-01 00:00:00.000      2015-07-31 23:59:59.000      11      NULL                                  NULL


Here's the entire query below:
declare @CurrentBusinessDate datetime = '8/26/2014'
declare @PartyId int = 143774
declare @InstallmentCount int = 10
declare @InstallmentMadeCount int = 0
declare @TotalDiscountAmountOnAccount money = 2500.00
declare @DiscountRefundInstallmentBoundId int = 3

;WITH                    
cteTally11 AS (                    
SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL                    
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11             
),
cteCombo AS (
select @PartyId as PartyId,
DATEADD(MONTH, DATEDIFF(MONTH, 0, @CurrentBusinessDate) + t.tally, 0) AS PeriodStart,
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m, 0, @CurrentBusinessDate) + t.tally + 1, 0)) as PeriodEnd,
row_number() over (order by t.tally) - 1 as num
FROM                    
cteTally11 t
)

select 
	cte.*,
	drb.PaymentsUpperBound,
	drb.RefundTypeId,
	drb.EarnedDiscount
from 
	cteCombo cte
	join DiscountRefundBound drb on drb.PaymentsUpperBound = (cte.num + (select max(installmentnumber) from InstallmentSchedule where CustomerAccountId = @PartyId and DueDate <= @CurrentBusinessDate))

where 
	drb.DiscountRefundInstallmentBoundId = @DiscountRefundInstallmentBoundId

Open in new window


My Actual Results:
PartyId   PeriodStart                            PeriodEnd                           num PaymentsUpperBound      EarnedDiscount
143774      2014-08-01 00:00:00.000      2014-08-31 23:59:59.000      0      7      27.00
143774      2014-09-01 00:00:00.000      2014-09-30 23:59:59.000      1      8      28.00
143774      2014-10-01 00:00:00.000      2014-10-31 23:59:59.000      2      9      29.00
143774      2014-11-01 00:00:00.000      2014-11-30 23:59:59.000      3      10      30.00
143774      2014-12-01 00:00:00.000      2014-12-31 23:59:59.000      4      11      31.00
143774      2015-01-01 00:00:00.000      2015-01-31 23:59:59.000      5      12      32.00
LVL 8
pzozulkaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
do a left join instead of a normal join.
0
pzozulkaAuthor Commented:
Same results.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
sorry, you could have done a right join, or as I prefer lefts:

select 
	cte.*,
	drb.PaymentsUpperBound,
	drb.RefundTypeId,
	drb.EarnedDiscount
from 
	cteCombo cte
        left	join DiscountRefundBound drb on (cte.num + (select max(installmentnumber) from InstallmentSchedule where CustomerAccountId = @PartyId and DueDate <= @CurrentBusinessDate)) =  drb.PaymentsUpperBound 

where 
	drb.DiscountRefundInstallmentBoundId = @DiscountRefundInstallmentBoundId

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pzozulkaAuthor Commented:
Left join produces same results.

A right join produces the following results, which is not what I'm looking for. Basically what I'm looking for is to ALWAYS list the 12 months of date ranges.

PartyId	PeriodStart	PeriodEnd	num	PaymentsUpperBound	RefundTypeId	EarnedDiscount
NULL	NULL	NULL	NULL	0	1	20.00
NULL	NULL	NULL	NULL	1	1	21.00
NULL	NULL	NULL	NULL	2	1	22.00
NULL	NULL	NULL	NULL	3	1	23.00
NULL	NULL	NULL	NULL	4	1	24.00
NULL	NULL	NULL	NULL	5	1	25.00
NULL	NULL	NULL	NULL	6	1	26.00
143774	2014-08-01 00:00:00.000	2014-08-31 23:59:59.000	0	7	1	27.00
143774	2014-09-01 00:00:00.000	2014-09-30 23:59:59.000	1	8	1	28.00
143774	2014-10-01 00:00:00.000	2014-10-31 23:59:59.000	2	9	1	29.00
143774	2014-11-01 00:00:00.000	2014-11-30 23:59:59.000	3	10	1	30.00
143774	2014-12-01 00:00:00.000	2014-12-31 23:59:59.000	4	11	1	31.00
143774	2015-01-01 00:00:00.000	2015-01-31 23:59:59.000	5	12	1	32.00

Open in new window

0
Kyle AbrahamsSenior .Net DeveloperCommented:
Do you copy the whole query?

Essentially you're starting with the CTE table, and adding on payment upperbound, refundtypeid, earned discount, but you want everything from the CTE.

so select * from CTE should give you all of the first 4 columns.

adding select * from cte
             left join DRB on cte [. . .] should change nothing about CTE or it's results
0
pzozulkaAuthor Commented:
Yep, here's the current query:
declare @CurrentBusinessDate datetime = '8/26/2014'
declare @PartyId int = 143774
declare @InstallmentCount int = 10
declare @InstallmentMadeCount int = 0
declare @TotalDiscountAmountOnAccount money = 2500.00
declare @DiscountRefundInstallmentBoundId int = 3

;WITH                    
cteTally11 AS (                    
SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL                    
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11             
),
cteCombo AS (
select @PartyId as PartyId,
DATEADD(MONTH, DATEDIFF(MONTH, 0, @CurrentBusinessDate) + t.tally, 0) AS PeriodStart,
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m, 0, @CurrentBusinessDate) + t.tally + 1, 0)) as PeriodEnd,
row_number() over (order by t.tally) - 1 as num
FROM                    
cteTally11 t
)

select 
	cte.*,
	drb.PaymentsUpperBound,
	drb.RefundTypeId,
	drb.EarnedDiscount
from 
	cteCombo cte
        left	join DiscountRefundBound drb on (cte.num + (select max(installmentnumber) from InstallmentSchedule where CustomerAccountId = @PartyId and DueDate <= @CurrentBusinessDate)) =  drb.PaymentsUpperBound 

where 
	drb.DiscountRefundInstallmentBoundId = @DiscountRefundInstallmentBoundId

Open in new window

And here are the current results:
143774	2014-08-01 00:00:00.000	2014-08-31 23:59:59.000	0	7	1	27.00
143774	2014-09-01 00:00:00.000	2014-09-30 23:59:59.000	1	8	1	28.00
143774	2014-10-01 00:00:00.000	2014-10-31 23:59:59.000	2	9	1	29.00
143774	2014-11-01 00:00:00.000	2014-11-30 23:59:59.000	3	10	1	30.00
143774	2014-12-01 00:00:00.000	2014-12-31 23:59:59.000	4	11	1	31.00
143774	2015-01-01 00:00:00.000	2015-01-31 23:59:59.000	5	12	1	32.00

Open in new window


I believe the results are changing because of the weird join condition, but I can't quite put my finger on it.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
It's actually the where clause.  

select
      cte.*,
      drb.PaymentsUpperBound,
      drb.RefundTypeId,
      drb.EarnedDiscount
from
      cteCombo cte
        left      join DiscountRefundBound drb on (cte.num + @delta) = drb.PaymentsUpperBound
where
drb.DiscountRefundInstallmentBoundId = @DiscountRefundInstallmentBoundId OR
drb.DiscountRefundInstallmentBoundId is null
0
pzozulkaAuthor Commented:
no luck
0
Kyle AbrahamsSenior .Net DeveloperCommented:
better answer:

note I forgot the definition for delta, you only need this once so:
declare @delta int
set @delta =  (select max(installmentnumber) from InstallmentSchedule where CustomerAccountId = @PartyId and DueDate <= @CurrentBusinessDate)

-- confirm 7, remove later
select @delta delta

select 
      cte.*,
      drb.PaymentsUpperBound,
      drb.RefundTypeId,
      drb.EarnedDiscount
from 
      cteCombo cte
left join 
--change the * to the columns you need.
(select * from DiscountRefundBound where
DiscountRefundInstallmentBoundId = @DiscountRefundInstallmentBoundId
) drb  on (cte.num + @delta) =  drb.PaymentsUpperBound 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pzozulkaAuthor Commented:
Yes, this works. Thanks much. I'm staring at it, and not seeing why this works, and why my query didn't.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
It has to do with the where clause.

Essentially I'm limiting the table data and putting the where condition inside the the sub query for DRB.

Like I said, a left join won't change CTE.  If you removed the where clause entirely, using a left join you should see similar results.
0
PortletPaulfreelancerCommented:
You do need the left join, then alter the original where clause into a join condition

select
      cte.*,
      drb.PaymentsUpperBound,
      drb.RefundTypeId,
      drb.EarnedDiscount
from
      cteCombo cte
      left join DiscountRefundBound drb
        on drb.PaymentsUpperBound = (cte.num + (select max(installmentnumber) from InstallmentSchedule where CustomerAccountId = @PartyId and DueDate <= @CurrentBusinessDate))
      and drb.DiscountRefundInstallmentBoundId = @DiscountRefundInstallmentBoundId


When you place conditions on an outer joined table in the where clause, as you can see above, you also need to cater for the possibility that the join produces a NULL result. In this case you are limiting only the left joined table, so it is simpler to place that condition on the join.

Test:
    CREATE TABLE DiscountRefundBound
        ([DiscountRefundInstallmentBoundId] int, [PaymentsUpperBound] int, [RefundTypeId] int, [EarnedDiscount] int)
    ;
        
    INSERT INTO DiscountRefundBound
        ([DiscountRefundInstallmentBoundId], [PaymentsUpperBound], [RefundTypeId], [EarnedDiscount])
    VALUES
        (3, 0, 1, 20.00),
        (3, 1, 1, 21.00),
        (3, 2, 1, 22.00),
        (3, 3, 1, 23.00),
        (3, 4, 1, 24.00),
        (3, 5, 1, 25.00),
        (3, 6, 1, 26.00),
        (3, 7, 1, 27.00),
        (3, 8, 1, 28.00),
        (3, 9, 1, 29.00),
        (3, 10, 1, 30.00),
        (3, 11, 1, 31.00),
        (3, 12, 1, 32.00)
    ;
    
    
    CREATE TABLE InstallmentSchedule 
        ([CustomerAccountId] int, [installmentnumber] int, [DueDate] datetime)
    ;
        
    INSERT INTO InstallmentSchedule 
        ([CustomerAccountId], [installmentnumber], [DueDate])
    VALUES
        (143774, 9, '2013-09-01 00:00:00')
    ;
    
**Query 1**:

    declare @CurrentBusinessDate datetime = '8/26/2014'
    declare @PartyId int = 143774
    declare @InstallmentCount int = 10
    declare @InstallmentMadeCount int = 0
    declare @TotalDiscountAmountOnAccount money = 2500.00
    declare @DiscountRefundInstallmentBoundId int = 3
    
    ;WITH                    
    cteTally11 AS (                    
    SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL                    
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11             
    ),
    cteCombo AS (
    select @PartyId as PartyId,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, @CurrentBusinessDate) + t.tally, 0) AS PeriodStart,
    DATEADD(s,-1,DATEADD(mm, DATEDIFF(m, 0, @CurrentBusinessDate) + t.tally + 1, 0)) as PeriodEnd,
    row_number() over (order by t.tally) - 1 as num
    FROM                    
    cteTally11 t
    )
    
    select 
    	cte.*,
    	drb.PaymentsUpperBound,
    	drb.RefundTypeId,
    	drb.EarnedDiscount
    from 
    	cteCombo cte
    	left join DiscountRefundBound drb on drb.PaymentsUpperBound = (cte.num + (select max(installmentnumber) from InstallmentSchedule where CustomerAccountId = @PartyId and DueDate <= @CurrentBusinessDate))
    
    and 
    	drb.DiscountRefundInstallmentBoundId = @DiscountRefundInstallmentBoundId

**[Results][2]**:
    | PartyId |                 PeriodStart |                   PeriodEnd | num | PaymentsUpperBound | RefundTypeId | EarnedDiscount |
    |---------|-----------------------------|-----------------------------|-----|--------------------|--------------|----------------|
    |  143774 |    August, 01 2014 00:00:00 |    August, 31 2014 23:59:59 |   0 |                  9 |            1 |             29 |
    |  143774 | September, 01 2014 00:00:00 | September, 30 2014 23:59:59 |   1 |                 10 |            1 |             30 |
    |  143774 |   October, 01 2014 00:00:00 |   October, 31 2014 23:59:59 |   2 |                 11 |            1 |             31 |
    |  143774 |  November, 01 2014 00:00:00 |  November, 30 2014 23:59:59 |   3 |                 12 |            1 |             32 |
    |  143774 |  December, 01 2014 00:00:00 |  December, 31 2014 23:59:59 |   4 |             (null) |       (null) |         (null) |
    |  143774 |   January, 01 2015 00:00:00 |   January, 31 2015 23:59:59 |   5 |             (null) |       (null) |         (null) |
    |  143774 |  February, 01 2015 00:00:00 |  February, 28 2015 23:59:59 |   6 |             (null) |       (null) |         (null) |
    |  143774 |     March, 01 2015 00:00:00 |     March, 31 2015 23:59:59 |   7 |             (null) |       (null) |         (null) |
    |  143774 |     April, 01 2015 00:00:00 |     April, 30 2015 23:59:59 |   8 |             (null) |       (null) |         (null) |
    |  143774 |       May, 01 2015 00:00:00 |       May, 31 2015 23:59:59 |   9 |             (null) |       (null) |         (null) |
    |  143774 |      June, 01 2015 00:00:00 |      June, 30 2015 23:59:59 |  10 |             (null) |       (null) |         (null) |
    |  143774 |      July, 01 2015 00:00:00 |      July, 31 2015 23:59:59 |  11 |             (null) |       (null) |         (null) |

  [1]: http://sqlfiddle.com/#!3/0a569/3
  [2]: http://sqlfiddle.com/#!3/0a569/3/0

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.