September 28, 2008 at 4:05 pm
HI,
I am trying to define the condition on month so that my query should give me the desired result.
If my months are from 04-12 then it should give the data with respect to the current year and if the months are 01-03 then it should give the data of the next year, therefore i am defining my query as,
----------------------------------------------------------------
Select cmonth,
comp_id,
cyear,
country,
sum(sales_amt_us) as [Amount]
from tbcr_base_sales
where twnbu='SV1'
and sales_dept<>'SVBH02'
and cyear=2008
and ( cmonth>=04) or (cmonth<=03 and cyear=(cyear+1))
group by cmonth,cyear,comp_id,country
-------------------------------------------------------------
it is picking the right data for the current year but not giving the data for the incremented year.
kindly suggest..
September 28, 2008 at 4:43 pm
sal527 (9/28/2008)
and cyear=(cyear+1))
Well, that's never going to be true. A value is never equal to itself + 1.
Considering that you're hardcoding 2008 in the other spot, shouldn't that be
or (cmonth<=03 and cyear=2009)
Otherwise, declare a variable and do something like this:
declare @currentYear smallint
set @currentYear = 2008
Select cmonth,
comp_id,
cyear,
country,
sum(sales_amt_us) as [Amount]
from tbcr_base_sales
where twnbu='SV1'
and sales_dept<>'SVBH02'
and cyear=@currentYear
and ( cmonth>=04) or (cmonth<=03 and cyear=(@currentYear+1))
group by cmonth,cyear,comp_id,country
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2008 at 5:41 pm
Thanks for the support, it is working fine when it is a hardcoded value but in my case i hardcoded it for the test, if cyear is passed as the parametre in the report (irrespective of the year), then how it might change in the code.
Kindly suggest..
September 28, 2008 at 6:00 pm
As in the example I gave, just make @currentyear a parameter rather than a variable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2008 at 3:28 pm
Part of the "where" clause is
and cyear=@currentYear
and ( cmonth>=04) or (cmonth<=03 and cyear=(@currentYear+1))
This is, of course, based on the original post where the comparison was to the literal '2008'. I don't see how both the first condition and the second option in the "OR" condition could be true. You can't have both cyear=@currentYear and cyear=(@currentYear+1). Try combining the year comparison and month comparisons, then using "OR".
where twnbu='SV1'
and sales_dept<>'SVBH02'
and ((cyear = @currentyear and month>=4) OR (cyear = @currentyear+1 and month<= 3) )
Your value of current year may be an input parameter to a SP as Gail suggests, or you could let SQL Server get it from the operating system. Take a look at the Help (Books On Line) for information on functions getdate() and Datepart().
This sort of windowing can be tricky, so you want to be sure you really understand how it works so if it comes up again, it won't be such a pain to work out. In many applicaions, the breakpoint could slide along depending on the current date. That is, rather than always breaking at month 4, the application may break dates at a point two months (for example) before the current month. Understanding the solution to this current task will help if you run up against the sliding date variant.
October 6, 2008 at 7:59 am
Thanks for all your support.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply