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!!!
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)