SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,157
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Post Need help on my query please

    Hi, I want to select the date and time of my employee whose date range 2014-01-05 to 2014-01-15 and time range to 6:00-9:00 from employee table

    but my query fails

    Code:
    SELECT DateFrom,DateTo,(SELECT TimeIn,TimeOut FROM  employe_table where TimeIn BETWEEN '6:00' and '9:00' 
                                   or TimeOut BETWEEN '6:00' and '9:00')
                                   
                                   FROM  employe_table WHERE
                                   DateFrom BETWEEN '2014-01-05' and '2014-01-15' or DateTo 
                                   BETWEEN '2014-01-05' and '2014-01-15'
    Thank you in advance

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Can you please post the output of SHOW CREATE TABLE employe_table ?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,157
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi SpacePhoenix!

    Code:
    CREATE TABLE `employe_table` (
      `EmpNo` int(11) NOT NULL AUTO_INCREMENT,
      `DateFrom` date NOT NULL,
      `DateTo` date NOT NULL,
      `TimeIn` time NOT NULL,
      `TimeOut` time NOT NULL,
      
      PRIMARY KEY (`EmpNo`)
    ) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT DateFrom
         , DateTo
         , TimeIn
         , TimeOut 
      FROM  employe_table 
     WHERE ( TimeIn BETWEEN '6:00' AND '9:00' 
          OR TimeOut BETWEEN '6:00' AND '9:00' )
       AND ( DateFrom BETWEEN '2014-01-05' AND '2014-01-15' 
          OR DateTo BETWEEN '2014-01-05' AND '2014-01-15' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,157
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937

  6. #6
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,157
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT DateFrom
         , DateTo
         , TimeIn
         , TimeOut 
      FROM  employe_table 
     WHERE ( TimeIn BETWEEN '6:00' AND '9:00' 
          OR TimeOut BETWEEN '6:00' AND '9:00' )
       AND ( DateFrom BETWEEN '2014-01-05' AND '2014-01-15' 
          OR DateTo BETWEEN '2014-01-05' AND '2014-01-15' )

    Hi r937,

    Can i ask help again? what if i have empno '10200' .how do i apply this to your query?

    Thank you in advance

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    append an AND condition to the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,157
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you r937 it helps me


Bookmarks

Posting Permissions

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