--0-1488711404-979034271=:41302
Content-Type: text/plain; charset=us-ascii


I think that you need to approach this by a divide and conquer approach.

There are various options open to you. Union queries that amalgamate the quarters and years into one table. The tables need to be
field compatable. I don't really like this approach because it will create complex SQL code that will be hard to maintain and lacks
flexibilty.

Or create an empty temporary table that you Update with records from the various quires and return with  a SELECT * FROM TEMP. 

The Temp table could be either on the Server or local (unattached ADO recordset).

But My own preference here would be to create an unattached ADO recordset then pass a series of date queries adding the resulting
data to your unattached ADO recordset.

This would result in a simple query between two dates. 

Whether the dates where for quarters or years would be immaterial.

 The computation for the Quarters and the Years preferably treated as a business object not embedded. 

Your problem would be then defining the start and end dates for quarters and for years. As a business object this is an attractive
approach because it is flexible for say tax Accounting periods etc. 


Another valid approach using a temporary table would be to create one that held pairs of dates in two fields plus a marker filed for
Year or Quarters. Then use this as a join table   for the purposes of your query. Return the resulting recordsets then delete the
temporary table.

I hope that these ideas will help you tackle your problem.

Roland


  Sue <Suzan.Koshkuson@h...> wrote: 
I am having trouble writing this query:

Display first x number of quarters, until the end of a whole year. 

ie. If we wanted a report for year 2000 in September, till 2005. The
report would display only the last quarter in 200, display 4 quarters in
2001 and then displayt the remaining years by year.

code:
'Month = CLng(Month(Now))
'Year = Clng(Year(Now))
'response.write lMonth & " " & (lMonth + 1) mod 12 & "

"

'build the 'to date' string
'sStartDate = "TO_DATE('01/" & lMonth & "/" & lYear & "
00:00:00','DD/MM/YYYY HH24:MI:SS')"
'EndDate = "TO_DATE('01/" & (lMonth + 1) mod 12 & "/" & lYear + 1 &"
00:00:00','DD/MM/YYYY HH24:MI:SS')"

'initialise the RS and run the query
Set oRS = server.createobject("adodb.recordset")
sSQL = "SELECT project_id, project_desc, (select employees.surname from
employees where employees.EMPLOYEE_NUM = projects.ORIGINATOR_EMP_NUM)
Originator_Surname" & _
", (select employees.first_name from employees where
employees.EMPLOYEE_NUM = projects.ORIGINATOR_EMP_NUM)
Originator_FirstName" & _
", (select divisions.division_name from divisions where
divisions.division_code = projects.division_code) division_desc" & _
", (select mrus.mru_name from mrus where mrus.mru_code 
projects.mru_code) mru_desc, approved_budget_value" &_
", (select cashflow_plans.plan_year_value from cashflow_plans
where cashflow_actual.project_ID = projects.project_ID" &_
", (select cashflow_actuals.actual_value from cashflow_actuals where
cashflow_plans.project_ID = projects.project_ID) actual" &_
", FROM projects WHERE IN_PLAN_FLAG = true" 

To unsubscribe send a blank email to leave-sql_language-$subst('Recip.MemberIDChar')@p2p.wrox.com



---------------------------------
Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online!

---
You are currently subscribed to sql_language as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-sql_language-$subst('Recip.MemberIDChar')@p2p.wrox.com