Group by Month(Date)

rbuser

I have not been using Report Builder in a while, and now I am back making a
report and I am not quite sure if I can easily group by the month of a date
field.

For example:

If I have 9/13/2001 in record 1 and 9/14/2001 in record 2 then I want to
have the group be September and list both dates.

The normal grouping is putting 9/13/2001 as a group date and 9/14/2001 as
another group header.

Any ideas?

Douglas

digitalmetaphors

You should be able to create a calculated field on the dataset, also sort
the data by that field , and then base the report group on that field value.
Usually there is a Month function you can use on your database when you
submit the query.

Cheers,

Jim Bennett
Digital Metaphors

rbuser

Could you give me a quick example if it is possible to do it with SQL only.

I am using Advantage Database TQuery component and I use the same component
for all reports, I just write the SQL differently. So if I can do the
calculated field somehow in SQL then that would be great, but I am not sure
how to do it the regular OnCalc method in order to use it for everything.

Douglas

digitalmetaphors

Consult the advantage documentation on the sql syntax, but it would be
something like:

SELECT Employee.Name,
Employee.HireDate,
Extract(Month From Employee.HireDate) AS Extract_Month
FROM Employee


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Hi Douglas,

I think this has been answered in Advantage's NGs.

regards,
Chris Ueberall;

rbuser

Yes, It was answered to me on the Advantage NGs. This is the answer for
those interested.

SELECT a.Fullname, a.Phone, a.BirthDate, Month(a.BirthDate) as MonthDate
FROM addressbook a
GROUP By Month(a.BirthDate), a.Birthdate, a.Fullname, a.Phone

Then in the report I group by MonthDate.

Thanks for all of your help guys, I do appreciate it.

Douglas