www.webdeveloper.com
Results 1 to 2 of 2

Thread: Showing the current week in a table

Hybrid View

  1. #1
    Join Date
    Nov 2007
    Posts
    8

    Showing the current week in a table

    Hello,

    Hopefully someone will be able to shed some light on this as I am stumped!

    Basically I have a website were users enter deals into. I have two tables in my database:

    One called "users" - This stores user information for example user ID username password etc.
    Another table called "deals" - This stores deal information. Deal name, deal ID, user ID (which user did the deal), Deal amount, and Date the format for date is (YYYY-MM-DD).

    I am now trying to display the total amount of deals done by each user for a current week for each day. For example in a 7 day calander type of style:

    USERS SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY

    USER 1 65 70 56 38 87 33 44

    USER 2 65 70 56 38 87 33 44

    USER 3 65 70 56 38 87 33 44

    Hopefully this makes sense so far!

    These are the sql queries i've come up with so far...

    This selects each deal done in the current week:

    Code:
    SELECT u.firstname, d.customername, d.dealamount, d.dealdate
    FROM deals d, users u
    WHERE u.uid = d.useriD
    AND YEARWEEK( dealdate ) = YEARWEEK( CURRENT_DATE );

    For example:

    USER 1 DEAL1 65 2013-03-15
    USER 1 DEAL2 65 2013-03-16


    This puts down the total sum for the week by user:

    Code:
    SELECT SUM( d.dealamount ) , u.firstname
    FROM deals d, users u
    WHERE u.uid = d.userid
    AND YEARWEEK( dealdate ) = YEARWEEK( CURRENT_DATE )
    GROUP BY u.firstname
    For example:

    USER 1 130

    Can anyone help expand on this so it comes out with current days along the top?

    Thanks in advanced!

  2. #2
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    If you're wanting to do dynamically named columns (which is OK, and I typically do this when someone wants a "report" they could use in excel or whatever), you'll need to use at least two queries if you want to partition across a preset segment of time (like, tomorrow... your column headings would be different)... ie: your column headings are determined at runtime

    you could alternatively do what you suggested, which is something like this:

    | uid | total |SUNDAY | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY |

    and then write it like this:

    Code:
    SELECT 
      u.uid as userID,
      SUM(dealamount) as total,
      SUM(IF(DAY(dealdate) = 'MONDAY', d.dealamount, 0)) as 'MONDAY',
      SUM(IF(DAY(dealdate) = 'TUESDAY', d.dealamount, 0)) as 'TUESDAY',
      SUM(IF(DAY(dealdate) = 'WEDNESDAY', d.dealamount, 0)) as 'WEDNESDAY',
      SUM(IF(DAY(dealdate) = 'THURSDAY', d.dealamount, 0)) as 'THURSDAY',
      SUM(IF(DAY(dealdate) = 'FRIDAY', d.dealamount, 0)) as 'FRIDAY',
      SUM(IF(DAY(dealdate) = 'SATURDAY', d.dealamount, 0)) as 'SATURDAY',
      SUM(IF(DAY(dealdate) = 'SUNDAY', d.dealamount, 0)) as 'SUNDAY',
    
    FROM deals d 
    JOIN users u 
      ON u.uid = d.useriD
    
    WHERE YEARWEEK( dealdate ) = YEARWEEK( CURRENT_DATE )
    GROUP BY u.uid; //This part is important because it is a summary/aggregate
    edit:
    just a note about style of code, please refrain from doing this:

    FROM deals d, users u
    WHERE u.uid = d.userid

    it might not matter at first, but when you start writing more advanced queries this will cause MySQL to create the product of both tables (INSANELY INEFFICIENT)... so if you have 1,000,000 users and 1,000,000 deals.... MySQL may try to create a tmp table with 1,000,000,000,000 records to handle this kind of query.... I say try because it will basically just waste hours and hours of computing time and not work.
    You wrote a cross join (or more notoriously called "a common join") and while MySQL may optimize this simple query, since it's such a common `mistake`, you should really be writing it like this:

    SELECT ... FROM deals INNER JOIN users ON (deads.userid = users.uid) ...

    Just to recap:
    FROM a JOIN b ON a.id=b.aid
    is short hand for
    FROM a INNER JOIN b ON a.id=b.aid

    and

    FROM a,b
    is short hand for
    FROM a CROSS JOIN b
    which is the cartesian product

    That all being said, sometimes a comma/cross join is a very elegant way to solve a problem. It can be the perfect tool in certain circumstances.
    Last edited by eval(BadCode); 03-20-2013 at 06:08 PM.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles