Need help pulling help

1

I need to do a strange query, (well strange to me anyways) and unsure how to do it.

Data is set up as follows:
I have a form that writes data into the database as follows:

LINE 1 would be fuel Purchase information, fuel card used and total for that line

This is repeated 8 times (Line 1 - Line 8)

What I am trying to do is pull out totals off all 8 lines of each record based on Fuel Card used then give me a grand total.

EXAMPLE:
FUEL_CARD_1 TRK_FUEL_COST_1
E $321.79
FUEL_CARD_2 TRK_FUEL_COST_2
E $297.62
FUEL_CARD_3 TRK_FUEL_COST_3
Q $341.12
FUEL_CARD_4 TRK_FUEL_COST_4
-- $0.00
FUEL_CARD_5 TRK_FUEL_COST_5
-- $0.00
FUEL_CARD_6 TRK_FUEL_COST_6
-- $0.00
FUEL_CARD_7 TRK_FUEL_COST_7
-- $0.00
FUEL_CARD_8 TRK_FUEL_COST_8
-- $0.00

What I'm looking to pull is a sum BY FUEL_CARD

So the result I desired returned would look similar to:

FUEL CARD TOTAL
E $619.41
Q $341.12

Not all 8 lines per record are populated as there may only be 2-3 fuel stops, but there could be a total of 8 stops.

The end result would be to get a report of ALL fuel purchased by a specific card for the entire year.

I attempted to use something similiar to the following but unsure how to assign the 8 FUEL_CARD fields to one entity named FUEL CARD and same thing with getting the totals.

DECLARE @reportYear int = (year(getdate()) - iif(month(getdate()) = 1, 1, 0));

SELECT FUEL_CARD_1, FUEL_CARD_2, FUEL_CARD_3, FUEL_CARD_4, FUEL_CARD_5, FUEL_CARD_6, FUEL_CARD_7, FUEL_CARD_8, sum(CARD_USED) as TOTAL
FROM Fuel_Table
WHERE YEAR = @reportYear
GROUP BY ********* (unknown)

2

please provide the data in a usable format?

create table #panda(column1 int, column2 money)

insert into #panda
select 1, 4.50 union
select 2, 5.90
3

Easiest is to use CROSS APPLY. If I understand your data structure correctly, then like this:

SELECT CA1.FUEL_CARD, SUM(CA1.FUEL_COST) AS TOTAL_COST
FROM Fuel_Table FT
CROSS APPLY ( VALUES
    (FUEL_CARD_1, TRK_FUEL_COST_1), (FUEL_CARD_2, TRK_FUEL_COST_2),
    (FUEL_CARD_3, TRK_FUEL_COST_3), (FUEL_CARD_4, TRK_FUEL_COST_4),
    (FUEL_CARD_5, TRK_FUEL_COST_5), (FUEL_CARD_6, TRK_FUEL_COST_6),
    (FUEL_CARD_7, TRK_FUEL_COST_7), (FUEL_CARD_8, TRK_FUEL_COST_8)
) AS CA1(FUEL_CARD, FUEL_COST)
WHERE YEAR = @reportYear AND CA1.FUEL_COST IS NOT NULL
GROUP BY CA1.FUEL_CARD
ORDER BY CA1.FUEL_CARD
4

I put together a quick sample and code:

Declare @fuelTable Table (
        fuel_card_1 char(1)
      , trk_fuel_cost_1 money
      , fuel_card_2 char(1)
      , trk_fuel_cost_2 money
      , fuel_card_3 char(1)
      , trk_fuel_cost_3 money
      , fuel_card_4 char(1)
      , trk_fuel_cost_4 money
      , fuel_card_5 char(1)
      , trk_fuel_cost_5 money
      , fuel_card_6 char(1)
      , trk_fuel_cost_6 money
      , fuel_card_7 char(1)
      , trk_fuel_cost_7 money
      , fuel_card_8 char(1)
      , trk_fuel_cost_8 money
        );

 Insert Into @fuelTable
 Values ('E', 321.79, 'E', 297.62, 'Q', 341.12, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null);

 Select fc.FuelCard
      , TotalFuelCost = sum(fc.FuelCost)
   From @fuelTable ft
  Cross Apply (
 Values (1, fuel_card_1, trk_fuel_cost_1)
      , (2, fuel_card_2, trk_fuel_cost_2)
      , (3, fuel_card_3, trk_fuel_cost_3)
      , (4, fuel_card_4, trk_fuel_cost_4)
      , (5, fuel_card_5, trk_fuel_cost_5)
      , (6, fuel_card_6, trk_fuel_cost_6)
      , (7, fuel_card_7, trk_fuel_cost_7)
      , (8, fuel_card_8, trk_fuel_cost_8)
        ) As fc(FuelCount, FuelCard, FuelCost)
  Where fc.FuelCard Is Not Null 
  Group By
        fc.FuelCard;

With that said - it would be much easier to change the table structure. Instead of a table with up to 8 paired columns you should have a single table recording only those rows that are needed. For example:

Declare @tripTable Table (TripID int, TripDetails varchar(20));
 Insert Into @tripTable Values (1, 'Trip 1');

Declare @tripFuel Table (TripID int, FuelStop int, FuelCard char(1), FuelCost money);
 Insert Into @tripFuel (TripID, FuelStop, FuelCard, FuelCost)
 Values (1, 1, 'E', 321.79)
      , (1, 2, 'E', 297.62)
      , (1, 3, 'Q', 297.62);

 Select tt.TripID
      , tf.FuelCard
      , TotalFuelCost = sum(tf.FuelCost)
   From @tripTable              tt
  Inner Join @tripFuel          tf On tf.TripID = tt.TripID
  Group By
        tt.TripID
      , tf.FuelCard;
5

Your understanding is fully correct. Your sample works perfectly and need to make 2 small adjustments if possible please:

If I wished to add, say, Reefer Total (RFR_ADJ_COST is column name) cost to this It only allowing 2 columns in your cross apply.

Also in the DECLARE statement I need to make a change as overlooked something when initially:

DECLARE @reportYear int = (year(getdate()) - iif(month(getdate()) = 1, 1, 0));
DECLARE @startLoadNumber int = @reportYear * 1000 + 1
, @endLoadNumber int = @reportYear * 1000 + 999;

SELECT CA1.FUEL_CARD, SUM(CA1.FUEL_COST) AS TOTAL_COST
FROM Fuel_Table FT
CROSS APPLY ( VALUES
(FUEL_CARD_1, TRK_FUEL_COST_1), (FUEL_CARD_2, TRK_FUEL_COST_2),
(FUEL_CARD_3, TRK_FUEL_COST_3), (FUEL_CARD_4, TRK_FUEL_COST_4),
(FUEL_CARD_5, TRK_FUEL_COST_5), (FUEL_CARD_6, TRK_FUEL_COST_6),
(FUEL_CARD_7, TRK_FUEL_COST_7), (FUEL_CARD_8, TRK_FUEL_COST_8)
) AS CA1(FUEL_CARD, FUEL_COST)

WHERE PETS_LOAD_NUMB BETWEEN @startLoadNumber AND @endLoadNumber AND CA1.FUEL_COST IS NOT NULL
GROUP BY CA1.FUEL_CARD
ORDER BY CA1.FUEL_CARD

6

Maybe this?:

DECLARE @reportYear int = (year(getdate()) - iif(month(getdate()) = 1, 1, 0));
DECLARE @startLoadNumber int = @reportYear * 1000 + 1
, @endLoadNumber int = @reportYear * 1000 + 999;

SELECT CA1.FUEL_CARD, SUM(CA1.FUEL_COST) AS TOTAL_COST, MAX(CA1.RFR_ADJ_COST) AS RFR_ADJ_COST
FROM Fuel_Table FT
CROSS APPLY ( VALUES
    (FUEL_CARD_1, TRK_FUEL_COST_1, RFR_ADJ_COST), (FUEL_CARD_2, TRK_FUEL_COST_2, RFR_ADJ_COST),
    (FUEL_CARD_3, TRK_FUEL_COST_3, RFR_ADJ_COST), (FUEL_CARD_4, TRK_FUEL_COST_4, RFR_ADJ_COST),
    (FUEL_CARD_5, TRK_FUEL_COST_5, RFR_ADJ_COST), (FUEL_CARD_6, TRK_FUEL_COST_6, RFR_ADJ_COST),
    (FUEL_CARD_7, TRK_FUEL_COST_7, RFR_ADJ_COST), (FUEL_CARD_8, TRK_FUEL_COST_8, RFR_ADJ_COST)
) AS CA1(FUEL_CARD, FUEL_COST, RFR_ADJ_COST)
WHERE FT.PETS_LOAD_NUMB BETWEEN @startLoadNumber AND @endLoadNumber AND 
    CA1.FUEL_COST IS NOT NULL
GROUP BY CA1.FUEL_CARD
ORDER BY CA1.FUEL_CARD
1 Like
7

Excellent- exactly what I was looking for. I had done what you showed above, but neglected to put int he additional column name in the 'AS' field.Thus, why it did not work for me!! Thank you a ton!

1 Like
8

You're welcome, I'm very glad it helped!

9

I do have one question: Why did you use MAX instead of SUM here:

MAX(CA1.RFR_ADJ_COST) AS RFR_ADJ_COST

10

I thought it was a single value, not a separate one for each FUEL entry. If so, then adding it up multiple times would give an overstated value. MAX() will just give you the original value back.

11

Yeah there is a separate data entry (even if it is a '0') for each line. There are a possible 8 lines per fueling record.

I changed MAX to SUM and got closer to the number I was looking for. MAX only returned the MAX number of each fuel record and disregarded the others.

Seems there is some differences between 2 reports as to which is closer. That is my problem though and will have to check against actual paper to ensure proper numbers were imported.