July 22, 2014 at 8:28 pm
I need some help with a script to count the total amount of members each month of current year.
I have a table with 3 columns:
MemberID, Effdat, Expdat
101 1/1/2013 6/30/2014
102 2/1/2014 7/31/2014
103 5/1/2013 1/31/2014
103 5/1/2014 12/31/9999
Can somebody help me with it?
Thanks,
Jian
July 23, 2014 at 11:33 am
The easiest way to do this is to have a calendar table. So I'd do something like this:
DECLARE @members TABLE
(
MemberID INT,
EffDate DATE,
ExpDate DATE
);
INSERT INTO @members
(MemberID, EffDate, ExpDate)
VALUES
(101, '1/1/2013', '6/30/2014'),
(102, '2/1/2014', '7/31/2014'),
(103, '5/1/2013', '1/31/2014'),
(103, '5/1/2014', '12/31/9999');
WITH calendar
AS (
SELECT
DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (
SELECT
NULL
)), '19990101') AS StartDate,
DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (
SELECT
NULL
)) + 1, '19990101') AS EndDate,
YEAR(DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (
SELECT
NULL
)), '19990101')) AS theYear,
MONTH(DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (
SELECT
NULL
)), '19990101')) AS theMonth
FROM
sys.all_objects AS AO
)
SELECT
TheYear,
TheMonth,
COUNT(M.MemberID) AS members
FROM
calendar AS Y
JOIN @members AS M
ON (
M.ExpDate >= Y.StartDate AND
M.EffDate <= Y.StartDate
) AND
(
M.EffDate <= Y.EndDate AND
M.ExpDate >= Y.EndDate
)
WHERE
Y.StartDate < DATEADD(YEAR, 1, GETDATE())
GROUP BY
Y.TheYear,
Y.TheMonth
ORDER BY
Y.theYear,
Y.theMonth;
This does more than the current year, but you should be able to figure out how to control that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply