Solved

Nested case statement

Posted on 2016-08-26
3
36 Views
Last Modified: 2016-08-26
Here is some sample data from a sql server 2012 table named MailHouse

Campaign ID      Offer1Type       Offer1DollarAmount            Offer2Type     Offer2DollarAmount
             1                 FCP                     10                                         Points               20
             2                Points                 40                                         FCP                   30
             3                 FCP                      50
             4                 FCP                      60                                       Food                  30                
The MailHouse table contains info for 2 different offer types per campaign

as you can from the above data,  a campaign may have 1 or 2 different offers and the offers can be in any order

I am trying to write a query that will look at the offertype  for each campaign and give an fcpdollar, pointsdollar, FoodDollar value

Using the above sample data I would like

CampaignID     FCPDollar   PointDollar      FoodDollar
 1                         10               20                      0  
 2                         30               40                      0
 3                         50                0                       0
4                          60                0                       30

I'm assuming the query when involve nested case statements on offer type but not sure
0
Comment
Question by:johnnyg123
  • 2
3 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 41772104
First, I would normalize the two offers into one per row.
select CampaignID, Offer1Type as OfferType, Offer1DollarAmount as OfferDollarAmount from MailHouse
union all
select CampaignID, Offer2Type, Offer2DollarAmount from MailHouse

Open in new window

Then we can work easily on each type:
select CampaignID,
       FCPDollar   = sum(case OfferType when 'FCP' then OfferDollarAmount end),
       PointDollar = sum(case OfferType when 'Points' then OfferDollarAmount end),
       FoodDollar  = sum(case OfferType when 'Food' then OfferDollarAmount end)
from (
  select CampaignID, Offer1Type as OfferType, Offer1DollarAmount as OfferDollarAmount from MailHouse
  union all
  select CampaignID, Offer2Type, Offer2DollarAmount from MailHouse
) data
group by CampaignID

Open in new window

0
 

Author Comment

by:johnnyg123
ID: 41772154
Thanks so much for the post! That absolutely does work

I was anticipating the answer to involve use of case statement so I didn't list all the fields


There are additional text fields that need to come along as well which can't be summed


CampaignID     FCPDollar   PointDollar      FoodDollar
  1                         10               20                      0  
  2                         30               40                      0
  3                         50                0                       0
 4                          60                0                       30




Also, this will be used inside a larger query


would it be possible to do it using nested case statements?
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 41772377
You can always group by those extra columns, but it can get cumbersome if there a lot of them.
You can use the result as a CTE or like I used the union (building a temporary table).

A nested or combined case would not change anything, just remove the union.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now