UsersMemberships.sql

USE StackOverflow;
GO
DROP TABLE IF EXISTS dbo.UsersMemberships;

CREATE TABLE dbo.UsersMemberships
  (Id INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
   UserId INT NOT NULL,
   StartDate DATETIME NOT NULL,
   EndDate DATETIME NOT NULL,
   CancelledEarlyDate DATETIME NULL);
GO
/*
Membership mix:
25% have started, but already expired.
    I'm using their CreationDate and LastAccessDate as-is.

25% have started, and will expire in the future.
    I'm using their CreationDate, but setting LastAccessDate to the future.

25% have not started yet, and will expire in the future.
	I'm setting both their CreationDate and LastAccessDate to the future.

25% started today and ended today, but at random times.
    A good chunk of these are going to have invalid start/end date/times,
	so if that's not appropriate for your own data, you can skip this.



25% have started, but already expired.
    I'm using their CreationDate and LastAccessDate as-is.
*/
INSERT INTO dbo.UsersMemberships(UserId, StartDate, EndDate)
  SELECT Id, CreationDate, LastAccessDate
  FROM dbo.Users
  WHERE Id % 4 = 0;
GO


/*
25% have started, and will expire in the future.
    I'm using their CreationDate, but setting LastAccessDate to the future.
*/
INSERT INTO dbo.UsersMemberships(UserId, StartDate, EndDate)
  SELECT Id, CreationDate, 
  CASE WHEN GETDATE() < DATEFROMPARTS(YEAR(GETDATE()),MONTH(LastAccessDate), DAY(LastAccessDate))
			THEN DATEFROMPARTS(YEAR(GETDATE()),MONTH(LastAccessDate), DAY(LastAccessDate))
		ELSE DATEFROMPARTS(YEAR(GETDATE()) + 1,MONTH(LastAccessDate), DAY(LastAccessDate)) END
  FROM dbo.Users
  WHERE Id % 4 = 3
    AND NOT(MONTH(LastAccessDate) = 2 AND DAY(LastAccessDate) > 28);
GO


/*
25% have not started yet, and will expire in the future.
	I'm setting both their CreationDate and LastAccessDate to the future.
*/
INSERT INTO dbo.UsersMemberships(UserId, StartDate, EndDate)
  SELECT Id, 
  CASE WHEN GETDATE() < DATEFROMPARTS(YEAR(GETDATE()),MONTH(LastAccessDate), DAY(LastAccessDate))
			THEN DATEFROMPARTS(YEAR(GETDATE()),MONTH(LastAccessDate), DAY(LastAccessDate))
		ELSE DATEFROMPARTS(YEAR(GETDATE()) + 1,MONTH(LastAccessDate), DAY(LastAccessDate)) END,
  DATEFROMPARTS(YEAR(GETDATE()) + 2,MONTH(LastAccessDate), DAY(LastAccessDate))
  FROM dbo.Users
  WHERE Id % 4 = 2
    AND NOT(MONTH(LastAccessDate) = 2 AND DAY(LastAccessDate) > 28);
GO



/*
25% started today and ended today, but at random times.
    A good chunk of these are going to have invalid start/end date/times,
	so if that's not appropriate for your own data, you can skip this.
*/
INSERT INTO dbo.UsersMemberships(UserId, StartDate, EndDate)
  SELECT Id,
  DATETIMEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()), DATEPART(hh, CreationDate), DATEPART(mi, CreationDate), DATEPART(ss, CreationDate), DATEPART(ms, CreationDate)),
  DATETIMEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()), DATEPART(hh, LastAccessDate), DATEPART(mi, LastAccessDate), DATEPART(ss, LastAccessDate), DATEPART(ms, LastAccessDate))
  FROM dbo.Users
  WHERE Id % 4 = 1;
GO

CREATE INDEX IX_StartDate_EndDate ON dbo.UsersMemberships(StartDate, EndDate) INCLUDE (Id, UserId);
CREATE INDEX IX_EndDate_StartDate ON dbo.UsersMemberships(EndDate, StartDate) INCLUDE (Id, UserId);
GO