Consoldate columns in a SELECT

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,FE
E_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,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_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,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL
)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_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,FE
E_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',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_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,FE
E_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,FE
E_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,ABConsolidat
ion,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 ORDERSAs 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|||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.
"Terri" <terri@.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
>
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> 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,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(2,'Buy','20060117',NULL,NULL,'D',NULL,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL[col
or=darkred]
> )
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(3,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'B',NULL,NULL,NULL,NULL,NULL,1[col
or=darkred]
> 5)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> 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',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(5,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'D',NULL,NULL,NULL,NULL,NULL,5[col
or=darkred]
> 4)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> 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
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES[/color]
(7,'Buy','20060117','A','C',NULL,NULL,'D
','E',3,99,NULL,NULL,14,NULL)red">
>
> Expect Results:
> OrderID,OrderType,OrderDate,ABConsolidat
ion,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
>
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OPVJGPUHGHA.2668@.tk2msftngp13.phx.gbl...
> 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.
Fee type A can be in any of the 6 fee fields so I don't think that will
work. I'm looking at Anith's solution to see if that will work.
thanks|||>
> Fee type A can be in any of the 6 fee fields so I don't think that will
> work. I'm looking at Anith's solution to see if that will work.
>
Ahhh... I misunderstood that in your first post. The problem is much more
interesting than I thought.
I like how Anith is getting 6 fields from one row to 6. I'll have to
remember this join technique in case I ever run into a similar situation.
If your final code differs from his, please let us know what you come up
with.