Post Reopened by Paul White♦

occurred Jan 27, 2016 at 15:12

edited tags

Link

edited Jan 27, 2016 at 15:11

Paul White ♦

90.3k
30
424
663

How to Insert/update table by calling function in it? Calculating multiple medians

edited body

Source Link

edited Jan 27, 2016 at 15:03

Kaishu

517
2
12
19

I have defined function with table value parameter which returns median as per values passed to it. Function defined as:

    CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY)
    RETURNS INT
    AS
    BEGIN
    <function body>
    END

And Table Type TypeMedian definition as:

CREATE TYPE [dbo].[TypeMedian] AS TABLE(
    [VALUE] [int] NULL
)

Now I have a table Listing with filled values in it as and a table RESULT to be filled according to table Listing:

Tables structure as

LISTING(ListingCol1,ListingCol2,ListingCol3,ListingCol4,ListingCol5)
RESULT(Col1,Col2,Col3,Col4,Col5)

Listing table has more that 1000 rows of data.

All column from both tables are of type int. Now I want to fill columns of RESULT table and that column could be calculate as:

Col1 = SUM(ListingCol1)
Col2 = SUM(ListingCol2)
Col3 = dbo.fn_GetMedian(ListingCol3)
Col4 = dbo.fn_GetMedian(ListingCol5ListingCol4)
Col5 = dbo.fn_GetMedain(ListingCol5)

And I'm doing so as:

INSERT INTO RESULT(Col1)
SELECT SUM(ListingCol1)

Update RESULT
SET Col2 = SUM(ListingCol2)

DECLARE @tbl_Median TypeMedian
    
INSERT INTO @tbl_Median
SELECT ListingCol3
FROM Listing
        
UPDATE RESULT
SET Col3 = dbo.fn_GetMedian(@tbl_Median)
    
-- For next column
DELETE FROM @tbl_Median
    
INSERT INTO @tbl_Median
SELECT ListingCol4
FROM Listing
    
UPDATE RESULT
SET Col4 = dbo.fn_GetMedian(@tbl_Median);

--And this update query I repeating for remaining columns. How could I do that in single query?

I have defined function with table value parameter which returns median as per values passed to it. Function defined as:

    CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY)
    RETURNS INT
    AS
    BEGIN
    <function body>
    END

And Table Type TypeMedian definition as:

CREATE TYPE [dbo].[TypeMedian] AS TABLE(
    [VALUE] [int] NULL
)

Now I have a table Listing with filled values in it as and a table RESULT to be filled according to table Listing:

Tables structure as

LISTING(ListingCol1,ListingCol2,ListingCol3,ListingCol4,ListingCol5)
RESULT(Col1,Col2,Col3,Col4,Col5)

All column from both tables are of type int. Now I want to fill columns of RESULT table and that column could be calculate as:

Col1 = SUM(ListingCol1)
Col2 = SUM(ListingCol2)
Col3 = dbo.fn_GetMedian(ListingCol3)
Col4 = dbo.fn_GetMedian(ListingCol5)
Col5 = dbo.fn_GetMedain(ListingCol5)

And I'm doing so as:

INSERT INTO RESULT(Col1)
SELECT SUM(ListingCol1)

Update RESULT
SET Col2 = SUM(ListingCol2)

DECLARE @tbl_Median TypeMedian
    
INSERT INTO @tbl_Median
SELECT ListingCol3
FROM Listing
        
UPDATE RESULT
SET Col3 = dbo.fn_GetMedian(@tbl_Median)
    
-- For next column
DELETE FROM @tbl_Median
    
INSERT INTO @tbl_Median
SELECT ListingCol4
FROM Listing
    
UPDATE RESULT
SET Col4 = dbo.fn_GetMedian(@tbl_Median);

--And this update query I repeating for remaining columns. How could I do that in single query?

I have defined function with table value parameter which returns median as per values passed to it. Function defined as:

    CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY)
    RETURNS INT
    AS
    BEGIN
    <function body>
    END

And Table Type TypeMedian definition as:

CREATE TYPE [dbo].[TypeMedian] AS TABLE(
    [VALUE] [int] NULL
)

Now I have a table Listing with filled values in it as and a table RESULT to be filled according to table Listing:

Tables structure as

LISTING(ListingCol1,ListingCol2,ListingCol3,ListingCol4,ListingCol5)
RESULT(Col1,Col2,Col3,Col4,Col5)

Listing table has more that 1000 rows of data.

All column from both tables are of type int. Now I want to fill columns of RESULT table and that column could be calculate as:

Col1 = SUM(ListingCol1)
Col2 = SUM(ListingCol2)
Col3 = dbo.fn_GetMedian(ListingCol3)
Col4 = dbo.fn_GetMedian(ListingCol4)
Col5 = dbo.fn_GetMedain(ListingCol5)

And I'm doing so as:

INSERT INTO RESULT(Col1)
SELECT SUM(ListingCol1)

Update RESULT
SET Col2 = SUM(ListingCol2)

DECLARE @tbl_Median TypeMedian
    
INSERT INTO @tbl_Median
SELECT ListingCol3
FROM Listing
        
UPDATE RESULT
SET Col3 = dbo.fn_GetMedian(@tbl_Median)
    
-- For next column
DELETE FROM @tbl_Median
    
INSERT INTO @tbl_Median
SELECT ListingCol4
FROM Listing
    
UPDATE RESULT
SET Col4 = dbo.fn_GetMedian(@tbl_Median);

--And this update query I repeating for remaining columns. How could I do that in single query?

deleted 361 characters in body

Source Link

edited Jan 27, 2016 at 14:35

Kaishu

517
2
12
19

    CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY)
    RETURNS INT
    AS
    BEGIN
    <function body>
    END

Now I have a table with certain columnListing with filled values in it as and wants to filla table with values.

All columnsRESULT ofto be filled according to table would filled by calculating SUMListing and :

MEDIANTables structure ofas

LISTING(ListingCol1,ListingCol2,ListingCol3,ListingCol4,ListingCol5)
RESULT(Col1,Col2,Col3,Col4,Col5)

All columnscolumn from anotherboth tabletables are of type int.

So when Now I goingwant to fill tablecolumns I'm doing followingof operationRESULT table for eachand that column could be calculate as:

    DECLARE @tbl_Median TypeMedian

    INSERT INTO @tbl_Median
    SELECT L.Col1
    FROM @tbl_Listing L;
   = SUM(ListingCol1)
   Col2 UPDATE= @tbl_ResultSUM(ListingCol2)
    SET Col1Col3 = dbo.fn_GetMedian(@tbl_MedianListingCol3); --which returns `MEDIAN`

    DELETE FROM @tbl_Median
    INSERT INTO @tbl_Median
    SELECT L.Col2
    FROM @tbl_Listing L;

   UPDATE @tbl_Result
   SET Col2Col4 = dbo.fn_GetMedian(@tbl_MedianListingCol5)
   .
   .
 Col5 = dbo.fn_GetMedain(ListingCol5)

Like aboveAnd I'm doing thisso as for all column. How to do that in a single query instead of repeating update query for each column?

Eg.:

CREATEINSERT TABLEINTO LISTINGRESULT(
ListingCol1 INT,
ListingCol2 INT,
ListingCol3 INT,
ListingCol4 INT,Col1)
ListingCol5SELECT INTSUM(ListingCol1)

CREATE TABLEUpdate RESULT(
Col1SET INT,
Col2 INT,
Col3 INT,
Col4 INT,
Col5= INT
SUM(ListingCol2)

DECLARE @tbl_Median TypeMedian
 
-- For first column insert

INSERT INTO @tbl_Median
SELECT ListingCol1ListingCol3
FROM Listing
    
INSERT INTO   
UPDATE RESULT(Col1)
Values(SET Col3 = dbo.fn_GetMedian(@tbl_Median))
    
-- For secondnext column
DELETE FROM @tbl_Median
    
INSERT INTO @tbl_Median
SELECT ListingCol2ListingCol4
FROM Listing
    
UPDATE RESULT
SET Col2Col4 = dbo.fn_GetMedian(@tbl_Median); --And this update I repeating for remaining columns

--And this update query I repeating for remaining columns. How could I do that in single query?

CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY)
RETURNS INT
AS
BEGIN
<function body>
END

I have a table with certain column and wants to fill table with values.

All columns of table would filled by calculating SUM and MEDIAN of columns from another table.

So when I going to fill table I'm doing following operation for each column:

    DECLARE @tbl_Median TypeMedian

    INSERT INTO @tbl_Median
    SELECT L.Col1
    FROM @tbl_Listing L;
    
    UPDATE @tbl_Result
    SET Col1 = dbo.fn_GetMedian(@tbl_Median); --which returns `MEDIAN`

    DELETE FROM @tbl_Median
    INSERT INTO @tbl_Median
    SELECT L.Col2
    FROM @tbl_Listing L;

   UPDATE @tbl_Result
   SET Col2 = dbo.fn_GetMedian(@tbl_Median)
   .
   .
   .

Like above I'm doing this as for all column. How to do that in a single query instead of repeating update query for each column?

Eg.

CREATE TABLE LISTING(
ListingCol1 INT,
ListingCol2 INT,
ListingCol3 INT,
ListingCol4 INT,
ListingCol5 INT)

CREATE TABLE RESULT(
Col1 INT,
Col2 INT,
Col3 INT,
Col4 INT,
Col5 INT
)

DECLARE @tbl_Median TypeMedian
 
-- For first column insert

INSERT INTO @tbl_Median
SELECT ListingCol1
FROM Listing
    
INSERT INTO RESULT(Col1)
Values(dbo.fn_GetMedian(@tbl_Median))

-- For second column
DELETE FROM @tbl_Median

INSERT INTO @tbl_Median
SELECT ListingCol2
FROM Listing

UPDATE RESULT
SET Col2 = dbo.fn_GetMedian(@tbl_Median); --And this update I repeating for remaining columns

    CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY)
    RETURNS INT
    AS
    BEGIN
    <function body>
    END

Now I have a table Listing with filled values in it as and a table RESULT to be filled according to table Listing:

Tables structure as

LISTING(ListingCol1,ListingCol2,ListingCol3,ListingCol4,ListingCol5)
RESULT(Col1,Col2,Col3,Col4,Col5)

All column from both tables are of type int. Now I want to fill columns of RESULT table and that column could be calculate as:

Col1 = SUM(ListingCol1)
Col2 = SUM(ListingCol2)
Col3 = dbo.fn_GetMedian(ListingCol3)
Col4 = dbo.fn_GetMedian(ListingCol5)
Col5 = dbo.fn_GetMedain(ListingCol5)

And I'm doing so as:

INSERT INTO RESULT(Col1)
SELECT SUM(ListingCol1)

Update RESULT
SET Col2 = SUM(ListingCol2)

DECLARE @tbl_Median TypeMedian
    
INSERT INTO @tbl_Median
SELECT ListingCol3
FROM Listing
        
UPDATE RESULT
SET Col3 = dbo.fn_GetMedian(@tbl_Median)
    
-- For next column
DELETE FROM @tbl_Median
    
INSERT INTO @tbl_Median
SELECT ListingCol4
FROM Listing
    
UPDATE RESULT
SET Col4 = dbo.fn_GetMedian(@tbl_Median);

--And this update query I repeating for remaining columns. How could I do that in single query?

Post Closed as "Needs details or clarity" by Paul White♦

occurred Jan 27, 2016 at 14:19

added 760 characters in body

Source Link

edited Jan 27, 2016 at 13:55

Kaishu

517
2
12
19

Loading

Source Link

asked Jan 27, 2016 at 13:44

Kaishu

517
2
12
19

Loading

Stack Exchange Network

Return to Question

How to Insert/update table by calling function in it? Calculating multiple medians