Brainteaser Group multiple fields in month totals

[TerriO]

CR 8.5

I have multiple fields that I need to total for each month
(about 10 additional date fields).

Pending Contracts Contracts Approvd
1/2/2005 1/12/2005
1/14/2005 2/5/2005
2/2/2005 3/1/2005

Totals for month (need
totals for month and grand totals )

January Pending Contracts 2
Contracts Appd 1
February Pending Contracts 1
Contracts Approved 1
March Pending Contracts 0
Contracts Approved 1

I am at a loss for ideas, have tried a few things but since I can't just group on a month or just don't know how..


Terri

 

[lbass]

I'm not sure what your fields are. Are "Pending Contract" and "Approved Contract" separate date fields? Or are they instances of one field like {table.contractstatus} with only one date field. The question is, how does your data look if you lay it out in the detail section? If these are in fact two different date fields, do they appear in the same record? If so, what is the field that connects them?

-LB

 

[synapsevampire]

You might use Running Totals with an evaluate->Use a formula of:

//January RT
month({table.date}) = 1

//February RT
month({table.date}) = 2

etc.

Build out as many as you need months for, for both types of dates.

-k

 

[TerriO]

The data laid out in the example as a record

LotID Pending Contract Contract Appvd
XYZ 1/14/2005 2/5/2005

I need to get a total summary for all that occurred in each month , if I had 5 pendings and 2 approved for January, etc.

Terri

 

[synapsevampire]

Right, that's why the RT's make sense here.

Another solution might be to create a UNION ALL Query in a View or SP:

select 'Pending' DataSource, LotID, PendingContract from table
UNION ALL
select 'Approved' DataSource, LotID, ContractAppvd from table

Now you'll have a different row for each date type and grouping by month and conventional summaries would work.

-k

 

[TerriO]

unfortunately Unions and views are not an option

Terri