Membership History

Hi Brian, you can try the following code snippet to find the members who were active on a given date in the past. I am assuming customers whose initiation date and expiry date range includes the given date were active on this date. I have added some sample columns. You can add other columns needed by you. There might be a better way to find the active customers on a given date in the past.

--Example: Find all the customers active on 01/01/2001

DECLARE @date DATETIME

SET @date='01/01/2001'

SELECT

      a.customer_no,

      [name]=ISNULL(b.fname+' ','')+ISNULL(b.mname+' ','')+ISNULL(b.lname,''),

      init_dt=CONVERT(CHAR(10),a.init_dt,101),

      expr_dt=CONVERT(CHAR(10),a.expr_dt,101),

      orig_expr_dt=CONVERT(CHAR(10),a.orig_expiry_dt,101),

      inception_dt=CONVERT(CHAR(10),a.inception_dt,101)

FROM

      tx_cust_membership a

      INNER JOIN t_customer b ON a.customer_no=b.customer_no

WHERE DATEDIFF(DAY,a.init_dt,@date)>=0

      AND DATEDIFF(DAY,ISNULL(orig_expiry_dt,expr_dt),@date)<=0

ORDER by a.customer_no

Hope this helps,

Hello all,

Normal 0 false false false MicrosoftInternetExplorer4

Does anyone have a report/sql for historical membership data? (e.g. all members who were active on a given date in the past)

thanks

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the forum or visit the site to search, read and post to the forums.

Hi Brian, you can try the following code snippet to find the members who were active on a given date in the past. I am assuming customers whose initiation date and expiry date range includes the given date were active on this date. I have added some sample columns. You can add other columns needed by you. There might be a better way to find the active customers on a given date in the past.

--Example: Find all the customers active on 01/01/2001

DECLARE @date DATETIME

SET @date='01/01/2001'

SELECT

      a.customer_no,

      [name]=ISNULL(b.fname+' ','')+ISNULL(b.mname+' ','')+ISNULL(b.lname,''),

      init_dt=CONVERT(CHAR(10),a.init_dt,101),

      expr_dt=CONVERT(CHAR(10),a.expr_dt,101),

      orig_expr_dt=CONVERT(CHAR(10),a.orig_expiry_dt,101),

      inception_dt=CONVERT(CHAR(10),a.inception_dt,101)

FROM

      tx_cust_membership a

      INNER JOIN t_customer b ON a.customer_no=b.customer_no

WHERE DATEDIFF(DAY,a.init_dt,@date)>=0

      AND DATEDIFF(DAY,ISNULL(orig_expiry_dt,expr_dt),@date)<=0

ORDER by a.customer_no

Hope this helps,

Hello all,

Normal 0 false false false MicrosoftInternetExplorer4

Does anyone have a report/sql for historical membership data? (e.g. all members who were active on a given date in the past)

thanks

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the forum or visit the site to search, read and post to the forums.