Grouping totals by state

K3JAE · December 1, 2020, 12:02pm

ALABAMA is a highly traveled state. That is only a sample of the data, we have states that have considerably more purchases.

WOW! You have high aspirations of my small company! LOL

To explain: My drivers complete a PDF file each load that is converted to a .CSV file then imported to my program. They chose the state they are fueling in and that is purely done by state abbreviations. I do have a separate table that I use in my program with full state names and abbreviations but that is not needed in this particular instance.

I am not a 'professional' database person nor a programmer, but what I do have works. I have learned a ton of coding and SQL and learn new stuff every day. It keeps me sane and active. I love to learn but sometimes I tend to bite off more than I can chew. I know things can be done and I scour the Internet looking for correct ways to do it. Sometimes nothing is relevant to what I am trying to accomplish so, as a last resort I come here and ask.

yosiasz · December 1, 2020, 12:46pm

one can only reach as high their aim so why not shoot for the stars! So the sample table #panda represents the pdf they fill in?

K3JAE · December 1, 2020, 2:20pm

Partially - there is considerably more info - that is imported into multiple tables. This is strictly for the FUEL and Unladen Mileage side of things.

yosiasz · December 1, 2020, 2:24pm

why are you opting for the horizontal data approach rather than vertical? anyways, your design which leads to the following scary option

use sqlteam
go

create table panda (
		STATE_1 nvarchar(5),
		STATE_2 nvarchar(5),
		STATE_3 nvarchar(5),
		STATE_4 nvarchar(5),
		STATE_5 nvarchar(5),
		STATE_6 nvarchar(5),
		STATE_7 nvarchar(5),
		STATE_8 nvarchar(5),
		TRK_FUEL_COST_1 money,
		TRK_FUEL_COST_2 money,
		TRK_FUEL_COST_3 money,
		TRK_FUEL_COST_4 money,
		TRK_FUEL_COST_5 money,
		TRK_FUEL_COST_6 money,
		TRK_FUEL_COST_7 money,
		TRK_FUEL_COST_8 money,
		TRK_GALS_PURCH_1 decimal(7, 3),
		TRK_GALS_PURCH_2 decimal(7, 3),
		TRK_GALS_PURCH_3 decimal(7, 3),
		TRK_GALS_PURCH_4 decimal(7, 3),
		TRK_GALS_PURCH_5 decimal(7, 3),
		TRK_GALS_PURCH_6 decimal(7, 3),
		TRK_GALS_PURCH_7 decimal(7, 3),
		TRK_GALS_PURCH_8 decimal(7, 3),
		RFR_FUEL_COST_1 money,
		RFR_FUEL_COST_2 money,
		RFR_FUEL_COST_3 money,
		RFR_FUEL_COST_4 money,
		RFR_FUEL_COST_5 money,
		RFR_FUEL_COST_6 money,
		RFR_FUEL_COST_7 money,
		RFR_FUEL_COST_8 money,
		RFR_GALS_PURCH_1 decimal(7, 3),
		RFR_GALS_PURCH_2 decimal(7, 3),
		RFR_GALS_PURCH_3 decimal(7, 3),
		RFR_GALS_PURCH_4 decimal(7, 3),
		RFR_GALS_PURCH_5 decimal(7, 3),
		RFR_GALS_PURCH_6 decimal(7, 3),
		RFR_GALS_PURCH_7 decimal(7, 3),
		RFR_GALS_PURCH_8 decimal(7, 3),
		DEF_COST_1 money,
		DEF_COST_2 money,
		DEF_COST_3 money,
		DEF_COST_4 money,
		DEF_COST_5 money,
		DEF_COST_6 money,
		DEF_COST_7 money,
		DEF_COST_8 money,
		DEF_GALS_1 decimal(7, 3),
		DEF_GALS_2 decimal(7, 3),
		DEF_GALS_3 decimal(7, 3),
		DEF_GALS_4 decimal(7, 3),
		DEF_GALS_5 decimal(7, 3),
		DEF_GALS_6 decimal(7, 3),
		DEF_GALS_7 decimal(7, 3),
		DEF_GALS_8 decimal(7, 3)
)

INSERT INTO panda (STATE_1, STATE_2, STATE_3, STATE_4, STATE_5, STATE_6, STATE_7, STATE_8,
TRK_FUEL_COST_1, TRK_FUEL_COST_2, TRK_FUEL_COST_3, TRK_FUEL_COST_4, TRK_FUEL_COST_5, TRK_FUEL_COST_6, TRK_FUEL_COST_7, TRK_FUEL_COST_8,
TRK_GALS_PURCH_1, TRK_GALS_PURCH_2, TRK_GALS_PURCH_3, TRK_GALS_PURCH_4, TRK_GALS_PURCH_5, TRK_GALS_PURCH_6, TRK_GALS_PURCH_7, TRK_GALS_PURCH_8,
RFR_FUEL_COST_1, RFR_FUEL_COST_2, RFR_FUEL_COST_3, RFR_FUEL_COST_4, RFR_FUEL_COST_5, RFR_FUEL_COST_6, RFR_FUEL_COST_7, RFR_FUEL_COST_8,
RFR_GALS_PURCH_1, RFR_GALS_PURCH_2, RFR_GALS_PURCH_3, RFR_GALS_PURCH_4, RFR_GALS_PURCH_5, RFR_GALS_PURCH_6, RFR_GALS_PURCH_7, RFR_GALS_PURCH_8,
DEF_COST_1, DEF_COST_2, DEF_COST_3, DEF_COST_4, DEF_COST_5, DEF_COST_6, DEF_COST_7, DEF_COST_8,
DEF_GALS_1, DEF_GALS_2, DEF_GALS_3, DEF_GALS_4, DEF_GALS_5, DEF_GALS_6, DEF_GALS_7, DEF_GALS_8)
VALUES ('AL','----','----','----','----','----','----','----','315.67','0.00','0.00','0.00','0.00','0.00','0.00','0.00','114.001','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('MO','OH','----','----','----','----','----','----','408.36','351.06','0.00','0.00','0.00','0.00','0.00','0.00','153.001','124.001','0.000','0.000','0.000','0.000','0.000','0.000','24.02','0.00','0.00','0.00','0.00','0.00','0.00','0.00','9.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','27.88','0.00','0.00','0.00','0.00','0.00','0.00','0.000','10.404','0.000','0.000','0.000','0.000','0.000','0.000'),
('FL','TX','AZ','----','----','----','----','----','274.97','332.14','270.30','0.00','0.00','0.00','0.00','0.00','109.001','144.001','108.005','0.000','0.000','0.000','0.000','0.000','28.60','20.78','20.02','0.00','0.00','0.00','0.00','0.00','13.007','9.007','8.001','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('AZ','NV','NV','----','----','----','----','----','117.63','276.22','166.27','0.00','0.00','0.00','0.00','0.00','41.001','104.000','56.001','0.000','0.000','0.000','0.000','0.000','28.69','13.31','62.35','0.00','0.00','0.00','0.00','0.00','10.000','5.012','21.001','0.000','0.000','0.000','0.000','0.000','44.39','0.00','0.00','0.00','0.00','0.00','0.00','0.00','15.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('WA','MT','WY','----','----','----','----','----','474.01','159.73','245.59','0.00','0.00','0.00','0.00','0.00','145.001','68.000','104.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','32.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','11.154','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','235.10','0.00','0.00','0.00','0.00','0.00','0.00','0.00','98.001','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','35.66','0.00','0.00','0.00','0.00','0.00','0.00','0.00','11.890','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('MO','----','----','----','----','----','----','----','198.66','0.00','0.00','0.00','0.00','0.00','0.00','0.00','96.024','0.000','0.000','0.000','0.000','0.000','0.000','0.000','22.43','0.00','0.00','0.00','0.00','0.00','0.00','0.00','10.842','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('TN','TN','----','----','----','----','----','----','0.00','202.83','0.00','0.00','0.00','0.00','0.00','0.00','0.000','202.830','0.000','0.000','0.000','0.000','0.000','0.000','15.04','0.00','0.00','0.00','0.00','0.00','0.00','0.00','7.003','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('TN','----','----','----','----','----','----','----','266.12','0.00','0.00','0.00','0.00','0.00','0.00','0.00','128.002','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','34.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','11.336','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('NC','----','----','----','----','----','----','----','133.14','0.00','0.00','0.00','0.00','0.00','0.00','0.00','60.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('NC','----','----','----','----','----','----','----','166.43','0.00','0.00','0.00','0.00','0.00','0.00','0.00','75.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('TN','----','----','----','----','----','----','----','197.84','0.00','0.00','0.00','0.00','0.00','0.00','0.00','107.002','0.000','0.000','0.000','0.000','0.000','0.000','0.000','20.34','0.00','0.00','0.00','0.00','0.00','0.00','0.00','11.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('IL','----','----','----','----','----','----','----','190.71','0.00','0.00','0.00','0.00','0.00','0.00','0.00','90.001','0.000','0.000','0.000','0.000','0.000','0.000','0.000','14.84','0.00','0.00','0.00','0.00','0.00','0.00','0.00','7.002','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('GA','----','----','----','----','----','----','----','240.51','0.00','0.00','0.00','0.00','0.00','0.00','0.00','128.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','33.82','0.00','0.00','0.00','0.00','0.00','0.00','0.00','18.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','32.99','0.00','0.00','0.00','0.00','0.00','0.00','0.00','11.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('MS','KY','----','----','----','----','----','----','97.83','193.76','0.00','0.00','0.00','0.00','0.00','0.00','59.427','105.016','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('OH','----','----','----','----','----','----','----','180.10','0.00','0.00','0.00','0.00','0.00','0.00','0.00','91.004','0.000','0.000','0.000','0.000','0.000','0.000','0.000','19.79','0.00','0.00','0.00','0.00','0.00','0.00','0.00','10.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('KY','AL','LA','----','----','----','----','----','0.00','211.76','125.18','0.00','0.00','0.00','0.00','0.00','0.000','107.001','62.000','0.000','0.000','0.000','0.000','0.000','26.29','0.00','29.94','0.00','0.00','0.00','0.00','0.00','11.002','0.000','13.021','0.000','0.000','0.000','0.000','0.000','0.00','0.00','30.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','10.003','0.000','0.000','0.000','0.000','0.000'),
('MO','----','----','----','----','----','----','----','226.28','0.00','0.00','0.00','0.00','0.00','0.00','0.00','132.071','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('IL','----','----','----','----','----','----','----','190.71','0.00','0.00','0.00','0.00','0.00','0.00','0.00','90.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('TN','----','----','----','----','----','----','----','246.26','0.00','0.00','0.00','0.00','0.00','0.00','0.00','140.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('TN','----','----','----','----','----','----','----','209.88','0.00','0.00','0.00','0.00','0.00','0.00','0.00','120.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','37.49','0.00','0.00','0.00','0.00','0.00','0.00','0.00','12.500','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('KY','----','----','----','----','----','----','----','158.32','0.00','0.00','0.00','0.00','0.00','0.00','0.00','80.002','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('WI','----','----','----','----','----','----','----','214.75','0.00','0.00','0.00','0.00','0.00','0.00','0.00','108.999','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('MS','----','----','----','----','----','----','----','254.32','0.00','0.00','0.00','0.00','0.00','0.00','0.00','139.400','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('KY','----','----','----','----','----','----','----','228.15','0.00','0.00','0.00','0.00','0.00','0.00','0.00','113.010','0.000','0.000','0.000','0.000','0.000','0.000','0.000','14.14','0.00','0.00','0.00','0.00','0.00','0.00','0.00','7.001','0.000','0.000','0.000','0.000','0.000','0.000','0.000','32.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','10.669','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('KY','NC','NC','NC','KY','KY','KY','----','209.62','285.28','0.00','255.14','182.32','42.40','274.43','0.00','107.004','141.996','0.000','127.001','95.006','20.010','143.004','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','30.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','10.071','0.000','0.000','0.000','0.000','0.000'),
('KY','NC','VA','TN','OH','----','----','----','189.58','307.91','68.07','303.02','342.55','0.00','0.00','0.00','87.003','146.002','30.001','143.001','149.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','34.79','0.00','0.00','0.00','0.00','0.00','0.00','0.00','11.600','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('MO','----','----','----','----','----','----','----','249.49','0.00','0.00','0.00','0.00','0.00','0.00','0.00','131.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('MO','----','----','----','----','----','----','----','272.99','0.00','0.00','0.00','0.00','0.00','0.00','0.00','143.001','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('----','----','----','----','----','----','----','----','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000'),
('MO','SD','MT','MT','----','----','----','----','85.10','287.58','271.72','98.25','0.00','0.00','0.00','0.00','41.000','146.003','138.001','52.012','0.000','0.000','0.000','0.000','36.52','0.00','0.00','15.14','0.00','0.00','0.00','0.00','18.001','0.000','0.000','8.018','0.000','0.000','0.000','0.000','0.00','0.00','42.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','14.639','0.000','0.000','0.000','0.000','0.000'),
('WA','MT','SD','TN','----','----','----','----','89.93','245.57','195.75','0.00','0.00','0.00','0.00','0.00','35.005','130.001','102.008','0.000','0.000','0.000','0.000','0.000','0.00','20.79','13.72','0.00','0.00','0.00','0.00','0.00','0.000','11.005','8.002','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','46.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','15.440','0.000','0.000','0.000','0.000'),
('TN','OH','VA','SC','KY','OH','TN','----','205.08','175.31','232.86','186.14','189.78','122.06','209.18','0.00','106.865','79.005','112.005','97.000','101.002','55.007','109.002','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','0.00','0.00','30.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','10.003','0.000','0.000','0.000'),
('NY','SC','SC','KY','KY','----','----','----','242.64','211.79','148.43','191.90','281.52','0.00','0.00','0.00','118.000','107.015','75.000','100.000','146.704','0.000','0.000','0.000','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.00','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.000','0.00','0.00','35.99','0.00','0.00','0.00','0.00','0.00','0.000','0.000','12.001','0.000','0.000','0.000','0.000','0.000')

--select case when OBJECT_ID()
--  From panda

declare @cols nvarchar(max), 
        @data varchar(max), 
		@object_suffix char(3),
		@query varchar(max);

create table #schema(columnname nvarchar(max))

create table #final(
	DEF_COST money,
	DEF_GALS decimal(7, 3),
	RFR_FUEL_COST money,
	RFR_GALS_PURCH  decimal(7, 3),
	STATE nvarchar(5),
	TRK_FUEL_COST money,
	TRK_GALS_PURCH  decimal(7, 3)
)
DECLARE gas CURSOR FOR  
select distinct '%' + right(c.name,2)
From sys.tables t
join sys.columns c on t.object_id = c.object_id
where t.name = 'panda'
order by 1;

OPEN gas;  
FETCH NEXT FROM gas INTO @object_suffix;  
WHILE @@FETCH_STATUS = 0  
   BEGIN
		--select @object_suffix;

		insert into #schema
		select c.name 
		  From sys.tables t
		  join sys.columns c on t.object_id = c.object_id
		 where t.name = 'panda'
		   and c.name like @object_suffix
		 order by name

		select @data = STUFF(( select  distinct ',' +  QUOTENAME(columnname)
		 from #schema
		  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

	   select @query = 'select ' + @data + ' from panda'
   
	   truncate table #schema;
	   insert into #final
	   exec (@query);

      FETCH NEXT FROM gas INTO @object_suffix;  
   END;  
CLOSE gas;  
DEALLOCATE gas;

--select STATE, TRK_FUEL_COST, TRK_GALS_PURCH, RFR_GALS_PURCH, DEF_COST, DEF_GALS
 
--  from #final
--  where STATE <> '----'
--  order by STATE

select STATE, sum(TRK_FUEL_COST) as TRK_FUEL_COST, sum(TRK_GALS_PURCH) TRK_GALS_PURCH, sum(RFR_GALS_PURCH) RFR_GALS_PURCH, 
       sum(DEF_COST) DEF_COST, sum(DEF_GALS) DEF_GALS
 
  from #final
  where STATE <> '----'
  group by STATE
  order by STATE

drop table panda
drop table #schema
drop table #final

yosiasz · December 1, 2020, 2:26pm

which results in. interestingly your table should really be designed like the image below.

Could you expound on what the _1 _2 _3 are, are they fuel stops or entries?

ScottPletcher · December 1, 2020, 3:23pm

In order to list all states, I had to create a states table so I could LEFT JOIN to that. I included 'DC' (District of Columbia) in the "states" since it is in the contiguous area -- remove it if you don't want it. Creating this data, btw, took me far longer than writing the query against the actual data (!).

The key to the query is to use a CROSS APPLY to normalize the data so that a standard SQL query can do the SUMs.

CREATE TABLE #states ( STATE char(2) NOT NULL PRIMARY KEY )
INSERT INTO #states VALUES
    ('AL'), ('AR'), ('AZ'), ('CA'), ('CO'), ('CT'), ('DC'), ('DE'), ('FL'), ('GA'), 
    ('IA'), ('ID'), ('IL'), ('IN'), ('KS'), ('KY'), ('LA'), ('MA'), ('MD'), ('ME'), 
    ('MI'), ('MN'), ('MO'), ('MS'), ('MT'), ('NC'), ('ND'), ('NE'), ('NH'), ('NJ'), 
    ('NM'), ('NV'), ('NY'), ('OH'), ('OK'), ('OR'), ('PA'), ('RI'), ('SC'), ('SD'), 
    ('TN'), ('TX'), ('UT'), ('VA'), ('VT'), ('WA'), ('WI'), ('WV'), ('WY')

SELECT 
    s.STATE,
    TRUCK_COST,
    TRUCK_GALLONS,
    REEFER_COST,
    REEFER_GALLONS,
    DEF_COST,
    DEF_GALLONS
FROM #states s
LEFT OUTER JOIN (
    SELECT 
        STATE,
        SUM(TRK_FUEL_COST) AS TRUCK_COST,
        SUM(TRK_GALS_PURCH) AS TRUCK_GALLONS,
        SUM(RFR_FUEL_COST) AS REEFER_COST,
        SUM(RFR_GALS_PURCH) AS REEFER_GALLONS,
        SUM(DEF_COST) AS DEF_COST,
        SUM(DEF_GALS_1) AS DEF_GALLONS
    FROM #panda p
    CROSS APPLY ( VALUES
        (STATE_1, TRK_FUEL_COST_1, TRK_GALS_PURCH_1, RFR_FUEL_COST_1, RFR_GALS_PURCH_1, DEF_COST_1, DEF_GALS_1),
        (STATE_2, TRK_FUEL_COST_2, TRK_GALS_PURCH_2, RFR_FUEL_COST_2, RFR_GALS_PURCH_2, DEF_COST_2, DEF_GALS_2),
        (STATE_3, TRK_FUEL_COST_3, TRK_GALS_PURCH_3, RFR_FUEL_COST_3, RFR_GALS_PURCH_3, DEF_COST_3, DEF_GALS_3),
        (STATE_4, TRK_FUEL_COST_4, TRK_GALS_PURCH_4, RFR_FUEL_COST_4, RFR_GALS_PURCH_4, DEF_COST_4, DEF_GALS_4),
        (STATE_5, TRK_FUEL_COST_5, TRK_GALS_PURCH_5, RFR_FUEL_COST_5, RFR_GALS_PURCH_5, DEF_COST_5, DEF_GALS_5),
        (STATE_6, TRK_FUEL_COST_6, TRK_GALS_PURCH_6, RFR_FUEL_COST_6, RFR_GALS_PURCH_6, DEF_COST_6, DEF_GALS_6),
        (STATE_7, TRK_FUEL_COST_7, TRK_GALS_PURCH_7, RFR_FUEL_COST_7, RFR_GALS_PURCH_7, DEF_COST_7, DEF_GALS_7),
        (STATE_8, TRK_FUEL_COST_8, TRK_GALS_PURCH_8, RFR_FUEL_COST_8, RFR_GALS_PURCH_8, DEF_COST_8, DEF_GALS_8)
    ) AS ca1 ( STATE, TRK_FUEL_COST, TRK_GALS_PURCH, RFR_FUEL_COST, RFR_GALS_PURCH, DEF_COST, DEF_GALS )
    WHERE STATE LIKE '[A-Z]%'
    GROUP BY STATE
) AS p ON p.STATE = s.STATE
ORDER BY s.STATE

K3JAE · December 1, 2020, 4:08pm

Scott;

Quick Question:
I have a states table (tbSTATES) already with both full and abbreviated data.

ID int
STATE nvarchar(20)
ABBREVIATION nvarchar(3)

Your script works very well and appears to give exactly what I am hoping but running it currently gives me null values across the board.I had not yet created the states table as you have since I already had one.
Also I used from Fuel_Table (instead of your #panda). Am I required to make a #panda in order to do this?

K3JAE · December 1, 2020, 4:25pm

There are up to 8 entries (or fuel stops) per record (load). In other words each load may have up to 8 fuel stops although the average fuel stop entries per load is 2. Longer hauls require more fuel stops.

TO expand, I MUST show every fuel stop made per load. I must show 'proof' of said fuel stop(s) each load so that IFTA can be properly applied to every state we drive through. Therefore drivers report each state they purchase fuel in, and how much fuel was purchased. I have to report every gallon of fuel I purchase in any state along with every mile I drive in any state. This is what dictates my Federal IFTA payment required each quarter.

So the table image you show above is exactly what I must file each and every quarter. Your table above would show all gallons etc that continues to grow each day for the foreseeable future. I must be able to extract out fuel purchased each quarter than a 'final' for the entire year.

Below is a screenshot of part of the load PDF of an actual load ran in January showing the various fuel stops as input by the Drivers:
FuelStops

To further expand understanding... The 'Fuel Tax rate' & 'Adjusted Fuel Cost' are auto calculated as are the line totals - not input by the drivers.

You will notice 3 of the 5 stops were fuel and reefer fuel purchases. 1 stop was strictly a reefer purchase and one stop was strictly a DEF purchase.

ScottPletcher · December 1, 2020, 4:31pm

I've adjusted the query to use your actual table names. Since table aliases are used, the name only has to change in the FROM clause and nowhere else. Naturally you can use the full state name instead if you prefer to do that.

SELECT 
    s.ABBREVIATION AS STATE,
    TRUCK_COST,
    TRUCK_GALLONS,
    REEFER_COST,
    REEFER_GALLONS,
    DEF_COST,
    DEF_GALLONS
FROM tbSTATES s --<<--<<--
LEFT OUTER JOIN (
    SELECT 
        STATE,
        SUM(TRK_FUEL_COST) AS TRUCK_COST,
        SUM(TRK_GALS_PURCH) AS TRUCK_GALLONS,
        SUM(RFR_FUEL_COST) AS REEFER_COST,
        SUM(RFR_GALS_PURCH) AS REEFER_GALLONS,
        SUM(DEF_COST) AS DEF_COST,
        SUM(DEF_GALS_1) AS DEF_GALLONS
    FROM Fuel_Table p --<<--<<--
    CROSS APPLY ( VALUES
        (STATE_1, TRK_FUEL_COST_1, TRK_GALS_PURCH_1, RFR_FUEL_COST_1, RFR_GALS_PURCH_1, DEF_COST_1, DEF_GALS_1),
        (STATE_2, TRK_FUEL_COST_2, TRK_GALS_PURCH_2, RFR_FUEL_COST_2, RFR_GALS_PURCH_2, DEF_COST_2, DEF_GALS_2),
        (STATE_3, TRK_FUEL_COST_3, TRK_GALS_PURCH_3, RFR_FUEL_COST_3, RFR_GALS_PURCH_3, DEF_COST_3, DEF_GALS_3),
        (STATE_4, TRK_FUEL_COST_4, TRK_GALS_PURCH_4, RFR_FUEL_COST_4, RFR_GALS_PURCH_4, DEF_COST_4, DEF_GALS_4),
        (STATE_5, TRK_FUEL_COST_5, TRK_GALS_PURCH_5, RFR_FUEL_COST_5, RFR_GALS_PURCH_5, DEF_COST_5, DEF_GALS_5),
        (STATE_6, TRK_FUEL_COST_6, TRK_GALS_PURCH_6, RFR_FUEL_COST_6, RFR_GALS_PURCH_6, DEF_COST_6, DEF_GALS_6),
        (STATE_7, TRK_FUEL_COST_7, TRK_GALS_PURCH_7, RFR_FUEL_COST_7, RFR_GALS_PURCH_7, DEF_COST_7, DEF_GALS_7),
        (STATE_8, TRK_FUEL_COST_8, TRK_GALS_PURCH_8, RFR_FUEL_COST_8, RFR_GALS_PURCH_8, DEF_COST_8, DEF_GALS_8)
    ) AS ca1 ( STATE, TRK_FUEL_COST, TRK_GALS_PURCH, RFR_FUEL_COST, RFR_GALS_PURCH, DEF_COST, DEF_GALS )
    WHERE STATE LIKE '[A-Z]%'
    GROUP BY STATE
) AS p ON p.STATE = s.ABBREVIATION
ORDER BY s.ABBREVIATION

K3JAE · December 1, 2020, 4:33pm

OK, still receiving null values in the query as I believe I need to point to the column in tbSTATES that has the abbreviation (column name is ABBREVIATION)?

K3JAE · December 1, 2020, 4:39pm

AH, I see you made the same change at the bottom of the query as I just made... that worked.
Now I just need to add in a way to pull only records from a specific time frame like a specific quarter or a year....

I will work on passing a begin/end date parameter into the SQL from my program and see if this can be completed....

ScottPletcher · December 1, 2020, 4:44pm

I don't see any date in the DDL you posted, so I can't help you with that.

yosiasz · December 1, 2020, 4:45pm

Awesome @ScottPletcher that cross apply niftiness!!

yosiasz · December 1, 2020, 4:51pm

So 8 entries horizontally?

K3JAE · December 1, 2020, 5:03pm

Yeah, I neglected to add the date field in... the dates are columned like: FS1_DATE thru FS8_DATE (date, Null)

I would like something much cleaner than:

WHERE STATE LIKE '[A-Z]%'
AND FS1_DATE between '10-01-2020' and '12-31-2020'
or FS2_DATE between '10-01-2020' and '12-31-2020'
or FS3_DATE between '10-01-2020' and '12-31-2020'
or FS4_DATE between '10-01-2020' and '12-31-2020'
or FS5_DATE between '10-01-2020' and '12-31-2020'
or FS6_DATE between '10-01-2020' and '12-31-2020'
or FS7_DATE between '10-01-2020' and '12-31-2020'
or FS8_DATE between '10-01-2020' and '12-31-2020'

ScottPletcher · December 1, 2020, 5:13pm

Just add the date to the CROSS APPLY, and change the WHERE clause like so:

DECLARE @start_date date
DECLARE @end_date date
SET @start_date = '20201001'
SET @end_date = '20201231'
...rest of query...
WHERE STATE LIKE '[A-Z]%' AND DATE BETWEEN @start_date AND @end_date
...rest of query...

K3JAE · December 1, 2020, 5:20pm

The problem in the declare statement is you have it setting the start/end date - that is fine but DATE is not a column name in my db.

As shown above my column names are FS1_date through FS8_DATE

Is there a way to assign those 8 columns to DATE or whatever?

ScottPletcher · December 1, 2020, 7:42pm

Yes, add the date to the CROSS APPLY, as I stated earlier:

Change:
(STATE_1, TRK_FUEL_COST_1, TRK_GALS_PURCH_1, RFR_FUEL_COST_1, RFR_GALS_PURCH_1, DEF_COST_1, DEF_GALS_1),
to
(FS1_DATE, STATE_1, TRK_FUEL_COST_1, TRK_GALS_PURCH_1, RFR_FUEL_COST_1, RFR_GALS_PURCH_1, DEF_COST_1, DEF_GALS_1),
...and so on for _2, _3, ..., _8

And change ca1 to have that column as well:
ca1 ( DATE, STATE, TRK_FUEL_COST, TRK_GALS_PURCH, RFR_FUEL_COST, RFR_GALS_PURCH, DEF_COST, DEF_GALS )

K3JAE · December 2, 2020, 9:00am

Sorry for the delayed response but I was out of internet most of the day unexpectedly... Someone cut into a line I had heard.

My main reason for posting here is to sincerely thank BOTH @yosiasz and @ScottPletcher for the absolute spot on help. Hats off to you gentlemen! Both of you have taught me more about SQL in 24 hours than I had learned in many days. I cannot express how truly thankful I am for your patience and willingness to 'teach' an old salty truck driver, turned owner, new tricks!!

The old report I had cobbled together used to take near a minute to complete and it now takes, literally, less than a few seconds.This is a beautiful thing!

I had no idea about CROSS APPLY. That trick will now allow me to re-work many reports I had done the old way to a much more efficient way.

In my VB.NET code I was able to code in the parameters that allows me to look at a year in full, a Quarter or even monthly, than pass those parameter dates over to Crystal Reports via {?ADateBeg} and {?ADateEnd} parameters.

May your upcoming CHRISTmas and New Years be joyful!

As a final comment for those interested that may read this later - the fully working code, now included in my program (using Crystal Reports) is as follows:

DECLARE @start_date date
DECLARE @end_date date
SET @start_date = '{?ADateBeg}'
SET @end_date = '{?ADateEnd}'

SELECT 
    s.STATE,
    TRUCK_COST,
    TRUCK_GALLONS,
    REEFER_COST,
    REEFER_GALLONS,
    DEF_COST,
    DEF_GALLONS
FROM tbSTATES s 
LEFT OUTER JOIN (
    SELECT 
        STATE,
        SUM(TRK_FUEL_COST) AS TRUCK_COST,
        SUM(TRK_GALS_PURCH) AS TRUCK_GALLONS,
        SUM(RFR_FUEL_COST) AS REEFER_COST,
        SUM(RFR_GALS_PURCH) AS REEFER_GALLONS,
        SUM(DEF_COST) AS DEF_COST,
        SUM(DEF_GALS_1) AS DEF_GALLONS
    FROM Fuel_Table f
    CROSS APPLY ( VALUES
        (FS1_DATE, STATE_1, TRK_FUEL_COST_1, TRK_GALS_PURCH_1, RFR_FUEL_COST_1, RFR_GALS_PURCH_1, DEF_COST_1, DEF_GALS_1),
        (FS2_DATE, STATE_2, TRK_FUEL_COST_2, TRK_GALS_PURCH_2, RFR_FUEL_COST_2, RFR_GALS_PURCH_2, DEF_COST_2, DEF_GALS_2),
        (FS3_DATE, STATE_3, TRK_FUEL_COST_3, TRK_GALS_PURCH_3, RFR_FUEL_COST_3, RFR_GALS_PURCH_3, DEF_COST_3, DEF_GALS_3),
        (FS4_DATE, STATE_4, TRK_FUEL_COST_4, TRK_GALS_PURCH_4, RFR_FUEL_COST_4, RFR_GALS_PURCH_4, DEF_COST_4, DEF_GALS_4),
        (FS5_DATE, STATE_5, TRK_FUEL_COST_5, TRK_GALS_PURCH_5, RFR_FUEL_COST_5, RFR_GALS_PURCH_5, DEF_COST_5, DEF_GALS_5),
        (FS6_DATE, STATE_6, TRK_FUEL_COST_6, TRK_GALS_PURCH_6, RFR_FUEL_COST_6, RFR_GALS_PURCH_6, DEF_COST_6, DEF_GALS_6),
        (FS7_DATE, STATE_7, TRK_FUEL_COST_7, TRK_GALS_PURCH_7, RFR_FUEL_COST_7, RFR_GALS_PURCH_7, DEF_COST_7, DEF_GALS_7),
        (FS8_DATE, STATE_8, TRK_FUEL_COST_8, TRK_GALS_PURCH_8, RFR_FUEL_COST_8, RFR_GALS_PURCH_8, DEF_COST_8, DEF_GALS_8)
    ) AS ca1 (DATE, STATE, TRK_FUEL_COST, TRK_GALS_PURCH, RFR_FUEL_COST, RFR_GALS_PURCH, DEF_COST, DEF_GALS )
    WHERE STATE LIKE '[A-Z]%' AND DATE BETWEEN @start_date AND @end_date
    GROUP BY STATE
) AS f ON f.STATE = s.ABBREVIATION
ORDER BY s.STATE

ScottPletcher · December 2, 2020, 2:52pm

Thanks so much for the great feedback.

I'm glad we were able to help you out.