SQL Server 2008 :: Test If Each Subset Meets Requirements

CREATE TABLE #tblTemplateBlocks
(
TemplateID int,
BlockID int,
OrderID int

[Code] ....

I have a table called TemplateBlocks which contains which Blocks are on a Template. In this example - just one template - with three Blocks.

Table tblFields contains a list of Fields that are on each TemplateID/BlockID. In this example there are 3 fields on each TemplateID/BlockID pair.

Before I can use a template, I have to check that, in tblFields, for each Template/BlockID pairing - one of the fields must be set as the Stage Base (I cannot have 2 fields as StageBase or no fields as StageBase). In the example data above, the data would be okay as each Template/BlockID pairing has one row where StageBase is true.

Having checked that each Template/BlockID pairing has a StageBase, I need to check that each row where StageBase is true has a value for the WeekStart column and that, taking into account the order of the Blocks in tblTemplateBlocks, the values in WeekStart for each TemplateID/BlockID pairing are getting progressively bigger.

So, for example, the example data above would fail because the third TemplateID/BlockID pairing has no value for the WeekStart column in the row where StageBase is true.

If I added a value of 2 for WeekStart in the row for the third TemplateID/Block that has a StageBase of true - again the data would fail because, taking into account the order of the Blocks - the values for WeekStart would be 0,3,2 and these numbers need to increase.

0,3,4 would be fine.
0,3,10 would be fine.
0,3,3 would fail.

I can do this easily using a cursor or two - but how to do this without cursors.