Re: data design optimization

[BattleMan BattleClan]

it shows that my design lack great deal of clarity and sense.

what i was trying to do is to seperate the sets into two categories
1. the main group category [identified by an activityid=0]
2. the activies of the certain group [activityid<>0]

the above query finds all of the activities which their corresponding activity happens within a certain date range.


that is there are two parts for the query
SELECT * FROM BudgetActivities WHERE
ActivityGroupID IN (
SELECT ActivityGroupID FROM BudgetActivities WHERE
ActivityID = 0
AND
PeriodStart < _PeriodEnd
AND
PeriodEnd > _PeriodStart
) ORDER BY ActivityGroupID,ActivityID;

the inner part is
\SELECT ActivityGroupID FROM BudgetActivities WHERE
ActivityID = 0
AND
PeriodStart < _PeriodEnd
AND
PeriodEnd > _PeriodStart

which selects all of the groups [identified by their activitygroupid] which their main item [identified by activityid=0] happens in a certain date range

the second part [the complete query] selects all of the data sets which belong to any of the groups found in the first part

i can only assume that my design is so flawed that it is beyond description

i assume i need to split the data into two tables

to make things simpler i'll describe my needs
i need a
1. group dataset. with "Name" and "Date Range"[period start and period end]
2. activity dataset. with "Name" and other properties but with a link to the "group dataset"

my need is to select activities which belong to groups that answer certain criterias [in this example, data range]

i thank you alot for your help. it is much apriciated.