Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Dec 2006
    Posts
    417
    Thanks
    168
    Thanked 1 Time in 1 Post

    mySQL query for every 4 week block in a calendar week?

    I have a table with about 5 years of dates (2011 - 2016).

    Each date is stored in a column called "dt" as YYY-MM-DD (ie. 2012-01-04) and "y" as year (2012, 2013). I also have a column called "w" which is the week number for each week in the calendar year which helps. It resets to 0 for the first week of each year and then increments to 52 to the last week.

    I am trying to find the correct MYSQL SELECT query to extract 4 week blocks (pay periods) from each calendar year.

    So for 2014, one would expect 13 pay blocks.. I want to return the results as follows:

    Period 1
    Week 1 (2012-12-31 to 2012-01-05)
    Week 2 (2012-01-06 to 2012-01-12)
    Week 3 (2012-01...
    Week 4 (2012..

    Period 2
    Week 5 (2012..
    Week 6 (2012..
    Week 7 (2012..
    ...

    Period 3

    ..

    ..

    Period 13

    ..

    Code:
    $currentYear="2014"
    for ($i = 0; $i <= 52; $i++) {
    $sql="SELECT * FROM calendar WHERE Y='$currentYear' AND w=$i GROUP BY w";
    $result=mysql_query($sql);
    while($row=mysql_query($result)){
      ... is this the most effiecient way?  once I figure out the select statement then I will switch the PHP over to MYSQLi
    
    }
    }
    I am not even sure how to structure the SELECT statement efficiently which I suspect should be resulted in a while loop... what is the most efficient way to do this?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    You know, there really was no reason to have the Y and W columns in that table.

    MySQL could easily derive both of those from the DT field.

    But oh well, since you have them, let's use them.

    Anyway, the point you are completely missing is *WHAT* you want to show for each week.

    That is, do you want to show the detailed payroll (every paycheck for every person) for each week? Or so you want to show the total payroll for the week? Or what?

    No, you CERTAINLY should *NOT* do a FOR loop getting one week at a time. Yuck.

    But you need to tell us what data you want to display for each week.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Bobafart (01-19-2013)


  •  

    Posting Permissions

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