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.
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
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
WHERE YEAR = @reportYear
GROUP BY ********* (unknown)