Thanks,
can you provide me with some instructions as to how to go about this, please?
>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?