March 6, 2014 at 3:23 am
Hi,
I'm a newbie here and I've been recommended to contact you guys for help with SQL.
I have a set of policy data that were sold at different times of the year over past few years and some policies are still active while others have cancelled. I'm trying to create Weekly date flags to show which policies were active at each weekly snapshots retrospectively. So I want to end up with 52 or more (if required) weekly flags that will show whether a policy was active or not at each weekly points over the last 52 or more weeks.
The data set contains, policy start date, policy active status and cancel date. I can use case statements to create these weekly flags however this will mean that I will need to create 52 or more case statements, so I'm assuming there is a better way.
I am also using WinSQL Professional editor if that makes a difference.
Any help and advise will be most appreciated.
March 6, 2014 at 5:13 am
We'd be happy to help. What you really need is to post some DDL and sample data so we have a better idea of what you're dealing with. Here are some hints on how to do this: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Also, moved to the T-SQL forum under SQL 2012. Not sure of your version, but the old forum wasn't the spot for this question.
March 6, 2014 at 1:28 pm
This is an interesting question. Can you define what determines active during a week? Basically, I'm asking when does a week start/end and if my policy is active on a single day in a week does that mean it shows as active that week?
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
March 11, 2014 at 9:15 am
Hi there,
Thank you for your reply.
I've attached an example of what I'm trying to achieve. The first table shows example of policy data held in the database unique at policy level.
You can see when the policy started, when it's status changed (StatusChangeDate) i.e. if the policy renewed after 12months or cancelled whenever. So the first policy started on the 01/01/2011 and continued to renew every 12 months and it's last renewal time was 01/01/2014 and it is still active. So the StatusChangeDate is dynamic.
Now what I want to be able to do is create weekly variables to flag each policies status at a point in time. The example weekly flags are in the table on the right. You can see the flags contain '1' to show the policy was active for each week. Week 1 starts from 1st of April to 7th April and so on.
The reason I need this is so that I can dynamically show the total number of active policies at any point in time on a weekly basis. This will be for an interactive dashboard.
Hope this makes sense and of course if there is other ways of doing this rather than just weekly flags then please do let me know.
your help is most appreciated
Many thanks,
Shah
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply