Consoldate columns in a SELECT

19 Jan 2006 9:17 PM

Terri

I have a vendor supplied orders table that tracks the fee types associated
with an order. The allowable fee types are A, B, C, D, and E. These fee
types can be in anyone of the six fee code fields. In the corresponding fee
amount field is the fee amount. The application allows NULL fee amounts even
when there is a corresponding fee code entered. See OrderID 7 where fee code
'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.

For the purposes of my SELECT I want to consolidate and sum all fee types
into 2 master types:

AB_Consolidation-All A and B fee types
CDE_Consolidation--All C, D, and E fee types.

See CREATE TABLE and INSERT statements as well as expected results below.

Thanks to anyone who could help.

CREATE TABLE [dbo].[Orders] (
[OrderID] [smallint],
[OrderType] [char] (4),
[OrderDate] [Datetime],
[FEE_1_CD] [char] (1)NULL,
[FEE_2_CD] [char] (1)NULL,
[FEE_3_CD] [char] (1)NULL,
[FEE_4_CD] [char] (1)NULL,
[FEE_5_CD] [char] (1)NULL,
[FEE_6_CD] [char] (1)NULL,
[FEE_1_AMT] [decimal](18, 2) NULL,
[FEE_2_AMT] [decimal](18, 2) NULL,
[FEE_3_AMT] [decimal](18, 2) NULL,
[FEE_4_AMT] [decimal](18, 2) NULL,
[FEE_5_AMT] [decimal](18, 2) NULL,
[FEE_6_AMT] [decimal](18, 2) NULL
) ON [PRIMARY]

INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FEE_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(1,'Buy','20060117','A','C',NULL,NULL,NULL,NULL,8,9,NULL,NULL,NULL,NULL)

INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FEE_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(2,'Buy','20060117',NULL,NULL,'D',NULL,NULL,NULL,NULL,NULL,11,NULL,NULL,NULL
)

INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FEE_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(3,'Sell','20060117',NULL,NULL,NULL,NULL,NULL,'B',NULL,NULL,NULL,NULL,NULL,1
5)

INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FEE_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(4,'Buy','20060116','A',NULL,NULL,'B',NULL,NULL,18,NUll,NULL,85,NULL,NULL)

INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FEE_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(5,'Sell','20060117',NULL,NULL,NULL,NULL,NULL,'D',NULL,NULL,NULL,NULL,NULL,5
4)

INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FEE_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (6,'Buy','20060116','A','C','D','B',NULL,NULL,8,9,11,12,NULL,NULL)

INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FEE_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (7,'Buy','20060117','A','C',NULL,NULL,'D','E',3,99,NULL,NULL,14,NULL)

Expect Results:

OrderID,OrderType,OrderDate,ABConsolidation,DEConsolidation
1,Buy,20060117,8,9
2,Buy,20060117,0,11
3,Sell,20060117,15,0
4,Buy,20060116,103,0
5,Sell,20060117,0,54
6,Buy,20060116,20,20
7,Buy,20060117,3,113

Partial SELECT statement

SELECT
OrderID,
OrderType,
OrderDate,
Perform calcs here AS AB_Consolidation,
Perform calcs here AS CDE_Consolidation
FROM ORDERS

19 Jan 2006 9:52 PM

Anith Sen

As you might have noticed, the vendor table schema is mediocre. In any case,
you can use a derived table to transpose the data & get the results you
need. If this table is being used in multiple areas, a view might be better.
You can create one as:

ALTER VIEW vw ( order_id, order_type, order_date, seq, code, amount )
AS SELECT OrderID, OrderType, OrderDate, seq,
          CASE seq WHEN 1 THEN FEE_1_CD
                   WHEN 2 THEN FEE_2_CD
                   WHEN 3 THEN FEE_3_CD
                   WHEN 4 THEN FEE_4_CD
                   WHEN 5 THEN FEE_5_CD
                   WHEN 6 THEN FEE_6_CD
          END,
          CASE seq WHEN 1 THEN FEE_1_AMT
                   WHEN 2 THEN FEE_2_AMT
                   WHEN 3 THEN FEE_3_AMT
                   WHEN 4 THEN FEE_4_AMT
                   WHEN 5 THEN FEE_5_AMT
                   WHEN 6 THEN FEE_6_AMT
          END
     FROM Orders, ( SELECT 1 UNION
                    SELECT 2 UNION
                    SELECT 3 UNION
                    SELECT 4 UNION
                    SELECT 5 UNION
                    SELECT 6) S ( seq )
GO

Now you can do the query for consolidation like:

SELECT order_id, order_type, order_date,
       SUM( CASE WHEN code IN ( 'A', 'B' )
                 THEN Amt ELSE 0 END ) AS "AB_",
       SUM( CASE WHEN code IN ( 'C', 'D', 'E' )
                 THEN Amt ELSE 0 END ) AS "CDE_"
FROM vw
GROUP BY order_id, order_type, order_date,
ORDER BY order_id ;

--
Anith

19 Jan 2006 10:00 PM

Jim Underwood

cant you just do this, or am I missing something?

SELECT
OrderID,
OrderType,
OrderDate,
coalesce(FEE_1_AMT,0)+coalesce(FEE_2_AMT,0) AS AB_Consolidation,
coalesce(FEE_3_AMT,0) + coalesce(FEE_4_AMT,0) + coalesce(FEE_5_AMT,0) AS
CDE_Consolidation
FROM ORDERS

Basically, just add the collumns using coaless to handle the nulls.

Show quote

"Terri" <te***@cybernets.com> wrote in message
news:dqovlt$v0d$2@reader2.nmix.net...
> I have a vendor supplied orders table that tracks the fee types associated
> with an order. The allowable fee types are A, B, C, D, and E. These fee
> types can be in anyone of the six fee code fields. In the corresponding
fee
> amount field is the fee amount. The application allows NULL fee amounts
even
> when there is a corresponding fee code entered. See OrderID 7 where fee
code
> 'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.
>
> For the purposes of my SELECT I want to consolidate and sum all fee types
> into 2 master types:
>
> AB_Consolidation-All A and B fee types
> CDE_Consolidation--All C, D, and E fee types.
>
> See CREATE TABLE and INSERT statements as well as expected results below.
>
> Thanks to anyone who could help.
>
>
> CREATE TABLE [dbo].[Orders] (
> [OrderID] [smallint],
> [OrderType] [char] (4),
> [OrderDate] [Datetime],
> [FEE_1_CD] [char] (1)NULL,
> [FEE_2_CD] [char] (1)NULL,
> [FEE_3_CD] [char] (1)NULL,
> [FEE_4_CD] [char] (1)NULL,
> [FEE_5_CD] [char] (1)NULL,
> [FEE_6_CD] [char] (1)NULL,
> [FEE_1_AMT] [decimal](18, 2) NULL,
> [FEE_2_AMT] [decimal](18, 2) NULL,
> [FEE_3_AMT] [decimal](18, 2) NULL,
> [FEE_4_AMT] [decimal](18, 2) NULL,
> [FEE_5_AMT] [decimal](18, 2) NULL,
> [FEE_6_AMT] [decimal](18, 2) NULL
> ) ON [PRIMARY]
>
>
> INSERT INTO ORDERS
>