Board index » delphi » Grouping by a month part of a date field

Grouping by a month part of a date field

In my Paradox 7 table, I have a date_entered field. I want to count the
number of records entered for each month of each year, for example:

Month: 12. Year: 1999. Records entered: 89.
Month: 1. Year: 2000. Records entered: 130.
Month: 2. Year: 2000. Records entered: 198.
Month: 3. Year: 2000. Records entered: 121.

How would I do this?

Thanks.
Steve
-----------
Custom Software Systems - Web Design Specialists
http://www.cssweb.co.uk/

 

Re:Grouping by a month part of a date field


The following SQL comes quite close, but doesn't group by the month:

SELECT
  COUNT(EXTRACT(MONTH FROM DATE_ENTERED)) AS THEMONTH,
  DATE_ENTERED
FROM
  TABLE
GROUP BY
  DATE_ENTERED

Quote
"Stevio" <ste...@mail.com> wrote in message

news:8vtj72$f5p$1@news8.svr.pol.co.uk...
Quote
> In my Paradox 7 table, I have a date_entered field. I want to count the
> number of records entered for each month of each year, for example:

> Month: 12. Year: 1999. Records entered: 89.
> Month: 1. Year: 2000. Records entered: 130.
> Month: 2. Year: 2000. Records entered: 198.
> Month: 3. Year: 2000. Records entered: 121.

> How would I do this?

> Thanks.
> Steve
> -----------
> Custom Software Systems - Web Design Specialists
> http://www.cssweb.co.uk/

Re:Grouping by a month part of a date field


Use:

SELECT EXTRACT(MONTH FROM date_entered), EXTRACT(YEAR FROM date_entered),
COUNT(*)
FROM Table
GROUP BY EXTRACT(YEAR FROM date_entered), EXTRACT(MONTH FROM date_entered)

"Stevio" <ste...@mail.com> schreef in bericht
news:8vtj72$f5p$1@news8.svr.pol.co.uk...

Quote
> In my Paradox 7 table, I have a date_entered field. I want to count the
> number of records entered for each month of each year, for example:

> Month: 12. Year: 1999. Records entered: 89.
> Month: 1. Year: 2000. Records entered: 130.
> Month: 2. Year: 2000. Records entered: 198.
> Month: 3. Year: 2000. Records entered: 121.

> How would I do this?

> Thanks.
> Steve
> -----------
> Custom Software Systems - Web Design Specialists
> http://www.cssweb.co.uk/

Re:Grouping by a month part of a date field


Steve,

Try creating another table with two date fields and one record
for each month/year combo of interest, where one field contains
the first of the month and another contains the last of the month
(hint: use code and a loop (or two) to populate the new table
(the daysInMonth() field will come in handy for populating the
second field)).  Then use this table as criteria for your other
query, with a count all in some field:

| MyDate                                 |
| >=_s, <=_e, calc count all as RecCount |

| StartDate | EndDate |
| Check _s  | _e      |

This will give you a table with the first of each month in the
StartDate field and a count of records in that month in the
RecCount field.  (Note, you must have one of those two criteria
date fields checked.)

For more on criteria tables, see the Corel news server
(cnews.corel.com) and the FAQ group (corelsupport.faqs.paradox).

Let us know if you need help with the code for populating that
criteria table.  (PS: Key that table on StartDate.)

Regards,

Liz
---
Get the lead out before you reply

Quote
Stevio wrote:

> In my Paradox 7 table, I have a date_entered field. I want to count the
> number of records entered for each month of each year, for example:

> Month: 12. Year: 1999. Records entered: 89.
> Month: 1. Year: 2000. Records entered: 130.
> Month: 2. Year: 2000. Records entered: 198.
> Month: 3. Year: 2000. Records entered: 121.

> How would I do this?

> Thanks.
> Steve
> -----------
> Custom Software Systems - Web Design Specialists
> http://www.cssweb.co.uk/

Re:Grouping by a month part of a date field


Thanks but I tried that and it didn't work.

Error message was "Capability not supported"

If you take away the count(*) line the error message is the same.

Any more ideas?
Thanks,
Stephen

Quote
"M.H. Avegaart" <avegaartNOS...@mccomm.nl> wrote in message

news:8vtk9t$6kd$1@porthos.nl.uu.net...
Quote
> Use:

> SELECT EXTRACT(MONTH FROM date_entered), EXTRACT(YEAR FROM date_entered),
> COUNT(*)
> FROM Table
> GROUP BY EXTRACT(YEAR FROM date_entered), EXTRACT(MONTH FROM date_entered)

> "Stevio" <ste...@mail.com> schreef in bericht
> news:8vtj72$f5p$1@news8.svr.pol.co.uk...
> > In my Paradox 7 table, I have a date_entered field. I want to count the
> > number of records entered for each month of each year, for example:

> > Month: 12. Year: 1999. Records entered: 89.
> > Month: 1. Year: 2000. Records entered: 130.
> > Month: 2. Year: 2000. Records entered: 198.
> > Month: 3. Year: 2000. Records entered: 121.

> > How would I do this?

> > Thanks.
> > Steve
> > -----------
> > Custom Software Systems - Web Design Specialists
> > http://www.cssweb.co.uk/

Re:Grouping by a month part of a date field


Hi Liz,

Thanks for the reply. I have it working quite nicely now.

I have a question however. I just created a table manually to try this out.
However, I need this query for when a user generates reports from the
application I'm developing. Should I recreate/repopulate the criteria table
every time they try to create this report? Won't this be quite slow? The
users are sharing a database on a server, but each PC would have their own
criteria table right?

If you can get me started with the code for the criteria table I'd
appreciate that too, but if not don't worry, I'll work it out.

Thanks,
Stephen

Quote
"Liz" <lead...@aros.net> wrote in message news:3A2255E8.54C8ECDC@aros.net...
> Steve,

> Try creating another table with two date fields and one record
> for each month/year combo of interest, where one field contains
> the first of the month and another contains the last of the month
> (hint: use code and a loop (or two) to populate the new table
> (the daysInMonth() field will come in handy for populating the
> second field)).  Then use this table as criteria for your other
> query, with a count all in some field:

> | MyDate                                 |
> | >=_s, <=_e, calc count all as RecCount |

> | StartDate | EndDate |
> | Check _s  | _e      |

> This will give you a table with the first of each month in the
> StartDate field and a count of records in that month in the
> RecCount field.  (Note, you must have one of those two criteria
> date fields checked.)

> For more on criteria tables, see the Corel news server
> (cnews.corel.com) and the FAQ group (corelsupport.faqs.paradox).

> Let us know if you need help with the code for populating that
> criteria table.  (PS: Key that table on StartDate.)
> Stevio wrote:

> > In my Paradox 7 table, I have a date_entered field. I want to count the
> > number of records entered for each month of each year, for example:

> > Month: 12. Year: 1999. Records entered: 89.
> > Month: 1. Year: 2000. Records entered: 130.
> > Month: 2. Year: 2000. Records entered: 198.
> > Month: 3. Year: 2000. Records entered: 121.

> > How would I do this?

> > Thanks.
> > Steve
> > -----------
> > Custom Software Systems - Web Design Specialists
> > http://www.cssweb.co.uk/

Re:Grouping by a month part of a date field


Steve,

If each user needs to query for a different date range, then yes,
each should have their own table in their :PRIV: (or similar
unique/user alias).

I'll assume a table with this structure:

DATECRIT.DB
StartDate D *
EndDate   D

So, you'd get start and end month and year from the user -
perhaps via drop-down lists (or list fields) or entry fields on a
form, perhaps via dialogs.  I'll let you decide on that part, for
my example, I'll just assign variables certain values.

var
  siStartYear, siEndYear,
  siStartMonth, siEndMonth SmallInt
  dStart, dEnd, dEndMonth Date
  tcDateCrit TCursor
endVar

;// assign these vars however you wish...
siStartYear = 1999
siStartMonth = 8
siEndYear = 2000
siEndMonth = 7

;// this is the last month for which
;// a record will be entered
dEndMonth = date(siEndMonth,1,siEndYear)

;// this is the first day of the first
;// month for which a record is entered
dStart = date(siStartMonth,1,siStartYear)

;// this is the last day of the first
;// month for which a record is entered
dEnd = date(siStartMonth,daysInMonth(dStart),siStartYear)

tcDateCrit.open(":PRIV:SEDATE.DB")
tcDateCrit.edit()

while dStart <= dEndMonth
  tcDateCrit.insertRecord()
  tcDateCrit."StartDate" = dStart
  tcDateCrit."EndDate" = dEnd
  tcDateCrit.postRecord()
  if month(dStart) = 12 then
    siStartYear = siStartYear + 1
    siStartMonth = 1
  else
    siStartMonth = siStartMonth + 1
  endIf
  dStart = date(siStartMonth,1,siStartYear)
  dEnd = date(siStartMonth,daysInMonth(dStart),siStartYear)
endWhile

tcDateCrit.close()
msgInfo("Done","Criteria table populated.")

Regards,

Liz
---
Get the lead out before you reply

Quote
Stevio wrote:

> Hi Liz,

> Thanks for the reply. I have it working quite nicely now.

> I have a question however. I just created a table manually to try this out.
> However, I need this query for when a user generates reports from the
> application I'm developing. Should I recreate/repopulate the criteria table
> every time they try to create this report? Won't this be quite slow? The
> users are sharing a database on a server, but each PC would have their own
> criteria table right?

> If you can get me started with the code for the criteria table I'd
> appreciate that too, but if not don't worry, I'll work it out.

> Thanks,
> Stephen

Re:Grouping by a month part of a date field


Create a Month field in your table.
Populate by running a scan loop
Scan
  [month] = Month([datefield]) ; numerictype  month,
                               ; MOY() for alphatype
EndScan

Now you can (sub) group on [month]

Steve U

Quote
On Mon, 27 Nov 2000 12:04:57 -0000, "Stevio" <ste...@mail.com> wrote:
>In my Paradox 7 table, I have a date_entered field. I want to count the
>number of records entered for each month of each year, for example:

>Month: 12. Year: 1999. Records entered: 89.
>Month: 1. Year: 2000. Records entered: 130.
>Month: 2. Year: 2000. Records entered: 198.
>Month: 3. Year: 2000. Records entered: 121.

>How would I do this?

>Thanks.
>Steve
>-----------
>Custom Software Systems - Web Design Specialists
>http://www.cssweb.co.uk/

,        _
,       | \                           Steve Urbach
,       |  )erek
,   ____|_/ragonsclaw                 thedu...@best.com
,  / / /                              

Other Threads