1 (edited by bemorhona-qt 2017-04-14 17:26:09)

Topic: Recursive Dates

Dear Dmitriy and all MVD fans!
Help me please with this problem.
First of all I have a SQL-query on a Button like shown below.

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

SELECT

"dateValue" as Сана
( Select SUM(kat) from profile where
       (CASE WHEN {ComboBox1} =-1 then
        (CASE WHEN {ComboBox2} =-1 then 1=1 end) else
        (CASE WHEN {ComboBox2} =-1 then profile.id Between
         (Select Min(id) from profile where profile.id_department = {ComboBox1}) and
         (Select Max(id) from profile where profile.id_department = {ComboBox1})
        else profile.id= {Combobox2} end) end)
) as Катхо

FROM dates

The above query works perfect, but if I change it like this it does not work.

( Select SUM(kat) from profile where
       WITH RECURSIVE dates(dateValue) AS (
  VALUES({DateTimePicker1})
  UNION ALL
  SELECT date(dateValue, '+1 day')
  FROM dates
  WHERE dateValue < {DateTimePicker2}
)

SELECT

"dateValue" as Сана
( Select SUM(kat) from profile where
       (CASE WHEN {ComboBox1} =-1 then
        (CASE WHEN {ComboBox2} =-1 then 1=1 end) else
        (CASE WHEN {ComboBox2} =-1 then profile.id Between
         (Select Min(id) from profile where profile.id_department = {ComboBox1}) and
         (Select Max(id) from profile where profile.id_department = {ComboBox1})
        else profile.id= {Combobox2} end) end) / 31
) as Катхо

FROM dates

In the first query the result is shown in the TableGrid, in my case its a number. In the second query I want to divide this number by 31, but without success. Help me with this case. Thank you.

P.S. The SQL-query is in Form2.Button3.

Post's attachments

Attachment icon demoFond_fixed_2.zip 8.74 kb, 447 downloads since 2017-04-14