T-SQL 计算百分比 [英] T-SQL Calculate Percentage

问题描述

我有一个包含客户(健康俱乐部)的 SQL Server (2000) 数据库.

I have a SQL Server (2000) database with customers (health club).

客户每年都会加入.我希望能够计算更新会员资格的客户百分比(随着时间的推移和过去的一年).活动状态字段表明他们是否仍然是会员.
JoinDate 不会改变.当会员续订时，有一个 ActiveDate 和一个DueDate(当客户需要续订时)，DueDate 比活动日期早 1 年.我想要做的是尝试了解续订客户的百分比，如果可能的话，按过去几年或整体细分.有任何想法吗.谢谢.客户表中的字段包含:

The customers join on a yearly basis. I want to be able to calculate the percentage of customers who renew there membership (over time and an the past current year). The Active Status field indicates whether they are still a member.
The JoinDate doesn't change. When a member renews there is an ActiveDate and also a DueDate (when a customer is due to renew), the DueDate is 1 year ahead of the Active Date. What I am looking to do is try to get some sense of the % percentage of the customers that renew, broken down by past years if possible or overall. Any ideas. Thanks. The fields in the customer table contain:

    CustomerID    JOIN Date    Cancel Date   ActiveStatus ActiveDate   DueDate

     12345        10/01/2011   NULL          Yes         10/01/2012  10/01/2013

     12346        1/1/2010     12/31/2011    No          1/1/2010     1/1/2011

推荐答案

您可以这样计算每位客户的续订次数..

You can calculate the number of renewals per customer like this..

select
    *,
    datediff(yyyy, joindate, 
         case activestatus when 'yes' then  getdate() else CancelDate end
    )
from yourtable

这篇关于T-SQL 计算百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！