Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    2

    Question Unanswered: Calculating sales for the previous 12 months

    Hi Group,
    I'm trying to figure out a query that will give me the sum of qty_sold for the previous (last) 12 months. My table consist of part_code, qty_sold, and sale_date. Can someone please help me with the query?

    Thx,
    aperales10

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    where saledate >= dateadd('Month', -12, getdate())
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2009
    Posts
    2
    thx blind man. I noticed that stament subtracts the month but keeps the day of year. is there a way to exclude the current month.

    I came up with this statement but it is returning the message: incorrect sytax near ')'. Do you know what I am doing wrong?

    SELECT sale_date FROM sumsales where
    (sale_date BETWEEN CONVERT(varchar,
    DATEADD(Month, - 12, DATEADD(Day, - (1 * (DAY(GETDATE()) - 1)), GETDATE())), 101) AND CONVERT(varchar, DATEADD(Day, - 1,
    DATEADD(Day, - (1 * (DAY(GETDATE()) - 1)), GETDATE())), 101))) AS prev_12_sls)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Looks overly complicate to me, so you are probably just missing a parenthesis somewhere.

    What, for instance, is this supposed to accomplish:
    1 * (DAY(GETDATE()) - 1)
    It's been a while since 6th grade math class, but as I recall anything multiplied by one is itself.

    Give me a few sample dates and the results you would like for each, and I'll have a better idea of what you are trying to accomplish.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    aperales10, try this for calculating the start month (day 1) and end month (day last day)

    First day of month 12 months from last day of previous month:

    DATEADD(M, -12, DATEADD(D, -(DAY(date-col) -1), date-col) )

    and last day of previous month:

    DATEADD(D, -DAY(date-col), date-col)

    Today's date: 2009-05-18
    Last day prev month: 2009-05-18 - 18 days = 2009-04-30
    First day 12 months from previous end of month: (2009-05-18 - (18 - 1) ) - 12 months = 2008-05-01

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •