Dynamically select the columns to update based on condition

Hi,

I have table - Ctry_Sales

columns
Country
year
Jansales
Febsales
Marsales
Aprsales --------DecSales

For Current year, when I do the update on sales amount, it should do only for current and future months. Past months should not be changed.

There is tmp_sales, table where calculation is done, and will like below for March 2019

Country----year----JanSales----FebSales-----MarSales---AprSales----MaySales-------DecSales
ABC--------2019------0----------------0--------------5000----------10000--------2000--------------1000
XYZ---------2019------0-------------25000----------10000-------2500--------------------5000
PQR--------2019------0-----------------0--------------1000---------0-------------------------1000

I don't want the zero to be updated to past month, because table Ctry_Sales have the past month data updated by month end process. So it should not be changed during weekly update of current and future months.

I can't change the table structure.

Please any suggestion, will be of great help.

Thanks in advance.

Not really sure what you are aiming for i.e. the data used for the update but:
It's a really bad design for a table. Given that you can't change it (presumably the structure does change as the months progress) it would be easier to reformat the data in your process then update the tab;e at the end with conditions based round the columns (the process will need to be changed as the table changes but only the formatting parts not the calculations). Reformat to Country, StartOfMonthDate, SalesValue then you will probably find the updates easy and the update back to the table format won't be difficult. You also might find you can maintain this structure and use a view to reformat for presentation.

You can do the update by a series of conditional statements
update tbl
set Jan = case when getdate() < '20190101' then updateJanVal else Jan end
Feb = ...
from updatevaluestable

1 Like