Sum and date ranges

[Matt]

Hi all,

I have a database with a table storing a list of names, invoice dates, and
invoice amounts.

What I'm looking to do is to create a sum of the invouice amounts based on a
range of invoice dates. e.g. Sum of invouice amounts for invoice date range
between date1 and date2 then sum of invoice amount for date range between 3
and 4.

Doing this in seperate steps works fine, so one query for date range 1 and 2
and then another for 3 and 4 but to produce the correct report I need all in
one query.

A crosstabe query grouping by month is almost what I'm looking for, but I
need specific date ranges not calendar months.

Any help would be greatly appreciated.

Cheers.

[Smartin]

Matt wrote:

Hi all,

I have a database with a table storing a list of names, invoice dates, and
invoice amounts.

What I'm looking to do is to create a sum of the invouice amounts based on a
range of invoice dates. e.g. Sum of invouice amounts for invoice date range
between date1 and date2 then sum of invoice amount for date range between 3
and 4.

Doing this in seperate steps works fine, so one query for date range 1 and 2
and then another for 3 and 4 but to produce the correct report I need all in
one query.

A crosstabe query grouping by month is almost what I'm looking for, but I
need specific date ranges not calendar months.

Any help would be greatly appreciated.

Cheers.

You could combine the separate queries you have working into a single
UNION ALL query.

To keep the results from each piece separate you could add opposing Null
fields to each, like

SELECT
<most stuff from Q1>,
<total from Q1>,
Null as total from Q2
UNION ALL
SELECT
<most stuff from Q2>,
Null as total from Q1,
<total from Q2>
;


--
Smartin