on 05-08-2018 9:24 PM
Hi Experts, Could you help me to write below query for fatching data s below :
no of users who have not placed an order in 7 days.
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.
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
5 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.