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?