How to select only those records which are present in given all DATE range

user_11297619 0
Viewed 72

I wanted to select only data which are present for all date with repeated data count.

Example My table data is :

user_id row_created
8SRWS3hMR 2020-12-14 00:13:31
8SRWS3hMR 2020-12-14 00:35:06
8SRWS3hMR 2020-12-14 12:11:37
8SRWS3hMR 2020-12-14 13:16:27
8SRWS3hMR 2020-12-14 16:30:00
8SRWS3hMR 2020-12-14 19:25:11
8SRWS3hMR 2020-12-14 19:27:07
8SRWS3hMR 2020-12-15 17:14:06
8SRWS3hMR 2020-12-16 14:53:54

And if I select the date range between 2020-12-14 to 2020-12-17 then the output should be null.

And if I select the date range between 2020-12-14 to 2020-12-16 then the output should be below

user_id total row_created
8SRWS3hMR 7 2020-12-14 00:13:31
8SRWS3hMR 1 2020-12-15 17:14:06
8SRWS3hMR 1 2020-12-16 14:53:54

I had tried the below query

select user_id, count(user_id) as total, row_created from reward
where user_id = '8SRWS3hMR' AND DATE(row_created) BETWEEN '2020-12-14' AND '2020-12-17'
group by DATE(row_created);

But as per my requirement, the output should come null because in BETWEEN date range I have pass '2020-12-17'.

Please ignore the grammar mistakes.

mysql sql
3 Answers

You can use the HAVING clause as follows:

SELECT USER_ID,
       COUNT(USER_ID) AS TOTAL,
       ROW_CREATED
  FROM REWARD
 WHERE USER_ID = '8SRWS3hMR'
   AND DATE(ROW_CREATED) BETWEEN '2020-12-14' AND '2020-12-17'
 GROUP BY DATE(ROW_CREATED) 
 HAVING COUNT(DISTINCT DATE(ROW_CREATED)) = datediff('2020-12-17', '2020-12-14') + 1;

--UPDATE

SELECT * FROM
(SELECT USER_ID,
       COUNT(USER_ID) AS TOTAL,
       ROW_CREATED,
       COUNT(DISTINCT DATE(ROW_CREATED)) OVER (PARTITION BY USER_ID) AS CNT
  FROM REWARD
 WHERE USER_ID = '8SRWS3hMR'
   AND DATE(ROW_CREATED) BETWEEN '2020-12-14' AND '2020-12-17'
 GROUP BY USER_ID, DATE(ROW_CREATED) 
) T WHERE CNT = datediff('2020-12-17', '2020-12-14') + 1
user_115656290

This is a little complicated, because you want the summary by date. You can use window functions:

SELECT r.*
FROM (SELECT USER_ID, DATE(ROW_CREATED) as date, COUNT(*) AS TOTAL,
             MIN(ROW_CREATED) as ROW_CREATED,
             COUNT(*) OVER (PARTITION BY USER_ID) as num_days,
             DATEDIFF(x.end_date, x.start_date) + 1 AS total_days
      FROM REWARD R CROSS JOIN
           (SELECT DATE('2020-12-14') as START_DATE, DATE('2020-12-17') as END_DATE
           ) params
      WHERE USER_ID = '8SRWS3hMR' AND
            ROW_CREATED >= x.start_date AND
            ROW_CREATE <= x.end_date + interval 1 day
      GROUP BY DATE(ROW_CREATED) 
     ) R
WHERE num_days = total_days;

The subquery summarizes by day. It includes two counts:

  • The number of distinct days for the USER_ID.
  • The total number of days.

To avoid entering the same dates more than once, these are defined in a subquery. Also note that the date comparisons are re-arranged. Instead of using the DATE() function, inequalities are used. This makes the expression more compatible with indexes.

user_11440350

select user_id, count(user_id) as total, row_created from reward where user_id = '8SRWS3hMR' AND DATE(row_created) >= '2020-12-14' AND DATE(row_created) <= '2020-12-17' group by DATE(row_created) having count(user_id) >= DATEDIFF( row_created, '2020-12-17')

user_128885720
Related