1 (edited by vaskar 2016-02-02 14:07:13)

Topic: Скрипт для рассчета значения в пропорции (+)

В приложении пример проекта, в котором необходимо рассчитать среднюю ренту за выбранный период времени.
Например, с 00:00  10Фев2015 по 00:00 20Mай2015.   Как видно , в этот период входят часть 1го, 2й и часть 3го периода.

Как посчитать, чтобы суммировались не просто полные(целые) периоды, а вычислялись именно от даты до даты, включая(исключая) дробные части?

Post's attachments

Attachment icon Proportion.zip 326.43 kb, 417 downloads since 2016-02-02 

Re: Скрипт для рассчета значения в пропорции (+)

Сделал SQL запрос в вашем проекте для расчета стоимости, нажмите на кнопку Average rent for the calendar period, стоимость будет выведена в TableGrid.


Надеюсь правильно считает, на всякий случай проверьте.

Post's attachments

Attachment icon Proportion_fixed.zip 5.22 kb, 427 downloads since 2016-02-03 

Dmitry.

3 (edited by vaskar 2016-02-03 10:03:49)

Re: Скрипт для рассчета значения в пропорции (+)

Дмитрий, спасибо за ответ.

Пересчитал на примере вручную, получается разница

В приложении следующие данные.

    car1    25/05/2015    13/07/2015    49    49000       
    car1    13/07/2015    27/07/2015    14    14000       
    car1    27/07/2015    05/08/2015    9    9000       

Программа подсчитала с 1 июля по 1 августа = 33000

Вручную получается 31000

    car1    01/07/2015    13/07/2015    12    12000       
    car1    13/07/2015    27/07/2015    14    14000       
    car 1    27/07/2015    01/08/2015    5    5000       


По-видимому SQL запрос возвращает лишние 3 дня (специально , для наглядности, в примере брал 1000 в день)

+++

В проекте car1    13/07/2015    по 27/07/2015    = 14000    ,  Если делает выборку с этими датами через SQL запрос, то получаем 16000 

А если в запросе ставим 14-26, то получаем искомые 14000.   Очевидно, что возвращает лишнюю тысячу слева, и справа.

Post's attachments

Attachment icon Proportion_fixedA.zip 326.83 kb, 407 downloads since 2016-02-03 

Re: Скрипт для рассчета значения в пропорции (+)

Еще подскажите  пожалуйста, как необходимо изменить запрос, чтобы давал результат только для определенного в комбобоксе CARа
Проект приложен

Post's attachments

Attachment icon Proportion_fixed_extended.zip 327.27 kb, 424 downloads since 2016-02-03 

Re: Скрипт для рассчета значения в пропорции (+)

У вас каждая аренда накладывается друг на друга на один день, поэтому и лишние 2 тысячи.

Dmitry.

Re: Скрипт для рассчета значения в пропорции (+)

vaskar wrote:

Еще подскажите  пожалуйста, как необходимо изменить запрос, чтобы давал результат только для определенного в комбобоксе CARа
Проект приложен

Тогда запрос будет таким

WITH RECURSIVE dates(date) AS (
  VALUES({edbegdate})
  UNION ALL
  SELECT date(date, '+1 day')
  FROM dates
  WHERE date < {edenddate}
)

SELECT
"date",
TOTAL(
     (
     SELECT TOTAL(rent.rent / (julianday(rent."end") - julianday(rent."begin"))) FROM rent WHERE "date" > rent."begin" AND "date" < rent."end"
     AND (CASE WHEN {ComboBox1}=-1 THEN 1=1 ELSE rent.id_car={ComboBox1} END)
     )
     ) as TotalResult

FROM dates

если в ComboBox не будет выбрана машина, тогда запрос посчитает сумму для всех машин.

Dmitry.

7 (edited by vaskar 2016-02-03 21:11:42)

Re: Скрипт для рассчета значения в пропорции (+)

Спасибо , Дмитрий


DriveSoft wrote:

У вас каждая аренда накладывается друг на друга на один день, поэтому и лишние 2 тысячи.

Я понял.........считаются целые дни.   А возможно , чтобы выборка происходила с определенног времени определенного дня по определенное время определенного дня?

И подскажите пожалуйста, как в TotalResult сделать округление до 2го знака после запятой?

Re: Скрипт для рассчета значения в пропорции (+)

Попробуйте такой SQL запрос

WITH RECURSIVE dates(date) AS (
  VALUES({edbegdate})
  UNION ALL
  SELECT date("date", '+1 day')
  FROM dates
  WHERE "date" < date({edenddate})
)

SELECT

"date",
printf("%.2f",
TOTAL(
     (
     SELECT TOTAL(rent.rent / (julianday(rent."end") - julianday(rent."begin"))) FROM rent WHERE "date" > rent."begin" AND "date" < rent."end"
     AND (CASE WHEN {ComboBox1}=-1 THEN 1=1 ELSE rent.id_car={ComboBox1} END)
     )
     )
     )

FROM dates

также округляет до 2го знака.
К сожалению как сделать с учетом времени не смогу подсказать, SQL запрос работает с полями типа ДАТА, измените ваши поля с типа ДАТА/ВРЕМЯ на тип ДАТА

Dmitry.

Re: Скрипт для рассчета значения в пропорции (+)

Спасибо за ответ.

Поля заменил на тип ДАТА (проект приложен) . Все равно что-то непонятное с подсчетом происходит.

Для наглядности взял период 1-30  и значение 30000.   По идее должно считать каждый день как 1000. Тоесть 1-10 как 10000,  1-15 , как 15000.     К сожалению в реалии считает 9310,34  и 14482.76  соответственно.    из-за чего так происходит?

Post's attachments

Attachment icon Proportion2.zip 327.69 kb, 429 downloads since 2016-02-04 

Re: Скрипт для рассчета значения в пропорции (+)

vaskar
Что то я запутался, вы писали что вторая дата это До, значит последняя дата не должна быть оплачена.
т.е. с 1 января до 2 января это одни сутки аренды, а с 1 января до 30 января это 29 суток аренды.

Dmitry.

Re: Скрипт для рассчета значения в пропорции (+)

Изначально я имел в виду с момента суток до момента искомых. Но раз такое сделать нельзя, то тогда удобнее было бы считать как
1 января до 30 января это 30 суток аренды.  Как такое сделать? Что нужно изменить?

Re: Скрипт для рассчета значения в пропорции (+)

vaskar wrote:

Изначально я имел в виду с момента суток до момента искомых. Но раз такое сделать нельзя, то тогда удобнее было бы считать как
1 января до 30 января это 30 суток аренды.  Как такое сделать? Что нужно изменить?

Тогда так

WITH RECURSIVE dates(date) AS (
  VALUES({edbegdate})
  UNION ALL
  SELECT date("date", '+1 day')
  FROM dates
  WHERE "date" < date({edenddate})
)

SELECT

"date",
printf("%.2f",
TOTAL(
     (
     SELECT TOTAL(rent.rent / ((julianday(rent."end") - julianday(rent."begin")+1))) FROM rent WHERE "date" >= rent."begin" AND "date" < rent."end"
     AND (CASE WHEN {ComboBox1}=-1 THEN 1=1 ELSE rent.id_car={ComboBox1} END)
     )
     )
     )



FROM dates
Dmitry.

Re: Скрипт для рассчета значения в пропорции (+)

Благодарю!

Re: Скрипт для рассчета значения в пропорции (+)

vaskar!


Здесь проект с другим подходом, правильно считает сумму аренды за указанный период.

А вариант с With - какая-то темная лошадка.

Post's attachments

Attachment icon Proportion_CorrectScript.zip 336.46 kb, 427 downloads since 2016-02-08 

15 (edited by vaskar 2016-02-08 15:03:27)

Re: Скрипт для рассчета значения в пропорции (+)

Дмитрий!

Почему-то  SQL запрос не хочет работать с MySql

Можете посмотреть, в чем проблема?

Post's attachments

Attachment icon err2016-02-08_165137.jpg 26.88 kb, 236 downloads since 2016-02-08 

Re: Скрипт для рассчета значения в пропорции (+)

еще скрин

Post's attachments

Attachment icon err22016-02-08_165208.jpg 36.59 kb, 233 downloads since 2016-02-08 

Re: Скрипт для рассчета значения в пропорции (+)

vaskar
Да, данный запрос работает только с SQLite, попробуйте вариант пользователя dbsurgeon

Dmitry.

Re: Скрипт для рассчета значения в пропорции (+)

dbsurgeon,

я очень благодарен вам за помощь, но у меня , к сожалению,  не получился точный подсчет

попробуйте пример  1 янв 2016 - 30 янв 2016 - 30000

получается

2016-01-01    2016-01-01    2016-01-30    2016-01-30    30000    29    29,8    30859,8

Re: Скрипт для рассчета значения в пропорции (+)

там надо привести дату начала и конца периода к только дате без минут, а также поработать с > и <
было when (  {edbegdate} > Date (begin)......
надо when ( Date ({edbegdate}) >= Date (begin)....

во всех условиях надо определиться со сравнением даты - либо >, либо >=
мне щас некогда , но это легко на практике проверить

Re: Скрипт для рассчета значения в пропорции (+)

Дмитрий, а может ли работать с MySQL вариант, если создать таблицу с датами на пару-тройку лет вперед, вместо временной, и оперировать датами из нее? Пусть это будет таблица DATAS

Как тогда будет выглядеть правильный запрос?

Re: Скрипт для рассчета значения в пропорции (+)

Есть ли какой-то шанс, что в вашей программе будет поддержка PostgreSQL?

Re: Скрипт для рассчета значения в пропорции (+)

vaskar wrote:

Дмитрий, а может ли работать с MySQL вариант, если создать таблицу с датами на пару-тройку лет вперед, вместо временной, и оперировать датами из нее? Пусть это будет таблица DATAS

Как тогда будет выглядеть правильный запрос?

Для MySQL попробуйте такой запрос

select
SUM(
(
     SELECT SUM(rent.rent / (datediff(rent.`end`, rent.`begin`)+1)) FROM rent WHERE `selected_date` >= rent.`begin` AND `selected_date` <= rent.`end`
     AND (CASE WHEN {ComboBox1}=-1 THEN 1=1 ELSE rent.id_car={ComboBox1} END)
)
)

from

(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v

where selected_date between {edbegdate} and {edenddate}


но перед этим скачайте пожалуйста снова бета версию 2.4, кое что пришлось исправить.
https://www.dropbox.com/s/ro1hs53q8rdv5 … a.zip?dl=0

Dmitry.

Re: Скрипт для рассчета значения в пропорции (+)

vaskar wrote:

Есть ли какой-то шанс, что в вашей программе будет поддержка PostgreSQL?

Пока не планируется.

Dmitry.

24 (edited by vaskar 2016-02-09 11:59:19)

Re: Скрипт для рассчета значения в пропорции (+)

Все работает. Еще раз спасибо за помощь

25 (edited by vaskar 2016-02-10 10:19:26)

Re: Скрипт для рассчета значения в пропорции (+)

Пытаюсь изменить запрос, чтобы в репорте выводились не только значение rent за период, но и то, что выбрано в Combobox1 ,
и сами даты периода.

Подскажите пожалуйста, как это правильно сделать?