flexible search query

[former_member628401]

Hi Experts, Could you help me to write below query for fatching data s below :

1. no of users who have not placed an order in 7 days.

2. no of users created since go online

I was tried but not getting exact data as expected .

Can you please help me with this?

A query would be highly appreciated.

[former_member387866]

Hi Satish,
Post your queries and people might be able to help you improve them.
Regards,
Luke

[Former Member · Accepted solution]

Please check if these work in your situation:

no of users who have not placed an order in 7 days:

 SELECT COUNT(*) FROM 
 ({{
 SELECT DISTINCT({u.pk})
 FROM {User as u
   JOIN Order as o
   ON {u.pk} = {o.user}
 }
 WHERE {o.modifiedtime} < NOW() - INTERVAL 7 DAY
 }}) as x

no of users created since go online:

 SELECT  COUNT(*)
 FROM {User as u}
 WHERE {u.creationtime} >='2017-12-07 00:00:00.0' //date of go live

Hope this helps and Best Regards,

[former_member628401]

Hi Jakub,

Thanks for your reply again .

But its still throwing error : "sql syntax error : incorect syntax near "7" " .

Is there any other way to write date in flexible search query .....

Regards,

Satish

[former_member628401]

Hi All,

Could anyone help to modified below query provided by one of my friend "Jakub". It seems like good but still not working fine as expected and throwing syntax error because of date format etc .

no of users who have not placed an order in 7 days:

SELECT COUNT(*) FROM

({{ SELECT DISTINCT({u.pk}) FROM {User as u JOIN Order as o ON {u.pk} = {o.user} } WHERE {o.modifiedtime} < NOW() - INTERVAL 7 DAY }}) as x

[Former Member]

Hi Satish,

You could also try this, more simple query:

 SELECT COUNT(DISTINCT({u.pk}))
 FROM {User as u
  JOIN Order as o
  ON {u.pk} = {o.user}
 }
 WHERE {o.modifiedtime} < NOW() - INTERVAL 7 DAY

Best Regards,

[former_member628401]

Hi Jakub, Thanks for your reply . But getting exception like "Error "SAP DBTech JDBC: [257] (at ...): sql syntax error: line 1 col ... (at pos ..."'

Do you have any idea why its throwing exception like above ?

Please suggest if possible

[Former Member]

Hi sathish,

How is rasie a question in experts.first select topic and rasied question.need clarification