Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Dates

Answered Dates

  • Friday, June 15, 2012 12:46 PM
     
      Has Code
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    0

    Hi There

    You can write some thing like this

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

    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

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful



     
  • Friday, June 15, 2012 1:05 PM
     
      Has Code
    Sign In to Vote
    0

    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,
				COUNT([Bonus Header].[Consultant No_]) as NoOf7
	FROM [Bonus Header] (NOLOCK) INNER JOIN [PROVEA RUSSIA$Customer] (NOLOCK) ON [Bonus Header].[Consultant No_]=[PROVEA RUSSIA$Customer].[No_]
WHERE	[Bonus Header].[Period Bonus Calculation]=YEAR(@BeginDate)*100+MONTH(@BeginDate)
		AND [Bonus Header].[Rank End of Period]='07-DIR'
	GROUP BY [PROVEA RUSSIA$Customer].[SPP Code]

     
  • Friday, June 15, 2012 1:08 PM
    Moderator
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    1

    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
DATEPART(month, dateadd(month,-1,getdate())) as Previous_Month
-- this expression will give you 5 which is month 5 now we are in june
    select
DATEPART(year, dateadd(month,-1,getdate())) as Previous_Month_year


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

    Many thanks

    Syed




     
  • Friday, June 15, 2012 2:23 PM
     
     
    Sign In to Vote
    0

    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 Navruz3 Friday, June 15, 2012 2:23 PM
    •  
     
  • Friday, June 15, 2012 2:32 PM
    Moderator
     
      Has Code
    Sign In to Vote
    1

    Hi There

    Please use this expression

    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,
				COUNT([Bonus Header].[Consultant No_]) as NoOf7
	FROM [Bonus Header] (NOLOCK) INNER JOIN [PROVEA RUSSIA$Customer] (NOLOCK) ON [Bonus Header].[Consultant No_]=[PROVEA RUSSIA$Customer].[No_]
WHERE	[Bonus Header].[Period Bonus Calculation]=convert(int,(convert(varchar(10),DATEPART(year, dateadd(month,-1,@BeginDate)))+convert(varchar(10),DATEPART(month, dateadd(month,-1,@BeginDate))))) 
    		AND [Bonus Header].[Rank End of Period]='07-DIR'
	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

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful


     
  • Friday, June 15, 2012 2:47 PM
     
     
    Sign In to Vote
    0
    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
     
     
    Sign In to Vote
    0
    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
     
      Has Code
    Sign In to Vote
    0

    hi There

    Do you have the data after that date inisde  your table

    Please change to this one

    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,
				COUNT([Bonus Header].[Consultant No_]) as NoOf7
	FROM [Bonus Header] (NOLOCK) INNER JOIN [PROVEA RUSSIA$Customer] (NOLOCK) ON [Bonus Header].[Consultant No_]=[PROVEA RUSSIA$Customer].[No_]
WHERE	[Bonus Header].[Period Bonus Calculation]=convert(int,(convert(varchar(10),DATEPART(year, @BeginDate))+convert(varchar(10),DATEPART(month, dateadd(month,-1,@BeginDate))))) 		AND [Bonus Header].[Rank End of Period]='07-DIR'
	GROUP BY [PROVEA RUSSIA$Customer].[SPP Code]

    Many Thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

     
  • Friday, June 15, 2012 3:01 PM
     
     
    Sign In to Vote
    0

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

    Answering your question: Yes, there are data.

     
  • Friday, June 15, 2012 3:06 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi There

    Can you please screenshot of your data to me.

    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

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

     
  • Friday, June 15, 2012 3:12 PM
     
     
    Sign In to Vote
    0

    The date picker is not in use.

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

     
  • Friday, June 15, 2012 3:28 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    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

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful


     
  • Friday, June 15, 2012 3:32 PM
     
     
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    0

    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

    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

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

     
  • Monday, June 18, 2012 7:43 AM
     
     
    Sign In to Vote
    0

    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
     
     Answered Has Code
    Sign In to Vote
    0

    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 Navruz3 Monday, June 18, 2012 7:48 AM
    •  
     
  • Monday, June 18, 2012 7:55 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    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,
				COUNT([Bonus Header].[Consultant No_]) as NoOf7
	FROM [Bonus Header] (NOLOCK) INNER JOIN [PROVEA RUSSIA$Customer] (NOLOCK) ON [Bonus Header].[Consultant No_]=[PROVEA RUSSIA$Customer].[No_]
WHERE	replace([Bonus Header].[Period Bonus Calculation],0,'')=convert(int,(convert(varchar(10),DATEPART(year, @BeginDate))+convert(varchar(10),DATEPART(month, dateadd(month,-1,@BeginDate))))) 		AND [Bonus Header].[Rank End of Period]='07-DIR'
	GROUP BY [PROVEA RUSSIA$Customer].[SPP Code]

    This way it will always take care of 0 as well

    Please mark it as answer if you think it resolve your problem

    Many thanks

    Syed