Dates

# Dates

• Friday, June 15, 2012 12:46 PM

Hello!

I need that in this code will be calculated the data on @BeginDate (entering by user) minus one month.

Example:

@BeginDate = 03.01.2012

And the calculation goes on 02.01.2012

How to write it in the code?

`WHERE	[Bonus Header].[Period Bonus Calculation]=YEAR(@BeginDate)*100+MONTH(@BeginDate)`

### All Replies

• Friday, June 15, 2012 12:57 PM
Moderator

Hi There

You can write some thing like this

`WHERE [Bonus Header].[Period Bonus Calculation]=DATEADD(month,-1,@BeginDate)`

However if you would like to check between the begindate and  begindate minus one month you can write

`WHERE [Bonus Header].[Period Bonus Calculation]between DATEADD(month,-1,@BeginDate) and @BeginDate`
I am assuming that [Period Bonus Calculation] is a datetime field

Many Thanks

Syed Qazafi Anjum

• Friday, June 15, 2012 1:05 PM

This request's result is nothing

I give you a full code of this dataset

```SELECT		CASE WHEN([PROVEA RUSSIA\$Customer].[SPP Code]='' or [PROVEA RUSSIA\$Customer].[SPP Code] is null) THEN 'OTHER' ELSE [PROVEA RUSSIA\$Customer].[SPP Code] END AS SPP,
FROM [Bonus Header] (NOLOCK) INNER JOIN [PROVEA RUSSIA\$Customer] (NOLOCK) ON [Bonus Header].[Consultant No_]=[PROVEA RUSSIA\$Customer].[No_]
AND [Bonus Header].[Rank End of Period]='07-DIR'
GROUP BY [PROVEA RUSSIA\$Customer].[SPP Code]```

• Friday, June 15, 2012 1:08 PM
Moderator

Hi There

First of all please elaborate these

What is the data type of [Period Bonus Calculation] column?

And what are you trying to do with this expression YEAR(@BeginDate)*100+MONTH(@BeginDate)

Many Thanks

Syed

• Friday, June 15, 2012 1:20 PM

The data type is integer

I want do modify this expression:

YEAR(@BeginDate)*100+MONTH(@BeginDate)

by the way, that it might show the data not on month written by user (@BeginDate), but on minus one month.

Example:

@BeginDate = 03.01.2012

And the calculation goes on 02.01.2012

• Friday, June 15, 2012 1:25 PM
Moderator

Hi There

Thanks for your posting. Can you please also let me know what data [Period Bonus Calculation] column contains like does it contain

year+month like 20115 which mean 2011 year and month may

```select
-- this expression will give you 5 which is month 5 now we are in june```
```select

please let me know then it would be easy to give you right direction

Many thanks

Syed

• Friday, June 15, 2012 2:23 PM

It doesn't return a conflict, but the calculation goes on the date in @BeginDate without minusing the month.

[Period Bonus Calculation] is like that 201205 - 2012 year May

Could you please show me how to modify all the code of the request, that it will work?
• Edited by Friday, June 15, 2012 2:23 PM
•
• Friday, June 15, 2012 2:32 PM
Moderator

Hi There

```SELECT		CASE WHEN([PROVEA RUSSIA\$Customer].[SPP Code]='' or [PROVEA RUSSIA\$Customer].[SPP Code] is null) THEN 'OTHER' ELSE [PROVEA RUSSIA\$Customer].[SPP Code] END AS SPP,
FROM [Bonus Header] (NOLOCK) INNER JOIN [PROVEA RUSSIA\$Customer] (NOLOCK) ON [Bonus Header].[Consultant No_]=[PROVEA RUSSIA\$Customer].[No_]
GROUP BY [PROVEA RUSSIA\$Customer].[SPP Code]```

this expression will give you 20125

`select convert(int,(convert(varchar(10),DATEPART(year, dateadd(month,-1,@BeginDate)))+convert(varchar(10),DATEPART(month, dateadd(month,-1,@BeginDate))))) as Previous_Month_year`

Many Thanks

Syed Qazafi Anjum

• Friday, June 15, 2012 2:47 PM

It works!
But it works just with the max date 01.01.2012
Works in all 2011, but doesn't work in 2012.
• Friday, June 15, 2012 2:51 PM

I mean, that there are no any relult, when entering as @BeginDate for example 05.01.2012, works till 01.01.2012 and at all after that date, but not works after the January 2012
• Friday, June 15, 2012 2:57 PM
Moderator

hi There

Do you have the data after that date inisde  your table

```SELECT		CASE WHEN([PROVEA RUSSIA\$Customer].[SPP Code]='' or [PROVEA RUSSIA\$Customer].[SPP Code] is null) THEN 'OTHER' ELSE [PROVEA RUSSIA\$Customer].[SPP Code] END AS SPP,
FROM [Bonus Header] (NOLOCK) INNER JOIN [PROVEA RUSSIA\$Customer] (NOLOCK) ON [Bonus Header].[Consultant No_]=[PROVEA RUSSIA\$Customer].[No_]
GROUP BY [PROVEA RUSSIA\$Customer].[SPP Code]```

Many Thanks

Syed Qazafi Anjum

• Friday, June 15, 2012 3:01 PM

This code doesn't showing me nothing - in 2012 and in other years(((

• Friday, June 15, 2012 3:06 PM
Moderator

Hi There

are you saying When you change to new expression it does not show you anything even for 2012 and previous years(2011)

Can you please also explain are you using date picker for your @BeginDaten parameter

Many Thanks

Syed Qazafi Anjum

• Friday, June 15, 2012 3:12 PM

The date picker is not in use.

Yes, with new expression it doesn't show nothing.

• Friday, June 15, 2012 3:28 PM
Moderator

Hi There

Can you please do the following?

Create a new dataset inside your report and put this expression and see what result you get as I am confused because you are not using date picker and it could be the issue related to your date format

Please put those date where you are not getting any values and copy the value coming from the below expression and and then try to hardcode those value inside your query and see if you get any result

`select convert(int,(convert(varchar(10),DATEPART(year, dateadd(month,-1,@BeginDate)))+convert(varchar(10),DATEPART(month, dateadd(month,-1,@BeginDate))))) as YearMonth`

Many Thanks

Syed Qazafi Anjum

• Friday, June 15, 2012 3:32 PM

Here the results:

@Begin Date = 03.01.2012

Result = 20122

@Begin Date = 02.01.2012

Result = 20121

@Begin Date = 01.01.2012

Result = 201112

@Begin Date = 12.01.2012

Result = 201111

• Saturday, June 16, 2012 3:50 AM
Moderator

Hi There

To me the expression is displaying the result as expected. AS far I can say I think it might be issue with your data so  please do the following, please select that month which is not producing any result inside your query and try to hard code those values inside your query, say June 2012 is not producing any result then put

`Bonus Header].[Period Bonus Calculation]=20126`

If still your query not returning any result please check your data or other part of query which might be creating problem

I hope this will help

Many Thanks

Syed Qazafi Anjum

• Monday, June 18, 2012 7:43 AM

I cheked it. Result is not returning on June 2012, but returning on May 2012 and earlier.

One remark: The data is filled in the Period Bonus Calculation in the type not 20126, but 201206

• Monday, June 18, 2012 7:48 AM

I found a solution!

Just added multiplying 100 to the year's calculating!

I thank you so much, Syed!

`[Bonus Header].[Period Bonus Calculation]=convert(int,(convert(varchar(10),DATEPART(year, @BeginDate))*100+convert(varchar(10),DATEPART(month, dateadd(month,-1,@BeginDate)))))`

• Marked As Answer by Monday, June 18, 2012 7:48 AM
•
• Monday, June 18, 2012 7:55 AM
Moderator

Hi There

the best way to resolve this

```SELECT		CASE WHEN([PROVEA RUSSIA\$Customer].[SPP Code]='' or [PROVEA RUSSIA\$Customer].[SPP Code] is null) THEN 'OTHER' ELSE [PROVEA RUSSIA\$Customer].[SPP Code] END AS SPP,