SQL Query comparing Float to Decimal Help

Not sure why but I know without the trims for some reason comparing both in some cases was not matching.

Without actual case showing that you need that TRIMs drop them. E.g.

SELECT  LTRIM(STR(98235.12, 10, 2)) ,
        '"' + STR(98235.12, 10, 2) + '"';

Select all Open in new window

But as you said your types are FLOAT and DECIMAL, then it does not apply.

See attached, I have made couple of comparisons.
those are nullable fields, however, did not make any of those as null to compare..
but, that's something that you can play with and see how far you can take that ..

@John, please post code, not images..

I believe that typing 6 lines of code is not that cumbersome..
I get your point though.. perhaps in the near future postings.

Thanks guys I am going through these this AM.

Hey guys so this is what I am seeing.  Notice the .8 and .80 in the data.  Also notice the NULL issue with the AmountUSD Null compare.

Sample:

  CREATE TABLE #TempCustodianMVData
	   (
		  PortfolioID int not null,
		  MVDate datetime not null,
		  Amount float null,
		  AmountUSD float null,
		  ReconcileFlag char(1) null,
		  CurrencyID int not null
	   )

INSERT INTO #TempCustodianMVData
(
PortfolioID, 
MVDate, 
Amount,
AmountUSD, 
ReconcileFlag,
CurrencyID
)
VALUES
(
1775,
'5/31/2015',
39619139.8,
NULL,
'F',
8
)

 CREATE TABLE #TempCustodianMVSnap
	   (
		  PortfolioID int not null,
		  MVDate datetime not null,
		  Amount decimal(18,2) null,
		  AmountUSD decimal(18,2) null,
		  ReconcileFlag char(1) null,
		  CurrencyID int not null
	   )
	   
  INSERT INTO #TempCustodianMVSnap
(
PortfolioID, 
MVDate, 
Amount,
AmountUSD, 
ReconcileFlag,
CurrencyID
)
VALUES
(
1775,
'5/31/2015',
39619139.80,
NULL,
'F',
8
)

SELECT PortfolioID, 
			   MVDate, 
			   Amount,
			   AmountUSD, 
			   ReconcileFlag,
			   CurrencyID	   
		FROM #TempCustodianMVData CMV
		WHERE CMV.ReconcileFlag IN ('F','R') AND NOT EXISTS
			  (SELECT * FROM #TempCustodianMVSnap MVS 
						WHERE MVS.MVDate = CMV.MVDate
							  AND LTRIM(RTRIM(MVS.Amount)) = LTRIM(RTRIM(CAST(CMV.Amount AS decimal(18,2))))
							  AND LTRIM(RTRIM(MVS.AmountUSD)) = LTRIM(RTRIM(CAST(CMV.AmountUSD AS decimal(18,2))))
							  AND MVS.ReconcileFlag = CMV.ReconcileFlag
							  AND MVS.CurrencyID = CMV.CurrencyID)

Select all Open in new window

This is returning a record where it should not.  If I take out the AmountUSD compare then it works where I don't return a record.   If I do this it works and does not return a record:

AND LTRIM(RTRIM(ISNULL(MVS.AmountUSD,0))) = LTRIM(RTRIM(CAST(ISNULL(CMV.AmountUSD,0) AS decimal(18,2))))

The trims also were needed for another issue I saw but can't remember where.

.8 is equal to .80. Thus it's correct.

You cannot compare NULL with the equal operator. Unknown cannot be equal to Unknown. You need the IS NULL operator.

Don't use the TRIMs when you don't know why you need it..

E.g.

SET DATEFORMAT MDY;

DECLARE @TempCustodianMVData TABLE
    (
      PortfolioID INT NOT NULL ,
      MVDate DATETIME NOT NULL ,
      Amount FLOAT NULL ,
      AmountUSD FLOAT NULL ,
      ReconcileFlag CHAR(1) NULL ,
      CurrencyID INT NOT NULL
    );

INSERT  INTO @TempCustodianMVData
VALUES  ( 1775, '5/31/2015', 39619139.8, NULL, 'F', 8 );

DECLARE @TempCustodianMVSnap TABLE
    (
      PortfolioID INT NOT NULL ,
      MVDate DATETIME NOT NULL ,
      Amount DECIMAL(18, 2) NULL ,
      AmountUSD DECIMAL(18, 2) NULL ,
      ReconcileFlag CHAR(1) NULL ,
      CurrencyID INT NOT NULL
    );
	   
INSERT  INTO @TempCustodianMVSnap
VALUES  ( 1775, '5/31/2015', 39619139.80, NULL, 'F', 8 );

SELECT  PortfolioID ,
        MVDate ,
        Amount ,
        AmountUSD ,
        ReconcileFlag ,
        CurrencyID
FROM    @TempCustodianMVData CMV
WHERE   NOT EXISTS ( SELECT *
                     FROM   @TempCustodianMVSnap MVS
                     WHERE  MVS.MVDate = CMV.MVDate
                            AND MVS.Amount = CMV.Amount
                            AND ( MVS.AmountUSD = CMV.AmountUSD
                                  OR MVS.AmountUSD IS NULL
                                  OR CMV.AmountUSD IS NULL
                                )
                            AND MVS.ReconcileFlag = CMV.ReconcileFlag
                            AND MVS.CurrencyID = CMV.CurrencyID );

Select all Open in new window

ste5an its okay to just do ISNULL(X,0) right on both sides?

It depends on whether you want

 MVS.AmountUSD = CMV.AmountUSD
 OR MVS.AmountUSD IS NULL
 OR CMV.AmountUSD IS NULL

Select all Open in new window

or

 MVS.AmountUSD = CMV.AmountUSD
 OR (
    MVS.AmountUSD IS NULL 
    AND 
    CMV.AmountUSD IS NULL 
)

Select all Open in new window

In the first case it's equivalent.

thanks