I have a question that’s of a design problem than a Code one. This is based on a complex structure. It’s really dificult to get all the important information for the question destiled here without revealing too much about the bussines and also without makeing an imposibly long post. I’ll do my best to explain everything as clear as I can. But if you think something is not clear, please let me know and I’ll try to explain it in another way.
Lets asume the next scenario:
I have to create a dashbaord that shows the following fields:
PriceInUSD Total Tons in USD Total Tons in USD with Tax 1 Total Tons in USD with Tax 1 + Tax 1
The calculations for these fields ar as follows:
PriceInUSD = Price of invoice in Local currency
Exchange RateTotal Tons in USD = PriceInSUD
Total TonsTax 1 = Complex calculation where I need to check the category of the produc and then get it's value from e table for the specific Date. If there is not a value in that table, ia need to go through a list of 5 defaults alternatives (Different regions/dates in the same table) in an specific order until I get one and if I can't still find a value, i need to report the invoice as an error to the client.
Tax 2 = same as tax 1 but with another Table.
Exchange Rate = Same as taxes
My first try with this query was something like this:
Aliases:
Price in Local Currency = PriceInLC Total Tons in USD + Tax 1 = TotalTonsinUSDTax1 Total Tons in USD + Tax 1 + Tax 2 = TotalTonsinUSDTax1Tax2
Select *,
(PriceInLC * ExchangeRate) as ProceInUSD,
(PriceInLC * ExchangeRate) * TotalTons as TotalTonsInUSD,
(PriceInLC * ExchangeRate) * TotalTons * 0.95 as TotalTonsinUSDTax1, -- At first, there were not calculations only plain numbers
(PriceInLC * ExchangeRate) * TotalTons * 0.95 * 0.21 as TotalTonsinUSDTax1Tax2, -- same with Tax 2, was only a number at first
From TableA
Left join TableB
Left Join TableC
I undestood that copying the same calculations multiple times was not a good Idea, but as they were almost too simple, there was no much point on using subselcts. In case of a future change, it was quite simple to understand and to change all the lines at once.
But then, everyhing started to go to hell when we’ve discovered all the exceptiones to the rules.
The Exchange Rate changed by category and month (yeah, Third world country problems…), So, I end up doing something like this:
declare @OrderRegion Table (
OrderID Integer,
Zona NVarchar(50)
)
Insert Into @OrderRegion Select * From ( --I need to do this becouse I need to take an specific value as a default in an specific order depending on the region. Those "Region#" are given an specific order depending on the importance they have as a default. In this example, they have numbers, but in the real code are the names of the real regions.
SELECT 1 as OrderID, 'Region1' as Region UNION ALL
SELECT 2 as OrderID, 'Region2' as Region UNION ALL
SELECT 3 as OrderID, 'Region3' as Region UNION ALL
SELECT 4 as OrderID, 'Region4' as Region UNION ALL
SELECT 5 as OrderID, 'Region5' as Region
) as SourceQuery
DECLARE @Category1 [nvarchar] (255)
SELECT @Category1 = (
Select Top 1 Valor
FROM TableB as Category
Inner Join @OrderRegion as MyOrder
on Category.[Region] = MyOrder.region
WHERE Grano = 'Category1'
AND Fechas = CAst(Format(GetDate(), 'MM/yyyy') AS NVARCHAR(25))
AND Fecha_Carga = '2024-04-24' --Cast(Getdate() AS DATE)
Order by CASE WHEN Valor IS NULL Or Ltrim(Rtrim(Valor)) = '-' or Ltrim(Rtrim(Valor)) = '*' THEN 2 ELSE 1 END,
ORderID
)
-- And so on for every Category
And then, in the proper Select I have to do something like this:
TRY_CAST(ISNULL(
CASE WHEN LTRIM(RTRIM(Region.Valor)) = '-' Or LTRIM(RTRIM(Region.Valor)) = '*' --More exceptions... values that might not be numeric and can't be filtered out by ISNUMERIC as '-' returns 1... I know I can clean that up earlier, but that's not the point of the question. I will move this to earlier in the store later
THEN NULL
ELSE Pizarra.Valor
END,
CASE BC.Grano
WHEN 'Region1'
THEN @Region1
WHEN 'Region2'
THEN @Region2
WHEN 'Region3'
THEN @Region3
WHEN 'Region4'
THEN @Region4
WHEN 'Region5'
THEN @Region5
WHEN 'Region6'
THEN @Region6
END) AS DECIMAL(18, 2)) AS ExchangeRate`
As you can see, having this as a part of the calculations is already not so easy to maintain. Even less if I have to copy it many times in a row.
And the same happes with both taxes. So, my next idea whas to do something like this:
(I’ll coy the same code for the taxes with just different aliases for the sake of simplicity, but imagine a similar code but with other tables and conditions for both taxes)
--Exchange Rate
declare @OrderRegion Table (
OrderID Integer,
Zona NVarchar(50)
)
Insert Into @OrderRegion Select * From ( --I need to do this becouse I need to take an specific value as a default in an specific order depending on the region. Those "Region#" are given an specific order depending on the importance they have as a default. In this example, they have numbers, but in the real code are the names of the real regions.
SELECT 1 as OrderID, 'Region1' as Region UNION ALL
SELECT 2 as OrderID, 'Region2' as Region UNION ALL
SELECT 3 as OrderID, 'Region3' as Region UNION ALL
SELECT 4 as OrderID, 'Region4' as Region UNION ALL
SELECT 5 as OrderID, 'Region5' as Region
) as SourceQuery
DECLARE @Category1 [nvarchar] (255)
SELECT @Category1 = (
Select Top 1 Valor
FROM TableB as Category
Inner Join @OrderRegion as MyOrder
on Category.[Region] = MyOrder.Region
WHERE Grano = 'Category1'
AND Fechas = CAst(Format(GetDate(), 'MM/yyyy') AS NVARCHAR(25))
AND Fecha_Carga = '2024-04-24' --Cast(Getdate() AS DATE)
Order by CASE WHEN Valor IS NULL Or Ltrim(Rtrim(Valor)) = '-' or Ltrim(Rtrim(Valor)) = '*' THEN 2 ELSE 1 END,
ORderID
---Tax 1
declare @OrderRegion Table (
OrderID Integer,
Zona NVarchar(50)
)
Insert Into @OrderRegion Select * From ( --I need to do this becouse I need to take an specific value as a default in an specific order depending on the region. Those "Region#" are given an specific order depending on the importance they have as a default. In this example, they have numbers, but in the real code are the names of the real regions.
SELECT 1 as OrderID, 'Region1' as Region UNION ALL
SELECT 2 as OrderID, 'Region2' as Region UNION ALL
SELECT 3 as OrderID, 'Region3' as Region UNION ALL
SELECT 4 as OrderID, 'Region4' as Region UNION ALL
SELECT 5 as OrderID, 'Region5' as Region
) as SourceQuery
DECLARE @Tax1Category1 [nvarchar] (255)
SELECT @Tax1Category1 = (
Select Top 1 Valor
FROM TableTax1 as Category
Inner Join @OrderRegion as MyOrder
on Category.[Region] = MyOrder.region
WHERE Grano = 'Category1'
AND Fechas = CAst(Format(GetDate(), 'MM/yyyy') AS NVARCHAR(25))
AND Fecha_Carga = '2024-04-24' --Cast(Getdate() AS DATE)
Order by CASE WHEN Valor IS NULL Or Ltrim(Rtrim(Valor)) = '-' or Ltrim(Rtrim(Valor)) = '*' THEN 2 ELSE 1 END,
ORderID
--- Tax 2
declare @OrderRegion Table (
OrderID Integer,
Zona NVarchar(50)
)
Insert Into @OrderRegion Select * From ( --I need to do this becouse I need to take an specific value as a default in an specific order depending on the region. Those "Region#" are given an specific order depending on the importance they have as a default. In this example, they have numbers, but in the real code are the names of the real regions.
SELECT 1 as OrderID, 'Region1' as Region UNION ALL
SELECT 2 as OrderID, 'Region2' as Region UNION ALL
SELECT 3 as OrderID, 'Region3' as Region UNION ALL
SELECT 4 as OrderID, 'Region4' as Region UNION ALL
SELECT 5 as OrderID, 'Region5' as Region
) as SourceQuery
DECLARE @Tax2Category1 [nvarchar] (255)
SELECT @Tax2Category1 = (
Select Top 1 Valor
FROM TableTax2 as Category
Inner Join @OrderRegion as MyOrder
on Category.[Region] = MyOrder.region
WHERE Grano = 'Category1'
AND Fechas = CAst(Format(GetDate(), 'MM/yyyy') AS NVARCHAR(25))
AND Fecha_Carga = '2024-04-24' --Cast(Getdate() AS DATE)
Order by CASE WHEN Valor IS NULL Or Ltrim(Rtrim(Valor)) = '-' or Ltrim(Rtrim(Valor)) = '*' THEN 2 ELSE 1 END,
ORderID
Select
*,
(PriceInLC * ExchangeRate) as ProceInUSD,
(PriceInLC * ExchangeRate) * TotalTons as TotalTonsInUSD,
(PriceInLC * ExchangeRate) * TotalTons * Tax1 as TotalTonsinUSDTax1,
(PriceInLC * ExchangeRate) * TotalTons * Tax1 * Tax2 as TotalTonsinUSDTax1Tax2,
From (
Select * ,
,TRY_CAST(ISNULL(
CASE WHEN LTRIM(RTRIM(Region.Valor)) = '-' Or LTRIM(RTRIM(Region.Valor)) = '*' --More exceptions... values that might not be numeric and can't be filtered out by ISNUMERIC as '-' returns 1... I know I can clean that up earlier, but that's not the point of the question. I will move this to earlier in the store later
THEN NULL
ELSE Pizarra.Valor
END,
CASE BC.Grano
WHEN 'Region1'
THEN @Region1
WHEN 'Region2'
THEN @Region2
WHEN 'Region3'
THEN @Region3
WHEN 'Region4'
THEN @Region4
WHEN 'Region5'
THEN @Region5
WHEN 'Region6'
THEN @Region6
END) AS DECIMAL(18, 2)) AS ExchangeRate,
,TRY_CAST(ISNULL(
CASE WHEN LTRIM(RTRIM(Region.Valor)) = '-' Or LTRIM(RTRIM(Region.Valor)) = '*' --More exceptions... values that might not be numeric and can't be filtered out by ISNUMERIC as '-' returns 1... I know I can clean that up earlier, but that's not the point of the question. I will move this to earlier in the store later
THEN NULL
ELSE Pizarra.Valor
END,
CASE BC.Grano
WHEN 'Region1'
THEN @Region1
WHEN 'Region2'
THEN @Region2
WHEN 'Region3'
THEN @Region3
WHEN 'Region4'
THEN @Region4
WHEN 'Region5'
THEN @Region5
WHEN 'Region6'
THEN @Region6
END) AS DECIMAL(18, 2)) AS Tax1,
,TRY_CAST(ISNULL(
CASE WHEN LTRIM(RTRIM(Region.Valor)) = '-' Or LTRIM(RTRIM(Region.Valor)) = '*' --More exceptions... values that might not be numeric and can't be filtered out by ISNUMERIC as '-' returns 1... I know I can clean that up earlier, but that's not the point of the question. I will move this to earlier in the store later
THEN NULL
ELSE Pizarra.Valor
END,
CASE BC.Grano
WHEN 'Region1'
THEN @Region1
WHEN 'Region2'
THEN @Region2
WHEN 'Region3'
THEN @Region3
WHEN 'Region4'
THEN @Region4
WHEN 'Region5'
THEN @Region5
WHEN 'Region6'
THEN @Region6
END) AS DECIMAL(18, 2)) AS Tax2
From TableA
left Join table B
Left join etcetera....
)
As you can see, this code started to get out of hands. And this is just one select (Not exactly, but you can get what I mean). And that’s not all. After this, the client, told me about more exceptions. In these, I need to check for an specific dimmension in order to use one of these calculations or to get another value from another table with it’s new calculations. It ends up something like:
This:
(PriceInLC * ExchangeRate) as ProceInUSD,
Becoming This:
(PriceInLC * Case When Dimension1 = 'Option1' then Exchangerate Else New value from another table with the same complexity End) as ProceInUSD,
I won’t copie the same code again but I think you might get an Idea of what I’m dealing with (And I’m afraid this might not be the last exception I might have to deal with, adding more complexity to the query)
So the real question I’m asking in this post is where do i drew the line between Stacking nested queries and to start creating temporary tables and spliting the code up in smaller chunks. I’m afraid that I might end up with a shit ton of temprari tables just for one calculation each, but all of them using the exact same tables. What’s your criteria for this kind of situations and how do you deal with them?
PS: Please, don’t ask me about the source tables for the rates, I have no control of how the information gets made, just what I can do with it. Everything comes from excel files created by the third partie of a third party of a third party. And nobody in the chain wan’ts to take responsability for them.
Nicolas Lopez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.