问获取日期限制

EN

CREATE PROCEDURE dbo.spGetAdminTotalYTD_AB -- always use schema prefix!
  @Begin_Date  DATETIME, 
  @End_Date    DATETIME,
  @program_id  INT = NULL -- use a name consistent with the column
AS
BEGIN
  SET NOCOUNT ON; -- always use SET NOCOUNT ON and semi-colons

  DECLARE @year INT = 2014; -- why should this be hard-coded? 
                     -- So you can refactor the code in January? 
                     -- And again the following January?

  -- let's get the beginning of this year instead:
  DECLARE @thisyear DATETIME = DATEADD(DAY, 
    1-DATEPART(DAYOFYEAR, GETDATE()), DATEDIFF(DAY,0,GETDATE()));

  -- if @Begin_Date pre-dates that, make it Jan 1:
  DECLARE @startrange DATETIME = CASE
    WHEN @Begin_Date < @thisyear THEN @thisyear ELSE @Begin_Date END;

  -- make sure the end of the range is actually at the
  -- end of the month *before* the end date they actually
  -- specify. Assumes they're basing this on GETDATE().
  -- If they pass the last day of the month, you may need
  -- to add logic to make that a valid selection.
  DECLARE @endrange DATETIME = DATEADD
  ( 
    DAY, 
    1-DATEPART(DAY, DATEADD(DAY, DATEDIFF(DAY,0,@End_Date),0)), 
    DATEADD(DAY, DATEDIFF(DAY,0,@End_Date), 0)
  );

  ;WITH x AS
  (
    SELECT company_id, dealer_id, 
      [Jan],[Feb],[Mar],[Apr],[May],[Jun],
      [Jul],[Aug],[Sep],[Oct],[Nov],[Dec],

      -- to group YTD by company_id + dealer_id:
      YTD = [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]
           +[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec]

      -- to group YTD by only company_id:
      YTD2 = SUM([Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]
           +[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec]) 
           OVER (PARTITION BY Company_id)
    FROM
    (
      SELECT company_id, dealer_id,
        [Jan] = COALESCE([Jan],0), [Feb] = COALESCE([Feb],0), [Mar] = COALESCE([Mar],0),
        [Apr] = COALESCE([Apr],0), [May] = COALESCE([May],0), [Jun] = COALESCE([Jun],0),
        [Jul] = COALESCE([Jul],0), [Aug] = COALESCE([Aug],0), [Sep] = COALESCE([Sep],0),
        [Oct] = COALESCE([Oct],0), [Nov] = COALESCE([Nov],0), [Dec] = COALESCE([Dec],0)
      FROM
      (
        SELECT m = LEFT(DATENAME(MONTH,DATEADD(MONTH,
                   DATEDIFF(MONTH,0,Funded_date), 0)),3),
          company_id, 
          dealer_id, 
          mc = COUNT(*)
        FROM dbo.tContract
        WHERE program_id = @program_id
          AND Funded_date >= @startrange
          AND Funded_date < @endrange -- don't use BETWEEN for range queries
        GROUP BY LEFT(DATENAME(MONTH,DATEADD(MONTH,
            DATEDIFF(MONTH,0,Funded_date), 0)),3),
          company_id, 
          dealer_id
      )
      AS x
      PIVOT 
      (
        MAX(mc) FOR m IN 
        (
          [Jan],[Feb],[Mar],[Apr],[May],[Jun],
          [Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
        )
      ) AS p
    ) AS y
  ) 
  SELECT 
    comp.name, deal.dealer_code, -- don't use meaningless aliases like a,b,c,d
    cont.last_name, cont.city, cont.[state], cont.phone, 
    x.[Jan],x.[Feb],x.[Mar],x.[Apr],x.[May],x.[Jun],
    x.[Jul],x.[Aug],x.[Sep],x.[Oct],x.[Nov],x.[Dec],
    x.YTD, x.YTD2
  FROM x
  INNER JOIN dbo.tDealer AS deal
  ON x.dealer_id = deal.dealer_id
  INNER JOIN dbo.tCompany AS comp
  ON x.company_id = comp.company_id
  INNER JOIN dbo.tContact AS cont
  ON deal.contact_id = cont.contact_id;
END
GO

AND c.Funded_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-4, 0)

DATEADD(day,-(DAY( getdate())-1) ,Convert(date,getdate()))

WHERE YEAR(c.Funded_date) = @Year 
AND c.Funded_date < DATEADD(day,-(DAY( getdate())-1) ,Convert(date,getdate()))
and c.program_id = @program 
And c.funded_date Between @Begin_Date And @End_Date