Quarter of Year Parameter - SQL Server Forums

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)

Quarter of Year Parameter

Author

Topic

tmcivery
Starting Member

46 Posts

Posted - 2008-08-18 : 14:20:27

Hello all,

I need a parameter that will allow a user to pick a quarter(1 through 4 of course) and have the report return all tickets created(createondate) in that quarter and the preceeding quarter. The report is meant to have the user compare the chosen quarters tickets to that of the quarter before it. Can anyone give me some direction on how to create this type of parameter?

Thanks ahead of time!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-18 : 15:43:12

You'll have to tweak this to fit your specs, this assumes that the user is chosing quarters from just the current year. It should give you something to go on

Jim

declare @qtr int
declare @thisdate datetime

declare @startdate datetime
declare @endDate datetime

set @qtr = 4

set @endDate = DATEADD(q,@qtr,'12/31/2007')

set @startDate =DATEADD(q,-2,@endDate+1)

select @startDate,@endDate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 00:11:50

or use datepart

select fields
from yourtable
where datepart(q,createdondate)=@Quarter
AND datepart(yy,createdondate)=@Year

for current and

select fields
from yourtable
where datepart(q,createdondate)=@Quarter-1
AND datepart(yy,createdondate)=@Year

for previous.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-19 : 07:39:14

I was trying to avoid putting a function on the column, this way he can do
where createdondate >= @startdate and createdondate <= @endDate and maybe take advantage of indexes.

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 07:43:39

quote:
Originally posted by jimf

I was trying to avoid putting a function on the column, this way he can do
where createdondate >= @startdate and createdondate <= @endDate and maybe take advantage of indexes.

Jim

hmm...makes sense. didnt understand significance of using '12/31/2007'? can you explain it?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-19 : 08:44:12

quote:
hmm...makes sense. didnt understand significance of using '12/31/2007'? can you explain it?

No significance other than as an example. The OP didn't fully describe the parameter selection process, so I just assumed the quarter selected would be the current year for clarity's sake.

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 09:46:41

quote:
Originally posted by jimf

quote:
hmm...makes sense. didnt understand significance of using '12/31/2007'? can you explain it?

No significance other than as an example. The OP didn't fully describe the parameter selection process, so I just assumed the quarter selected would be the current year for clarity's sake.

Jim

ok...was wondering the relevance of the hardcoded value
thanks for clarification

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 10:48:18

[code]DECLARE @Qtr SMALLINT,
@StartDate DATETIME,
@EndDate DATETIME

SET @Qtr = 4

SELECT @EndDate = DATEADD(QUARTER, @Qtr, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()), '19000101')),
@StartDate = DATEADD(QUARTER, -2, @EndDate)

SELECT @StartDate,
@EndDate

SELECT *
FROM Table1
WHERE Col1 >= @StartDate
AND Col < @EndDate[/code]

E 12°55'05.25"
N 56°04'39.16"

tmcivery
Starting Member

46 Posts

Posted - 2008-08-26 : 11:20:22

Hey all,

Thanks for the replies. Am I to understand that these queries that you've posted are to be my dataset and that I'm not going to require a formula for my paramter?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 13:20:49

quote:
Originally posted by tmcivery

Hey all,

Thanks for the replies. Am I to understand that these queries that you've posted are to be my dataset and that I'm not going to require a formula for my paramter?

the provided queries are for your dataset. The values for quarter parameter will be 1 to 4. If you've a calendar table, you can use it to get values for financialyear or just use
SELECT DISTINCT YEAR(createdondate)
FROM YourTable to get year values from table. Make sure you dont have any gaps in data in your table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources