如何计算活动和非活动成员 [英] How to count active and inactive member

问题描述

我有一个会员数据库。

字段：姓氏，名字，中间名，paymentDate，有效期（月）。

  DECLARE   @ Currendate   as   date  
  DECLARE   @ ACTIVEDATE   AS   DATE  
  DECLARE   @ INACTIVEDATE   AS   DATE  
  SET  @ Currendate = GETDATE（）
  SET   @ INACTIVEDATE  = DATEADD（月，-24， @ Currendate ）
  SET   @ ACTIVEDATE  = DATEADD（DAY，+ 1， @ INACTIVEDATE ）

for active

 < span class =code-keyword> SELECT  COUNT（*） AS  TOTAL 
 来自成员
  WHERE  DATEADD（月，+有效期，付款日期）> =  @ ACTIVEDATE  

表示非活动

  SELECT  COUNT （*） AS 总计
 来自成员
  WHERE  DATEADD（MONTH，+ validity，paymentDate）< =  @ INACTIVEDATE  

i希望添加这个条件：

如果有效期> = 24则有效期= 24

如果有效期< = 12则有效期= 12

如果有效期在12到24之间，那么有效期=有效期（例如：有效期= 18）

解决方案

我希望，我理解你...

   -    active  
  SELECT  COUNT（*） AS  TOTAL 
  FROM 成员
  WHERE  DATDIFF（MONTH，GETDATE（），paymentDate） < =  12  
   -   无效 
  SELECT  COUNT（*） AS  TOTAL 
  FROM 成员
  WHERE  DATDIFF（MONTH，GETDATE（），paymentDate）> =  24  

详情请见：日期（SQL） [ ^ ]

I have a member database.
field: familyname, firstname, middlename, paymentDate, validity(months).

DECLARE @Currendate as date
DECLARE @ACTIVEDATE AS DATE
DECLARE @INACTIVEDATE AS DATE
SET @Currendate= GETDATE()
SET @INACTIVEDATE = DATEADD(MONTH, -24, @Currendate)
SET @ACTIVEDATE = DATEADD(DAY, +1, @INACTIVEDATE)

for active

SELECT COUNT (*) AS TOTAL
from Member
WHERE DATEADD(MONTH, +validity ,paymentDate) >= @ACTIVEDATE

for inactive

SELECT COUNT (*) AS TOTAL
from Member
WHERE DATEADD(MONTH, +validity,paymentDate) <= @INACTIVEDATE

i want to add this condition:
if validity >= 24 then validity =24
if validity <= 12 then validity = 12
if validity between 12 and 24 then validity = validity(ex: validity=18)

解决方案

I hope, i understand you well...

--active
SELECT COUNT (*) AS TOTAL
FROM Member
WHERE DATDIFF(MONTH, GETDATE(), paymentDate) <= 12 
--inactive
SELECT COUNT (*) AS TOTAL
FROM Member
WHERE DATDIFF(MONTH, GETDATE(), paymentDate) >= 24 

For further information, please see: DATEDIFF (SQL)[^]

这篇关于如何计算活动和非活动成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！