Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

strange result in stored procedure or PROBLEM Expand / Collapse
Author
Message
Posted Sunday, December 23, 2007 4:58 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2008 8:42 AM
Points: 302, Visits: 271
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



Post #436059
Posted Sunday, December 23, 2007 6:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 37,075, Visits: 31,631
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #436070
Posted Sunday, December 23, 2007 11:32 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 43,002, Visits: 36,158
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 2008, MVP
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

Post #436095
Posted Monday, December 24, 2007 7:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2008 8:42 AM
Points: 302, Visits: 271
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



Post #436154
Posted Tuesday, December 25, 2007 2:57 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2008 8:42 AM
Points: 302, Visits: 271
yes yes
you right
TNX



Post #436270
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse