After inserting multiple rows in the temporary table, a new row will be inserted at the end of the t-CodePudding

Can anyone help me how to code this on SQL: I have this temporary table:

TableX:

ID  Code1  Code2  Sold    Money1      Money2   ISCode
 1   GB     GB     10    100.000000              1
 1   2GB    2GB     5    150.000000              1
 1   AB     NULL    1    150.000000              0
 1   CB     BK      1    150.000000              0
 1   NULL   DE     NULL  150.000000              1

I need to compute each row for Money2 Column. The logic is this:

check the rows ISCode= 1
compute the Money2 for each row by this formula: Money1/Sold

I then need to Insert this new row at the end of the row in the temporary table: TableX

    ID  Code1  Code2    Sold   Money1        Money2     ISCode
     1  total   total    15   400.000000    16.670000    1

The logic of

Sold for the new column is Add all the sold in the rows where ISCode = 1
Money1 for the new column is Add all the money1 in the rows where ISCode = 1

Money2 for the new column is Money1/sold. but there is a row that has null values in the sold column so it will be excluded in the computation of Money2.

   sold = 10   5 = 15
   money1 = 310.000000
   money2 = 400.00000/15   -- Row #3 will be excluded in the computation of money2

ID  Code1  Code2    Sold    Money1      Money2     ISCode
  1   GB     GB     10    100.000000   10.000000    1
  1   2GB    2GB     5    150.000000   30.000000    1
  1   NULL   DE     NULL  150.000000    NULL        1

  1   total  total  15    400.000000   16.670000    1

Is this possible using temporary tables or it is much better to use Table value parameter table for this scenario above?

SAMPLE DML AND DDL

CREATE TABLE #tableX(
    [ID] [int] NULL,
    [Code1] [varchar](4) NULL,
    [Code2] [varchar](6) NULL,
    [Sold]  [int] NULL,
    [Money1] [money] NULL,
    [Money2] [money] NULL 
 )
 
 INSERT INTO #tableX(
    [ID],
    [Code1],
    [Code2],
    [Sold],
    [Money1],
    [Money2]
 )VALUES(
   1,
   'GB',
   'GB',
   10,
   100.000000,
   NULL
 ),
 (
   1,
   '2GB',
   '2GB',
   5,
   150.000000,
   NULL
 ),
 (
   1,
   'AB',
   NULL,
   0
   150.000000,
   NULL
 ),
 (
   1,
   'CB',
   'BK',
   1
   150.000000,
   NULL
 ),
 (
   1,
   NULL,
   'DE',
   NULL
   150.000000,
   NULL
 )

CodePudding user response：

Ok, following some comments before, check if this is what you meant.

You have this table (that is a temp table)

DECLARE @tableX AS TABLE(
    [ID] [int] NULL,
    [Code1] [varchar](4) NULL,
    [Code2] [varchar](6) NULL,
    [Sold]  [int] NULL,
    [Money1] [money] NULL,
    [Money2] [money] NULL,
    [IsCode] [bit] NULL
 )

Notice that i added the "IsCode" column - at the head of your question it existed, but in the create table didn't - so i assume is there.

The insert statements you do could be changed to this:

INSERT INTO @tableX([ID],[Code1],[Code2],[Sold],[Money1],[Money2],[IsCode])
SELECT
  pack1.ID,
  pack1.Code1,
  pack1.Code2,
  pack1.Sold,
  pack1.Money1,
  (CASE WHEN pack1.IsCode = 1 THEN pack1.Money1/pack1.Sold ELSE NULL END),
  pack1.IsCode
FROM
  (SELECT 1 AS [ID],'GB' AS [Code1],'GB' AS [Code2],10 AS [Sold],100.000000 AS [Money1],1 AS [IsCode]
   UNION ALL
   SELECT 1,'2GB','2GB',5,150.000000,1
   UNION ALL
   SELECT 1,'AB',NULL,0,150.000000,0
   UNION ALL
   SELECT 1,'CB','BK',1,150.000000,0
   UNION ALL
   SELECT 1,NULL,'DE',NULL,150.000000,1) AS pack1

SELECT * FROM @tableX WHERE IsCode=1
UNION ALL
SELECT 1,'total','total',SUM(Sold),SUM(Money1),(SUM(Money1)/SUM(Sold)),1 FROM @tableX WHERE IsCode=1

Alternativaly, you could place the inserts as you did, and after then run a UPDATE just on the Money2 column. Then proceed with the final select that calls all the data and a "total" line at the end.

CodePudding user response：

1- In order to fill the Money2 column with values:

update  #tableX set Money2 = 
case isnull(Sold,0) when 0 then Null else CONVERT(DECIMAL(10,2),Money1/Sold) end where iscode = 1

Note that I have rounded the ratio Money1/Sold to the second decimal.

2- In order to insert the summary row in the table, there are many scenarios that I would take into consideration.

a- You want to compute a summary per ID (in your case on ID = 1 only exists, but in case you have many IDs)

insert into #tableX 
select ID , 'ttl','ttl',sum(Sold) Sold,Sum(isnull(Money1,0)) Money1 ,
case isnull(Sum(Sold),0) when 0 then Null else CONVERT(DECIMAL(10,2),Sum(Money1)/Sum(Sold)) end  Money2,0 as iscode
from #tableX
where iscode = 1 and isnull(sold,0) <> 0
Group by ID

In this case, you will have multiple summary lines for each ID.

b- ID is irrelevant, you only want one line for the entire dataset

 insert into #tableX 
 select 99999999 , 'ttl','ttl',sum(Sold) Sold,Sum(isnull(Money1,0)) Money1 ,
 case isnull(Sum(Sold),0) when 0 then Null else CONVERT(DECIMAL(10,2),Sum(Money1)/Sum(Sold)) end  Money2,0 as iscode
 from #tableX
 where iscode = 1 and isnull(sold,0) <> 0