Frequency of Address changes in number of days SQL-CodePudding

Hi I'm trying to find out how frequently a business would change their address. I've got two tables one with trading address and the other with office address. The complicated part is one id will have several sequence numbers. I need to find out the difference between one address's create date and another address create date.

Trading address table

ID	Create_date	Seq_no	Address
1	2002-03-23	1	20 bottle way
1	2002-05-23	2	12 sunset blvd
2	2003-01-14	1	76 moonrise ct

Office address table

ID	Create_date	Seq_no	Address
1	2004-02-13	1	12 paper st
2	2005-03-01	1	30 pencil way
2	2005-04-01	2	25 mouse rd
2	2005-08-01	3	89 glass cct

My result set will be

Difference	NumberOfID's
30 days	1
60 days	1
120 days	1
Other	2

CodePudding user response：

I think I solved it. Steps are

I did an union and created a separate column to find out actual sequence no for the union set.
Used LEAD function to create a separate column of to bring up the date.
Date difference to find out the actual difference between id's

Case statement to categorize the days and counting the id's

WITH BASE AS (

SELECT ID,SEQ_NO,CREATE_DATE
FROM TradingAddress
UNION ALL
SELECT ID,SEQ_NO,CREATE_DATE
FROM OfficeAddress
),

WORKINGS AS  (
              SELECT ID,CREATE_DATE,
              DENSE_RANK() OVER (PARTITION BY ID ORDER BY CREATE_DATE ASC) AS SNO,
              LEAD(CREATE_DATE) OVER (PARTITION BY ID ORDER BY CREATE_DATE) AS REF_DATE,
              DATEDIFF(DAY,CREATE_DATE,LEAD(CREATE_DATE) OVER (PARTITION BY ID ORDER BY CREATE_DATE)) AS DATE_DIFFERENCE
              FROM BASE
             ),

WORKINGS_2 AS (
         SELECT *,
         CASE WHEN DATE_DIFFERENCE BETWEEN 1 AND 30 THEN '1-30 DAYS'
         WHEN DATE_DIFFERENCE BETWEEN 31 AND 60 THEN '31-60 DAYS'
         WHEN DATE_DIFFERENCE BETWEEN 61 AND 90 THEN '61-90 DAYS'
         WHEN DATE_DIFFERENCE BETWEEN 91 AND 120 THEN '91-120 DAYS'ELSE 'MORE THAN 120 DAYS' 
         END AS DIFFERENCE_DAYS
         FROM WORKINGS
         WHERE REF_DATE IS NOT NULL
      )

            SELECT DIFFERENCE_DAYS,COUNT(DIFFERENCE_DAYS) AS NUMBEROFIDS
            FROM WORKINGS_2
            GROUP BY DIFFERENCE_DAYS

CodePudding user response：

you can do this in this way

SELECT DATEDIFF(day,t1.create_date,t2.create_date) AS 'yourdats', Count (*) as ids FROM test1 t1 join test2 t2 on t1.id = t2.id GROUP BY DATEDIFF(day,t1.create_date,t2.create_date)