Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Stored Proc Help

Answered Stored Proc Help

  • Wednesday, February 13, 2013 7:06 PM
     
     
    Sign In to Vote
    0

    Hi All,

    How to add existing stored procedures below SQL Statements.If i am running this stored proc in first quarter it will pick only first quarter dates.How to add case or if statement.

    --FIRST QUARTER RUN

    SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()) -1,0) AS DOSSTART

    ,dateadd(quarter, datediff(quarter, -1, getdate()) - 1, -1) AS DOSEND

     ,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()) -1,0) AS PAIDSTART

    ,DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1) AS PAIDEND

    --SECOND QUARTER RUN

    SELECT dateadd(MM, datediff(MM, 0, getdate())-10, 0) AS DOSSTART

    ,DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1) AS DOSEND

    , dateadd(MM, datediff(MM, 0, getdate())-10, 0) AS PAIDSTART

    ,DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),89) AS PAIDEND

    --THIRD QUARTER RUN

    SELECT dateadd(MM, datediff(MM, 0, getdate())-7, 0) AS DOSSTART

    ,DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),89) AS DOSEND

    ,dateadd(MM, datediff(MM, 0, getdate())-7, 0) AS PAIDSTART

    ,DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),180) AS PAIDEND

    --FOURTH QUARTER

    SELECT dateadd(MM, datediff(MM, 0, getdate())-4, 0) AS DOSSTART

    ,DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),180) AS DOSEND

    ,dateadd(MM, datediff(MM, 0, getdate())-4, 0 )AS PAIDSTART

    ,DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),272) AS PAIDEND

    Thanks

    CMK..

     

All Replies

  • Wednesday, February 13, 2013 7:50 PM
    Moderator
     
      Has Code
    Sign In to Vote
    1
     
    --declare @dt datetime
--Set @dt='1/2/2013'
--Set @dt='4/2/2013'
--Set @dt='8/2/2013'
--Set @dt='11/2/2013'

--Replace Getdate() with @dt to test and the code will run as you required

SELECT  DATEADD(Year,-1,DATEADD(Q, DATEDIFF(Q, 0, Getdate()) , 0) ) as DOSSTART,
 Dateadd(ms,-3,DATEADD(Year,0,DATEADD(Q, DATEDIFF(Q, 0, Getdate()) , 0) )) as DOSEND
 ,DATEADD(Year,-1,DATEADD(Q, DATEDIFF(Q, 0, Getdate()) , 0) ) as PAIDSTART
 , Dateadd(ms,-3,DATEADD(Q, DATEDIFF(Q, 0, Getdate()) +1 , 0)) as PAIDEND

     
  • Wednesday, February 13, 2013 9:24 PM
     
      Has Code
    Sign In to Vote
    0

    I removed the time from the dates in Jingyang Li example above. 

    declare @dt datetime
--Set @dt='1/2/2013'
--Set @dt='4/2/2013'
--Set @dt='8/2/2013'
Set @dt='11/2/2013'
--Replace Getdate() with @dt to test and the code will run as you required
SELECT  DATEADD(Year,-1,DATEADD(Q, DATEDIFF(Q, 0, @dt) , 0) ) as DOSSTART,
 convert(datetime,cast(Dateadd(ms,-3,DATEADD(Year,0,DATEADD(Q, DATEDIFF(Q, 0, @dt) , 0) )) as varchar(12)),111) as DOSEND
 ,DATEADD(Year,-1,DATEADD(Q, DATEDIFF(Q, 0, @dt) , 0) ) as PAIDSTART
 , convert(datetime,cast(Dateadd(ms,-3,DATEADD(Q, DATEDIFF(Q, 0, @dt) +1 , 0)) as varchar(12)),111) as PAIDEND

    Chuck


     
  • Wednesday, February 13, 2013 9:30 PM
    Moderator
     
     
    Sign In to Vote
    0
    Actually I keep the time portion of the end dates for a a real reason. It is the the end time for datetime  data type for the date. If you start with mid night, you may be on risk to miss some data on that ending date.
     
  • Wednesday, February 13, 2013 9:36 PM
     
     
    Sign In to Vote
    0
    Yes, I normally will go with the full datetime, but cmk1's procedure did not have times.

    Chuck

     
  • Wednesday, February 13, 2013 10:13 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi cmk1

    I ran your above queries but I can't seem to tell what your dates are supposed to be. They seem wrong to me.

    The question I believe you are asking is that you want one query that run at any date will give you back the correct 1 row result.

    Here is a case statement that could be useful for you, if you include the meaning behind the dates I might be able to elaborate on my query.

    -- Logic
DECLARE @QUARTER INT
SELECT @QUARTER = CASE
	WHEN MONTH(CURRENT_TIMESTAMP) BETWEEN 1 AND 3 THEN 1
	WHEN MONTH(CURRENT_TIMESTAMP) BETWEEN 4 AND 6 THEN 2
	WHEN MONTH(CURRENT_TIMESTAMP) BETWEEN 7 AND 9 THEN 3
	WHEN MONTH(CURRENT_TIMESTAMP) BETWEEN 10 AND 12 THEN 4
END

    Pérez