Solved

select date sql (only show results within this week)

Posted on 2004-09-28
12
532 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:Timothy Golden
  • 6
  • 5
12 Comments
 
LVL 3

Author Comment

by:Timothy Golden
ID: 12171453
anyone have a sugestion?

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

0
 
LVL 9

Expert Comment

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

Trust it helps you.

0
 
LVL 3

Author Comment

by:Timothy Golden
ID: 12172393
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Author Comment

by:Timothy Golden
ID: 12172412
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
ID: 12172947
Wouldn't you want DATE_ADD instead of DATE_SUB?
0
 
LVL 3

Author Comment

by:Timothy Golden
ID: 12172976
not sure that's why im asking :)

0
 
LVL 13

Expert Comment

by:cLFlaVA
ID: 12172989
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
ID: 12172993
and that should be DATE_ADD not DATEADD
0
 
LVL 13

Expert Comment

by:cLFlaVA
ID: 12173034
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:Timothy Golden
ID: 12173172
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
ID: 12173212
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:Timothy Golden
ID: 12173450
thanks!  that was it!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
No row return after calling the fillschema method 4 48
PHP query / monitor data from Telnet to MySQL 7 52
mysql database, schema and table creation 13 58
mysql db 3 69
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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