0

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
  • If you want the current month and the passed months why should July be visible in August and not in July? – ypercubeᵀᴹ Jun 10 '14 at 15:51
  • Because the month has to be a full month gone in order to see all the contracts funded. So if we are in June we shouldn't be able to see it because the month isn't over. – user3684379 Jun 10 '14 at 16:05
  • And I am using SQL Server 2008 R2 – user3684379 Jun 10 '14 at 16:06
  • So, you do NOT want the current month. But only up to the last day of the previous month, right? – ypercubeᵀᴹ Jun 10 '14 at 16:06
  • 1
    So YTD is for company, not company + dealer combination? Your current query has dealer_id in the group by, so it would return a separate YTD count for each company + dealer combination. This is getting more confusing every time you add details, so yes, sample data and desired results would be much more helpful. I'll post a query of what I think you want in the meantime, but you may be on your own to modify that as needed if you haven't described things adequately. – Aaron Bertrand Jun 10 '14 at 17:19
2

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:

  • Should there also be a semicolon after the END that ends the CREATE PROCEDURE statement? – ypercubeᵀᴹ Jun 10 '14 at 18:03
  • @ypercube I don't believe it is necessary at that block level, no, because nothing can occur between that and the GO - it may be valid but there are other block constructs where it isn't (try putting a semi-colon after BEGIN TRY / END TRY / BEGIN CATCH / END CATCH - they don't all work. – Aaron Bertrand Jun 10 '14 at 18:06
  • For the record, I was just curious, did not mean the comment to correct you. – ypercubeᵀᴹ Jun 10 '14 at 18:16
  • @ypercube I didn't take it that way as a correction - was just answering the question. :-) – Aaron Bertrand Jun 10 '14 at 18:18
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.