How to create a dynamic variable to load a field in a table in SQL Server

Question

I'm stuck with a scenario where i have to create a dynamic variable for a select query which hold the data of a particular filed. Here is the scenario

Table 'Customer' has below data

Column0 Column1 Column2     Column3     Column4 Column5
--------------------------------------------------------
H       123X                11/27/2017  C       10.23
D       123X    78462F103   11/28/2017  A       112.35
D       123X    55024U109   11/28/2017  A       25.30
H       456X                11/27/2017  B       5263.44
D       456X    78462F103   11/28/2017  A       23.00
D       456X    55024U109   11/28/2017  A       12123.00
D       456X    78462F103   11/28/2017  A       56.08
D       456X    55024U109   11/28/2017  C       45.07

Now i have to create variable when Column0='H' using the Sum(column5) and load it into Column5 when Column0='D' until the Column0='H' value changes(Table has multiple H values).

Example: I have to calculate the variable when Column0='H' lets say If Column0='H' then Variable=Sum(Column5) which is '5.36'. Now When Coulmn1='D' then Column5's value shlould be loaded as 5.63 when the When the new Column0='H' comes the value of variable changes to '6.33' and it should load the updated value into 'D' records.

Any suggestions on this?

Thank you for posting some sample data. That helps. However, it is not at all clear what you want in this new column. Given your sample what should the output be? — Sean Lange, Nov 30 '17 at 20:43
"Until"? Based on what ordering? Desired results would help clarify. — JNevill, Nov 30 '17 at 20:44
I have to calculate the variable when Column0='H' lets say If Column0='H' then Variable=Sum(Column5) which is '5.36'. Now When Coulmn1='D' then Column5's value shlould be loaded as 5.63 when the When the new Column0='H' comes the value of variable changes to '6.33' and it should load the updated value into 'D' records. — Meet Rohan, Nov 30 '17 at 20:57
Post your expected results. This makes no sense. I can assertain that H means header and D means detail. — KeithL, Nov 30 '17 at 21:17
Make 2 tables one with H data and one with D data. Now you've got a relational database! Now you won't have to do things like "do stuff until the row changes to another H". Because one table only has H and one only has D. You have what appears to be a unique transaction number in column 1 to relate the data together. Then all you have to do is learn to GROUP BY Column1 and SUM. — Jacob H, Nov 30 '17 at 21:21
The values in your explanation don't make any sense at all. Those values are not in the sample data you posted. We can help you buy we can't read your mind or see your screen. Give us some details. Again I will ask...what is the desired output for the sample data you posted? — Sean Lange, Nov 30 '17 at 21:24
@SeanLange he has header & detail transaction data so he wants to sum the detail "d" columns for each transaction (column1) and then update the header total (column5) based on the sum. At least, that's what I've gathered from this mess. — Jacob H, Nov 30 '17 at 21:25
@JacobH OMG if that is true I have no words for how horrible this "design" is. I think you may be right but I am hopeful that you are not correct. — Sean Lange, Nov 30 '17 at 21:30
@JacobH: i'll work on your suggestion. Sorry the requirement is bit complicated to explain :( — Meet Rohan, Nov 30 '17 at 21:34
Can you confirm, with the data you provided, you would expect the first 'H' row column5 to be 137.65 (112.35 + 25.30)? — Jacob H, Nov 30 '17 at 21:35
If you are doing anything other than turning this into two tables you are fighting a losing battle. You desperately need to get this data normalized. — Sean Lange, Nov 30 '17 at 21:44
So for H, 123X you are expecting a value of 147.88 (10.23 + 112.35 + 25.30)? — Sean Lange, Nov 30 '17 at 21:56

score 0 · Answer 1 · answered Nov 30 '17 at 22:19

Assuming that I am correct you want the total of all rows for each Column1 to be the new value in Column5 when Column0 = H you can do something like this.

Notice I had to create sample data in a table before I could do anything.

declare @Something table 
(
    Column0 char(1)
    , Column1 char(4)
    , Column2 varchar(20)
    , Column3 date
    , Column4 char(1)
    , Column5 decimal (9,2)
)

insert @Something values
('H', '123X', '', '11/27/2017', 'C', 10.23)
, ('D', '123X', '78462F103', '11/28/2017', 'A', 112.35)
, ('D', '123X', '55024U109', '11/28/2017', 'A', 25.30)
, ('H', '456X', '', '11/27/2017', 'B', 5263.44)
, ('D', '456X', '78462F103', '11/28/2017', 'A', 23.00)
, ('D', '456X', '55024U109', '11/28/2017', 'A', 12123.00)
, ('D', '456X', '78462F103', '11/28/2017', 'A', 56.08)
, ('D', '456X', '55024U109', '11/28/2017', 'C', 45.07)
;

with SummaryData as
(
    select *
        , NewCol5 = case when Column0 = 'H' then sum(Column5) over(partition by Column1) else Column5 end
    from @Something
)

update SummaryData
set Column5 = NewCol5

select *
from @Something

I would very strongly urge to move away from this design immediately if not sooner. It is fraught with error. And even worse is this looks like sales information. With this design you can't track things like SalesTax, Shipping, discounts, etc....all the kinds of things you want to track about orders.

I was under the impression that the source was a flat file. Hence my solution — KeithL, Dec 01 '17 at 14:18

score 0 · Answer 2 · answered Dec 01 '17 at 14:16

I was under the impression that the source was a flat file and am providing a SSIS solution.

read data -> Conditional Split ("H" and "D")

Dpath
Multicast -> Write out detail records
  -> Aggregate on Col1 --> Merge Join to H path

H path:
Merge Join on Col1 to Aggregated Data

Finally either AddH Col5 to Aggregate or replace col 5 with aggregate (it's not clear in question.)

Write out your header records.

How to create a dynamic variable to load a field in a table in SQL Server

2 Answers2