Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Unanswered: Select help - multiple conditions

    Hallo!

    I am asking for your help with select again.
    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. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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"
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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
  •