#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    MS SQL QUERY NEEDED: Sales vs customer


    I need to create the following query. Hope someone can help.

    Query 2 tables with results ordered.

    Table 1: SUBSCRIBERS, which includes a field with date joined and email address.
    Table 2: Sales which aslo includes a date field and email address.

    Both tables can be queried by a Subscriber_ID field

    INTENDED RESULT: To discover which subscribers are still buying from us.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    this is far too vague. How you do you define "still buying from us"? What exactly do you wanna find out? All people who did a sale this week? This year? At all?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by Jacques1
    Hi,

    this is far too vague. How you do you define "still buying from us"? What exactly do you wanna find out? All people who did a sale this week? This year? At all?
    Hi Jacques

    I'm trying to cull our subscribers list to active subscribers. I'll do this by running the query. So for example, I see subscribers have not purchased from us for say 12 months or 2 or more years , then I can assume this subscriber is no longer active.

    The query would find the subscriber email in the SUBSCRIBERS table and try to match it to the email address in SALES table.

    Both tables have a date field.
    SUBSCRIBERS date field has the date subscribed
    SALES date field has the sale dates

    Cheers, Jackson
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by Jackson Moon
    Hi Jacques

    I'm trying to cull our subscribers list to active subscribers. I'll do this by running the query. So for example, I see subscribers have not purchased from us for say 12 months or 2 or more years , then I can assume this subscriber is no longer active.

    The query would find the subscriber email in the SUBSCRIBERS table and try to match it to the email address in SALES table.

    Both tables have a date field.
    SUBSCRIBERS date field has the date subscribed
    SALES date field has the sale dates

    Cheers, Jackson
    Hi Jackson,
    Please,look into this query.

    Code:
    select s.SalesID,s.SalesDate,sb.Subscriber_Email from #Sales s
    inner join #Subscriber sb
    on s.Subscriber_id=sb.Subscriber_id and s.SalesDate='2013-07-04'
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by wasifmuneer
    Hi Jackson,
    Please,look into this query.
    that's only going to return subscribers who bought something today

    please try again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    that's only going to return subscribers who bought something today

    please try again
    hi,
    you can use between 'some date' and 'Some date' so that you can find sales from date range.

    hope this is clear to you.

    Thanks
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by wasifmuneer
    hope this is clear to you.
    oh, it's clear to me all right

    the question is, is it clear to the OP (original poster)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo