Calculate Yearto Month

[system]

Hi,

In webi report, user wants data filtered dynamically by YeartoMonth.
Once user schedule a report once per month, report runs by yearto month every month. User will not have change the date every month.

For this month May 2013 it runs for
from 1/1/2013 to 4/30/2013

For month of June 2013 it runs for
from 1/1/2013 to 5/30/2013

CanSQL dateadd function used here…

Eg.
Begin date=dateadd(mm,datediff(m,0,getdate())-1,0)
End Date=dateadd(mm,-1,dateadd(mm,datediff(m,0,getdate()),0))

Need help with end date .

MP

---

patelm (BOB member since 2012-04-10)

[MarkP]

Have a look at this thread:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

Start of Current Year and End of Previous Month are the objects you need in your universe.

However, you may need to create a predefined condition when running for January - what would they want to show in January, whole of the previous year?

[system]

This Report are designed with data comparison with current year and pervious year.

Currenty I have perdifined filter at Universe for current and pervious year.

Current Year:
DM_INCURREDDATE.CALENDAR_DATE between @Prompt(‘Enter Service Begin Date’,‘D’,‘Incurred date\Calendar Date’,MONO,FREE,PERSISTENT,User:7)
and
@Prompt(‘Enter Service End Date’,‘D’,‘Incurred date\Calendar Date’,MONO,FREE,PERSISTENT,User:8)

Pervious Year:
DM_INCURREDDATE.CALENDAR_DATE between DateAdd(yy, -1, @Prompt(‘Enter Service Begin Date’,‘D’,‘Incurred date\Calendar Date’,MONO,FREE,PERSISTENT,User:7))
and DateAdd(yy, -1, @Prompt(‘Enter Service End Date’,‘D’,‘Incurred date\Calendar Date’,MONO,FREE,PERSISTENT,User:8))

With above perdefined filters user has to schedule report every month to get YeartoMonth information.

Your option of using Start of current year and End of Pevious month works for current year data.

Need your input on pervious year data, e.g. for current month it will be January 2012 to April 2012.

MP

---

patelm (BOB member since 2012-04-10)

[MarkP]

So you need an end of previous month - 1 year.
Use dateadd with the End of Previous Month syntax.

Just extend the logic and start thinking a bit.

[system]

Thanks so much…

I am able to get Pervious Year /Month

Dateadd(yy,-1,(dateadd(d,-1,cast(convert(char(7),getdate(),23)+’-01’ as datetime))))

MP

---

patelm (BOB member since 2012-04-10)