Hi!

Se answer by EMail...

>Hi Vlad
>Thanks for your invaluable advise on SQL. I'm looking to engage some consulting help on database design and developing n-tier application with VPM and foxfire. Would u be available to help. If yes, please let me know your professional rates. The tools I'm using includes
>
>1. xcase
>2. xcase2vpm
>3. vpm enterprise
>4. foxfire enterprise
>5. SQL Server/Oracle(maybe)
>
>Best regards
>>Ooops, sorry, it does not use sub-queries. It just uses the conditional fields.
>>
>>>Hi!
>>>
>>>Following is a query for the fixed number of the types:
>>>
>>>

>>>Select p."Patient ID", p.Name,
>>>    SUM(CASE v."Treatment Type" WHEN 1 THEN v.Amt ELSE 0 END) as Amt_1,
>>>    SUM(CASE v."Treatment Type" WHEN 2 THEN v.Amt ELSE 0 END) as Amt_2,
>>>    SUM(CASE v."Treatment Type" WHEN 3 THEN v.Amt ELSE 0 END) as Amt_3
>>>  from Patient P LEFT JOIN Visit V
>>>    ON P."Patient ID" = V."Patient ID"
>>>  Group By p."Patient ID", p.Name
>>>

>>>
>>>As you see, it uses Left-Joined subqueries. NVL is required to use 0 value instead of the NULL for case there are no amount for particular type.
>>>
>>>See help in MSDN, article index is "cross-tab reports (SQL Server)" for more explanations.
>>>
>>>If you have dynamic number of types, you can build query like above in a string and than run it:
>>>- in VFP using SQLEXEC() function, where you pass the query string as a second parameter.
>>>- in SQL Server stored procedure you can do a loop through the cursor of the treatment types and build a string, note that SQL Server 7.0 have a limitation to 8000 characters, so probably you will require to split a string or make a query written by as short way as possible. Than you can use 'EXEC ()' command to execute the query in a string, or use the sp_executesql stored procedure.
>>>
>>>HTH.
>>>
>>>>I have a patient table and a visits table.
>>>>Patient
>>>> Patient_ID
>>>> Name
>>>>
>>>>Visit
>>>> Patient ID
>>>> Date
>>>> Treatment Type
>>>> Amt
>>>>
>>>>I wish to write a SQL statement that will generate a cursor as follows
>>>>
>>>> Patient_ID, Name, Amt_1, Amt_2, Amt_3
>>>>
>>>>where amt_1,amt_2,amt_3 are expenses incurred for treatment type 1, 2 & 3 respectively. The output is actually sort of crosstab. All help appreciated. Thanks.
>>>>Best Regards