Sample Data: BCID and TimePeriodID are the primary and foreign keys which
hold the tables together.
TimePeriod
TimePeriodID TimePeriodStart TimePeriodEnd
1 01/01/2000 07/31/2000
2 08/01/2000 12/31/2000
3 01/01/2000 12/31/2000

Plan
BCID LicenseModule TimePeriodID Quantity
A238 A001 1 14
A238 A001 2 16
D723 A001 3 1
D779 A001 3 0
D793 A001 3 2

BCOrganization
BCID GroupName
A238 XCG
D723 CSS
D779 XCG
D793 XCG

Actual
BCID LicenseID TimePeriod
A238 1 3
D723 2 3
D779 3 3
Report to be generated:

GroupName YEPlan YTDPlan Actuals YTDDelta YEDelta
XCG 18 16 2 -12 -14
CSS 1 1 1 0 0


YEPlan: Year end planned quantity that would be reached in 12/00. The logic
is to sum all the planned quantities for each budget center identified by
BudgetCenter ID (BCID) where the TimePeriodStart is less than or equal to
12/01/00 and TimePeriodEnd is greater than or equal to 12/31/00. And group
the results by GroupName.

YTDPlan: Planned quantity that is as of today. The logic is to sum all the
planned quantities for each budget center identified by BudgetCenter ID (BCID)
where the TimePeriodStart is less than or equal to the current date (ex:07/12/00)
and TimePeriodEnd is greater than the current date(ex:07/12/00). And group
the results by GroupName.

Actuals: The actual quantity of licenses as of today. The logic is to count
all the licenseIDs that have a TimePeriodStart date less than or equal to
current date and a TimePeriodEnd date greater than the current date. Group
the results by GroupName.

YTDDelta: The difference between YTDPlan and Actuals.

YEDelta: The difference between YEPlan and Actuals

So far I have the following statement which does not provide me exactly the
report I wanted.

SELECT BCOrganization.GroupName, (SELECT Sum (Plan.Quantity) FROM TimePeriod
INNER JOIN (BCOrganization INNER JOIN Plan ON BCOrganization.BCID = Plan.BCID)
ON TimePeriod.TimePeriodID = Plan.TimePeriodID
WHERE ((([TimePeriod]![TimePeriodStart])<=#12/1/00#) AND (([TimePeriod]![TimePeriodEnd])>=#12/31/00#)))
AS YEPLAN,
(SELECT Sum (Plan.Quantity) FROM TimePeriod INNER JOIN (BCOrganization INNER
JOIN Plan ON BCOrganization.BCID = Plan.BCID) ON TimePeriod.TimePeriodID
= Plan.TimePeriodID
WHERE ((([TimePeriod]![TimePeriodStart])<=#07/13/00#) AND (([TimePeriod]![TimePeriodEnd])>#07/13/00#)))
AS YTDPLAN FROM TimePeriod INNER JOIN (BCOrganization INNER JOIN Plan ON
BCOrganization.BCID = Plan.BCID) ON TimePeriod.TimePeriodID = Plan.TimePeriodID
GROUP BY BCOrganization.GroupName;

It gives me the following result:

GroupName YEPLAN YTDPLAN
CSS 19 17
XCG 19 17

It just adds the plan quantities for all the BCID s, but don't group them
according to GroupName.

My question is how to write the SQL statement for getting the report I wanted?