Sql-server – Joining on same table without duplicating data in a field or using c#

sql servert-sql

I have the following table (a few fields omitted): 

CREATE TABLE [Device](
    [DeviceId] [int] IDENTITY(1,1) NOT NULL,
    [AuthorizedDate] [datetime2](0) NULL,
    [DeauthorizedDate] [datetime2](0) NULL,
    [MemberId] [int] NOT NULL,
    ...
 CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED 
(
    [DeviceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

The table is used to track the devices of each member – they're now only allowed to have 5 devices active at the same time and can only make 3 changes in
a 90 day period.
This table is not sufficient to enforce the 3 changes rule (I won't go into the specifics).
To that end I have decided to create a log table:

CREATE TABLE [SwapLog](
    [SwapLogId] [int] IDENTITY(1,1) NOT NULL,
    [MemberId] [int] NOT NULL,
    [DeauthorizationDate] [datetime] NOT NULL,
    [DeauthorizedDeviceId] [int] NOT NULL,
    [AuthorizationDate] [datetime] NULL,
    [AuthorizedDeviceId] [int] NULL,
 CONSTRAINT [PK_SwapLog] PRIMARY KEY CLUSTERED 
(
    [wapLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The plan is to populate this table when a device is de-authorized, and then to fill in the AuthorizationDate and Id when a new device is subsequently authorized

I need to populate the SwapLog table so that I can properly enforce the rules.
So, if the Device table has the following records:

DeviceId    AuthorizedDate          DeauthorizedDate        MemberId
60          2010-11-09 08:06:38     2010-11-10 00:00:00     2389
1795        2012-01-04 08:57:12     2013-09-02 15:32:59     2389
4467        2011-02-21 14:25:24     2013-09-02 15:33:52     2389
4733        2011-03-02 20:11:54     NULL                    2389
11835       2011-11-02 11:38:33     NULL                    2389
12707       2011-11-23 16:35:45     NULL                    2389
46663       2012-04-13 13:14:56     NULL                    2389
56651       2012-07-05 20:10:05     NULL                    2389
86247       2012-10-11 12:50:20     NULL                    2389
114493      2012-12-05 19:46:30     NULL                    2389
123867      2013-03-15 19:39:38     NULL                    2389
127595      2013-08-01 18:17:16     NULL                    2389
522826      2013-10-09 16:20:31     NULL                    2389
554246      2013-10-28 11:23:29     NULL                    2389
574637      2013-11-07 16:48:36     NULL                    2389
609731      2013-11-25 12:22:47     NULL                    2389
651808      2013-12-19 11:25:35     NULL                    2389
775957      2014-02-10 13:05:51     NULL                    2389
778288      2014-02-11 10:52:19     NULL                    2389
824090      2014-03-12 14:34:28     NULL                    2389
180842      2013-02-14 07:00:37     2013-08-23 09:49:58     225897
180857      2013-02-14 08:56:42     2013-08-23 09:50:01     225897
181974      2013-02-15 09:44:42     2013-08-23 09:50:04     225897
182026      2013-02-15 12:03:02     2013-08-23 09:50:07     225897
183139      2013-02-16 04:14:20     2014-04-11 09:44:47     225897
186993      2013-02-18 01:42:30     2014-04-11 09:44:55     225897
188424      2013-02-19 11:17:27     2014-04-11 09:45:02     225897
190263      2013-02-21 01:00:11     2014-04-11 09:45:10     225897
359562      2013-07-08 10:07:29     2014-04-11 09:45:17     225897
411095      2013-07-31 09:48:24     2013-08-16 15:08:39     225897
452576      2013-08-20 01:35:52     2013-08-23 09:50:09     225897
462050      2013-08-26 04:21:57     2013-09-06 16:16:14     225897
503700      2013-09-25 08:05:14     2013-10-15 15:02:43     225897
532174      2013-10-16 06:35:07     2013-10-16 12:33:32     225897
532177      2013-10-16 06:45:48     2013-10-16 12:33:35     225897
532301      2013-10-16 11:32:48     2013-10-16 12:33:38     225897
532340      2013-10-16 12:49:47     2013-10-23 13:13:29     225897
533323      2013-10-17 07:12:42     2013-10-23 13:13:37     225897
588192      2013-11-14 04:17:38     2013-12-17 09:16:34     225897
444955      2013-11-20 10:50:22     2013-12-11 09:09:05     225897
600932      2013-11-21 02:06:15     2013-12-17 12:58:32     225897
613745      2013-11-28 09:29:26     2014-02-13 17:21:25     225897
626805      2013-12-05 23:50:03     2014-02-13 17:21:35     225897
637629      2013-12-11 09:09:06     2014-02-13 17:21:42     225897
649121      2013-12-17 09:16:35     2014-02-13 17:21:38     225897
649238      2013-12-17 12:58:32     2014-02-13 17:21:31     225897
445006      2014-02-13 17:25:16     2014-02-14 13:41:05     225897
781025      2014-02-13 17:59:50     2014-02-14 13:41:11     225897
780992      2014-02-18 19:53:23     2014-03-20 08:30:12     225897
793156      2014-02-19 12:19:09     2014-03-20 09:12:42     225897
804491      2014-02-26 11:16:06     2014-03-20 10:40:43     225897
833582      2014-03-19 05:15:26     2014-04-11 09:45:57     225897
834404      2014-03-20 07:58:53     2014-04-11 09:45:52     225897
532426      2014-03-20 08:30:12     2014-04-11 09:45:30     225897
834414      2014-03-20 09:12:43     2014-04-11 09:45:46     225897
834446      2014-03-20 10:40:43     2014-04-11 09:45:36     225897
870418      2014-04-14 12:58:32 NULL    225897
871297      2014-04-15 07:16:26 NULL    225897
898213      2014-05-08 06:11:23 NULL    225897

I would expect the SwapLog table to have the following:

SwapLogId       MemberId        DeauthorizationDate     DeauthorizedDeviceId    AuthorizationDate       AuthorizedDeviceId
1               2389            2010-11-10 00:00:00     60                      NULL                    NULL
2               2389            2013-09-02 15:32:59     1795                    2013-10-09 16:20:31     522826
3               2389            2013-09-02 15:33:52     4467                    2013-10-28 11:23:29     554246

This way I can tell when device 1795 was deauthorized and exactly which device was authorized in its place.

The problem is that the query I'm using to migrate the data is going wrong. I'm getting duplicated data in the authorizationDate field:

MemberId    DeauthorizedDate        DeauthorizedDeviceId    AuthorizedDate          AuthorizedDeviceId
2389        2013-09-02 15:32:59     1795                    2013-10-09 16:20:31     522826
2389        2013-09-02 15:32:59     1795                    2013-10-28 11:23:29     554246
2389        2013-09-02 15:32:59     1795                    2013-11-07 16:48:36     574637
2389        2013-09-02 15:32:59     1795                    2013-11-25 12:22:47     609731
2389        2013-09-02 15:33:52     4467                    2013-10-09 16:20:31     522826
2389        2013-09-02 15:33:52     4467                    2013-10-28 11:23:29     554246
2389        2013-09-02 15:33:52     4467                    2013-11-07 16:48:36     574637
2389        2013-09-02 15:33:52     4467                    2013-11-25 12:22:47     609731

This is the query:

SELECT
    d.[MemberId]
    ,d.[DeauthorizedDate]
    ,d.[DeviceId] AS [DeauthorizedDeviceId]
    ,d2.[AuthorizedDate]
    ,d2.[DeviceId] AS [AuthorizedDeviceId]
FROM
    [Personalization].[Device] d
        LEFT JOIN [Personalization].[Device] d2 ON d.MemberId = d2.MemberId
WHERE
    d.DeauthorizedDate < d2.AuthorizedDate
    AND d2.AuthorizedDate <= DATEADD(day, 90, d.DeauthorizedDate)
    AND d.MemberId = 2389
ORDER BY MemberId, d.DeauthorizedDate

I could write something with c# to do this, but I suspect this will take ages as I have a large number of device records. Any help appreciated.

Best Answer

There are a couple of ways to approach this, the following is a recursive solution.

To perform optimally, it requires a couple of indexes on the Devices table, which if not generally useful may be removed after building the SwapLog rows:

CREATE UNIQUE NONCLUSTERED INDEX uq1
ON Personalization.Device 
    (MemberId, AuthorizedDate)
INCLUDE
    (DeviceId);

CREATE UNIQUE NONCLUSTERED INDEX uq2 
ON Personalization.Device 
    (MemberId, DeauthorizedDate) 
INCLUDE 
    (DeviceId) 
WHERE 
    DeauthorizedDate IS NOT NULL;

The recursive query itself is:

WITH R AS
(
    -- Anchor: Earliest deauthorization per member
    -- and its associated authorization, if any
    SELECT
        FirstDeauthPerMember.MemberId,
        FirstDeauthPerMember.DeauthorizedDate,
        DeauthorizedDeviceId = FirstDeauthPerMember.DeviceId,
        Auth.AuthorizedDate,
        AuthorizedDeviceId = Auth.DeviceId,
        RunningAuthDate = ISNULL(Auth.AuthorizedDate, FirstDeauthPerMember.DeauthorizedDate)
    FROM
    (
        SELECT
            DeauthPerMember.MemberId,
            DeauthPerMember.DeauthorizedDate,
            DeauthPerMember.DeviceId
        FROM
        (
            SELECT 
                D.MemberId,
                D.DeauthorizedDate,
                D.DeviceId,
                RN = ROW_NUMBER() OVER (
                    PARTITION BY D.MemberId 
                    ORDER BY D.DeauthorizedDate)
            FROM Personalization.Device AS D
            WHERE
                D.DeauthorizedDate IS NOT NULL
        ) AS DeauthPerMember
        WHERE
            DeauthPerMember.RN = 1
    ) AS FirstDeauthPerMember
    OUTER APPLY
    (
        SELECT TOP (1)
            D2.AuthorizedDate,
            D2.DeviceId
        FROM Personalization.Device AS D2
        WHERE
            D2.MemberId = FirstDeauthPerMember.MemberId
            AND D2.AuthorizedDate > FirstDeauthPerMember.DeauthorizedDate
            AND D2.AuthorizedDate <= DATEADD(DAY, 90, FirstDeauthPerMember.DeauthorizedDate)
        ORDER BY
            D2.AuthorizedDate ASC
    ) AS Auth

    UNION ALL

    -- Recursive part: next deauthorization in order
    -- and its associated authorization, if any
    SELECT
        D6.MemberId,
        D6.DeauthorizedDate,
        D6.DeauthorizedDeviceId,
        D6.AuthorizedDate,
        D6.AuthorizedDeviceId,
        D6.RunningAuthDate
    FROM 
    (
        SELECT
            D5.MemberId,
            D5.DeauthorizedDate,
            D5.DeauthorizedDeviceId,
            D5.AuthorizedDate,
            D5.AuthorizedDeviceId,
            D5.RunningAuthDate
        FROM 
        (
            SELECT
                D.MemberId,
                D.DeauthorizedDate,
                DeauthorizedDeviceId = D.DeviceId,
                D4.AuthorizedDate,
                AuthorizedDeviceId = D4.DeviceId,
                RN = ROW_NUMBER() OVER (
                    ORDER BY D.DeauthorizedDate),
                RunningAuthDate = ISNULL(D4.AuthorizedDate, R.RunningAuthDate)
            FROM Personalization.Device AS D
            JOIN R
                ON R.MemberId = D.MemberId
            OUTER APPLY
            (
                SELECT
                    D3.AuthorizedDate,
                    D3.DeviceId
                FROM 
                (
                    SELECT
                        D2.AuthorizedDate,
                        D2.DeviceId,
                        RN = ROW_NUMBER() OVER (
                            ORDER BY D2.AuthorizedDate)
                    FROM Personalization.Device AS D2
                    WHERE
                        D2.MemberId = D.MemberId
                        AND D2.AuthorizedDate > ISNULL(R.AuthorizedDate, R.RunningAuthDate)
                        AND D2.AuthorizedDate > D.DeauthorizedDate
                        AND D2.AuthorizedDate <= DATEADD(DAY, 90, D.DeauthorizedDate)
                ) AS D3
                WHERE
                    D3.RN = 1
            ) AS D4
            WHERE
                D.DeauthorizedDate IS NOT NULL
                AND D.DeauthorizedDate > R.DeauthorizedDate
        ) AS D5
        WHERE
            D5.RN = 1
    ) AS D6
)
SELECT
    R.MemberId,
    R.DeauthorizedDate,
    R.DeauthorizedDeviceId,
    R.AuthorizedDate,
    R.AuthorizedDeviceId
FROM R
ORDER BY
    R.MemberId,
    R.DeauthorizedDate
OPTION (MAXRECURSION 0);

Output:

Output

SQLFiddle (using dbo schema due to SQLFiddle permissions restrictions)

Execution plan:

Execution plan

Related Question