Check Code Correction Current Period Prior Period Movement
-
Please assist me?I am trying to calculate
Current Period
Prior Period
Movement
from my data, the date columns are:
Please check my code for any errors, code runs, i just want to make sure it does what it's supposed to do. Small data set is attached.
-- Use IF EXISTS to check for the view's existence before dropping it in T-SQL
IF OBJECT_ID('model.IMETA_ZTRB_BRACS_Model_TA_BW_View', 'V') IS NOT NULL
DROP VIEW model.IMETA_ZTRB_BRACS_Model_TA_BW_View;
-- Create the view
CREATE VIEW model.IMETA_ZTRB_BRACS_Model_TA_BW_View
AS
SELECT
t.ZTBR_TransactionCode,
-- ... (other columns)
t.SDM_Function,
-- Convert the amount to USD
CAST(ROUND(t.Amount_in_Company_Code_Currency / curr.ConversionRate, 2) AS NUMERIC(36, 2)) AS [Amount in USD],
-- Calculate the Current Period Amount
CASE
WHEN CAST(t.Fiscal_Year AS INT) = YEAR(GETDATE()) THEN t.Amount_in_Company_Code_Currency
ELSE NULL
END AS [Current Period],
-- Calculate the Prior Period Amount
CASE
WHEN CAST(t.Fiscal_Year AS INT) = YEAR(GETDATE()) - 1 THEN t.Amount_in_Company_Code_Currency
ELSE NULL
END AS [Prior Period],
-- Calculate the Movement
CASE
WHEN CAST(t.Fiscal_Year AS INT) = YEAR(GETDATE()) THEN t.Amount_in_Company_Code_Currency
WHEN CAST(t.Fiscal_Year AS INT) = YEAR(GETDATE()) - 1 THEN -t.Amount_in_Company_Code_Currency
ELSE NULL
END AS [Movement]
FROM
model.IMETA_ZTRB_BRACS_Model_TA_BW3 t
LEFT JOIN
(SELECT
IMETA_Master_Currency_Data_TA_BR.Currency,
IMETA_Master_Currency_Data_TA_BR.CurrencyName,
CASE
WHEN IMETA_Master_Currency_Data_TA_BR.Currency = 'USD' THEN 1
ELSE IMETA_Master_Currency_Data_TA_BR.CurrencyToValue
END AS ConversionRate
FROM
dim.IMETA_Master_Currency_Data_TA_BR
WHERE
IMETA_Master_Currency_Data_TA_BR.Scenario = 'BUD'
) curr ON t.Company_Code_Currency = curr.Currency;
-- In T-SQL, you cannot directly change the owner of a view like in PostgreSQL.
-- You would need to use sp_changeobjectowner or ALTER AUTHORIZATION depending on the version of SQL Server. -
Most people on the forum won't open attached items.
Can you provide the DDL-scripts of the tables involved?
Create table model.IMETA_ZTRB_BRACS_Model_TA_BW3
(...)
Create table dim.IMETA_Master_Currency_Data_TA_BR ...
Can you provide sample data
INSERT INTO model.IMETA_ZTRB_BRACS_Model_TA_BW3 values ....
Can you provide expected output?
-
Not red flags, but habits I like to get into. For SQL 2022, you can use the syntax "CREATE OR ALTER" which I find to be much nicer than the drop and create approach. Mind you that is just a personal preference. I prefer to ALTER if the object already exists rather than dropping and recreating it.
But looking at the query (and not having sample data), my eyes are saying that your period CASE statements are going to be in random currencies, not USD which probably isn't what you are wanting. Next, Fiscal year per row is only going to have 1 value, so if "current period" is populated, then "prior period" will ALWAYS be null. And same thing going the other way.
On top of that, Movement is going to be either the same as Current Period OR it will be negative of prior period. This doesn't feel like it will be accurate for what I would expect movement to be... I would expect movement to be the current period total minus the previous period total... or possibly subtracting the other way around depending on the requirements.
So if that is the expected output, then yes, it is doing what you are asking, but I have a feeling that several things are wrong. My GUESS is that the current period and prior period values SHOULD be on the same line and that the current period and prior period should be a sum of the rows in that period AND movement should be the difference between the current period and prior period... Mind you I am just guessing as I don't have your data, a good definition of the problem, or expected output.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Thank you. Some sample data attched.
-
@Mr. Brian Gale See Fact file for financial data.
-
Even with the sample data, I don't know what your expected output is. Tell me what you expect the output to look like and then I can try to help. As far as I know, your query does what you need...
Plus I'm not the type to just download random stuff off the Internet and hope it contains what I expect. Just post some sample data here on ssc. No need to have anybody download anything...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
I am trying to achieve this:
-
as you have been advised before we need you to post create table DDL, insert statements into the tables required, and what you tried - while a image is good for the desired output, everything else should be supplied in text format, using the CODE formatting available on the site.
something like (which you have seen before)
create table #temprates
(id int
,currency varchar(10)
,rate decimal(10, 5)
)
insert into #temprates
select *
from (values ('usd', 123.22)
, ('ukp', 333.12)
) t -
So looking at your expected results, your query isn't doing what you want... but I imagine you already knew that, right? You did try running it against your dataset, right? Your original question made it sound like you had validated the query against a small sample data set, but if you did that, you would know that the calculations do not work OR you have poor sample data.
In the future, it would be better to say something like "I am having trouble with the following query. It should be doing "XYZ" but my expected results are coming out wrong. I want it to calculate column A as <describe the calculation>, column B as <describe the calculation>: <DDL to recreate the table structure, DML to create some sample data, query having problems>. Expected output: <sample expected output>, actual output: <sample actual output>."
I think what you are going to want to look into is windowing functions and doing a SUM on those columns to get the data you want. Mind you, I could be completely out to lunch here as I don't know your data or how you are trying to sum things up or anything, I am just guessing, so the advice below could be off. Heck, you may not even need to sum anything up.
So, for current period, you would want something along the lines of:
SUM(CASE WHEN CAST(t.Fiscal_Year AS INT) = YEAR(GETDATE())
THEN t.Amount_in_Company_Code_Currency
ELSE 0
END)
OVER (PARTITION BY Description)
AS [Current Period]Mind you, I am not certain that the "Description" field is what you want to partition by... you may want to partition by Transaction code or the Function.
But using the above, you should be able to calculate out the current period, then repeat it with some tweaks to get the previous period.
Now, the one thing I can't see a good way to help you calculate is the Movement. My guess (as I saw no description of what you wanted here) was that it was the the movement was the current period minus previous period, but then I saw that movement on "Exchange Adjustment Receivables" was not that calculation. 27-(-2)=29, but your sample output shows it as being 28. SO movement must be some other calculation OR is wrong in the sample data OR the current and prior periods are rounded up (or down?) but movement is based on the unrounded values? What I mean is ceiling(26.1-(-1.1))=28 for example while ceiling(26.1)=27 and ceiling(-1.1)=2. What I would REALLY like to see is:
1 - sample data
2 - table definitions
3 - Definition of the calculations on the 3 fields (like "movement is the current period minus prior period" and "current period is the sum of the amount converted to USD per Transaction type")
Having a good definition of the problem will make solving the problem a lot easier. When you said "current period", you didn't specify that you needed to have that grouped by anything. My initial assumption was that it was a grand total you wanted, then in the sample data I learned it is broken up by SOMETHING. Unfortunately I can't say with any certainty what it is broken up by. It LOOKS like description is one of the fields it is broken up by, but you may have a better indexed column you can use for that lookup. AND it may not even be broken up by description - that assumption of mine was done based on the screenshot you provided... Plus, we have no idea if the current period, prior period, or movement need to have the currency converted.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Also, forgive my ignorance, but what do you mean by the statement
-- In T-SQL, you cannot directly change the owner of a view like in PostgreSQL.
-- You would need to use sp_changeobjectowner or ALTER AUTHORIZATION depending on the version of SQL Server.
In PostgreSQL you can only change the view owner after the view is created, same as with SQL Server. The only difference is you use "ALTER VIEW" instead of "ALTER AUTHORIZATION"? Plus, sp_changeobjectowner (if I am not mistaken) was recommended to be avoided way back in SQL 2005 and newer versions of SQL should use ALTER AUTHORIZATION. If your SQL server is older than 2005, wondering if I should use "sp_changeobjectowner" or "ALTER AUTHORIZATION" is likely not one of my thoughts... something that old I'm more praying it doesn't crash irrecoverably... Mind you, I do have a 2005 and a 2000 instance I am responsible for, but those are both slated for removal "soon".
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply