javamysqlsql-serverspringhibernate3

How to check all values for a particular column in a week


i have a table called timesheet.In that i has below rows

  ID Status ProjId ActId  Date
    1  pending  1     1     2014-aug-07
    2  denied   1     2     2014-aug-08
    3  saved    1     3     2014-aug-09
    4  approved 1     4     2014-aug-10

i have used below query for getting the desired result.

SELECT * from tblTimesheet 
WHERE DATEPART(wk,spentDate) = 32 AND ((timesheetstatus = 'Pending' or timesheetstatus = 'Approved') and (timesheetstatus <> 'Saved' or timesheetstatus <> 'Denied'))

My expected result for the above query is 0.But i get the result is 1.

 5  pending  1     1     2014-aug-11
    6  pending  1     2     2014-aug-12
    7  approved 1     3     2014-aug-13
    8  approved 1     4     2014-aug-14

this case my query works.

SELECT * from tblTimesheet 
WHERE DATEPART(wk,spentDate) = 32 AND ((timesheetstatus = 'Pending' or timesheetstatus = 'Approved') and (timesheetstatus <> 'Saved' or timesheetstatus <> 'Denied'))

My expected result for the above query is 1.I get the result is 1.

How to write the query for above requirement in mssql. Scenario 1 is worked.But scenario2 is not worked. How to acheive the desired result. Any help will be greatly appreciated!!!


Solution

  • Note that this is a solution for mysql, because that was also tagged. It uses MySQL's standard defintion for a 'week' - which appears to differ fron your own definition. You may be able to use WEEK(date,3) or something similar if that better matches your definition.

     DROP TABLE IF EXISTS my_table;
    
    CREATE TABLE my_table
    (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,Status VARCHAR(20) NOT NULL
    ,ProjId INT NOT NULL
    ,ActId INT NOT NULL
    ,Date DATE NOT NULL
    );
    
    INSERT INTO my_table VALUES
    (1  ,'pending',1     ,1     ,'2014-08-07'),
    (2  ,'denied',   1     ,2     ,'2014-08-08'),
    (3  ,'saved',    1     ,3     ,'2014-08-09'),
    (4  ,'approved', 1     ,4     ,'2014-08-10');
    
    SELECT *,WEEK(date) FROM my_table;
    +----+----------+--------+-------+------------+------------+
    | ID | Status   | ProjId | ActId | Date       | WEEK(date) |
    +----+----------+--------+-------+------------+------------+
    |  1 | pending  |      1 |     1 | 2014-08-07 |         31 |
    |  2 | denied   |      1 |     2 | 2014-08-08 |         31 |
    |  3 | saved    |      1 |     3 | 2014-08-09 |         31 |
    |  4 | approved |      1 |     4 | 2014-08-10 |         32 |
    +----+----------+--------+-------+------------+------------+
    
    SELECT x.* 
      FROM my_table x 
      LEFT 
      JOIN my_table y 
        ON WEEK(y.date) = WEEK(x.date) 
       AND y.status NOT IN ('approved','pending') 
     WHERE y.id IS NULL;
    +----+----------+--------+-------+------------+
    | ID | Status   | ProjId | ActId | Date       |
    +----+----------+--------+-------+------------+
    |  4 | approved |      1 |     4 | 2014-08-10 |
    +----+----------+--------+-------+------------+
    

    Edit: Result with WEEK(date,3)...

    SELECT x.* 
      FROM my_table x 
      LEFT 
      JOIN my_table y 
        ON WEEK(y.date,3) = WEEK(x.date,3) 
       AND y.status NOT IN ('approved','pending') 
     WHERE y.id IS NULL;
    Empty set (0.00 sec)