Board index » delphi » SQL For Grouping by Month

SQL For Grouping by Month

Hi,

Please can somebody help me i need to extract data with SQL and i need to
group the data by the month/year component of a date field. I'm battling to
get it write so i would really appreciate if someone could give me an
example.

Many Thanks.

Tim

 

Re:SQL For Grouping by Month


As far as I know there is no 'built-in' function in IB to return Month or
Year. To do that you will have to create a user defined function. There is
an extremely good article on UDFs at
http://www.interbase2000.org/doc_deatz_udf.htm

In order to group by a calculated field, you can create a VIEW which
contains the original fields from the underlying table in addition to your
calculated fields, eg

CREATE VIEW VW_BASETABLE AS
BEGIN
  SELECT STARTDATE, my_udf_mth(STARTDATE) AS STARTMONTH,
my_udf_year(STARTDATE) AS STARTYEAR FROM BASETABLE;
END(or something)

and then do your select on the view rather than the table eg
SELECT COUNT(*) FROM VW_BASETABLE GROUP BY STARTYEAR,STARTMONTH

Quote
"Tim Horn" <t...@umbani.com> wrote in message news:3c164213_2@dnews...
> Hi,

> Please can somebody help me i need to extract data with SQL and i need to
> group the data by the month/year component of a date field. I'm battling
to
> get it write so i would really appreciate if someone could give me an
> example.

> Many Thanks.

> Tim

Re:SQL For Grouping by Month


Hmm...

Depending on which IB version or dialect, I remember reading something
about Extract on dates to provide Mon,day and year.

This may be particular to IB 6 Dialect 3, not sure.  You can always
download the docs and read'em.

-Lou

Quote
Tim Horn wrote:

> Hi,

> Please can somebody help me i need to extract data with SQL and i need to
> group the data by the month/year component of a date field. I'm battling to
> get it write so i would really appreciate if someone could give me an
> example.

> Many Thanks.

> Tim

Other Threads