sql - How do I create a moving snapshot of pass holder information in my database -
i have membership database want create additional table in track how our membership changes on time (on month-by-month basis) full 12 months. i'm unsure best database design use.
our members have subscriptions start date , and end date. each month have few new members, ie. start date subscription falls in month. similarly, each month have few members leave, ie. end date subscription falls in month. remainder of our members, current full month, ie. start date of subscription falls before given month , end date of subscription falls after given month.
i have data in table created:
subscriptionsnaphot memberid int subscriptiontype varchar startdate datetime enddate datetime a member can appear more once in data if have more 1 subscription.
i'd supplement table include indication of status of member past 12 months. example, let's suppose member joined 9 months ago , held 6 month pass , left. status pass 12 months be: i,i,i,n,c,c,c,c,c,e,i,i i=inactive, n=new, c=current , e=expired.
a naive design might add 12 columns table, 1 each of past 12 months , update them using queries.
my questions:
- what's design represent moving snapshot
- what corresponding queries fill snapshot data (assuming have subscription data outlined above)
i'm not dealing large amount of data, nor require normalised design. i'm after that's simple create and extract data from. regenerate data on first of every month past 12 months.
i'm using sql server 2008, i'd prefer db agnostic solution if possible.
i write table clone of membership table, stripped of irrelevant information.
i'm gonna piggy on aspnet_membership table since know. let's call "asp_membership_audit".
i create table put unique datestamp (one time). let's call "tageroni" (long name "tag") add column asp_membership_audit table fk pk tageroni table.
then, once month, run job throws row tageroni table. , copy membership table (over aspnet_membership_audit table), tageroni fk. put date stamp in "audit" table, don't using time stamps unique identifier.....a int, bigint or uuid preference.
then have data need generate reports. if come "super clever" now, need may change. capturing audit data , correctly, can create "reports" single column piped values whenever want.
here concept. queries @ end of rudimentary @ best, long data captured, can create reports later.
but basically, tageroni table , clone table, you'll have perfect snapshot of "what did data on first of month of whatever month".......
if exists (select * dbo.sysobjects id = object_id(n'[dbo].[aspnet_membership_audit]') , objectproperty(id, n'isusertable') = 1) begin drop table [dbo].[aspnet_membership_audit] end go if exists (select * dbo.sysobjects id = object_id(n'[dbo].[tageroni]') , objectproperty(id, n'isusertable') = 1) begin drop table [dbo].[tageroni] end go create table [dbo].[tageroni] ( tageroniuuid [uniqueidentifier] not null default newsequentialid() , tageroniname varchar(64) not null , tageronidatestamp datetime not null ) go alter table dbo.tageroni add constraint pk_tageroni_tageroniuuid primary key clustered (tageroniuuid) go alter table dbo.tageroni add constraint ck_tageroni_tageroniname_unique unique (tageroniname) go create table [dbo].[aspnet_membership_audit]( aspnet_membership_audit_uuid [uniqueidentifier] not null default newsequentialid() , tageroniuuid [uniqueidentifier] not null, /* 3 columns below user, , "status" flags i'm interested in */ [userid] [uniqueidentifier] not null, [isapproved] [bit] not null, [islockedout] [bit] not null ) go alter table dbo.aspnet_membership_audit add constraint pk_aspnet_membership_audit_uuid primary key clustered (aspnet_membership_audit_uuid) go alter table [dbo].[aspnet_membership_audit] check add foreign key([tageroniuuid]) references [dbo].[tageroni] ([tageroniuuid]) go /* once month, run */ insert dbo.tageroni ( tageroniuuid , tageroniname , tageronidatestamp ) select '11111111-1111-1111-1111-111111111111' , 'my first tag, 2013' , '01/01/2013' union select '22222222-2222-2222-2222-222222222222' , 'my second tag, 2013' , '02/01/2013' union select '33333333-3333-3333-3333-333333333333' , 'my third tag, 2013' , '03/01/2013' /* run on jan 1, 2013 */ insert [dbo].[aspnet_membership_audit]( [userid] , tageroniuuid , [isapproved] , [islockedout] ) select '11111111-1111-1111-1111-111111111111' , userid , [isapproved] , [islockedout] [dbo].[aspnet_membership] /* run on feb 1, 2013 */ insert [dbo].[aspnet_membership_audit]( [userid] , tageroniuuid , [isapproved] , [islockedout] ) select '22222222-2222-2222-2222-222222222222' , userid , [isapproved] , [islockedout] [dbo].[aspnet_membership] /* run on march 1, 2013 */ insert [dbo].[aspnet_membership_audit]( [userid] , tageroniuuid , [isapproved] , [islockedout] ) select '33333333-3333-3333-3333-333333333333' , userid , [isapproved] , [islockedout] [dbo].[aspnet_membership] go select derivedjan.userid , derivedjan.[isapproved] janisapproved , derivedfeb.[isapproved] febisapproved , derivedmarch.[isapproved] marisapproved (select * [dbo].[aspnet_membership_audit] tageroniuuid = '11111111-1111-1111-1111-111111111111') derivedjan join (select * [dbo].[aspnet_membership_audit] tageroniuuid = '22222222-2222-2222-2222-222222222222') derivedfeb on derivedjan.userid = derivedfeb.userid join (select * [dbo].[aspnet_membership_audit] tageroniuuid = '33333333-3333-3333-3333-333333333333') derivedmarch on derivedjan.userid = derivedmarch.userid edit-------------
here "sliding" caveat............
if exists (select * dbo.sysobjects id = object_id(n'[dbo].[aspnet_membership_audit]') , objectproperty(id, n'isusertable') = 1) begin drop table [dbo].[aspnet_membership_audit] end go if exists (select * dbo.sysobjects id = object_id(n'[dbo].[tageroni]') , objectproperty(id, n'isusertable') = 1) begin drop table [dbo].[tageroni] end go create table [dbo].[tageroni] ( tageroniuuid [uniqueidentifier] not null default newsequentialid() , tageroniname varchar(64) not null , tageronidatestamp datetime not null , tageronimonthsintothepast int not null ) go alter table dbo.tageroni add constraint pk_tageroni_tageroniuuid primary key clustered (tageroniuuid) go alter table dbo.tageroni add constraint ck_tageroni_tageroniname_unique unique (tageroniname) go create table [dbo].[aspnet_membership_audit]( aspnet_membership_audit_uuid [uniqueidentifier] not null default newsequentialid() , tageroniuuid [uniqueidentifier] not null, /* 3 columns below user, , "status" flags i'm interested in */ [userid] [uniqueidentifier] not null, [isapproved] [bit] not null, [islockedout] [bit] not null ) go alter table dbo.aspnet_membership_audit add constraint pk_aspnet_membership_audit_uuid primary key clustered (aspnet_membership_audit_uuid) go alter table [dbo].[aspnet_membership_audit] check add foreign key([tageroniuuid]) references [dbo].[tageroni] ([tageroniuuid]) go /* once month, run */ /* , adjust tageronimonthsintothepast value "sliding" value */ insert dbo.tageroni ( tageroniuuid , tageroniname , tageronidatestamp , tageronimonthsintothepast) select '11111111-1111-1111-1111-111111111111' , 'my first tag, 2013' , '01/01/2013' , 4 union select '22222222-2222-2222-2222-222222222222' , 'my second tag, 2013' , '02/01/2013' , 3 union select '33333333-3333-3333-3333-333333333333' , 'my third tag, 2013' , '01/01/2013' , 2 /* run on jan 1, 2013 */ insert [dbo].[aspnet_membership_audit]( [userid] , tageroniuuid , [isapproved] , [islockedout] ) select '11111111-1111-1111-1111-111111111111' , userid , [isapproved] , [islockedout] [dbo].[aspnet_membership] /* run on feb 1, 2013 */ insert [dbo].[aspnet_membership_audit]( [userid] , tageroniuuid , [isapproved] , [islockedout] ) select '22222222-2222-2222-2222-222222222222' , userid , [isapproved] , [islockedout] [dbo].[aspnet_membership] /* run on march 1, 2013 */ insert [dbo].[aspnet_membership_audit]( [userid] , tageroniuuid , [isapproved] , [islockedout] ) select '33333333-3333-3333-3333-333333333333' , userid , [isapproved] , [islockedout] [dbo].[aspnet_membership] go select twomonthsagoderived.userid , twomonthsagoderived.[isapproved] twomonthsoldisapproved , threemonthsagoderived.[isapproved] threemonthsoldisapproved , fourmonthsagoderived.[isapproved] fourmonthsoldisapproved (select aud.* [dbo].[aspnet_membership_audit] aud join dbo.tageroni tag on aud.tageroniuuid = tag.tageroniuuid tageronimonthsintothepast = 2 ) twomonthsagoderived join (select aud.* [dbo].[aspnet_membership_audit] aud join dbo.tageroni tag on aud.tageroniuuid = tag.tageroniuuid tageronimonthsintothepast = 3) threemonthsagoderived on twomonthsagoderived.userid = threemonthsagoderived.userid join (select aud.* [dbo].[aspnet_membership_audit] aud join dbo.tageroni tag on aud.tageroniuuid = tag.tageroniuuid tageronimonthsintothepast = 4) fourmonthsagoderived on twomonthsagoderived.userid = fourmonthsagoderived.userid
Comments
Post a Comment