Solved

select date sql (only show results within this week)

Posted on 2004-09-28
12
526 Views
Last Modified: 2006-11-17
I am trying to select all the records to display that are only within the current week

This is my SQL

SELECT date, projectnumber, fullname, hoursworked , code, description
            FROM userData
            INNER JOIN projects
            ON userData.projectnumber = projects.number
            where fullname ='Bob Smith'
            AND  DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date
            ORDER BY date ASC

the  DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date
part does not return the correct data
results:
2004-09-22
2004-09-24
2004-09-27
2004-10-01
2004-10-30

I only want to show the records between
2004-09-26 AND 2004-10-02
(the current work week)

Then on Sunday of next week I only want to show the records for that week
2004-10-03  to 2004-10-09
and so on each week there after.

What do I need to change to get only the records to show for the current week?

Thanks
0
Comment
Question by:luckynh
  • 6
  • 5
12 Comments
 
LVL 3

Author Comment

by:luckynh
Comment Utility
anyone have a sugestion?

i guess i need to bump the points????

0
 
LVL 9

Expert Comment

by:gtkfreak
Comment Utility
In where clause, put in:
WEEK(day) = Week(curdate())

Trust it helps you.

0
 
LVL 3

Author Comment

by:luckynh
Comment Utility
is this instead of this:
DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date

so my SQL would be this:

SELECT id,date, projectnumber, fullname,hoursworked ,code, description
            FROM userData
            INNER JOIN projects
            ON userData.projectnumber = projects.number
            where fullname ='Bob Smith'
            AND WEEK(day) = Week(curdate())
            ORDER BY date ASC
0
 
LVL 3

Author Comment

by:luckynh
Comment Utility
changing to this did not hlep:
SELECT id,date, projectnumber, fullname,hoursworked ,code, description
          FROM userData
          INNER JOIN projects
          ON userData.projectnumber = projects.number
          where fullname ='Bob Smith'
          AND WEEK(day) = Week(curdate())
          ORDER BY date ASC

any other suggestions?
0
 
LVL 13

Expert Comment

by:cLFlaVA
Comment Utility
Wouldn't you want DATE_ADD instead of DATE_SUB?
0
 
LVL 3

Author Comment

by:luckynh
Comment Utility
not sure that's why im asking :)

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:cLFlaVA
Comment Utility
Like this?


    SELECT date
              , projectnumber
              , fullname
              , hoursworked
              , code
              , description
      FROM userData u INNER JOIN projects p
          ON u.projectnumber = p.number
    WHERE fullname = 'Bob Smith'
         AND date >= CURDATE()
         AND date < DATEADD(CURDATE(), INTERVAL 7 DAY)
ORDER BY date ASC
0
 
LVL 13

Expert Comment

by:cLFlaVA
Comment Utility
and that should be DATE_ADD not DATEADD
0
 
LVL 13

Expert Comment

by:cLFlaVA
Comment Utility
Also, it's unclear which columns you're requesting from which tables...maybe you should make it a little more explicit, for readability sake:

    SELECT u.date
              , u.projectnumber
              , u.fullname
              , u.hoursworked
              , u.code
              , u.description
      FROM userData u INNER JOIN projects p
          ON u.projectnumber = p.number
    WHERE u.fullname = 'Bob Smith'
         AND date >= CURDATE()
         AND date < DATEADD(CURDATE(), INTERVAL 7 DAY)
ORDER BY date ASC

Of course, I don't know which tables these fields are coming from, so I put the u...
0
 
LVL 3

Author Comment

by:luckynh
Comment Utility
does this help?

SELECT userData.id,
      userData.date,
      userData.projectnumber,
      userData.fullname,
      userData.hoursworked,
      projects.code,
      projects.description
      
      FROM userData
      
      INNER JOIN projects
      
      ON userData.projectnumber = projects.number
      
      where fullname ='Bob Smith'
      
      AND  DATE_ADD(CURDATE(),INTERVAL 7 DAY) <= userData.date
      
      ORDER BY date ASC

0
 
LVL 13

Accepted Solution

by:
cLFlaVA earned 25 total points
Comment Utility
Does it work???

    SELECT u.date
              , u.projectnumber
              , u.fullname
              , u.hoursworked
              , p.code
              , p.description
      FROM userData u INNER JOIN projects p
          ON u.projectnumber = p.number
    WHERE u.fullname = 'Bob Smith'
         AND u.date >= CURDATE()
         AND u.date < DATE_ADD(CURDATE(), INTERVAL 7 DAY)
ORDER BY u.date ASC
0
 
LVL 3

Author Comment

by:luckynh
Comment Utility
thanks!  that was it!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Creating and Managing Databases with phpMyAdmin in cPanel.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now