Bdtjtk

I’d like to create a query that checks if there is any duplicated ID that has been used as per date.

To this end, 60 days prior to, not the subsequent to, the baseline date, should be reviewed for any duplication.
A table example is as below.

CREATE TABLE SampleTable (

   pKey INT PRIMARY KEY,

   personalID INT NOT NULL,

   createDate DATETIME NOT NULL,

   value INT NULL

);

The baseline date corresponds to DATE(createDate) whereas ID to be checked for duplication is personallD.
Information needed herein can boil down to the number of today’s data and that of duplicated data.
The number of today’s data can be easily queried as follows.

SELECT

   COUNT(*)

FROM SampleTable

WHERE

   DATE(createDate) = DATE(NOW())

Out of today’s data, in addition, the number of duplicated data can be checked as follows.

SELECT

   COUNT(*)

FROM (

   SELECT

      personalID,

      COUNT(*)

   FROM SampleTable

   WHERE

      DATEDIFF(NOW(), trDate) <= 60

   GROUP BY personalID HAVING COUNT(*) > 1

) AS T

In conclusion, all I need to do is get the number of total data by date and the number of data that have the same personalID during the previous days.

[Sample Data]

pKey    personalID  createDate  value

1       1           2018-01-01  100

2       2           2018-01-01  300

3       3           2018-01-01  500

7       1           2018-01-02  100

8       2           2018-01-02  200

9       3           2018-01-02  200

10      4           2018-01-02  100

11      5           2018-01-02  100

12      3           2018-01-03  200

13      4           2018-01-03  100

14      5           2018-01-03  100

15      6           2018-01-03  50

[Desired result]

date        totalCount  duplicated

2018-01-01  3           0

2018-01-02  5           3

2018-01-03  4           3

If you want the ids that have multiple rows in the past 60 days:

select personid

from sampledata

where trdate >= curdate() - interval 60 day

group by personid

having count(*) >= 2;

If you want to also insist that the personid appears on the most recent date:

select personid

from sampledata

where trdate >= curdate() - interval 60 day

group by personid

having count(*) >= 2 and date(max(trdate)) = curdate();

EDIT:

This seems to be what you want, assuming no duplicates on a given day

select trdate, count(*) as num_persons,

       sum(num_dups > 0) as num_dups

from (select sd.*,

             (select count(*)

              from sampledata sd2

              where sd2.personid = sd.personid and

                    sd2.trdate < sd.trdate and

                    sd2.trdate >= sd.trdate - interval 60 day

             ) as num_dups

      from sampledata

     ) sd

group by trdate;

You can use the self join approach as well to find out this type of data. If you need to find out what ID repeated by comparing with previous dates , you can also use this approach.

Create table Testtbl (pkey int, personalID int, createddate date, value int); 



insert into Testtbl values 

(1  ,     1,           '2018-01-01' , 100) , 

(2 ,     2,           '2018-01-01' , 300) ,

(3  ,     3,           '2018-01-01' , 500) ,

(4  ,     1,           '2018-01-02' , 100) ,

(5  ,     2,           '2018-01-02' , 200) ,

(6  ,     3,           '2018-01-02' , 200) ,

(7  ,     4,           '2018-01-02' , 100) ,

(8  ,     5,           '2018-01-02' , 100) ,

(9  ,     3,           '2018-01-03' , 200) ,

(14  ,     3,           '2018-01-03' , 500) ,

(10  ,     4,           '2018-01-03' , 100) ,

(11  ,     5,           '2018-01-03' , 100) ,

(12  ,     6,           '2018-01-03' , 50),

(13  ,     6,           '2018-01-03' , 100)

Query: Left join will help to find out duplicated data without loss of total count. Distinct is to make sure that the same ID is not counted twice .

  select  t.createddate, count(Distinct t.pkey) TotalCount, 

  case when t.Createddate > t1.createddate  

  then Count(distinct t1.PersonalID) + case when t.Createddate = 

  t1.createddate and 

  t.personalID = t1.personalID  and t.pkey != t1.pkey then Count(distinct 

  t1.PersonalID) 

  else 0 end else 0     

  end   Duplicated   from Testtbl t 

  left join Testtbl t1 on t.personalID = t1.personalID 

                    and t.Createddate >= t1.Createddate and t.pkey != t1.pkey 

                    and DATEDIFF(t1.Createddate, t.Createddate) <= 60 

 Group by t.createddate

Output:

createddate  TotalCount Duplicated

2018-01-01     3         0

2018-01-02     5         3

2018-01-03     6         5