Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-12-2018
12:14 PM

10-12-2018
12:14 PM

So basically for each account type for a particular customer i want a start and end date.

The account type can be repeated but then i still need a different start and end date for that account type for a particular customer

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-12-2018
01:28 PM

10-12-2018
01:28 PM

There may be a cleaner or more efficent way, but here's a solution:

Our final results may have more than one row for each customer / account pair if the service have ever lapsed. First, we used ordered analytics functions to find, for every customer / account pair, rows where the current date is not exactly one month from the previous or next row. This gives us dates of start or end of enrollment. Then, for each start, we look for the next end and do some math to coun the number of rows.

I have tested this on your sample data and it produces the desired results (except you have typo on the last row of sample input - 2016 instead of 2006).

SELECT CUSTOMER_ID ,ACCT_TYPE ,DT AS START_MONTH --LOOK AT ALL THE FOLLOWING ROWS, FIND THE MIN ROW THAT IS A SESSION END ,MIN(CASE WHEN SESSION_END = 1 THEN DT END) OVER( PARTITION BY CUSTOMER_ID,ACCT_TYPE ORDER BY DT ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS END_MONTH --SUBTRACT THE DATES AND ADD ONE TO GET ACOUNT OF MONTHS ,((END_MONTH - START_MONTH) MONTH) + INTERVAL '1' MONTH AS NUMB_MONTH FROM ( SELECT CUSTOMER_ID ,ACCT_TYPE , DT --FOR EACH ROW, CHECK IF IT IS ONE MONTH AFTER THE PREVIOUS --THIS WILL FIND ALL STARTING MONTHS ,CASE WHEN MIN(DT) OVER( PARTITION BY CUSTOMER_ID,ACCT_TYPE ORDER BY DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING --COMPAIR TO PREV ROW ) + INTERVAL '1' MONTH = DT THEN 0 ELSE 1 END AS SESSION_START --FOR EACH ROW, CHECK IF IT IS ONE MONTH AFTER THE NEXT ROW --THIS WILL FIND ALL ENDING MONTHS ,CASE WHEN MIN(DT) OVER( PARTITION BY CUSTOMER_ID,ACCT_TYPE ORDER BY DT ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING --COMPAIR TO NEXT ROW ) - INTERVAL '1' MONTH = DT THEN 0 ELSE 1 END AS SESSION_END FROM SAMP --ONLY NEED TO RETURN ROWS THAT ARE EITHER START OR ENDS OF EACH SESSION QUALIFY (SESSION_START = 1 OR SESSION_END = 1) ) AS X --ONLY KEEP ROWS WHERE THE SESSION STARTED QUALIFY SESSION_START = 1 ORDER BY 1,3,2

1 ACCEPTED SOLUTION

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-12-2018
01:14 PM

10-12-2018
01:14 PM

Re: Urgent Help....

Why not simply:

SELECT customer_id, acct_type,

min(the_date) as start_date,

max(the_date) as end_date,

end_date - start_date MONTH(4) as Total_Month,

Group By customer_id, acct_type

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-12-2018
01:28 PM

10-12-2018
01:28 PM

There may be a cleaner or more efficent way, but here's a solution:

Our final results may have more than one row for each customer / account pair if the service have ever lapsed. First, we used ordered analytics functions to find, for every customer / account pair, rows where the current date is not exactly one month from the previous or next row. This gives us dates of start or end of enrollment. Then, for each start, we look for the next end and do some math to coun the number of rows.

I have tested this on your sample data and it produces the desired results (except you have typo on the last row of sample input - 2016 instead of 2006).

SELECT CUSTOMER_ID ,ACCT_TYPE ,DT AS START_MONTH --LOOK AT ALL THE FOLLOWING ROWS, FIND THE MIN ROW THAT IS A SESSION END ,MIN(CASE WHEN SESSION_END = 1 THEN DT END) OVER( PARTITION BY CUSTOMER_ID,ACCT_TYPE ORDER BY DT ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS END_MONTH --SUBTRACT THE DATES AND ADD ONE TO GET ACOUNT OF MONTHS ,((END_MONTH - START_MONTH) MONTH) + INTERVAL '1' MONTH AS NUMB_MONTH FROM ( SELECT CUSTOMER_ID ,ACCT_TYPE , DT --FOR EACH ROW, CHECK IF IT IS ONE MONTH AFTER THE PREVIOUS --THIS WILL FIND ALL STARTING MONTHS ,CASE WHEN MIN(DT) OVER( PARTITION BY CUSTOMER_ID,ACCT_TYPE ORDER BY DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING --COMPAIR TO PREV ROW ) + INTERVAL '1' MONTH = DT THEN 0 ELSE 1 END AS SESSION_START --FOR EACH ROW, CHECK IF IT IS ONE MONTH AFTER THE NEXT ROW --THIS WILL FIND ALL ENDING MONTHS ,CASE WHEN MIN(DT) OVER( PARTITION BY CUSTOMER_ID,ACCT_TYPE ORDER BY DT ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING --COMPAIR TO NEXT ROW ) - INTERVAL '1' MONTH = DT THEN 0 ELSE 1 END AS SESSION_END FROM SAMP --ONLY NEED TO RETURN ROWS THAT ARE EITHER START OR ENDS OF EACH SESSION QUALIFY (SESSION_START = 1 OR SESSION_END = 1) ) AS X --ONLY KEEP ROWS WHERE THE SESSION STARTED QUALIFY SESSION_START = 1 ORDER BY 1,3,2

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-12-2018
05:34 PM

10-12-2018
05:34 PM

Re: Urgent Help....

Thanks very much for your help,

I have tried to run your solution but getting the following error...

SELECT Failed. [7453] Interval field overflow.

Would you be able to suggest anything else...

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-13-2018
06:41 AM

10-13-2018
06:41 AM

Re: Urgent Help....

Thanks very much for you help MT186048

All Sorted :)