May 7, 2012 at 6:52 pm
I need to create member staus based on two variables (MbrID and MCDate). The logic is explained in at the bottom.
Data look like:
Use MYDatabase
Declare @CurrentDate varchar(10)
Set @CurrentDate= '05/01/2012'---Changes at the beggining of each month
CREATE TABLE Members
(
id INT,
MCDate datetime
);
INSERT INTO Members
VALUES(1, '1/1/2012')
INSERT INTO Members )
VALUES(1, '2/1/2012')
INSERT INTO Members )
VALUES(1, '3/1/2012')
INSERT INTO Members )
VALUES(1, '4/1/2012')
INSERT INTO Members )
VALUES(1, '5/1/2012')
INSERT INTO Members )
VALUES(2, '2/1/2012')
INSERT INTO Members )
VALUES(2, '3/1/2012')
INSERT INTO Members )
VALUES(2, '4/1/2012')
INSERT INTO Members )
VALUES(2, '5/1/2012')
INSERT INTO Members )
VALUES(3, '1/1/2012')
INSERT INTO Members )
VALUES(3, '2/1/2012')
INSERT INTO Members )
VALUES(3, '3/1/2012')
INSERT INTO Members )
VALUES(4, '4/1/2012')
INSERT INTO Members )
VALUES(4, '5/1/2012')
INSERT INTO Members )
VALUES(5, '2/1/2012')
INSERT INTO Members )
VALUES(5, '3/1/2012')
INSERT INTO Members )
VALUES(5, '4/1/2012')
INSERT INTO Members )
VALUES(5, '5/1/2012')
INSERT INTO Members )
VALUES(6, '5/1/2012','New')
The Result should look like this with Status assigned as New, Active, nad Inactive.
CREATE TABLE MemberStatus
(
id INT,
MCDate datetime,
Status VARCHAR(50)
);
INSERT INTO MemberStatus
VALUES(1, '1/1/2012','New')
INSERT INTO MemberStatus
VALUES(1, '2/1/2012','Active')
INSERT INTO MemberStatus
VALUES(1, '3/1/2012','Active')
INSERT INTO MemberStatus
VALUES(1, '4/1/2012','Active')
INSERT INTO MemberStatus
VALUES(1, '5/1/2012','Active')
INSERT INTO MemberStatus
VALUES(2, '2/1/2012','New')
INSERT INTO MemberStatus
VALUES(2, '3/1/2012','Active')
INSERT INTO MemberStatus
VALUES(2, '4/1/2012','Active')
INSERT INTO MemberStatus
VALUES(2, '5/1/2012','Active')
INSERT INTO MemberStatus
VALUES(3, '1/1/2012','New')
INSERT INTO MemberStatus
VALUES(3, '2/1/2012','Inactive')
INSERT INTO MemberStatus
VALUES(3, '3/1/2012','Inactive')
INSERT INTO MemberStatus
VALUES(4, '4/1/2012','New')
INSERT INTO MemberStatus
VALUES(4, '5/1/2012','Active')
INSERT INTO MemberStatus
VALUES(5, '2/1/2012','New')
INSERT INTO MemberStatus
VALUES(5, '3/1/2012','Active')
INSERT INTO MemberStatus
VALUES(5, '4/1/2012','Active')
INSERT INTO MemberStatus
VALUES(5, '5/1/2012','Active')
INSERT INTO MemberStatus
VALUES(6, '5/1/2012','New')
Logic:
Purpose: To populate Column C with Member Status Categories as Active, Inactive, New Member
Logic:
New Member:
1. A member is "New" for the month (MCDate) he/she joined and either "Active" or "Inactive" for the rest of the followed months. Example MbrIDs 1,2,4,5, and 6
2. If a member joined in the current date (in this case May 1st 2012) and has no previous records (based on MbrID and MCDate), then the Member is "New". Example: MbrID 6
Active Member:
1. if a member has consective MCDate from his/her Join Date thru current date (F2), then the member is "New" on the Join Date and "Active" for the rest. Example: MbrIDs 1,2,4,5
Inactive Member:
If a member doesn’t have consecutive MCDate from his/her join date, then the member is "New" on the Join Date and "Inactive" for the rest. Example: MbrID 3
Note: Current Date (varies each month)
5/1/2012 : Current Date varies each month and should be set as the begginng of current month
Than you for your help,
Helal
May 7, 2012 at 11:41 pm
It would be very nice of you and very helpful to everyone who might provide a solution to your requirement if you could have a look at the link mentioned in my signature and post the data accordingly(DDL, sample data and required result set).
I don't think anyone would be happy to download your document.
Please help us in helping you.
May 16, 2012 at 11:45 am
Thank you for bringing this to my attention. Does this look better now?
Helal
May 16, 2012 at 11:48 am
helal.mobasher 13209 (5/16/2012)
Thank you for bringing this to my attention. Does this look better now?Helal
Thanks for the ddl and sample data. Based on your sample data what should the desired output be?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2012 at 11:52 am
Sean Lange (5/16/2012)
helal.mobasher 13209 (5/16/2012)
Thank you for bringing this to my attention. Does this look better now?Helal
Thanks for the ddl and sample data. Based on your sample data what should the desired output be?
The second table is the desried results, Sean
May 16, 2012 at 11:54 am
ColdCoffee (5/16/2012)
Sean Lange (5/16/2012)
helal.mobasher 13209 (5/16/2012)
Thank you for bringing this to my attention. Does this look better now?Helal
Thanks for the ddl and sample data. Based on your sample data what should the desired output be?
The second table is the desried results, Sean
:blush:
/slaps himself upside the head for not reading all the details.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2012 at 12:07 pm
it's in the post right after test data. I indicated that the result should like this:
The Result should look like this with Status assigned as New, Active, nad Inactive.
CREATE TABLE MemberStatus
(
id INT,
MCDate datetime,
Status VARCHAR(50)
);
INSERT INTO MemberStatus
VALUES(1, '1/1/2012','New')
INSERT INTO MemberStatus
VALUES(1, '2/1/2012','Active')
INSERT INTO MemberStatus
VALUES(1, '3/1/2012','Active')
INSERT INTO MemberStatus
VALUES(1, '4/1/2012','Active')
INSERT INTO MemberStatus
VALUES(1, '5/1/2012','Active')
INSERT INTO MemberStatus
VALUES(2, '2/1/2012','New')
INSERT INTO MemberStatus
VALUES(2, '3/1/2012','Active')
INSERT INTO MemberStatus
VALUES(2, '4/1/2012','Active')
INSERT INTO MemberStatus
VALUES(2, '5/1/2012','Active')
INSERT INTO MemberStatus
VALUES(3, '1/1/2012','New')
INSERT INTO MemberStatus
VALUES(3, '2/1/2012','Inactive')
INSERT INTO MemberStatus
VALUES(3, '3/1/2012','Inactive')
INSERT INTO MemberStatus
VALUES(4, '4/1/2012','New')
INSERT INTO MemberStatus
VALUES(4, '5/1/2012','Active')
INSERT INTO MemberStatus
VALUES(5, '2/1/2012','New')
INSERT INTO MemberStatus
VALUES(5, '3/1/2012','Active')
INSERT INTO MemberStatus
VALUES(5, '4/1/2012','Active')
INSERT INTO MemberStatus
VALUES(5, '5/1/2012','Active')
INSERT INTO MemberStatus
VALUES(6, '5/1/2012','New')
Thanks,
Helal
May 16, 2012 at 12:36 pm
Something like this?
; WITH Numbered AS
(
SELECT MB.id
,MB.MCDate
,RN = ROW_NUMBER() OVER (PARTITION BY MB.id ORDER BY MB.MCDate)
FROM Members MB
)
, MinMaxCount AS
(
SELECT MB.id
,Status = CASE WHEN MAX(MB.MCDate) <> @CurrentDate THEN 'Inactive'
WHEN DATEDIFF(MM,MIN(MB.MCDate),MAX(MB.MCDate)) + 1 = COUNT(*) THEN 'Active'
ELSE 'Inactive'
END
FROM Members MB
GROUP BY MB.id
)
SELECT Base.id
,BASE.MCDate
,MemStatus = CASE WHEN Base.RN = 1 THEN 'New'
ELSE CrsApp.Status
END
FROM Numbered Base
OUTER APPLY (
SELECT MMC.Status
FROM MinMaxCount MMC
WHERE MMC.id = Base.id
) CrsApp
May 16, 2012 at 4:50 pm
Awesome...Thank you so much. Worked like a charm.
Helal
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply