strange result in stored procedure or PROBLEM

  • this is my problem in stored procedure

    1) TABLE SilokE IS MY TABLE OF THE ALL EMPLOYEE

    2) I need to see only the employee than in the table v_un

    3) i see all the employee

    4) problem in this line ( [new date] = @mydate2,[new_shift2] = )

    i see 2 rows of result from fields [new date] + ,[new_shift]

    how to see only 1 field for each field

    new date new_shift new date empid name

    ---------------------------------------------------------------

    2007-12-01 99 2007-12-02 99 2568947 aaa

    2007-12-01 99 2007-12-02 99 2845209 bbbb

    2007-12-01 99 2007-12-02 99 4807756 ccc

    2007-12-01 99 2007-12-02 99 9819590 ddd

    2007-12-01 99 2007-12-02 99 10055648 eee

    2007-12-01 99 2007-12-02 99 10815413 ffff

    2007-12-01 99 2007-12-02 99 11070042 gggg

    2007-12-01 99 2007-12-02 99 11162047 hhh

    -------------------------------------------------------------------------------

    i need to see only one for each field

    new date new_shift id name

    ---------------------------------------------------------------

    2007-12-01 99 2568947 aaa

    2007-12-01 99 2845209 bbbb

    2007-12-01 99 4807756 ccc

    2007-12-01 99 9819590 ddd

    2007-12-02 99 10055648 eee

    2007-12-02 99 10815413 ffff

    2007-12-02 99 11070042 gggg

    2007-12-02 99 11162047 hhh

    Code Block

    DECLARE @yeara [varchar](4)

    DECLARE @month1 [varchar](2)

    DECLARE @day1 [varchar](2)

    DECLARE @day2 [varchar](2)

    DECLARE @day3 [varchar](2)

    DECLARE @mydate1 [datetime]

    DECLARE @mydate2 [datetime]

    set @yeara ='2007'

    SET @month1 ='12'

    Set @day1 ='1'

    Set @day2 ='2'

    set @mydate1 = CONVERT([datetime] ,@day1 + '/'+ @month1 + '/' + @yearA ,103)

    set @mydate2 = CONVERT([datetime] ,@day2 + '/'+ @month1 + '/' + @yearA ,103)

    select

    [new date1]=@mydate1,[new_shift] =

    CASE

    WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 51 )))

    THEN

    1

    WHEN

    (empid IN (SELECT empid FROM v_un WHERE (shift = 11 )))

    THEN 2

    else

    99

    END,

    [new date2]=@mydate2,[new_shift2]=

    CASE

    WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 11 )))

    THEN 1

    WHEN

    (empid IN (SELECT empid FROM v_un WHERE (shift = 12 )))

    THEN

    2

    else

    88

    END,

    SilokE.empid, SilokE.Fname FROM SilokE

    tnx for all the help i get here

  • C'mon, Midan... this one is easy. Figure it out. If you're returning a column you don't want, then remove it from the SELECT list!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • In addition, corelated subqueries in the select caluse can result in poor performance as, depending on the plan chosen, SQL may have to execute each subquery once per row of the outer query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden (12/23/2007)


    C'mon, Midan... this one is easy. Figure it out. If you're returning a column you don't want, then remove it from the SELECT list!

    how

    i must to give next day new date it is condition for evry day

    so how to do it

    and the second problem is i need to see only the employee that existing in the "empid FROM v_un"

    now i see all

    TNX

  • yes yes

    you right

    TNX

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply