This topic is locked

insert date variable into sql

9/30/2020 3:55:23 AM
PHPRunner General questions
M
m.salahi author

hi every one,,
i want to create tow variable to this sql :
dateof(sv.Inspection_start) >= var_01

dateof(sv.Inspection_start) <= var_02

----------------------------------------------------
select m.municipality_name, u.user_full_name,

count(distinct sv.store_visit_id) total,

ROUND(nvl(sum(Inspection_end - Inspection_start) 24 60,0),3) as dur_Per_min,

ROUND(nvl(avg(Inspection_end- Inspection_start) 24 60,0),3) as average_per_min
from store_visits sv, users u, municipalities m
where sv.visit_status = 1

and sv.Inspector = u.user_id

and u.municipality_id = m.municipality_id

and dateof(sv.Inspection_start) >= '2020-01-01'

and dateof(sv.Inspection_start) <= '2020-09-30'

and (Inspection_end - Inspection_start) * 60 < 600.0
group by sv.Inspector,m.municipality_name, u.user_full_name

order by m.municipality_name, sv.Inspector,u.user_full_name
------------------------------------
how i can do that

Admin 9/30/2020

Instead of '2020-09-30' you can use now()

M
m.salahi author 10/1/2020

i want the user to enter the date
like i create an IReport

see the image
[media]https://imgur.com/e0lVTKN[/media]

Admin 10/1/2020

Use Search panel or Advanced Search and let users use BETWEEN search against this date field.