Hi.....I am trying to add records for each client based on the Fiscal Year (beginning) of the the Effective Dates and Expiry Dates. If the clinet only has a Effective Date, then I want to increment from the Fiscal Year of the Effective Date up to the Current Fiscal Year (todays date). If the client only has a Expiry Date, then increment from the Fiscal Year of the Expiry Date to the next Fiscal Year. NOTE: whenever there is a Expiry Date, the End for the increments is alway the following Fiscal Year from the Fiscal Year of the Expiry Date. How can new records be added and increment by a new Fiscal Year. Thanks.   Have:   Client Status Date 13223 Effective 20140503 13223 Expired 20160723 14239 Effective 20120124 14239 Expired 20150324 16982 Effective 20150112 17566 Expired 20130321   Want:   Client FiscalYear Type 13223 2014 New 13223 2015 Existing 13223 2016 Existing 13223 2017 Exit 14239 2011 New 14239 2012 Existing 14239 2013 Existing 14239 2014 Existing 14239 2015 Exit 16982 2014 New 16982 2015 Existing 16982 2016 Existing 17566 2012 Existing 17566 2013 Exit ... View more

Hi.....Is it possible from having to list the variable names in the Column Statement as the number of columns may change depending on the time frame for my dataset. For example, I am creating a report on the Annual Sales and each column represent the Sales for each year. Column names could be 2014, 2015, and 2016 if the dataset has the sales for these 3 years, However, the dataset could contain Sales for more than these 3 years and it is not known until the dataset is summarized. Any suggestions on how to get around this....Thanks. ... View more

Hi....I have a dataset where I would like to select or keep only those records(rows) where either the First_Choice or Second_Choice is greater than zero (0) and only keep the records(rows) where the Third_Choice=1 if that record(row) is unique (appears only once) grouped by Store_ID and Customer_ID. Thanks.     Have:   Store_ID Customer _Number Product_Number First_Choice Second_Choice Third_Choice 1231 23345 102011 3 0 0 1231 23345 102333 0 0 1 1231 23345 102441 0 1 0 1231 24898 101457 0 0 1 1231 24898 149845 0 0 1 1354 28779 112786 1 2 0 1354 28779 122654 0 2 0 1354 28779 125234 0 0 1 1354 28779 145889 1 0 0     Want:   Store_ID Customer _Number Product_Number First_Choice Second_Choice Third_Choice 1231 23345 102011 3 0 0 1231 23345 102441 0 1 0 1231 24898 101457 0 0 1 1354 28779 112786 1 2 0 1354 28779 122654 0 2 0 1354 28779 145889 1 0 0 ... View more

Hi....I have a dataset that contains a list of unique records for customers who are approved for specific products during a specific time period. The Approval dataset looks like this:   Customer_ID Product_ID Effective_Date Expiration_Date 1232 1127654 20160101 20160430 1232 1200782 20160201 20160525 1345 1097862 20151223 20160325 1345 1097862 20160401 20160428 1345 1097862 20160515 20160615 1345 1163452 20160201 20160630     I want to be able to check another dataset (Claims_Data) that contains all the claims submitted for these same customers and obtain the following:   Customer_ID Product_ID Count_Approved Count_Not_Approved No_Claims 1232 1127654 3 1   1232 1200782 4     1345 1097862 5 2   1345 1163452     1   Count_Approved is the number of claims where the claim_date from the Claims_Data dataset  is between the Effective_Date and Expiration Date for the respective customer for the each Product_ID. Count_Not_Approved is the number of claims submitted where the claim_date is outside the range for the Effective_Date and Expiration Date for the respective customer for the each Product_ID. As it is possible that customers may be approved for products and yet they may not have submitted any claims for the product. In this case, No_Claims=1 would be entered to keep track. Any suggestions. Thanks, ... View more

Hi...I have a dataset that contains the records for the application for product coverage which is given for a limited time period. I would like to obtain the overall coverage for each ID by Product number. If the coverage would have been overlapping for the same product number for each ID, it would have been easy by grouping by ID and Product Number and using the minimum of the Effective Dates and maximumof the Expiry Dates. However, it is possible to have a gap between coverages and this is the part that I am having problems with. Any suggestions....Thanks.   Have:   ID Product Number Effective Date Expiry Date 2303 11232 20160102 20160227 2303 11232 20160212 20160325 2303 14356 20160212 20160325 2303 14356 20160325 20160420 2409 17809 20160113 20160217 2409 17809 20160220 20160326 2409 17809 20160301 20160320 2409 17809 20160324 20160430 2409 18488 20160119 20160219 2409 18488 20160218 20160318 2512 15932 20160123 20160227 2512 15932 20160227 20160329 2512 16554 20160124 20160213 2512 16554 20160214 20160315 2512 16554 20160314 20160417 2512 16554 20160420 20160521 2512 16554 20160519 20160620     Want:   ID Product Number Effective Date Expiry Date 2303 11232 20160102 20160325 2303 14356 20160212 20160420 2409 17809 20160113 20160217 2409 17809 20160220 20160430 2409 18488 20160119 20160318 2512 15932 20160123 20160329 2512 16554 20160124 20160213 2512 16554 20160214 20160417 2512 16554 20160420 20160620 ... View more

Hi.....I am trying to calculate the accumulated differences between the cost and deductible amount for each ID. That is, I want to find the amount that exceeds the deductible for each ID as a new record is added for each ID. Thanks       ID Date Cost Deductible Amount 11234 1/23/2016 175 250 0 11234 2/27/2016 50 250 0 11234 3/17/2016 75 250 50 11234 4/21/2016 45 250 95 11234 5/25/2016 90 250 185 12266 1/20/2016 150 300 0 12266 2/27/2016 75 300 0 12266 3/30/2016 50 300 0 12266 4/15/2016 45 300 20 ... View more

Hi...I am trying to convert a character date to numeric date. The character date is in the formate of weekdate17. I can't seem to get it converted. ... View more