Using CASE on a Pivot?

  • I got a PIVOT thats giving me some trouble. Is meant to retrieve yearly expenses per coucillors.

    Problem is that the TOTALS are in fact divided by a field that stores either 'Local' or 'Overseas' to differentiate the nature of the expense.

    Additionally theres a large majority of NULL contained in this field. So basically 3 alternatives. I tried using CASE on the main SELECT but it splits the Councillors into 3 (one per expense type) so indeed its able to extract all amounts but its still taking one extra SELECT to group everything and display as needed.

    Im not very sure if I should go with one more SELECT and group everything or try to somehow get the values from the PIVOT query?

    SELECT

    UPPER(tc.FName + ' ' + tc.LName) AS Coun,

    ISNULL(df.Ferry,0) AS Ferr,

    ISNULL(df.Accommodation,0) AS Acco,

    ISNULL(df.Flights,0) AS Flig,

    ISNULL(df.Internet,0) AS Inte,

    ISNULL(df.Mileage,0) AS Mile,

    ISNULL(df.Other,0) AS Othe,

    ISNULL(df.Telephone,0) AS Tele,

    ISNULL(df.Meals,0) AS Meal,

    ISNULL(df.Train,0) AS Trai,

    ISNULL(df.Taxi,0) AS Taxi,

    ISNULL(df.Laundry,0) AS Laun,

    ISNULL(df.Ferry,0)+ISNULL(df.Accommodation,0)+ISNULL(df.Flights,0)+ISNULL(df.Internet,0)+ISNULL(df.Mileage,0)+ISNULL(df.Other,0)+ISNULL(df.Telephone,0)+ISNULL(df.Meals,0)+ISNULL(df.Train,0)+ISNULL(df.Taxi,0)+ISNULL(df.Laundry,0) AS TOTALS

    --,Tried using CASE in here but it would create extra records, triplicating coucillors for 'Local', 'Overseas' and NULL values for LocalOrOverseas field (commented down there)

    FROM

    (

    SELECT

    IDNo,

    ,Expense

    ,ExpenseType

    --,LocalOrOverseas

    FROM

    TblExpense

    WHERE

    ExpenseYear = 2016

    ) ps

    PIVOT

    (

    SUM (Expense) FOR ExpenseType IN

    (

    [Accommodation],

    [Flights],

    [Internet],

    [Mileage],

    [Monthly Allowance],

    [Other],

    [Telephone],

    [Meals],

    [Train],

    [Taxi],

    [Laundry],

    [Ferry]

    )

    ) df

    INNER JOIN

    dbo.TblCouncillor tc

    ON tc.IDNo = df.IDNo

    The way the result looks without trying to split the results:

    CounFerrAccoFligInteMileOtheTeleMealTraiTaxiLaunTOTALS

    MARK0.00858.373447.000.000.000.000.000.000.00150.270.004455.64

    JUNE0.000.001562.0017.000.000.0011.440.000.000.000.001590.44

    LOIS0.000.000.0033.000.000.008.020.000.000.000.0041.02

    NICK0.0050.000.0033.000.000.00105.000.000.0081.000.00269.00

    PHIL0.000.00192.5033.000.000.000.000.000.000.000.00225.50

    And this is what it looks using CASE, only used PHIL for this example but its splits all of them the same way:

    CounFerrAccoFligInteMileOtheTeleMealTraiTaxiLaunTOTALSLOCALOVERSNULL

    PHIL0.000.00100.5000.000.000.000.000.000.000.000.00100.50100.5000.000.0

    PHIL0.000.0092.5000.000.000.000.000.000.000.000.0092.500.0092.5000.0

    PHIL0.000.0000.033.000.000.000.000.000.000.000.0033.000.0000.033.0

    Any help its appretiated. Regards.


    My stack: VS2015 ~ C# ~ MSSQL 2014[/url] ~ DevCraft ~ ReSharper ~ DataGrip ~ Linqer[/url]

  • select UPPER(tc.FName + ' ' + tc.LName) AS Coun,

    SUM(CASE WHEN Expensetype = [Accommodation] THEN ISNULL(Expense,0) ELSE 0 END) Accommodation,

    SUM(CASE WHEN Expensetype = [Flights] THEN ISNULL(Expense,0) ELSE 0 END) Flights,

    SUM(Expense) as Totals

    FROM TblExpense

    WHERE ExpenseYear = 2016

    GROUP BY UPPER(tc.FName + ' ' + tc.LName)

    Not sure this will even work.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply