March 8, 2016 at 4:54 am
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:
Coun Ferr Acco Flig Inte Mile Othe Tele Meal Trai Taxi Laun TOTALS
MARK 0.00 858.37 3447.00 0.00 0.00 0.00 0.00 0.00 0.00 150.27 0.00 4455.64
JUNE 0.00 0.00 1562.00 17.00 0.00 0.00 11.44 0.00 0.00 0.00 0.00 1590.44
LOIS 0.00 0.00 0.00 33.00 0.00 0.00 8.02 0.00 0.00 0.00 0.00 41.02
NICK 0.00 50.00 0.00 33.00 0.00 0.00 105.00 0.00 0.00 81.00 0.00 269.00
PHIL 0.00 0.00 192.50 33.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 225.50
And this is what it looks using CASE, only used PHIL for this example but its splits all of them the same way:
Coun Ferr Acco Flig Inte Mile Othe Tele Meal Trai Taxi Laun TOTALS LOCAL OVERS NULL
PHIL 0.00 0.00 100.50 00.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.50 100.50 00.0 00.0
PHIL 0.00 0.00 92.50 00.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 92.50 0.00 92.50 00.0
PHIL 0.00 0.00 00.0 33.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 33.00 0.00 00.0 33.0
Any help its appretiated. Regards.
March 8, 2016 at 6:55 am
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