You’re offline. This is a read only version of the page.
Mabuhay!
Is there any chance that I can filter Total Visits field in RMS 2.0? For example, I have a customer a who register last month. On the next day He purchase so it counts on visit as 1. Daily He uses to visit the store and purchase items. After the end of the month when we check for his total visits it will post 30 visits because he purchase for the whole month.
My query for this report is I want to know his total visit just for the 15 days onwards, or filtering it via date, is it possible?
Thank you,
Chris
*This post is locked for comments
Don't forget to change the dates
The first query will show the results by store, so the number of visits is per store and any customer might appear more than once
The second query doesn't show the store, so the number of visits is per the whole company
select store.name,accountnumber,company,count(*) as Visits,sum(total)
from [transaction]
inner join customer on customer.id=customerid
inner join store on [transaction].storeid=store.id
where time >='2012-01-01'
and time < '2012-09-01'
group by store.name,accountnumber,company
order by 4 desc
select accountnumber,company,count(*) as Visits,sum(total)
from [transaction]
inner join customer on customer.id=customerid
where time >='2012-01-01'
and time < '2012-09-01'
group by accountnumber,company
order by 3 desc
A Hassan
RMS Leaders
Mabuhay Sir Hassan!
buying like i consider transaction.
1. account number, storeid, sales, company name
2. by date transaction
3. by times of total visit (excluding waht is not filtered by date)
Thank you,
Chris
What do u mean by "by buying"?
Please explain what u need as below: -
1- columns to retrieve
2- filters to be used
3- sorting order
Mabuhay Sir Hassan,
Thank you Sir so much, Id like to look for how many time they visit the stores by buying wiht my filter dates.
Id like to know who among the top visitors who visits our store for July - Aug 2012. Withouth including their previous visit before July 2012.
Thank you,
Chris
tell me what u wanna get and I will post the query for u
SELECT COUNT(*) AS Expr1,
Customer.AccountNumber,
Customer.TotalVisits,
Customer.EmailAddress,
[Transaction].TransactionNumber,
[Transaction].StoreID
FROM [Transaction]
INNER JOIN Store ON Store.ID = [Transaction].StoreID
INNER JOIN Customer ON Customer.ID = [Transaction].CustomerID AND Customer.StoreID = [Transaction].StoreID
WHERE [Transaction].[Time] between CONVERT(DATETIME, '2008-01-01') AND CONVERT(DATETIME, '2008-12-31') AND
Customer.AccountNumber <> ''
GROUP BY Customer.AccountNumber,
Customer.TotalVisits,
Customer.EmailAddress,
[Transaction].TransactionNumber,
[Transaction].StoreID
ORDER BY Customer.AccountNumber
is it counting the total visit by the query of the filtered date?
Mabuhay Sir Hassan,
Yes Sir, we are using SQL 2000. Im running the query on console root. I noticed it always "timeout expired" and if I run it on SQL Analyzer it will show the result.
Thank you very much sir :)
Strange enough!
u r using SQL 2000, right?
r u using the query with store or HQ database?
A Hassan
RMS Leaders
Mabuhay Sir Hasan,
Thank you very much for the quick response. Script is running on our Store Operation database but if I will try that code for the query in Enterprise Manager it always timeoutexpired? Or my resources needs a lot more memory or my query needs it?
By the way, Im sorry the script is running on tables query field is that the reason i lost my resources. And if i tried it on sql query analyzer it runs and it will produce the report?
Thank you,
Chris
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (