By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,727 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,727 IT Pros & Developers. It's quick & easy.

Quarterly trending

P: n/a
I have a reporting query that I need to do a quarterly trending on. A
user enters a begining date and end date, and from there, I have to
determine the closest quarter to it.

Also, the actual Quarter starts on Sept 4th, then add 13 weeks to that,
will be the second quarter.

I was able to do the trending for week/month/year, but I'm stuck on
this quarterly trending.

Any help would be greatly appreciated.

Example of the monthly trending that I have written:

SELECT [VALUE],DATEPART(MONTH,DATESAMPLE) AS[MONTH]
FROM #TABLE
GROUP BY [VALUE], DATEPART(MONTH,DATESAMPLE)
ORDER BY EDIT_SP, DATEPART(MONTH,DATESAMPLE)

Oct 24 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Stu
This would be easy if your business used calendar quarters instead;
can't you make them change? <G> You could then group by
DATEPART(Quarter, DateSample)

Since that's not likely, a solution might be to use a calendar table
that takes a key Date and maps it to the appropriate quarter, eg:

CREATE TABLE Calendar (DateKey smalldatetime, PeriodQuarter int....
(other columns))

INSERT INTO Calendar (DateKey, PeriodQuarter)
SELECT '9/1/2005', 2
UNION ALL
SELECT '9/4/2005', 3

etc

You could then join and group on the PeriodQuarter value.

Just an idea.
Stu

Oct 24 '05 #2

P: n/a
create a table of the quarters:

CREATE TABLE Quarters
(quarter_name CHAR(20) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date ));
.. determine the closest quarter to it. <<


Whatr does thst mean? give me an algorithm.

Oct 24 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.