Finding an end date based off of a parameter passed in?

[renee35]

I have a query that I have created with a parameter of @Qtr and based on this there is a @start and @end date, see below:

SET @Qtr = 3
SET @Year = 2008

-- determine dates for @qtr parameter
select @StartDate = case
when @qtr = 1 then '1/1/' + Convert(VarChar(4), @Year)
when @qtr = 2 then '4/1/' + Convert(VarChar(4), @Year)
when @qtr = 3 then '7/1/' + Convert(VarChar(4), @Year)
when @qtr = 4 then '10/1/' + Convert(VarChar(4), @Year)
when @qtr = 5 then '1/1/' + Convert(VarChar(4), @year)-- should include entire year?
-- Should not happen
end,
@EndDate = Case
when @qtr = 1 then Convert(DateTime, '4/1/' + Convert(VarChar(4), @Year)) - 1
when @qtr = 2 then Convert(DateTime, '7/1/' + Convert(VarChar(4), @Year)) - 1
when @qtr = 3 then Convert(DateTime, '10/1/' + Convert(VarChar(4), @Year))- 1
when @qtr = 4 then Convert(DateTime, '1/1/' + Convert(VarChar(4), @Year + 1)) - 1
when @qtr = 5 then Convert(DateTime, '1/1/' + Convert(VarChar(4), @year + 1)) - 1 -- should include entire year?

end

Now what I am trying to do is when I select @qtr = 3, how can I indicate in my code:

where a.date is between "previous qtr @end" and a.closedate?

My issue is how to I go about getting the "previous qtr @end" date? In this case it would be @qtr2 end date.

Thanks a bunch?

Thanks a bunch!!

-T

 

[RiverGuy]

Several issues/concerns:

1. Why do you need the previous quarter end date? I've never been asked to do a report by time period in which you would overlap 1 day across time periods like this. Why not just use quarter start and quarter end?

2. You should use a universal datetime format. Instead of M/dd/yyyy, use yyyyMMdd.

3. If you MUST get the end date for the previous quarter, just subtract 1 with the DATEADD function from your start date. You can make a new variable for simplicity.
DECLARE @PrevEndDate DATETIME
SELECT @PrevEndDate = DATEADD(day, -1, @StartDate)

 

[renee35]

Reason I need a previouse qtr enddate is because the check can be issued (checkdate) during qtr, but not actually effective until the next qtr? There is an effdate and checkdate in the system and depending on those dates can throw the numbers off when trying to close the books?

Thanks!

Thanks a bunch!!

-T

 

[AGus01]

Just create a stored proc and call it twice.

Stored proc:

CREATE PROCEDURE [dbo].[Term_Date]
	-- Add the parameters for the stored procedure here
	@Qtr int,
	@Year int,
	@StartDate varchar(10) OUTPUT ,
	@EndDate varchar(10) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


	-- determine dates for @qtr parameter
	select @StartDate = case
                when @qtr = 1 then '1/1/' + Convert(VarChar(4), @Year)
                when @qtr = 2 then '4/1/' + Convert(VarChar(4), @Year)
                when @qtr = 3 then '7/1/'  + Convert(VarChar(4), @Year)
                when @qtr = 4 then '10/1/'  + Convert(VarChar(4), @Year)
                when @qtr = 5 then '1/1/' + Convert(VarChar(4), @year)-- should include entire year?
                             -- Should not happen
                   end,
       @EndDate = Case
                when @qtr = 1 then Convert(DateTime, '4/1/' + Convert(VarChar(4), @Year)) - 1
                when @qtr = 2 then Convert(DateTime, '7/1/' + Convert(VarChar(4), @Year)) - 1
                when @qtr = 3 then Convert(DateTime, '10/1/' + Convert(VarChar(4), @Year))- 1
                when @qtr = 4 then Convert(DateTime, '1/1/' + Convert(VarChar(4), @Year + 1)) - 1
                when @qtr = 5 then Convert(DateTime, '1/1/' + Convert(VarChar(4), @year + 1)) - 1 -- should include entire year?

                   end
END

Code to call stored proc:

DECLARE @QtrCurrent int, @QtrPrevious int, @Year int


SET @QtrCurrent = 3
SET @QtrPrevious = 2
SET @Year = 2008

DECLARE @StartDateCurrent varchar(10)
DECLARE @EndDateCurrent varchar(10)
DECLARE @StartDatePrevious varchar(10)
DECLARE @EndDatePrevious varchar(10)

EXEC Term_Date 
	@QtrCurrent, 
	@Year,
	@StartDateCurrent OUTPUT,
	@EndDateCurrent OUTPUT
	 


EXEC Term_Date
	@QtrPrevious, 
	@Year,
	@StartDatePrevious OUTPUT,
	@EndDatePrevious  OUTPUT

 

[renee35]

Thanks!

Thanks a bunch!!

-T