Last available quarter of a reporting year

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)

Last available quarter of a reporting year

Author

Topic

milena
Starting Member

16 Posts

Posted - 2008-08-06 : 09:11:24

Hi guys ,

I am quite new in SQL and programminig as a whole.
I need to create a logic that would get the last available reporting quarter in a year selected in the report.For example if Year 2008 i selected I need to display as Current YTD the last available quarter of that year.If the last available quarter is Q2 the CurrentYTD should be 2008-06-30(the end of the reporting qurter).
Tried with writting a function in SSMStudio but it doesn`t seem to work.
The other thing I can think of is MDX .However I have no knowledge of MDX at all.
Would be really greatfull if someone can help.

Regards

Milena

cardgunner

326 Posts

Posted - 2008-08-06 : 09:51:03

what about a dateadd function?

select dateadd(Q,3,'2008-01-01 00:00:00.000')
result '2008-10-01 00:00:00.000'

select dateadd(Q,3,'2008-01-01 00:00:00.000')-1
result '2008-9-30 00:00:00.000'

Something like that work?

CardGunner

milena
Starting Member

16 Posts

Posted - 2008-08-06 : 10:10:38

Hi Card Gunner,
thanks for you reply.
As far as I get why is the adddate used , I don`t think it`s going to work .I will try to explain better.
I have to have in the report activity displayed for the last available quarter in the year selected.
The problem is that the year could be 2006 any year and then I have to select the max available quarter for the year selected.
So it could be Q3 in year 2006 - then the activity date I need is 2006-09-30.
the question here is what is the syntax where I can specify the year (probably as variable) , then how do I select max quarter for the year let`s say 2006 that is a vailable in table A and then I need to set the current YTD as current accountingperiodenddate.
Really confused here.
Would it be a function or not .

Thanks in advance
Milena

cardgunner

326 Posts

Posted - 2008-08-06 : 10:45:50

declare @year int
select @year=2006

select dateadd(q,3,dateadd(yy, @year-1900, 0))-1

result
2006-9-30 00:00:00.000

CardGunner

cardgunner

326 Posts

Posted - 2008-08-06 : 10:49:38

quote:

the question here is what is the syntax where I can specify the year (probably as variable)

I'm not sure what reportiing software you are using?

There needs to be a parameter field in the report where when the report it will ask the user to fill in a field. That fill in will relate to the @year in your select statement.

CardGunner

milena
Starting Member

16 Posts

Posted - 2008-08-06 : 11:14:48

I am using SQL Server 2005 Reporting Services.Thanks again for reply
Will see if that works .
Thanks

cardgunner

326 Posts

Posted - 2008-08-06 : 11:22:21

Not sure how 2005 is different then 2000 but there should be spmething called report parameters.

I can find by going to th etoolbar, go to report(Between format and Tools)go to report parameters> add parameter @year if it doesn't show up already.

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 14:07:07

quote:
Originally posted by milena

Hi guys ,

I am quite new in SQL and programminig as a whole.
I need to create a logic that would get the last available reporting quarter in a year selected in the report.For example if Year 2008 i selected I need to display as Current YTD the last available quarter of that year.If the last available quarter is Q2 the CurrentYTD should be 2008-06-30(the end of the reporting qurter).
Tried with writting a function in SSMStudio but it doesn`t seem to work.
The other thing I can think of is MDX .However I have no knowledge of MDX at all.
Would be really greatfull if someone can help.

Regards

Milena

I think you just need to create a dataset with source as

SELECT DATEADD(d,-1,DATEADD(q,DATEDIFF(q,0,GETDATE()),+1))

and just give this dataset as default value for YTD parameter

milena
Starting Member

16 Posts

Posted - 2008-08-08 : 06:49:21

quote:
Originally posted by visakh16

quote:
Originally posted by milena

Hi guys ,

I am quite new in SQL and programminig as a whole.
I need to create a logic that would get the last available reporting quarter in a year selected in the report.For example if Year 2008 i selected I need to display as Current YTD the last available quarter of that year.If the last available quarter is Q2 the CurrentYTD should be 2008-06-30(the end of the reporting qurter).
Tried with writting a function in SSMStudio but it doesn`t seem to work.
The other thing I can think of is MDX .However I have no knowledge of MDX at all.
Would be really greatfull if someone can help.

Regards

Milena

I think you just need to create a dataset with source as

SELECT DATEADD(d,-1,DATEADD(q,DATEDIFF(q,0,GETDATE()),+1))

and just give this dataset as default value for YTD parameter

Hi visakh 16,

thanks for that .Still unclear of how exactly Ishould put it together.Do I have to write a function so I can use the code above.
If I have parameter year and quarter.
The function should extract the last available quarter for a year selected .So I will have something like this :
select (max(accountingPeriodEnddate) from TableA where
accountingperiodendDate>'2006 -01-01')).(here the example is for year 2006)
If I use following function:
@Param1 int

RETURNS datetime
AS
BEGIN

DECLARE @Year int
DECLARE @YTDAccPeriod datetime

SET @Year=@Param1
IF @Year=2006
Set@YTDAccPeriod=(select(max(AccountingPeriodenddate) from table A
where AccountingPeriodEnddate<'2006-12-31' and AccountingPerodEndDate>'2006-01-01'
IF @Year=2007
Set@YTDAccPeriod=(select(max(AccountingPeriodenddate) from table A
where AccountingPeriodEnddate<'2007-12-31' and AccountingPerodEndDate>'2007-01-01'
and so on for each year.

.
.
.

Return @YTDAccPeriod
I used this function in the sql query for the Report in the following manner
this is a snippet;

CASE(WHEN Condition 1 and AcountingPeriodEnddate=dbo.RPT_Test4(this is the name of the function above)(DateName(Year,AccountingPeriodenddate)) Then AcctAmount ELSE 0 END)AS CurrentAccrualAmount.

When I browsed the cube in the currentaccruaammount I had 0 everywhere .
That made me think the query/or function are not right.
The target here is when a Year 2006 or any year is selected in the report in the currentaccrualcolumn to be showed the summed acct amount for the last available in the datamart quarter for the year selected.
for example if year 2006 is selected and we have only qurter 1 and 2 available for that year , the current acct amount to be for only accounting period 2006-06-30.
Really getting nowhere and any help or suggestions would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-08 : 09:50:24

i think you are loking this.

let @Year be your year parameter
then
DECLARE @MaxQuarter int

SELECT @MaxQuarter=DATEPART(q,MAX(AccountingPeriodenddate))
FROM table A
where YEAR(AccountingPeriodEnddate)=@Year

SUM(CASE WHEN Condition 1 and DATEPART(q,AcountingPeriodEnddate)<=@MaxQuarter Then AcctAmount ELSE 0 END)AS CurrentAccrualAmount.

milena
Starting Member

16 Posts

Posted - 2008-08-26 : 05:36:41

quote:
Originally posted by visakh16

i think you are loking this.

let @Year be your year parameter
then
DECLARE @MaxQuarter int

SELECT @MaxQuarter=DATEPART(q,MAX(AccountingPeriodenddate))
FROM table A
where YEAR(AccountingPeriodEnddate)=@Year

SUM(CASE WHEN Condition 1 and DATEPART(q,AcountingPeriodEnddate)<=@MaxQuarter Then AcctAmount ELSE 0 END)AS CurrentAccrualAmount.

Thanks a ton visakh16. That is exactly what I need .
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 06:02:18

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources