0

I have create a procedure which returns the Quarter dates, like Q1 date, Q2 date, Q3 date and Q4 date based on the QuarterString provided, and that I am using in my other stored procedure to get the dates. The problem is if I pass QuarterString as '1' then its not giving any records for as it uses other Quarters which are empty(supposed to be). but if I give QuarterString as '12' then its returning the records. Here is my first procedure for Quarters.

alter proc spGetQuarters 
@myvar nvarchar(10),
@year nvarchar(4),
@Q1S datetime = null out,
@Q1E datetime = null out,
@Q2S datetime= null out,
@Q2E datetime= null out,
@Q3S datetime= null out,
@Q3E datetime= null out,
@Q4S datetime= null out,
@Q4E datetime= null out
as 
begin 
--Quarter1
if CHARINDEX('1',@myvar) > 0
begin
set @Q1S =ISNULL((CONVERT(Datetime, CAST(@year AS varchar(10)) + '-10-01', 120)),'')
set @Q1E = ISNULL((CONVERT(Datetime, CAST(@year AS varchar(10)) + '-12-31', 120) ),'')
end
--Quarter2
if CHARINDEX('2',@myvar) > 0
begin
set @Q2S = ISNULL((CONVERT(Datetime, CAST(@year+1 AS varchar(10)) + '-01-01', 120)  ),'')
set @Q2E = ISNULL((CONVERT(Datetime, CAST(@year+1 AS varchar(10)) + '-03-31', 120) ),'')
end
--Quarter3
if CHARINDEX('3',@myvar) > 0
begin
set @Q3S =ISNULL(( CONVERT(Datetime, CAST(@year+1 AS varchar(10)) + '-04-01', 120)),'')  
set @Q3E = ISNULL((CONVERT(Datetime, CAST(@year+1 AS varchar(10)) + '-06-30', 120) ),'')
end
--Quarter4
if CHARINDEX('4',@myvar) > 0
begin
set @Q4S =ISNULL(( CONVERT(Datetime, CAST(@year+1 AS varchar(10)) + '-07-01', 120) ),'')
set @Q4E = ISNULL((CONVERT(Datetime, CAST(@year+1 AS varchar(10)) + '-09-30', 120) ),'')
end
end


DECLARE @q1start nvarchar(20) , @q1end nvarchar(20), @q2start nvarchar(20),
 @q2end nvarchar(20) , @q3start nvarchar(20), @q3end nvarchar(20), @q4start nvarchar(20), @q4end nvarchar(20);
exec spGetQuarters '1', 2014,  @Q1S = @q1start output ,@Q1E= @q1end out ,@Q2S =@q2start out , 
@Q2E =@q2end out ,@Q3S=@q3start out, @Q3E =@q3end out ,@Q4S =@q4start out  , @Q4E =@q4end out
print @q1start
print @q1end
print @q2start
print @q2end
print @q3start
print @q3end
print @q4start
print @q4end

--Here is I am using the Quarter procedure .
set @MyQuery='
SELECT  DISTINCT 
ABC
FROM XYZTable
WHERE (a = ''0'' )  AND (b = ''b''')and 
(EXPECTDT >= '''+@q1start+'''  and EXPECTDT <=  '''+@q1end+'''   or     EXPECTDT >=  '''+@q2start+'''  and EXPECTDT <= '''+@q2end+''') and Div IN (''' +  @my_Div +''' ) 
 AND (STATUS = ''T'' OR STATUS = ''A'')'
Daniel A. White
  • 187,200
  • 47
  • 362
  • 445
Khan
  • 271
  • 1
  • 5
  • 13
  • Just an aside, why is QuarterString not an int?? It would make checking values much simpler - using = instead of charindex – ADyson Jan 04 '17 at 11:31
  • **ADyson**, Even If I make that it does not work in the proper order. – Khan Jan 04 '17 at 11:33
  • I wasn't saying it would solve the problem, was just saying it seemed a bit pointless to write all that charindex stuff if you are always going to pass in a number. Year could be an int too. – ADyson Jan 04 '17 at 11:36
  • **ADyson**, ok, I got it, but did you have any solution for my issue. – Khan Jan 04 '17 at 11:39
  • see this question - http://stackoverflow.com/questions/18844218/get-quarters-startdate-and-enddate-from-year the query in the accepted answer there gives you a much simpler way to get the quarters. You can probably adapt to your needs to only return the quarter requested. – ADyson Jan 04 '17 at 11:40
  • **ADyson**, Actually I want to have a stored procedure which I can use in any other stored procedure or etc. and my date conditions in the using stored procedure if you see is using all quarters, if remaining 3 quarters are empty or with some dates. – Khan Jan 04 '17 at 11:45
  • ok so you can adapt that code to only output the quarter you want, just substitute the quarter ID variable into the where clause. And you can put the output dates into variables to use elsewhere, same as you are doing now. – ADyson Jan 04 '17 at 12:15
  • **ADyson**, yes now you got it. – Khan Jan 04 '17 at 12:29

0 Answers0