>>>>Hi,
>>>>
>>>>using MM.NET 2.6, C#, ASP.NET, SSRS
>>>>
>>>>I have a Policy Business Object which has a method to calculate the Date the policy has been paid up to (this is done by totalling the number of premium payments that have been received). I need to display this date (as well as another calculated date: the date the next premium is/was due) on a report in SSRS.
>>>>
>>>>How should I approach this? Add Paid To Date and Next Premium Due date fields onto the Policy table? I don't like this way because of the possibility of things getting out of sync, but if there's no other way...
>>>
>>>There are a few possibilities.
>>>
>>>1. Create a view (or SP or table-valued inline function) that will get your dates.
>>>
>>>2. If these dates are used very often, add computed columns to your table.
>>
>>Thanks Naomi,
>>
>>this is my code in the BO:
>>
>>

        public DateTime CalculatePaidToDate()
>>        {
>>            // get all premiums paid
>>            DataSet dsPremiumsPaid = this.GetPremiumsPaid(this.Entity.PolicyHeaderFK);
>>
>>            // work out paid to date based on issued date and premium mode
>>            // Monthly, Quarterly, Semi-Annually, Annually
>>            DateTime dt = DateTime.MinValue;
>>            int monthsToAdd = 0;
>>            if (this.Entity.IssueDate != null && dsPremiumsPaid.Tables["PremiumsPaidList"].Rows.Count > 0)
>>                {
>>                dt = (DateTime)this.Entity.IssueDate;
>>
>>                for (int i = 0; i < dsPremiumsPaid.Tables["PremiumsPaidList"].Rows.Count; i++)
>>			    {
>>                    Int16 numberOfPremiums = Int16.Parse(dsPremiumsPaid.Tables["PremiumsPaidList"].Rows[i]["NumberOfPremiums"].ToString());
>>                    if (this.Entity.PremiumMode == "Monthly")
>>                        monthsToAdd += 1 * numberOfPremiums;
>>                    if (this.Entity.PremiumMode == "Quarterly")
>>                        monthsToAdd += 3 * numberOfPremiums;
>>                    if (this.Entity.PremiumMode == "Semi-Annually")
>>                        monthsToAdd += 6 * numberOfPremiums;
>>                    if (this.Entity.PremiumMode == "Annually")
>>                        monthsToAdd += 12 * numberOfPremiums;
>>			    }
>>            }
>>            return dt.AddMonths(monthsToAdd);
>>        }
>>

>>
>>Is it easy to do this as a computed column?
>
>Yes, of course. Using CASE expression and DATEADD fnction.

Hi Naomi,

I'm struggling a bit with this. I hope you or someone else has the time to give me a hand.

I think what I need to do is create a scalar-valued function that returns the date and then add in a computed column to the table which calls this function. Is that right?

My C# code gets a list of premiums paid and what the Premium Mode was for the payment, then loops through the result table adding months to the starting date. Here is where I get stuck. I don't know how to loop through a table in T-SQL, or if there is a better way. Any hints or help you can provide would be greatly appreciated.