Database

Re: More advice on a slighltly more complex select statement

12-Sep-08 3:40

tblVisitor is a list of visitors that is linked to a record in the tblEmployee table and what I need to do is select the last visitor based on the ClockedIn column, which is a DateTime.

So my question is how do I insert this :-

SELECT TOP 1 * FROM tblVisitors WHERE intEmpID = tblEmployee.intEmpID  ORDER BY ClockedIn DESC

into this SELECT statement :-

SELECT     TOP (100) PERCENT employee.intEmpID AS ID, employee.strEmpID AS CardReference, 
		CASE WHEN Employee.intStatus = 0 THEN 
			NULL 
        ELSE 
			Employee.strTitle + ' ' + Employee.strForename + ' ' + Employee.strSurname 
		END AS Visitor, 
		Visitor.Company, 
        Visitor.VisitorCardEmpId AS ResponsiblePersonnel, 
		CASE Employee.intStatus 
			WHEN 1 THEN
				-1 
			ELSE 
				0 
		END AS ClockedIn,
		CASE WHEN Employee.intStatus = 0 THEN
			NULL
		ELSE
			Visitor.TimeIn
		END As ArrivalTime
	FROM         tblEmployee AS employee LEFT OUTER JOIN
                      dbo.tblVisitors AS Visitor ON Visitor.intEmpId = employee.intEmpID 
	WHERE     (employee.strEmpID LIKE 'V%')
	ORDER BY CardReference

So that the result is one row pre employee.

Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.

Ashfield12-Sep-08 4:23

Re: More advice on a slighltly more complex select statement

12-Sep-08 4:23

Not sure I quite understand, but how about something like

.... Where Clockedin = (select max(clockedin) from tblvisitors)

i.e. you are selecting the latest clocked in time and using that to match back to your visitor.

BTW, why the SELECT TOP (100) PERCENT .., surely thats just selecting them all, but causing the server extra work?

Bob
Ashfield Consultants Ltd

Steven J Jowett12-Sep-08 4:45

Re: More advice on a slighltly more complex select statement

12-Sep-08 4:45

Bob,

Thanks very much, you pointed me in the right direction. I did n't spot the SELECT TOP (100) percent, which was added by the View designer.

My final solution if anyone is interested :-

SELECT DISTINCT employee.intEmpID AS ID, employee.strEmpID AS CardReference, 
		CASE WHEN Employee.intStatus = 0 THEN 
			NULL 
        ELSE 
			Employee.strTitle + ' ' + Employee.strForename + ' ' + Employee.strSurname 
		END AS Visitor, 
		CASE WHEN Employee.intStatus = 0 THEN
			NULL
		ELSE
			(SELECT TOP 1 Company FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
		END As Company,
		CASE WHEN Employee.intStatus = 0 THEN
			NULL
		ELSE
			(SELECT TOP 1 VisitorCardEmpId FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
		END As ResponsiblePersonnelID,
		CASE WHEN Employee.intStatus = 0 THEN
			NULL
		ELSE
			dbo.fnGetEmployeeName((SELECT TOP 1 VisitorCardEmpId FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC))
		END As ResponsiblePersonnel,
		CASE Employee.intStatus 
			WHEN 1 THEN
				-1 
			ELSE 
				0 
		END AS ClockedIn,
		CASE WHEN Employee.intStatus = 0 THEN
			NULL
		ELSE
			(SELECT TOP 1 TimeIn FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
		END As ArrivalTime
	FROM         Tensor.dbo.tblEmployee AS employee LEFT OUTER JOIN
                      dbo.tblVisitors AS Visitor ON Visitor.intEmpId = employee.intEmpID 
	WHERE     (employee.strEmpID LIKE 'V%')
	ORDER BY CardReference

Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.

Ashfield12-Sep-08 8:34

SQL Linked Server/Web Service Question

12-Sep-08 8:34

Pleased to have been of use.

Bob
Ashfield Consultants Ltd

dhbaer12-Sep-08 3:40

dhbaer

12-Sep-08 3:40

Good morning,

I have a question regarding if something is possible to do in SQL. Is it possible to setup a Linked Server in SQL, or some other SQL connection that will access an existing Web Service? I have found various web pages about setting up SQL data to be accessable as a Web Service, but I found nothing that says if it's possible for SQL to access a Web Service to pull back records as part of a stored procedure.

The reason I'm wondering about this is because as we are moving to a new database engine, it has been suggested to turn off access to Linked Servers.

David

Re: SQL Linked Server/Web Service Question

Steven J Jowett12-Sep-08 4:05

Advice on Select query please

12-Sep-08 4:05

I my be wrong, but I think you will need to write a .NET extension for SQL Server, which can then subscribe to a Web Service

Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.

Steven J Jowett12-Sep-08 0:32

12-Sep-08 0:32

I have a need to return a table of data, for a MS SQL Server 2005 Stored Procedure, based on the following :-

ID = Table1.ID
If Table1.Status = 0 Then
Name = Table1.Name
Company = NULL
Else
Name = Table2.Name
Company = Table2.Company
Where Table2.ID = Table1.ID
End

I hope this mud is clear. Confused | :confused:

My T-SQL is not bad for the run-of-mill type tasks, but I do struggle with the more complex ones. (But I am learning)

Any pointers on where I should start would be great.

Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.

dojohansen12-Sep-08 1:17

dojohansen

12-Sep-08 1:17

Hi,

the exact approach depends on whether you just want to invoke the procedure and check it's return value or if you need to retrieve other results from it as well (a procedure returns an integer with the RETURN statement, but can also return a data set from a SELECT statement or another proc). If you wish to retrieve a dataset returned by the procedure, the simplest approach is to use a data adapter and configure it's SelectCommand to call the procedure. If you only need the return value it's more efficient to use an SqlCommand directly without an adapter.

In any case, you configure the command as follows.

First, set command text and type to specify a proc and it's name:

cmd.CommandText = "myproc"; // name of stored procedure
cmd.CommandType = CommandType.StoredProcedure;

Next, create parameter objects:

SqlParameter name = cmd.Parameters.Add("@name");

If you need the return value from the procedure, add a parameter of type ReturnValue using another Add() overload.

Assign the parameter values according to your logic:

If ...
name.Value = Table1.Name
...
Else
name.Value = Table2.Name
...

Then execute: DataAdapter.Fill() if you're using the adapter, or SqlCommand.ExecuteNonQuery() if you only need the return value. Note that ExecuteNonQuery() returns number of rows affected (in the last batch of the procedure, see @@ROWCOUNT in Sql Server docs), not the return value from the procedure. To get this, read the Value property of the SqlParameter you added.

Ashfield12-Sep-08 1:39

12-Sep-08 1:39

try this

select ID = Table1.ID
If Table1.Status = 0 Then
Name = Case when Table1.Status = 0 then Table1.Name else Table2.Name end,
Company = case when Table1.Status = 0 then NULL else Table2.Company end
from Table1
inner join Table2 on Table2.ID = Table1.ID

Bob
Ashfield Consultants Ltd

Steven J Jowett12-Sep-08 2:18

12-Sep-08 2:18

Thanks Bob, with a bit of refinement I have what I need based on what you gave me.

Cheers

Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.

Ashfield12-Sep-08 4:16