Multiple Date ranges covering multiple years

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Multiple Date ranges covering multiple years

Author

Topic

ejholm
Starting Member

1 Post

Posted - 2008-12-04 : 14:00:39

I am trying to create a report that requires multiple date ranges. An order date range and a pickup date range. I am then attempting to compare current year to prior year. When I tried to use a cube as a data source I did not like the prior year due to the second date range. I then tried to create the report using two data sources for the report. With that attempt I had trouble with the tables/lists/textboxes only binding to one data source, so I couldn't run any calculations on the current and prior year numbers. If anyone has any insight as to how I can get around this I would appreciate it. Currently the report is created using a pivot table in excel. I would like to automate the report and run it off a cube if possible.

Thanks in advance,

Erik

Erik Holm

lavvu
Starting Member

13 Posts

Posted - 2008-12-10 : 02:03:37

We had a similar situation in our project.
We needed to generate the number of resources utilized in a particular technology for the current year and the last year for comparision. What we did was this:

In the query we retrived the data corresponding to the two years using the year(date)=year(getdate()) and year(date)=year(getdate()-1).

Then used a matrix to display the data. In the matrix we added a column group for the year.

The report could be compared for the two years - current and last.

Hope this helps.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 03:08:04

This would be the fastest approach since it can utlize any present index on OrderDate

SELECT	DATEADD(YEAR, DATEDIFF(YEAR, 400, GETDATE()), 0) AS PreviousYearFirstDate,
	DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS CurrentYearFirstDate,
	DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), 0) AS NextYearFirstDate
	
SELECT		Store,
		SUM(CASE WHEN OrderDate >= PreviousYearFirstDate AND OrderDate < ThisYearFirstDate THEN Price ELSE 0 END) AS PreviousYear,
		SUM(CASE WHEN OrderDate >= CurrentYearFirstDate AND OrderDate < NextYearFirstDate THEN Price ELSE 0 END) AS CurrentYear
FROM		Orders
WHERE		OrderDate >= PreviousYearFirstDate
		AND OrderDate < NextYearFirstDate
GROUP BY	Store

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources