SQL find count within date range

user_6901319 0
Viewed 69

I have a table as below:

+--------+------------+------------+----------------+---------------+------------+
| UserID | CaseNumber | CourseName | CourseAttended | Training_Date | Created_On |
+--------+------------+------------+----------------+---------------+------------+
| 1      | 101        | X          | T              | 01/01/2020    | 01/12/2019 |
| 1      | 103        | X          | T              | 01/05/2020    | 01/12/2018 |
| 1      | 105        | X          | T              | 01/02/2020    | 03/03/2020 |
| 2      | 101        | X          | T              | 05/01/2020    | 03/01/2020 |
| 3      | 103        | X          | T              | 05/05/2020    | 01/01/2020 |
| 4      | 104        | X          | T              | 04/01/2020    | 01/08/2020 |
| 5      | 101        | X          | T              | 01/07/2020    | 01/02/2020 |
| 5      | 105        | X          | T              | 01/08/2020    | 01/12/2019 |
| 5      | 109        | X          | T              | 01/09/2020    | 01/10/2019 |

I want to calculate the count of CaseNumber where the training date is less than Creation Date and also count cases where the training date >creation date. The users have multiple training dates only consider the latest training date of the user. The count should be only for the cases which have been created +-3 months of training date. For example, if the creation date is 2018 and training date is 2020 it should not be counted. The cases only needs to be counted when within +-3 months of training date.

mysql sql
2 Answers

Final One Shot Query

Based on your decription, I came up with the following by implementing your requirements step by step:

SELECT count(1)
FROM
  (SELECT d.userid,
          d.caseNumber,
          d.training_Date,
          d.created_on,
          period_diff(date_format(d.training_Date, '%Y%m'), date_format(d.created_on, '%Y%m')) AS monthsDiff
   FROM DATA d
   INNER JOIN
     (SELECT userId,
             max(training_date) AS lastTraining
      FROM DATA
      GROUP BY userid)ltu ON d.userid = ltu.userid
   AND d.training_Date = ltu.lastTraining) d
WHERE (monthsDiff >=0
       AND monthsDiff < 4)
  AND (monthsDiff <0
       AND monthsDiff > -4);

Solution Using Views (Including Fake Data)

Here is the data:

create table data (userid int,
              caseNumber int,
              training_Date date,
              created_On date);

Insert Statements:

insert into data( userid, caseNumber, training_Date, created_on) values(                                1   ,   101 ,STR_TO_DATE('01-01-2020','%d-%c-%Y'),STR_TO_DATE('01-12-2019','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values(                                1   ,   103 ,STR_TO_DATE('01-05-2020','%d-%c-%Y'),STR_TO_DATE('01-12-2018','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values(                                1   ,   105 ,STR_TO_DATE('01-02-2020','%d-%c-%Y'),STR_TO_DATE('03-03-2020','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values(                                2   ,   101 ,STR_TO_DATE('05-01-2020','%d-%c-%Y'),STR_TO_DATE('03-01-2020','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values(                                3   ,   103 ,STR_TO_DATE('05-05-2020','%d-%c-%Y'),STR_TO_DATE('01-01-2020','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values(                                4   ,   104 ,STR_TO_DATE('04-01-2020','%d-%c-%Y'),STR_TO_DATE('01-08-2020','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values(                                5   ,   101 ,STR_TO_DATE('01-07-2020','%d-%c-%Y'),STR_TO_DATE('01-02-2020','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values(                                5   ,   105 ,STR_TO_DATE('01-08-2020','%d-%c-%Y'),STR_TO_DATE('01-12-2019','%d-%c-%Y'));
insert into data( userid, caseNumber, training_Date, created_on) values(                                5   ,   109 ,STR_TO_DATE('01-09-2020','%d-%c-%Y'),STR_TO_DATE('01-10-2019','%d-%c-%Y'));

Get only the last training dates:

create View lastTrainingByUser as 
select userId, max(training_date) as lastTraining from data group by userid;

Get only the cases with the last training dates. Also calculate month difference between training and created.

create view lastTrainingDatesByUser as
select d.userid, d.caseNumber, d.training_Date, d.created_on,
period_diff(date_format(d.training_Date, '%Y%m'), date_format(d.created_on, '%Y%m')) as monthsDiff
from data d inner join lastTrainingByUser ltu
on d.userid = ltu.userid AND d.training_Date = ltu.lastTraining;

The query without the count

select d.userid, d.caseNumber, d.training_Date, d.created_on 
,d.monthsDiff
from lastTrainingDatesByUser d
where 
(monthsDiff >=0 AND monthsDiff < 4) AND
(monthsDiff <=0 AND monthsDiff > -4);

The query with the count:

select count(1)
from lastTrainingDatesByUser d
where 
(monthsDiff >=0 AND monthsDiff < 4) AND
(monthsDiff < 0 AND monthsDiff > -4);

SQLFiddle: http://sqlfiddle.com/#!9/9184b0/7

user_16884410

Based on the description, this sounds like a window function to get the maximum training date and then some aggregation:

select sum(training_date < create_date) as num_before,
       sum(training_date > create_date) as num_after       
from (select t.*,
             max(training_date) over (partition by user) as max_td
     )
where training_date = max_training_date and
      training_date >= create_date - interval 3 month and
      training_date <= create_date + interval 3 month ;
user_11440350
Related