mardi 28 juin 2016

Using Case When to Classify Customers in Teradata

I am researching customers and their shopping habits using SQL in Teradata.

Say I have the following information:

Table wklytrans
CustNm  GroceryStore  # WklyTrxns  Sum WklyTrxns  CustType
Jack    Publix        2            $70            Publix
Ruby    Kroger        1            $15            Both
Moe     Kroger        4            $120           Kroger
Ruby    Publix        1            $20            Both

I would like to create a new table that classifies them as follows:

CustNm  CustType  # PubTrxns  Sum PubTrxns  # KroTrxns  Sum KroTrxns
Jack    Publix    2           $70           NULL        NULL
Ruby    Both      1           $20           1           $15
Moe     Kroger    NULL        NULL          4           $120

The code I'm currently trying to run is as follows:

CREATE MULTISET VOLATILE TABLE custclass AS(
SEL
CustNm,
CustType,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Publix' THEN # WklyTrxns
WHEN CustType IN 'Publix' THEN # WklyTrxns
END AS # PubTrxns,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Publix' THEN Sum WklyTrxns
WHEN CustType IN 'Publix' THEN Sum WklyTrxns
END AS Sum PubTrxns,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Kroger' THEN # WklyTrxns
WHEN CustType IN 'Kroger' THEN # WklyTrxns
END AS # KroTrxns,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Kroger' THEN Sum WklyTrxns
WHEN CustType IN 'Kroger' THEN Sum WklyTrxns
END AS Sum KroTrxns
FROM wklytrans
)
WITH DATA                                      
PRIMARY INDEX (CustNm)                          
ON COMMIT PRESERVE ROWS;

This is what is currently being outputted:

CustNm  CustType  # PubTrxns  Sum PubTrxns  # KroTrxns  Sum KroTrxns
Jack    Publix    2           $70           NULL        NULL
Ruby    Both      1           $20           NULL        NULL
Ruby    Both      NULL        NULL          1           $15
Moe     Kroger    NULL        NULL          4           $120

How can I get my code to combine Ruby's transactions onto the same line so that it looks like the output I desire?

Any help is appreciated!

Aucun commentaire:

Enregistrer un commentaire