Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2012
    Posts
    5

    Unanswered: Help Writing Query

    I want to pull data based on sales personel from the past 24 months into a table. This may not be possible, but ideally I would want the table to be set up like this (just a quick show):
    December 2012 November 2012 October 2012
    Sold Bought Sold Bought Sold Bought
    J.J.
    R.M.
    E.L.

    So have a heading of the month/year and a sub heading of sold and bought, then have the sales person's name. Simply running
    Code:
    Select salesPerson, unitsSold, unitsPurchased, dateSold, datePurchased 
    FROM employeeSales 
    ORDER BY datePurchased DESC
    Shows what I want to, but not in the correct formatting, I spend endless amount of time formatting within Excel. Is the above possible, and if so how would I Set that up?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    Select salesPerson, unitsSold, unitsPurchased, dateSold, datePurchased 
       FROM employeeSales 
       WHERE  DateAdd(month, -24, GetDate()) < datePurchased
       ORDER BY datePurchased DESC
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2012
    Posts
    5
    Quote Originally Posted by Pat Phelan View Post
    Code:
    Select salesPerson, unitsSold, unitsPurchased, dateSold, datePurchased 
       FROM employeeSales 
       WHERE  DateAdd(month, -24, GetDate()) < datePurchased
       ORDER BY datePurchased DESC
    -PatP

    What does the DateAdd(month, -24, GetDate()) piece do?

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    What happens when you run it? What happens when you change the 24 to something else?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by IndigoMontoya View Post
    What does the DateAdd(month, -24, GetDate()) piece do?
    It computes one of the parts you specified in post #1 above. Check out DATEADD (Transact-SQL) and GETDATE (Transact-SQL) and it should all make sense.

    As papadi suggested, run the SQL and play with the expression just a bit and it ought to become crystal clear pretty quick!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Dec 2012
    Posts
    5
    That is a really cool T-SQL Feature, I will make note of it and use it again!!

    One additional question, the code is listing out (example) each date a unit is sold so it could be 12/18/2012, 12/17/2012, 12/16/2012 is there a way to only show one entry per month, so using the example above it would show 3 for December, not 3 entry's?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a guess based on assumptions for a number of details that you didn't explicitly specify, so you need to read it critically to be sure that it does what you want. For the sake of efficiency this computes using the first of the month for the datePurchased.
    Code:
    Select Count(*)
    ,  DateAdd(month, DateDiff(month, 0, datePurchased), 0) AS MonthPurchased
    ,  salesPerson
       FROM employeeSales 
       WHERE  DateAdd(month, -24, GetDate()) < datePurchased
       GROUP BY DateAdd(month, DateDiff(month, 0, datePurchased), 0)
       ORDER BY DateAdd(month, DateDiff(month, 0, datePurchased), 0) DESC
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Dec 2012
    Posts
    5
    Apologies, I did not mean to omit any information critical to the completion of the SQL Statement. However, even in my omission you were able to write a valid SQL statement for exactly what I was going for. Thank you tremendously for that. http://www.dbforums.com/db_images_v3...ilies/beer.gif

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No apology needed. I always tell folks (especially at the hardware store) that whenever people get into trouble it is rarely what they know that got them into trouble as much as what they didn't know!

    I've been at the "database thing" and working in business for a few decades, so I often make good guesses about what people want. I usually know enough to at least warn them when I'm making guesses and I try to write code that is readable enough that they can confirm those guesses.

    Its good to know that you've got what you wanted!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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