Flexible Search

[Former Member]

Hi Experts , Could you please help me in below query if possible :

No. of users who have not placed an order in 5 days ".

SELECT COUNT(*) FROM {User as u} WHERE {o.modifiedtime} < NOW() - INTERVAL 5 DAY ({{ SELECT {pk} from {Order as o} WHERE {o.user} = {u.pk} }})

Thanks in advance ....

[Former Member]

Hi Deepak, Thanks for your reply . but its difficult for me as you said ....i tried but its not worked still .

[Former Member]

if second one is working fine you can just count the total no of user id you are getting in result .it will be unique values and you can minus the no of result from total no of users brother.....

[Former Member]

Hi Deepak, Thanks for your reply again . 2nd query provided is working as u said but 1st one throwing some error whenever try to excute : "SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near ".": line 1 col 21 (at pos 21)" If you suggest more on this , it would be too help for me .

[Former Member]

select count{u.uid} from {order as o join user as u on {o.user}={u.pk}} where {o.creationTime} >'2018-05-09 00:00:00'

it will give you the no of users who have orders for the last 5 days.minus this no from the total no of users you will have your solution.

it is to see the user id which have placed order in the last 5 days. select distinct{u.uid} from {order as o join user as u on {o.user}={u.pk}} where {o.creationTime} >'2018-05-05 00:00:00'

[Former Member]

Hi Deepak, Thanks for your reply . But its still not working ....Where condition not working perfectly for not placed order in last 5 days .

[Former Member]

can we use date like this "05-05-2018" ?