Joining on same table without duplicating data in a field or using c#
sql-server add tag
CarllDev (imported from SE) 
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.
Top Answer
Paul White (imported from SE) 
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][1]

[**SQLFiddle**][2] (using dbo schema due to SQLFiddle permissions restrictions)

Execution plan:

![Execution plan][3]

  [1]: https://i.stack.imgur.com/s5xCn.png
  [2]: http://sqlfiddle.com/#!6/9d81c/1
  [3]: https://i.stack.imgur.com/kCKFz.png
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.