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.