select query left outer join not returning expected result

Spunny 326 Reputation points
2024-03-21T13:45:39.6833333+00:00

declare @pTargetFund varchar(8) = '500', @tolAmt money = 1

declare @test1 table (

amount money,

tradeid int,

cusip varchar(12),

principal decimal(14,0),

principalx decimal(2,0),

accruint decimal(14,0),

accruintx decimal(2,0),

transnum varchar(20),

masttick varchar(20),

maturity varchar(50),

settledate varchar(50),

transtype varchar(50),

transcode varchar(4),

rectype decimal(3,0),

series varchar(4),

settleloc varchar(3),

ticker varchar(8),

shortname varchar(20),

fund varchar(8)

)

declare @test2 table (

dollaramount varchar(10),

centsamount varchar(2),

ID int,

cusip varchar(12),

statuscode char(1),

securitysubtype varchar(3),

SCDTransCode varchar(50),

participantnumber varchar(4),

sourceDate datetime

)

Insert into @test1

SELECT 97778333.33,	675984,	'47816FGB1',	9777833333,	2,	0,	0,	758130,	758129,	'20240711',	'20240207',	'B',	'OA',	'2',	'CP',	'DTC',	'JNJPP',	'WILL CAP APP',	'500'

select * from @test1

INSERT INTO @test2

SELECT '0048889166', '66', 145864, '47816FGB1', 'M', '525', 'BUY', '8486', '2024-02-07 00:00:00.000'

union all

SELECT '0048889166', '66', 145865, '47816FGB1', 'M', '525', 'BUY', '8486', '2024-02-07 00:00:00.000'

union all

SELECT '0021474753',	'15',	145866,		'59157THL0', 'M', '525', 'BUY', '8486', '2024-02-07 00:00:00.000'

union all

SELECT '0050000000',	'00',	145869,	'06417LYZ4', 'M', '525', 'BUY',  '8486', '2024-02-07 00:00:00.000'

union all

SELECT '0050000000',	'00',	145870,	'06417LYZ4', 'M', '525', 'BUY','8486', '2024-02-07 00:00:00.000'

select * from @test2

SELECT 

	sum(cast(Ds.DollarAmount as money)) As dollarAmount, 

	sum(cast(ds.CentsAmount as money)) As CentsAmount, 		 		

	bt.shortname,

	ds.CUSIP

FROM @test2 DS

LEFT OUTER JOIN @test1 BT ON bt.cusip = ds.CUSIP

	AND CONVERT(money, DollarAmount + '.' + CentsAmount)

		BETWEEN bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) - @tolAmt

			AND bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) + @tolAmt

	AND bt.settledate = '20240207'

	AND bt.transtype = 'B'

	AND bt.series = 'CP'

	AND bt.transcode in ('CPA', 'PA', 'OA', 'COA', 'OMB')

	AND bt.settleloc = 'DTC'

WHERE DS.StatusCode = 'M'

	AND SecuritySubType IN (520, 525, 526)

	AND ISNULL(bt.fund, @pTargetFund) = @pTargetFund 

	AND ScdTransCode = 'BUY'

	AND ParticipantNumber = '8486'

	AND DS.sourceDate = '20240207'

	GROUP BY DollarAmount, CentsAmount, DS.CUSIP, bt.shortname
```When I do left outer join of test2 with test1, all rows should be returned from test2 with matching rows from test1. Then why is shortName blank.

can someone please help.

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,583 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
35 questions
{count} votes

3 answers

Sort by: Most helpful
  1. CHIRANJIB MAZUMDAR 0 Reputation points
    2024-03-21T14:18:59.29+00:00

    Please try commenting out the where clauses first and see if the data shows up in the initial output, then gradually put the where clauses back.


  2. Bruce (SqlWork.com) 54,621 Reputation points
    2024-03-21T16:11:25.9566667+00:00

    short name is from the right table. you are doing a left outer join, so you get all left rows that match the join, and all left rows that don't match (with null for right values), then filtered by the where.

    in the join clause only left row with id '47816FGB1' have a right row match. but the join clause:

    AND CONVERT(money, DollarAmount + '.' + CentsAmount)
    	BETWEEN bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) - @tolAmt
          AND bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) + @tolAmt
    

    removes any matching right rows. so you only get left rows with no matching right values.

    0 comments No comments

  3. CosmogHong-MSFT 22,466 Reputation points Microsoft Vendor
    2024-03-22T01:55:00.4366667+00:00

    Hi @Spunny

    As answered above, the issue caused by this clause:

    AND CONVERT(money, DollarAmount + '.' + CentsAmount) BETWEEN bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) - @tolAmt AND bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) + @tolAmt
    

    For the cusip 47816FGB1, the calculated result is 48889166.66 between 97778332.33 and 97778334.33 which obviously not matched the join condition.

    Please check this clause and take some modification.

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments