I am developing SQL reports in Visual Studio 2005 and have written a report that processes the following records.
1. A case has 1 case record.
2. A case record has 1 or more related service records.
3. A case record has 1 or more related activity records.
4. A case record has 1 or more related meeting records.
5. A service record has 1 or more related activity records.
6. A service record has 1 or more related meeting records.
I have written code that works to return all activities and meetings for a Case. This is the code:
DECLARE @strCaseid AS varchar(20)
-- 1. Get activities and meetings by Case - a case has 1 Case ID
SELECT Subject, DocType, CreationDate, CreationBy
FROM
(
---get all activities for the case
SELECT subject AS Subject, activitytypecodename AS DocType, createdon AS CreationDate,
createdbyname AS CreationBy
FROM FilteredActivityPointer
WHERE (regardingobjectid = @strCaseid)
UNION
---get all meetings for the case
SELECT '' AS Subject, 'Meeting' AS Expr1, createdon, createdbyname
FROM FilteredNew_meeting
WHERE (new_casenoid = @strCaseid)
) AS A
ORDER BY CreationDate DESC
I have written code to return all activities and meetings for a Service. This is the code:
DECLARE @ServiceName AS varchar(20)
-- 2. Get activities and meetings by Service - a case has 1 or more services so loop through a cursor
BEGIN
-- Set up cursor to loop through all service records
DECLARE curService CURSOR FOR
SELECT new_case
FROM filterednew_service
WHERE (new_caseid = @strCaseid)
OPEN curService
FETCH NEXT FROM curService into @ServiceName
-- Loop through all the activities for all the services in the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
---get all activities for all services
SELECT subject AS Subject, activitytypecodename AS DocType, createdon AS CreationDate, createdbyname AS CreationBy
FROM FilteredActivityPointer
WHERE (regardingobjectidname = @ServiceName)
---get all meetings for all services
SELECT '' AS Subject, 'Meeting' AS Expr1, createdon, createdbyname
FROM FilteredNew_meeting
WHERE (new_regardingobjectidname = @ServiceName)
-- Get next service record
FETCH NEXT FROM curService into @ServiceName
END -- End of cursor loop
CLOSE curService
DEALLOCATE curService
END
When using an example of 1 Case record with 2 Service records the cursor only processes 1 Service record. It does not loop through all the service records. So the cursor processing isn't working properly.
I need to combine both parts of this code into one so that I return a single redordset. Any help or suggestions on a better way to achieve this will be much appreciated.
Thank you.
Saners.
1. A case has 1 case record.
2. A case record has 1 or more related service records.
3. A case record has 1 or more related activity records.
4. A case record has 1 or more related meeting records.
5. A service record has 1 or more related activity records.
6. A service record has 1 or more related meeting records.
I have written code that works to return all activities and meetings for a Case. This is the code:
DECLARE @strCaseid AS varchar(20)
-- 1. Get activities and meetings by Case - a case has 1 Case ID
SELECT Subject, DocType, CreationDate, CreationBy
FROM
(
---get all activities for the case
SELECT subject AS Subject, activitytypecodename AS DocType, createdon AS CreationDate,
createdbyname AS CreationBy
FROM FilteredActivityPointer
WHERE (regardingobjectid = @strCaseid)
UNION
---get all meetings for the case
SELECT '' AS Subject, 'Meeting' AS Expr1, createdon, createdbyname
FROM FilteredNew_meeting
WHERE (new_casenoid = @strCaseid)
) AS A
ORDER BY CreationDate DESC
I have written code to return all activities and meetings for a Service. This is the code:
DECLARE @ServiceName AS varchar(20)
-- 2. Get activities and meetings by Service - a case has 1 or more services so loop through a cursor
BEGIN
-- Set up cursor to loop through all service records
DECLARE curService CURSOR FOR
SELECT new_case
FROM filterednew_service
WHERE (new_caseid = @strCaseid)
OPEN curService
FETCH NEXT FROM curService into @ServiceName
-- Loop through all the activities for all the services in the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
---get all activities for all services
SELECT subject AS Subject, activitytypecodename AS DocType, createdon AS CreationDate, createdbyname AS CreationBy
FROM FilteredActivityPointer
WHERE (regardingobjectidname = @ServiceName)
---get all meetings for all services
SELECT '' AS Subject, 'Meeting' AS Expr1, createdon, createdbyname
FROM FilteredNew_meeting
WHERE (new_regardingobjectidname = @ServiceName)
-- Get next service record
FETCH NEXT FROM curService into @ServiceName
END -- End of cursor loop
CLOSE curService
DEALLOCATE curService
END
When using an example of 1 Case record with 2 Service records the cursor only processes 1 Service record. It does not loop through all the service records. So the cursor processing isn't working properly.
I need to combine both parts of this code into one so that I return a single redordset. Any help or suggestions on a better way to achieve this will be much appreciated.
Thank you.
Saners.