Re: Grouping by a date period.

[Felix Geerinckx]

R A wrote:

> I want to group the records by a date period of 1 month, starting at the 3rd day of each month.

This is another example that would greatly benefit from a calendar table.

Create a calendar table like so:

DROP TABLE IF EXISTS calendar;
CREATE TABLE calendar (
dt DATE NOT NULL PRIMARY KEY,
period TINYINT NOT NULL
);

And fill it with relevant data (a Spreadsheet Program is an excellent tool for this ;-)
I only show some INSERT statement here to get the idea (look especially at period changes,
consistent with your business requirement):

INSERT INTO calendar VALUES ('2005-01-01', 0);
INSERT INTO calendar VALUES ('2005-01-02', 0);
INSERT INTO calendar VALUES ('2005-01-03', 1);
INSERT INTO calendar VALUES ('2005-01-03', 1);
...
INSERT INTO calendar VALUES ('2005-02-02', 1);
INSERT INTO calendar VALUES ('2005-02-03', 2);
...
INSERT INTO calendar VALUES ('2005-12-31', 12);

You can then find the cost per period per extension with the following simple query (without the need to mess with date comparisons etc.):

SELECT
calendar.period,
pabx.extension,
SUM(pabx.cost)
FROM pabx
JOIN calendar ON calendar.dt = pabx.dt
GROUP BY calendar.period, pabx.extension;

--
felix
Please use BBCode to format your messages in this forum.