SELECT LTRIM(STR(98235.12, 10, 2)) ,
'"' + STR(98235.12, 10, 2) + '"';
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)
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 );
MVS.AmountUSD = CMV.AmountUSD
OR MVS.AmountUSD IS NULL
OR CMV.AmountUSD IS NULL
MVS.AmountUSD = CMV.AmountUSD
OR (
MVS.AmountUSD IS NULL
AND
CMV.AmountUSD IS NULL
)