thanks that worked in both 2008 and 2012.  code is updated and users are happy

ok so got that,

Convert(CHAR(10),DATEADD(day,-1,DATEADD(mm,DATEDIFF(mm,0,DATEFROMPARTS(YearID,REPLACE(PeriodIDValue,'P',''),1))+1,0)),101) AS PeriodEndDate

However I just realized/remembered my test server is on 2012, but my production server will not be on 2012(I.e. still on 2008) until Feb 24,...

ok, so this is a view, that is used to send a TXT file to a third party application.  so I need to use CONVERT.  I will need to research....

ok basically that worked like a charm.  but the answer was 2019-12-31 00:00:00.000

I need 12/31/2019

and with a couple changes this did  work,  but I think I will go with the table solution.  But thanks  for all your work.

WITH CTE AS (

Select CASE

WHEN ENTITY +...

I GOT IT, thank everyone

SELECT A.YearID, B.Label AS PeriodIDValue

, B.Description AS PeriodID

, C.Label AS Entity

,Account = ISNULL(G.MasterAccount, D.label)

,E.Label AS Currency

, CAST(A.dData AS Decimal(18, 2)) AS Amt

FROM HYPEA.dbo.CIM_FACT AS A LEFT...

ok so I  added  the new table and it is now available.  but I cannot figure out how to use this new table in the D.Label AS Account

SELECT A.YearID, B.Label...

so C.Label and d.label are NVARCHAR

So I will make the new table the same.

thanks I will check  the data types  now.

all right so lets start over.  I actually have a query that limits the raw data.  I do not send all the raw information over. We have about 30 Entity...

• This reply was modified 5 years, 3 months ago by  randyetheridge.

ok I understand that.  And I think the end users would like that.  the table would give them easier visibility to the rollups.

Let me  work  on that.

so you did an amazing job interpreting my code 🙂 sorry I am just learning proper coding syntax rules.   And Yes you did get all the columns.

tried  the above code,...

First just to be clear I am not a developer.  I am just an accountant who knows enough SQL to get myself into these predicaments 🙂  So please be patient...

it will take me a few minutes but I can create the code for a table with the 4,000 rows. If need I will post the table code over the...

sorry I missed the truncation

Raw data

HFMENTITY > account > Amt

ENT_1010 ACC_1560000 15,015,915.00

ENT_1010 ACC_1560020    2,646,01400

ENT_1010 ACC_1560100   4,411,360.69

ENT_1010 ACC_1560400   2,544,500.00

ENT_1010 ACC_1560300      68,223.56

Current output from my view

ENT_1010 ACC_1560000...