You can have either a calculated field in a table or perform calculations in a stored procedure. If you go the second route, then I suggest to have Calendar table in your database. Having this Calendar table (that will put IsHoliday or IsWeekend for Holidays and Weekends) will make the calculations of Working days trivial.


>Hi,
>
>I have the following (simplified) table structures in SQL Server but am developing the UI in VFP:
>
>Table Claims:
>ClaimID
>Forwarded (Date)
>
>Table ClaimItems
>ClaimItemID
>ClaimID (foreign key to Claims)
>Received (Date)
>
>One Claim can have one or many ClaimItems
>
>The Status of the ClaimItems is calculated like this (VFP pseudocode):
>
>

* if not yet received
>If EMPTY(Received)
>   * work out the number of working days (Monday to Friday, not holidays) since forwarded
>   LapsedDays = WorkingDays(Forwarded, Current Date) - Holidays(Forwarded, Current Date)
>   if LapsedDays > Limit
>       Status = "OverDue"
>   else
>      Status = "Pending"
>   endif
>else
>   Status = "Settled"
>endif

>
>The working days function I got from fox.wikis.com:
>
>

Lparameters date1, date2, include_date1
>Local jn1, jn2
>
>jn1 = Val(Sys(11, m.date1))  && ALTERNATIVE:  jn1=Val(Sys(11, Min(m.date1, m.date2)) - iif(include_date1, 1, 0)
>jn2 = Val(Sys(11, m.date2))  && ALTERNATIVE:  jn2=Val(Sys(11, Max(m.date1, m.date2))
>jn1 = iif(include_date1 AND m.jn1<=m.jn2, m.jn1 - 1, m.jn1)  && ALTERNATIVE:  remove this line
>jn2 = iif(include_date1 AND m.jn1>m.jn2, m.jn2 - 1, m.jn2)   && ALTERNATIVE:  remove this line
>
>Return (int(m.jn2 / 7) - int(m.jn1 / 7)) * 5 - Min(m.jn1 % 7 + 1, 5) + Min(m.jn2 % 7 + 1, 5)

>
>The Holidays is just a matter of pulling out a count of all holidays between the two specified dates from a holidays table.
>
>The Status of the Claim is based on the status of the Claim's ClaimItems according to the following rules:
>
>1. If any Claim Items are Overdue, then Claim is Overdue
>2. Else If all Claim Items are Settled, then Claim is Settled
>3. Otherwise Status is Pending
>
>I want the user to be able to choose a Status and then I build up some SQL to pull back all those claims that have that status.
>
>Can anybody help kick-start my brain in how to achieve this, please?
>
>In VFP I would write some functions to do the calculations, but I don't know how to do that in SQL Server. Should I use a calculated field? CTE? Stored Procedure? Sub Select? And How?