Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2009
    Posts
    12

    Unanswered: problem with select query

    Hi All,

    I have an Select query as shown below...

    Code:
    SELECT time_cards.emp_code,
    		convert(varchar,Tx_Date,101), case tx_type when 'I' then convert(varchar,Tx_Date,108) end as 'IN Time',
    		case tx_type when 'O' then convert(varchar,Tx_Date,108) end as 'Out Time'
    	FROM time_cards, emp_master
    	WHERE emp_master.emp_code = time_cards.emp_code
    	  AND emp_master.loc_cd LIKE @LocationCode
    	  AND emp_master.dept_cd LIKE @Department
    	  AND emp_master.emp_code LIKE @EmployeeCode
    	  AND Time_Cards.tx_date >= @From_Date
    	  AND Time_Cards.tx_date <= DateAdd(d,1,@To_Date)
    	  Order by time_cards.emp_code, time_cards.tx_date, time_cards.tx_type
    the Output of this query is :

    Running [dbo].[edocsnet_rpt_TimeCard] ( @From_Date = 24/02/2009, @To_Date = 23/03/2009, @EmployeeCode = 1004, @Department = 0111, @LocationCode = EG01 ).

    Code:
    emp_code Column1                        IN Time                        Out Time                       
    -------- ------------------------------ ------------------------------ ------------------------------ 
    1004     02/24/2009                     12:21:56                       NULL                           
    1004     02/24/2009                     NULL                           21:46:10                       
    1004     02/25/2009                     12:21:56                       NULL                           
    1004     02/25/2009                     NULL                           21:46:10                       
    1004     02/26/2009                     12:21:56                       NULL                           
    1004     02/26/2009                     NULL                           21:46:10                       
    1004     02/27/2009                     12:21:56                       NULL                           
    1004     02/27/2009                     NULL                           21:46:10                       
    1004     02/28/2009                     12:21:56                       NULL                           
    1004     02/28/2009                     NULL                           21:46:10                       
    1004     03/01/2009                     12:21:56                       NULL                           
    1004     03/01/2009                     NULL                           21:46:10
    But i want both the In time and OUt Time to display in a single row for the same date

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    ChinnuBlr, You can try the MAX Function and Group By:
    Code:
    SELECT time_cards.emp_code
         , convert(varchar,Tx_Date,101) AS Tx_Date
         , MAX(case tx_type 
                    when 'I' then convert(varchar,Tx_Date,108) 
               end) as 'IN Time'
         , MAX(case tx_type 
                when 'O' then convert(varchar,Tx_Date,108) 
           end) as 'Out Time'
    FROM time_cards
       , emp_master
    WHERE emp_master.emp_code   = time_cards.emp_code
      AND emp_master.loc_cd   LIKE @LocationCode
      AND emp_master.dept_cd  LIKE @Department
      AND emp_master.emp_code LIKE @EmployeeCode
      AND Time_Cards.tx_date    >= @From_Date
      AND Time_Cards.tx_date    <= DateAdd(d,1,@To_Date)
    GROUP BY Time_cards.emp_code
           , Tx_Date
    Order by time_cards.emp_code
           , time_cards.tx_date
    I am not exactly sure how the Max will function with a NULL. If it is a problem you can add an ELSE with a value that can be used as a Default for missing (and is less than the actual value) to each of the CASE expressions.

    If this still does not work, since you have only 2 values, you could do two derived tables (one for IN and one for OUT) and Join them to get the results you want.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Stealth_DBA
    I am not exactly sure how the Max will function with a NULL.
    how terribly embarrassing to have to admit this on a public forum

    to save future pain, you should remember that aggregate functions (like MIN, MAX, AVG and COUNT) ignore NULLs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    r937, that is true, but I believe in full disclosure.

    The standard at my company is not to allow NULL values on columns and I use the ELSE (with a CASE) and COALESCE to remove NULLs from SQL. That being the case, I don't have much interaction with Nulls.

    And since I was not at a place to try it, I didn't want to mislead with a possibly incorrect answer.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Stealth_DBA
    The standard at my company is not to allow NULL values on columns
    oh, sweet

    and what do you put as a column value if you don't have a column value to put?

    lemme guess, zeroes and empty strings ...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    r937, correct again. I don't have to like it, I just have to work with it. I personally have nothing against Nulls.

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    personally i'd go with something like

    Code:
    SELECT		[In].emp_code, [In].[Date], [In].Time as [In Time], [Out].[Time] as [Out Time]
    FROM		(SELECT		time_cards.emp_code,
    						convert(varchar,Tx_Date,101) as [Date],
    						convert(varchar,Tx_Date,108) end as [Time],
    			FROM		time_cards
    			INNER JOIN	emp_master
    				ON	emp_master.emp_code = time_cards.emp_code
    			Where		tx_type = 'I'
    				AND	emp_master.loc_cd LIKE @LocationCode
    				AND	emp_master.dept_cd LIKE @Department
    				AND	emp_master.emp_code LIKE @EmployeeCode) as [In]
    Inner Join	(SELECT		time_cards.emp_code,
    						convert(varchar,Tx_Date,101),
    						convert(varchar,Tx_Date,108) end as [Time],
    			FROM		time_cards
    			INNER JOIN	emp_master
    				ON	emp_master.emp_code = time_cards.emp_code
    			Where		tx_type = 'O'
    				AND	emp_master.loc_cd LIKE @LocationCode
    				AND	emp_master.dept_cd LIKE @Department
    				AND	emp_master.emp_code LIKE @EmployeeCode) as Out
    		On	[In].emp_code = [Out].emp_code AND [In].[Date]=[Out].[Date]
    WHERE		Time_Cards.tx_date >= @From_Date
    		AND	Time_Cards.tx_date <= DateAdd(d,1,@To_Date)
    Order by	time_cards.emp_code, time_cards.tx_date, time_cards.tx_type
    or do a pivot
    Crosstab queries using PIVOT in SQL Server 2005
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    which i think should look like
    Code:
    SELECT emp_code, convert(varchar,Tx_Date,101, I AS [In Time], O AS [Out Time]
    FROM
    (
    	SELECT time_cards.emp_code,
    		convert(varchar,Tx_Date,101) as [Date]
    		tx_type,
    		convert(varchar,Tx_Date,108) as [Time]
    	FROM	time_cards Inner join emp_master
    	ON	emp_master.emp_code = time_cards.emp_code
    	Where	emp_master.loc_cd LIKE @LocationCode
    	AND	emp_master.dept_cd LIKE @Department
    	AND	emp_master.emp_code LIKE @EmployeeCode
    	AND	Time_Cards.tx_date >= @From_Date
    	AND	Time_Cards.tx_date <= DateAdd(d,1,@To_Date)
    ) source
    PIVOT
    (
    MAX ([Time])
    FOR tx_type IN
    ( [I], [O])
    ) AS pvt
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Stealth_DBA
    I personally have nothing against Nulls.
    Isn't having "nothing against nulls" a bit redundant?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    blindman, Ah, but a Null is not Nothing (it is not Something, either). Since they are not synonymous, they can't be redundant...

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Personally, I do not know whether or not I have nothing against nulls.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    0 is clearly better than NULL as this code demonstrates:

    if NULL > 0 print 'NULL wins!' else print 'zero wins!'


  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT CASE WHEN 0 > NULL THEN '0 wins!' ELSE 'NULL wins!' END

    beatdown

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    ouch! your beatdown powers are beating me down to a degree i can't express! I never looked at it that way!

Posting Permissions

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