Click here to Skip to main content
15,562,816 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a member database.
field: familyname, firstname, middlename, paymentDate, validity(months).
SQL
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
SQL
SELECT COUNT (*) AS TOTAL
from Member
WHERE DATEADD(MONTH, +validity ,paymentDate) >= @ACTIVEDATE

for inactive
SQL
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)
Posted
Updated 12-May-15 23:11pm
v3
Comments
Sergey Alexandrovich Kryukov 12-May-15 23:36pm    
And..?
—SA
Member 11685941 12-May-15 23:44pm    
i'm having problem with my query. i dont how to add the condition. need to count the active and inactive members.
King Fisher 13-May-15 2:24am    
not clear.
Member 11685941 13-May-15 2:40am    
i want to count the active and inactive member. base on the current date.

activedate is -24months on the current date. active member whos paymentdate+validity(months) must be greater than or equal to active date.

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

hope this make it clear.

please help me guys. thanks for all the response.
King Fisher 13-May-15 3:05am    
Post your table Script with some dummy records to understand what are you trying to do,that may be better to understand .

1 solution

I hope, i understand you well...
SQL
--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)[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900