A DBA walks into a restaurant, then runs screaming from the building.  Why?  All of the tables were empty.

What do you get when you cross a shark with a porcupine?  A SQL(sea quill).  Or SQL BITeS

Try this:
SELECT L.Entity, L.Account,
  COALESCE(R.Local_Currency_ID, L.Report_Currency_ID) AS Local_Currency_ID,
  L.Report_Currency_ID,
  L.Amt AS USAmt,
  COALESCE(R.Amt, L.Amt) AS Amt
FROM (SELECT U.Entity, U.Account, U.Currency AS Report_Currency_ID,...

CREATE TABLE #CIM_FACT
(
    YearYYYY    nvarchar (4) not null ,
    Period        nvarchar (10) not null,
    Entity        nvarchar (10) not null,
    Account        nvarchar (10) not null,
    currency    nvarchar (10) not null,
    Amt            Decimal (18, 2) not null

);

Insert INTO #cim_Fact
VALUES ('2018','January','ENT_1000','1588','USD Total',-888823191.97),
    ('2018','January','ENT_1000','1591','USD...

You don't need the loop if you combine Sean's code with the answer you got from your other post.
https://www.sqlservercentral.com/Forums/1932209/How-do-I-count-the-number-of-occurrences-the-character-P-in-the-string

Not sure, but give this a try:
SELECT L.Entity, L.Account,
    R.Local_Currency_ID,
    L.Report_Currency_ID,
    L.Amt,
    R.Amt
FROM (SELECT U.Entity, U.Account, U.Currency_ID AS Report_Currency_ID, R.Amt
        FROM Your_Table AS U
        WHERE Currency_ID...

There may be a better way, but here you go:
DECLARE @yearMonth CHAR(7);
DECLARE @prioryearMonth CHAR(7);

SET @yearMonth = '2017-06';

SET @prioryearMonth = CONCAT(CAST(LEFT(@yearMonth, 4) AS...

Do you know why Jeff had to get out of the construction business?
He refused to use RBAR!

Click on your profile name, up in the right corner.  In your Profile look under 'Topics' for posts you've started.  Click on 'Replies' for those that you've replied to.

ELTL, Is the way I would describe how I prefer to handle this data.  Trying to figure out what record in a delimited file has the bad value(using ETL) when...