CODEWITHSUNDEEP
phpmysqldatabase
Boon Yao Tan
Boon Yao Tan

Reputation: 85

SQL count distinct visits of a customer with opening hours from 10am to 6am

I am writing a customer loyalty software for a club that opens from 10am to 6am everyday. The data is store in MYSQL and I'd like to count the customer's total visits for the month.

I am using count(distinct(date)) but if the player came at 5pm and stayed till 3am with 2 transactions at 10pm and 2am. It will be counted as 2 visits instead of 1.

I have a transaction table with the columns listed below:

ps: anything in the brackets () is not real data. I get about 2000 transactions a day. I am also able to change the table structure 

 Transaction_ID | Date(not Date/Time) | Customer_ID | Item | price | timestamp
   1            | 11-06-2015    (6pm) | Jane        | drink| 2.00  | 156165166
   2            | 09-06-2015    (2pm) | Jane        | drink| 2.00  | 1433858493
   3            | 10-06-2015    (3am) | Jane        | drink| 2.00  | 1433906073
   4            | 06-06-2015    (6pm) | Jane        | drink| 2.00  | 156165166

Current code returns {4, Jane}. The answer I'm looking for is {3,Jane}. Transaction {2,3} should be considered as one visit

SELECT count(distinct(Date)) as visit, Customer_ID 
FROM transaction  
GROUP BY Customer_ID 
WHERE timestamp BETWEEN $timestamp1 AND $timestamp2

$timestamp1 = strtotime("first day of february +10am");
$timestamp2 = strtotime("first day of march +6am");

How do you suggest to accurately count the total visits below? I am able to change the table structure from Date to Date/time.

The easiest answer with least changes to my codes.

SELECT count(DISTINCT(DATE(DATE_SUB(from_unixtime(timestamp),INTERVAL 6 HOUR))) as visit, Customer_ID 
FROM transaction  
GROUP BY Customer_ID 
WHERE timestamp BETWEEN $timestamp1 AND $timestamp2

Upvotes: 7

Views: 551

Answers (2)

valex
valex

Reputation: 24134

The easiest way is to shift your datetime (date,timestamp?) field back for 6 hours in a SQL statement and then you will get an interval in one day from 4AM to 12PM:

DISTINCT(DATE(DATE_SUB(dt,INTERVAL 6 HOUR)))

SQLFiddle demo

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

Here is the code you need:

SELECT
  Customer_ID 'Customer ID'
  , COUNT(DISTINCT visit) as 'Visits per month'
  , MONTH(visit) 'Month'
  , YEAR(visit) 'Year'
FROM
  (SELECT
    *
    , CASE 
        WHEN (t_timestamp > Date_StartDate AND t_timestamp < Date_EndDate)
          THEN d_date
        WHEN (t_timestamp < Date_StartDate)
          THEN date_add(d_date, INTERVAL -1 DAY)
       END 'visit'
  FROM
    (SELECT *
      , DATE_ADD(CAST(d_date AS DATETIME), INTERVAL 10 HOUR) Date_StartDate
      , DATE_ADD(DATE_ADD(cast(d_date AS DATETIME), INTERVAL 6 HOUR), INTERVAL 1 DAY) Date_EndDate
    FROM transactions) Results
   ) Results
GROUP BY customer_id, month(visit), year(visit)

Also, here is a SQLFiddle with the results of the code.

I haven't used the exact format for your Customer_ID (I've used INTEGER instead of VARCHAR) and didn't use the exact dates you used in your example, but obviously it should work for anything.

Consider adjusting the name of the columns used in my query to the appropriate column names and you should be fine.

Upvotes: 0

Related Questions