Nested conditional query - or what

Last Post 05 Jul 2007 05:30 AM by hdsit. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
hdsit
New Member
New Member

--
05 Jul 2007 05:30 AM
I have the following SP:
Create PROCEDURE [dbo].[GetTree_extended]
@paramID int,
@PNO int
as
begin
--USE pdatabase1;
WITH DirectReports(parentid, ID, name, selcat, Level) AS
(
SELECT parentid, ID, name, selcat, 0 AS Level
FROM dbo.tbl_Tree
WHERE id = @paramID
UNION ALL
SELECT e.parentid, e.ID, e.name, e.selcat, Level + 1
FROM dbo.tbl_Tree e
INNER JOIN DirectReports d
ON e.parentid = d.ID
)
SELECT parentid, ID, name, selcat, Level, View_modulSched_Supplier_extended.Prepare_RFQ, View_modulSched_Supplier_extended.Enquiery_FirstPrice, View_modulSched_Supplier_extended.Evaluation,
View_modulSched_Supplier_extended.Delivery_Time, View_modulSched_Supplier_extended.Shipment, View_modulSched_Supplier_extended.Plus_Minus, View_modulSched_Supplier_extended.SuppliedBy, tbl_DesignAreas.DeadLine, tbl_DesignAreas.ProjectID,
tbl_DesignAreas.AreaID

FROM DirectReports dr

left outer join tbl_DesignAreas on dr.parentid = tbl_DesignAreas.modultreeid
left outer join View_modulSched_Supplier_extended on dr.selcat = View_modulSched_Supplier_extended.treeid
where tbl_DesignAreas.ProjectID = @PNO or tbl_DesignAreas.ProjectID is null
END

It is used as input for a report in reporting-server as seen here:
Screenshot
Well "Del. at contractor" is from tbl_DesignAreas.DeadLine +/- the value from View_modulSched_Supplier_extended.Plus_Minus (an integer value)

When i click on the "Del. at contractor" i open a sub-report, which show the components added here (0, 1 or more components)
Here i use following parameters: (ProjectID = @ProjectID) AND (DesignArea = @AreaID) AND (Category = @selcat)

Each component has a field: plus_minus_prio which hold an integer value that should override the value from View_modulSched_Supplier_extended.Plus_Minus if the value is different from 0 (default).

As far as i see it, then i should have extended my primary SP.
For each record it should run another query to 1: see if there is 1 or more records (in the subreport-query)
Loop through these records, and find the max value of plus_minus_prio if some > 0 exists
If a value >0 is found - then it should replace/override the "original" View_modulSched_Supplier_extended.Plus_Minus int-value

Is this possible at all?( im still rather a newbee with these SP's)
Sincerely Uffe Pedersen
You are not authorized to post a reply.

Acceptable Use Policy