Solved

select date sql (only show results within this week)

Posted on 2004-09-28
12
530 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
 
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
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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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

16 Experts available now in Live!

Get 1:1 Help Now