August 11, 2015 at 8:54 am
I am trying to find the members who are having monthstartdate continuesly for 11 months ;
here in my example 123 wont have monthstartdate continuesly for 11 months it has break for february '2014-02-01'; where as 222 and 223 has continus 11 months , so i need to pull such members
Can someone please help me in finding out the members continuesly(enrolled) having 11 months.
Below is the sample data i am refering. Thanks,Kalyan
memid MonthStartDate
123 2014-01-01
123 2014-03-01
1232014-04-01
1232014-05-01
1232014-06-01
1232014-07-01
1232014-08-01
1232014-09-01
1232014-10-01
1232014-11-01
1232014-12-01
222 2014-01-01
222 2014-02-01
2222014-03-01
2222014-04-01
2222014-05-01
2222014-06-01
2222014-07-01
2222014-08-01
2222014-09-01
2222014-10-01
2222014-11-01
223 2014-02-01
223 2014-03-01
2232014-04-01
2232014-05-01
2232014-06-01
2232014-07-01
2232014-08-01
2232014-09-01
2232014-10-01
2232014-11-01
2232014-12-01
August 11, 2015 at 9:55 am
I am adding table and insert scipts to a sample table for ease of understanding. thanks
create table #tabA
(
memid varchar(10),
monthStartDate datetime
)
--drop table #tabA
Insert into #tabA (memid,monthStartDate)
values(123,'2014-01-01'),
(123,'2014-03-01'),
(123,'2014-04-01'),
(123,'2014-05-01'),
(123,'2014-06-01'),
(123,'2014-07-01'),
(123,'2014-08-01'),
(123,'2014-09-01'),
(123,'2014-10-01'),
(123,'2014-11-01'),
(123,'2014-12-01'),
(222, '2014-01-01'),
(222 , '2014-02-01'),
(222, '2014-03-01'),
(222 , '2014-04-01'),
(222, '2014-05-01'),
(222, '2014-06-01'),
(222 , '2014-07-01'),
(222 , '2014-08-01'),
(222 , '2014-09-01'),
(222, '2014-10-01'),
(222, '2014-11-01'),
(223 , '2014-02-01'),
(223 , '2014-03-01'),
(223 , '2014-04-01'),
(223 , '2014-05-01'),
(223 , '2014-06-01'),
(223 , '2014-07-01'),
(223 , '2014-08-01'),
(223 , '2014-09-01'),
(223 , '2014-10-01'),
(223 , '2014-11-01'),
(223 , '2014-12-01')
select * from #tabA
August 11, 2015 at 10:06 am
Something like this?
with TestData as (
select memid, MonthStartDate
from
(values
(123,'2014-01-01'),
(123,'2014-03-01'),
(123,'2014-04-01'),
(123,'2014-05-01'),
(123,'2014-06-01'),
(123,'2014-07-01'),
(123,'2014-08-01'),
(123,'2014-09-01'),
(123,'2014-10-01'),
(123,'2014-11-01'),
(123,'2014-12-01'),
(222,'2014-01-01'),
(222,'2014-02-01'),
(222,'2014-03-01'),
(222,'2014-04-01'),
(222,'2014-05-01'),
(222,'2014-06-01'),
(222,'2014-07-01'),
(222,'2014-08-01'),
(222,'2014-09-01'),
(222,'2014-10-01'),
(222,'2014-11-01'),
(223,'2014-02-01'),
(223,'2014-03-01'),
(223,'2014-04-01'),
(223,'2014-05-01'),
(223,'2014-06-01'),
(223,'2014-07-01'),
(223,'2014-08-01'),
(223,'2014-09-01'),
(223,'2014-10-01'),
(223,'2014-11-01'),
(223,'2014-12-01'))dt(memid,MonthStartDate)),
BaseData as (
SELECT
*,
rn = row_number() over (partition by memid order by MonthStartDate)
FROM
TestData),
WorkingData as (
select
memid,
MonthStartDate,
RowCnt = count(*) over (partition by memid,dateadd(month,-rn,MonthStartDate))
from BaseData)
select
memid,
MonthStartDate
from
WorkingData
where
RowCnt >= 11;
August 11, 2015 at 12:37 pm
This is just a different variation of Lynn's solution...
IF OBJECT_ID('tempdb..#tabA') IS NOT NULL
DROP TABLE #tabA;
create table #tabA
(
memid varchar(10),
monthStartDate datetime
);
--drop table #tabA
Insert into #tabA (memid,monthStartDate)
values(123,'2014-01-01'),
(123,'2014-03-01'),
(123,'2014-04-01'),
(123,'2014-05-01'),
(123,'2014-06-01'),
(123,'2014-07-01'),
(123,'2014-08-01'),
(123,'2014-09-01'),
(123,'2014-10-01'),
(123,'2014-11-01'),
(123,'2014-12-01'),
(222, '2014-01-01'),
(222 , '2014-02-01'),
(222, '2014-03-01'),
(222 , '2014-04-01'),
(222, '2014-05-01'),
(222, '2014-06-01'),
(222 , '2014-07-01'),
(222 , '2014-08-01'),
(222 , '2014-09-01'),
(222, '2014-10-01'),
(222, '2014-11-01'),
(223 , '2014-02-01'),
(223 , '2014-03-01'),
(223 , '2014-04-01'),
(223 , '2014-05-01'),
(223 , '2014-06-01'),
(223 , '2014-07-01'),
(223 , '2014-08-01'),
(223 , '2014-09-01'),
(223 , '2014-10-01'),
(223 , '2014-11-01'),
(223 , '2014-12-01');
WITH GetRN AS (
SELECT
ta.memid,
ta.monthStartDate,
-1 * ROW_NUMBER() OVER (PARTITION BY ta.memid ORDER BY ta.monthStartDate) AS RN
FROM
#tabA ta
), GetStartOfConsecDates AS (
SELECT
gr.memid,
DATEADD(mm, gr.RN, gr.monthStartDate) AS StartOfConsecDates
FROM
GetRN gr
)
SELECT
gscd.memid,
COUNT(*) ConsecutiveMonthCount
FROM
GetStartOfConsecDates gscd
GROUP BY
gscd.memid,
gscd.StartOfConsecDates
HAVING
COUNT(*) >= 11;
August 11, 2015 at 1:33 pm
This is a faster version, because it only requires one sort.
with TestData as (
select memid, MonthStartDate
from
(values
(123,'2014-01-01'),
(123,'2014-03-01'),
(123,'2014-04-01'),
(123,'2014-05-01'),
(123,'2014-06-01'),
(123,'2014-07-01'),
(123,'2014-08-01'),
(123,'2014-09-01'),
(123,'2014-10-01'),
(123,'2014-11-01'),
(123,'2014-12-01'),
(222,'2014-01-01'),
(222,'2014-02-01'),
(222,'2014-03-01'),
(222,'2014-04-01'),
(222,'2014-05-01'),
(222,'2014-06-01'),
(222,'2014-07-01'),
(222,'2014-08-01'),
(222,'2014-09-01'),
(222,'2014-10-01'),
(222,'2014-11-01'),
(223,'2014-02-01'),
(223,'2014-03-01'),
(223,'2014-04-01'),
(223,'2014-05-01'),
(223,'2014-06-01'),
(223,'2014-07-01'),
(223,'2014-08-01'),
(223,'2014-09-01'),
(223,'2014-10-01'),
(223,'2014-11-01'),
(223,'2014-12-01'))dt(memid,MonthStartDate)),
BaseData as (
SELECT
*,
DATEDIFF(MONTH, LAG(MonthStartDate, 10) OVER(PARTITION BY memid ORDER BY MonthStartDate), MonthStartDate) AS MonthDiff
FROM TestData
)
select
memid,
MonthStartDate
from BaseData
WHERE MonthDiff = 10
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 14, 2015 at 12:27 pm
Thanks Jason, it works for me.
August 14, 2015 at 12:28 pm
Hi Drew,
Thanks for your reply but i am getting the below error.
"The Parallel Data Warehouse (PDW) features are not enabled."
Thanks,
kalyan.
August 14, 2015 at 1:00 pm
dhanekulakalyan (8/14/2015)
Hi Drew,Thanks for your reply but i am getting the below error.
"The Parallel Data Warehouse (PDW) features are not enabled."
Thanks,
kalyan.
LAG was introduced in SQL 2012. This error indicates that you are trying to run it on a SQL 2008 machine. Since this was posted in a SQL 2012 forum, I used functionality that was available in SQL 2012.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 14, 2015 at 3:33 pm
Sure, Not a problem... I will convert to SQL 2008. Anyway thank you for your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply