Get Date Limitation

Question

So what I need is a limitation for the user of the funded contracts. So this is what I am trying to implement. I am trying to let the user enter dates within the year 2014, which will bring up how many funded contracts are there for each month. I ONLY want up to the last day of the previous month and the passed months within the year 2014. So for example we are in June, if the user inputs any dates that are before June he can see those months of how many funded contracts are in there - but not any contract from June.

So the user should not be able to see July until we hit August which we can see July from there. Here is my query.

I feel that my where clause for the GetDate is incorrect. Any help will be appreciated

          Alter Proc spGetAdminTotalYTD

           (@Begin_Date  DATETIME, 
            @End_Date DATETIME,
            @program int=null) As

    Begin
Declare @year int
Set @year = 2014

        SELECT  d.name, a.dealer_code, b.last_name, b.city, b.state, b.phone
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 1 THEN 1 ELSE NULL END) January
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 2 THEN 1 ELSE NULL END) Feburary
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 3 THEN 1 ELSE NULL END) March
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 4 THEN 1 ELSE NULL END) April
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 5 THEN 1 ELSE NULL END) May
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 6 THEN 1 ELSE NULL END) June
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 7 THEN 1 ELSE NULL END) July
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 8 THEN 1 ELSE NULL END) August
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 9 THEN 1 ELSE NULL END) September
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 10 THEN 1 ELSE NULL END) October
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 11 THEN 1 ELSE NULL END) November
            , COUNT(CASE WHEN  MONTH(c.Funded_date) = 12 THEN 1 ELSE NULL END) December,
            COUNT(1) AS YTD
        FROM tdealer a JOIN tContact b ON a.contact_id = b.contact_id 
             JOIN tContract  c ON a.dealer_id = c.dealer_id JOIN tCompany d 
             ON  c.company_id = d.company_id

       WHERE YEAR(c.Funded_date) = @Year
             AND c.Funded_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0,GETDATE())-4,0)
             AND c.Funded_date <  DATEADD(MONTH, DATEDIFF(MONTH,-1,GETDATE())-4,0)
             AND c.program_id = @program
             AND c.funded_date Between @Begin_Date And @End_Date

            GROUP BY
    d.name,
    a.dealer_code,
    b.last_name,
    b.city,
    b.state,
    b.phone
    END

Answer 1

Try this procedure instead:

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

Many of the comments have a lot of background, and it's not just me ranting about how you should write code like I do:

• Bad habits to kick : avoiding the schema prefix
• Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)
• Bad habits to kick : mis-handling date / range queries
• What do BETWEEN and the devil have in common?
• Ladies and gentlemen, start your semi-colons!
• My stored procedure "best practices" checklist

Answer 2

If you are already specifying the year, than you can keep it simple by removing

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

If you are just looking to get the first day of the month, this might be a simple approach:

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

So the new WHERE clause could look like:

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