Board index » delphi » Grouping by month

Grouping by month

I'm trying to create in report using Delphi 3 using the built in Quick
Reports package.

The report selects items from a table by date, and I want to summarise
the data by month. Is there an elegant way to do this with SQL or a
Quick Report function I have missed, or do I just have to go the long
way round?

John Birch

 

Re:Grouping by month


Quote
John Birch wrote in message <36B97F2F.1...@{*word*269}.net>...
>I'm trying to create in report using Delphi 3 using the built in Quick
>Reports package.

>The report selects items from a table by date, and I want to summarise
>the data by month. Is there an elegant way to do this with SQL or a
>Quick Report function I have missed, or do I just have to go the long
>way round?

>John Birch

Hi John,
if you are working on an SQL Server, you got the possibility of creating
views that seperates your date field into 3 integer fields, _day, _month and
_year (use the SQL datetime functions from your server, on MS-SQL e.g. it's
the datepart function).

If you got the view, you can make a group by month, like

select sum(ammount) from tableX group by _month...

NB: in MS-SQL you can directly make a group by Datepart(m,Datefield), so no
need of a view. But on other SQL servers like Interbase, this does not
work...

Tom

Re:Grouping by month


Quote
Tom wrote:

> John Birch wrote in message <36B97F2F.1...@{*word*269}.net>...
> >I'm trying to create in report using Delphi 3 using the built in Quick
> >Reports package.

> >The report selects items from a table by date, and I want to summarise
> >the data by month. Is there an elegant way to do this with SQL or a
> >Quick Report function I have missed, or do I just have to go the long
> >way round?

> >John Birch

> Hi John,
> if you are working on an SQL Server, you got the possibility of creating
> views that seperates your date field into 3 integer fields, _day, _month and
> _year (use the SQL datetime functions from your server, on MS-SQL e.g. it's
> the datepart function).

> If you got the view, you can make a group by month, like

> select sum(ammount) from tableX group by _month...

> NB: in MS-SQL you can directly make a group by Datepart(m,Datefield), so no
> need of a view. But on other SQL servers like Interbase, this does not
> work...

> Tom

Thanks Tom, unfortunately its a Paradox table. Sorry I didn't mention
it.

Re:Grouping by month


I am also in need of the exact same thing.  I am using D4 With all updates
and Pdox 7 tables.

Thanks in advance

--
Doug Maurer
Innovative Software Solutions, Ltd.

Re:Grouping by month


On Thu, 04 Feb 1999 15:09:34 +0000, John Birch <john.bi...@{*word*269}.net>
wrote:

[...]

Quote
>Thanks Tom, unfortunately its a Paradox table. Sorry I didn't mention
>it.

Alas, no. There is no direct way to do this using local SQL. This is
because local SQL does not allow a GROUP BY clause to be based on a derived
value, such as the EXTRACT function when used to get a date column's month
portion. You can produce a result set using local SQL in a slightly
different way. To do so, you would need to connect one monthly summary
SELECT query for each month (i.e., 12 SELECT queries) with UNION clauses.

To summarize one month, using the sample Paradox table ORDERS.DB, in this
case for January, 1995:

  SELECT "01", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 1) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)

To summarize on a monthly basis for the same year, repeat the same SELECT
query once for each of the twelve months and connect them together into a
single result set with UNION clauses.

  SELECT "01", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 1) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "02", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 2) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "03", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 3) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "04", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 4) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "05", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 5) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "06", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 6) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "07", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 7) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "08", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 8) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "09", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 9) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "10", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 10) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "11", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 11) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "12", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 12) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)

//////////////////////////////////////////////////////////////////////////
Steve Koterski                   "Writers have two main problems. One is
Technical Publications           writer's block, when the words won't come
INPRISE Corporation              at all, and the other is logorrhea, when
http://www.inprise.com/delphi    the words come so fast that they can
                                 hardly get to the wastebasket in time."
                                                    -- Cecelia Bartholomew

Re:Grouping by month


This should work :

  SELECT EXTRACT(MONTH FROM O."SALEDATE") AS m, SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  GROUP BY m

Re:Grouping by month


On Fri, 05 Feb 1999 08:58:27 +0100, Johan Berrevoets

Quote
<Berre...@btmaa.bel.alcatel.be> wrote:
>This should work :

>  SELECT EXTRACT(MONTH FROM O."SALEDATE") AS m, SUM(O."AMOUNTPAID")
>  FROM "ORDERS.DB" O
>  WHERE (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
>  GROUP BY m

Should -- but doesn't. Not in local SQL, the BDE's implementation of SQL
used for dBASE and Paradox tables. At least this is true as of version 5.02
of the BDE (the latest).

For instance, the SQL statement below using the sample Paradox table
ORDERS.DB...

  SELECT EXTRACT(MONTH FROM O.SaleDate) AS M, SUM(O.AmountPaid)
  FROM "Orders.db" O
  WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988)
  GROUP BY M

...results in the error "Invalid field name", referring to M as used in the
GROUP BY clause.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                   "Writers have two main problems. One is
Technical Publications           writer's block, when the words won't come
INPRISE Corporation              at all, and the other is logorrhea, when
http://www.inprise.com/delphi    the words come so fast that they can
                                 hardly get to the wastebasket in time."
                                                    -- Cecelia Bartholomew

Re:Grouping by month


Mr. Birch-
    I ran into the exact same situation you did.  Here's the workaround
I came up with.  First, run a query like this:

SELECT Extract(MONTH from FuelingDate) currmth, Extract(YEAR from
FuelingDate) curryr,
 ...
FROM ...
WHERE ...
ORDERBY FuelingDate

The SELECT statement containing the EXTRACT command is the important part of
the SQL.  Also, make sure you also order the data correctly using an ORDERBY.
 By extracting the month and year, you'll use these values in a TQRGroup
band.  When you add a TQRGroup, you'll automatically get a groupheader band.
You might have to do some manipulating to get this band into the correct
position.  I don't know how familiar you are with Quick Reports, but it tends
to order the bands the way it wants to, not the way you want to.  Since it
sounds like you simply wish to summarize the data, the Group Header band you
added will probably be blank.  You have to leave it on the report even if
it's empty.  The band you're really interested in is the GroupFooter band.
To add this, just add a TQRBand and set its type to Groupfooter.  Once you
get the groupheader in place above the detail band, the Groupfooter should
show up below the detail band. Without the GroupHeader band, the report won't
break correctly.  The TQRGroup bands break on any change in the TQRGroup's
Expression property.  You must use the TQRGroup component to get the
GroupHeader.  Quick Reports allows you to drop a TQRBand onto the report and
set its type to GroupHeader, but this does not allow you to group correctly.
If you want to group on month, use the following in your TQRGroup Expression:

STR(qryRMVMonthly.currmth) + STR(qryRMVMonthly.curryr)

Converting these values to a string first and then concatenating them is
important!  If you simply were to sum these two values, it wouldn't
work.  For instance Feb. 1994 would be 2 + 1994 = 1996  which would be
the same as Jan. 1995 (1 + 1995 = 1996).  Therefore, your grouping would
be off.  By concatenating the string, you get 21994 for Feb. 1994 and
11995 for Jan 1995.  Since these values are different, Quick Reports
will break the band correctly.  Unfortunately, Quick Reports doesn't
seem to directly support grouping on months.
Good Luck,
Charles Borromeo

PS there is a plethora of information on Quick Reports at the
borland.public.delphi.reporting-charting newsgroup.

In article <36B97F2F.1...@{*word*269}.net>,

Quote
  john.bi...@{*word*269}.net wrote:
> I'm trying to create in report using Delphi 3 using the built in Quick
> Reports package.

> The report selects items from a table by date, and I want to summarise
> the data by month. Is there an elegant way to do this with SQL or a
> Quick Report function I have missed, or do I just have to go the long
> way round?

> John Birch

Chuck Borromeo

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    

Other Threads