# Thread: Select help - multiple conditions

1. Registered User
Join Date
Dec 2007
Posts
2

## Unanswered: Select help - multiple conditions

Hallo!

I have a Work table (WEmpID,WDate,Whours…). I want to find records for specific employees in a month. If the work is done on a specific day, WHours should go in column A, else in column B. The problem begins here. Specific days are different for every employee.

My solution was to use UNION SELECT statement, selecting records which meet conditions and mark them as "A" and all others and mark them as "B". Then I would put my result in Excel and make a pivot table to get what I want.

SELECT 'A' 'New', WEmpID,WDate,WHours FROM Work
WHERE year(WDate)=2010 AND month(WDate)=7
AND ( (day(WDate) in (1 , 2 , 26 , 27 , 28 , 29 , 30) and WEmpID=31)
OR (day(WDate) in (1 , 2 , 6 , 7 , 8 , 16) and WEmpID=33)
OR (day(WDate) in (26 , 27 , 28 , 29 , 30) and WEmpID=34))
UNION SELECT 'B' 'New', WEmpID,WDate,WHours FROM Work
WHERE year(WDate)=2010 AND month(WDate)=7
AND WEmpID in (31, 33, 34)
AND not((day(WDate) in (1 , 2 , 26 , 27 , 28 , 29 , 30) and WEmpID=31)
OR (day(WDate) in (1 , 2 , 6 , 7 , 8 , 16) and WEmpID=33)
OR (day(WDate) in (26 , 27 , 28 , 29 , 30) and WEmpID=34))
Code:
```Result should be like this
WEmpID	WDate	A	B
31	01.07.2010	0	6,5
31	02.07.2010	0	7,5
31	05.07.2010	7,5	0
33	01.07.2010	0	7,5
33	02.07.2010	0	7,5
33	05.07.2010	8,5	0
34	01.07.2010	0	7,5
34	02.07.2010	0	8,5
34	05.07.2010	0	6,5```
Any help or guidance would be appreciated.

2. Registered User
Join Date
Nov 2004
Posts
1,428
Instead of specifying those days per person in your query, you could create a dedicated table that will store those special dates for those employees:
Code:
```CREATE TABLE Work(
WEmpID	INT		NOT NULL,
WDate	DATETIME	NOT NULL,
WHours	DEC(3,1)	NOT NULL
)

INSERT INTO Work
SELECT 31, CONVERT(DATETIME, '2010-07-01', 120), 6.5
UNION ALL
SELECT 31, CONVERT(DATETIME, '2010-07-02', 120), 7.5
UNION ALL
SELECT 31, CONVERT(DATETIME, '2010-07-05', 120), 7.5

SELECT * FROM Work

CREATE TABLE DaTable(
WEmpID	INT		NOT NULL,
WDate	DATETIME	NOT NULL
)

INSERT INTO DaTable
SELECT 31, CONVERT(DATETIME, '2010-07-01', 120)
UNION ALL
SELECT 31, CONVERT(DATETIME, '2010-07-02', 120)
UNION ALL
SELECT 31, CONVERT(DATETIME, '2010-07-26', 120)
UNION ALL
SELECT 31, CONVERT(DATETIME, '2010-07-27', 120)
UNION ALL
SELECT 31, CONVERT(DATETIME, '2010-07-28', 120)
UNION ALL
SELECT 31, CONVERT(DATETIME, '2010-07-29', 120)
UNION ALL
SELECT 31, CONVERT(DATETIME, '2010-07-30', 120)

SELECT * from DaTable

SELECT	Work.WEmpID,
CONVERT(VARCHAR(10), Work.WDate, 120) as WDate,
CASE WHEN DaTable.WEmpID IS NOT NULL
THEN Work.WHours
END as A,
CASE WHEN DaTable.WEmpID IS NULL
THEN Work.WHours
END as B
FROM Work
LEFT OUTER JOIN DaTable ON
Work.WEmpID = DaTable.WEmpID AND
Work.WDate = DaTable.WDate
WHERE	YEAR(Work.WDate) = 2010 AND
MONTH(Work.WDate) = 7```
The advantage is that your can write a form on that table , so a user can maintain those special dates per person.
In another window you could ask for two values (year and month), and generate the report. So you can also get rid of those ad hoc queries and reports.

I'm sure you can come up with a far better name for that extra table, instead of "DaTable"

3. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
You can always handle in case statement as well, if this is one off type thing.
Code:
```SELECT WEmpID
,WDate
,case when day(WDate) in (1 , 2 , 26 , 27 , 28 , 29 , 30)
and WEmpID=31 then WHours
when day(WDate) in (1 , 2 , 6 , 7 , 8 , 16)
and WEmpID=33 then WHours
when day(WDate) in (26 , 27 , 28 , 29 , 30)
and WEmpID=34 then WHours
end as A
,case when day(WDate) not in (1 , 2 , 26 , 27 , 28 , 29 , 30)
and WEmpID=31 then WHours
when day(WDate) not in (1 , 2 , 6 , 7 , 8 , 16)
and WEmpID=33 then WHours
when day(WDate) not in (26 , 27 , 28 , 29 , 30)
and WEmpID=34 then WHours
end as B
FROM Work
WHERE year(WDate)=2010
AND month(WDate)=7
and WEmpID in (31,33,34)```
Dave

#### Posting Permissions

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