Hi I am trying to write a SQL query but it is just to much for me.
I have records of refueling my car and I want to calculate prices for kilometer intervals.
Every record have actual kilometer state, how much liters I bought, price and currency and if I fill full tank. Consumption can be calculated only if I bought full tank, because only than I know how much liters I used. So it should by like I am using diesel which cost 1.3 EUR/l with consumption 6.2 l/100km and I have 23l of it. Then I start using diesel which cost 1.38 EUR/l and I have 45l of it and then consumption changed to 6.5 l/100km, ...
I hope you know what I mean :)
So the result of the query should by like:
KmFrom KmTo PricePerKmEUR
156123 156874 0.1
156875 157147 0.12
This are the records what I have:
CREATE TABLE "CAR_Refueling"(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
,[Date] DATE NOT NULL
,[KmTotal] INTEGER NOT NULL
,[Liters] NUMERIC(9) NOT NULL
,[PricePerLiter] NUMERIC(9) NOT NULL
,[CurrencyId] INTEGER NOT NULL
,[FullTank] BOOLEAN NOT NULL
,[Consumption] numeric(9)
);
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("1","2011-06-21","156327","70","32.88","8","1","0");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("2","2011-09-02","157036","52.28","1.359","9","1","7.37");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("3","2011-09-15","157832","52.32","5.47","13","1","6.57");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("4","2011-10-03","158830","61.48","2.44","14","1","6.16");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("5","2011-10-06","159054","14.38","2.46","14","1","6.42");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("6","2011-10-31","160070","20","1.456","9","0","0");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("7","2012-01-02","160367","6.765","1.478","9","0","0");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("8","2012-04-27","160414","19.59","1.48","9","0","0");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("9","2012-05-04","160808","66.36","187","16","1","6.43");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("10","2012-05-11","161767","56.37","9.99","15","1","5.88");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("11","2012-05-14","162088","21.07","1.337","9","1","6.56");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("12","2012-05-19","163043","65.12","1.439","9","1","6.82");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("13","2012-10-17","164029","65.67","1.203","9","1","6.66");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("14","2012-10-23","164916","51.14","1.369","9","1","5.77");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("15","2013-05-07","165937","62.45","1.329","9","1","6.12");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("16","2013-05-18","167098","15.05","1.329","9","0","0");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("17","2013-05-18","167359","63.47","1.138","9","1","5.52");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("18","2013-05-20","168335","56.83","1.309","9","1","5.82");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("19","2013-05-21","169051","38.43","1.369","9","1","5.37");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("20","2013-08-25","170096","65.12","1.399","9","1","6.23");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("21","2013-08-28","170636","30","1.43","9","1","5.56");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("22","2013-09-03","171471","30","1.349","9","0","0");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("23","2013-10-26","171953","61.62","1.136","9","1","6.96");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("24","2013-10-29","172196","13.02","1.278","9","1","5.36");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("25","2013-11-18","173101","55.92","1.359","9","1","6.18");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("26","2014-02-26","173963","50.49","1.309","9","1","5.86");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("27","2014-06-03","174667","49.78","1.349","9","1","7.07");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("28","2014-08-05","175501","62.78","1.289","9","1","7.53");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("29","2014-08-09","177195","55.74","1.279","9","1","6.36");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("30","2014-09-10","178266","66.22","34.9","8","1","6.18");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("31","2014-08-07","176318","51.93","1.278","9","1","6.36");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("32","2014-10-16","178888","41.04","1.339","9","1","6.6");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("33","2014-10-18","179498","42.28","1.239","9","1","6.93");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("34","2014-10-19","180037","34.71","1.214","9","1","6.44");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("35","2014-12-02","180860","55.79","1.229","9","1","6.78");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("36","2015-02-04","181579","53.26","1.089","9","1","7.41");
Insert Into [CAR_Refueling] ([Id],[Date],[KmTotal],[Liters],[PricePerLiter],[CurrencyId],[FullTank],[Consumption]) Values("37","2015-02-17","182125","32.6","1.119","9","1","5.97");
Aucun commentaire:
Enregistrer un commentaire