Re: Time Periods
Posted by: Felix Geerinckx
Date: October 12, 2006 02:47AM

Use a calendar table and fill it with all relevant dates and additional info.
Then join with this table:

CREATE TABLE calendar ( 
  dt DATE NOT NULL PRIMARY KEY, 
  period INT UNSIGNED NOT NULL,

  KEY (dt, period)
);

INSERT INTO calendar (dt, period) VALUES
('2006-01-01', 1), ..., ('2006-02-11', 1),
('2006-02-12', 2), ..., ('2006-03-25', 2),
... ;

SELECT
  c.period,
  SUM(s.somevalue)
FROM calendar c
JOIN statistics s ON s.dt = c.dt
WHERE
  c.dt BETWEEN @adate AND @anotherdate
GROUP BY c.period

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

Options: ReplyQuote


Subject
Written By
Posted
October 11, 2006 06:08PM
Re: Time Periods
October 12, 2006 02:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.