Showing posts with label employee. Show all posts
Showing posts with label employee. Show all posts

Wednesday, March 28, 2012

query

hi,

i want to achive the IIF functionality.

example

select

iff(employee.empid is not null,'sdfsdf',iif(employee.deptid is not null,'dssdf','sdfsd')) as 'sdsd'

from employee

how to solve this.

thank you

Hi,

did not understand what you wants to achieve! You may use if and try...catch block or you may use case statements too.

Refer CASE and try...catch for more in BOL.

Regards

Heamntgiri S. Goswami

|||

IFF is not a part of TSQL syntax as far as i know...though this function is provided in sql server 2005 reporting services (thats the place i used it)...

finctionality of IFF is

IFF(mycondition , ValueWhenMyConditionTrue, ValueWhenMyConditionFalse)

IFF can also be nested...so for ValueWhenMyConditionFalse u can write another IFF...as is done in ur case...i hope u'll be able to 'decode' ur actual code now...

|||You can use an IF in SQL.

Its normally shown as

IF EXISTS (SELECT * FROM Orders WHERE OrderNo = @.OrderNo) THEN|||Also you use NotIsNull(Expr1, Expr2)
|||

IIF is not available in T-SQL.

But you can use Case When,

Case When employee.empid is NOT NULL Then 'sdfsdf' Else (Case When employee.deptid is not null Then 'dssdf' Else 'sdfsd' End) End

Monday, March 26, 2012

query

I haven't done this for a while and i am stuck, any help would be appriciate
d.
I need to return the last employee id for each record in my database. Here
is my table.
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 12 7/30/05
3 2 8/2/05
I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id.
Only 1 employee per Appt_ID. Any help would be great! Thanks!Try,
select Appt_ID, Employee_ID, Time_Stamp
from t1 as a
where Time_Stamp = (select max(b.Time_Stamp) from t1 as b where b.Appt_ID =
a.Appt_ID)
AMB
"B.A. Barakus" wrote:

> I haven't done this for a while and i am stuck, any help would be appricia
ted.
> I need to return the last employee id for each record in my database. Her
e
> is my table.
>
> Appt_ID, Employee_ID, Time_Stamp
> 1 5 7/25/05
> 2 7 7/26/05
> 2 12 7/30/05
> 3 2 8/2/05
>
> I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id
.
> Only 1 employee per Appt_ID. Any help would be great! Thanks!|||Hi
you can do it as:
SELECT Appt_ID, MAX(Employee_ID)
FROM <EmployeeTable>
GROUP BY appt_ID
please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"B.A. Barakus" wrote:

> I haven't done this for a while and i am stuck, any help would be appricia
ted.
> I need to return the last employee id for each record in my database. Her
e
> is my table.
>
> Appt_ID, Employee_ID, Time_Stamp
> 1 5 7/25/05
> 2 7 7/26/05
> 2 12 7/30/05
> 3 2 8/2/05
>
> I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id
.
> Only 1 employee per Appt_ID. Any help would be great! Thanks!|||Apearently, On the basis of provided data Chandra's query works fine.
But I think that solution from Alejandro Mesa is the correct one
because Chandra, if we use MAX(Employee_ID), it will always means
Maximum for a perticular appt_ID, where as "last employee" should be in
respect to Max(Time_Stamp).|||thats true, but time stamp might take a new value when the data is updated
too. the new emplyee will have a max number.
that was my assumption
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Zero.NULL" wrote:

> Apearently, On the basis of provided data Chandra's query works fine.
> But I think that solution from Alejandro Mesa is the correct one
> because Chandra, if we use MAX(Employee_ID), it will always means
> Maximum for a perticular appt_ID, where as "last employee" should be in
> respect to Max(Time_Stamp).
>|||The employee could change internally to an existing employee. Think of the
situation as an account manager where accounts could be transfered between
then for various reasons. I just need a single appointment id, the id of th
e
current employee handing the account which would be based off the highest
date time stamp. Hope this helps.
"Chandra" wrote:
> thats true, but time stamp might take a new value when the data is updated
> too. the new emplyee will have a max number.
> that was my assumption
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Zero.NULL" wrote:
>|||Chandra, I understand, according to your assumption the query is
perfect. But as
B. A. Barakus has cleared his stand on this, I think he is loking for
such a query given by Alejandro Mesa.
Ok I try to clear it more, If his table is like this:
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 12 7/30/05
3 2 8/2/05
and if Employee_ID gets changed from 12 to any ID less then 12 (say 6)
for Appt_ID: 2 and Time_Stamp: 7/30/05
the data would look like:
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 6 7/30/05
3 2 8/2/05
now query by Alejandro Mesa will fetch you
1 5 7/25/05
2 6 7/30/05
3 2 8/2/05
where as query by Chandra gets:
1 5 7/25/05
2 7 7/30/05
3 2 8/2/05
Hope this solves the issue.

Tuesday, March 20, 2012

queries

1> how to select 3rd maximim salary from employee table
which contains empid,name,salary.
2> how to select duplicates from a table which doesnt have primary key
*** Sent via Developersdex http://www.codecomments.com ***
Please post this question in programming.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"murali yesuraj" <murali088@.hotmail.com> wrote in message
news:eUhkM1xnFHA.3304@.tk2msftngp13.phx.gbl...
> 1> how to select 3rd maximim salary from employee table
> which contains empid,name,salary.
> 2> how to select duplicates from a table which doesnt have primary key
>
> *** Sent via Developersdex http://www.codecomments.com ***