How to get this out put

Hi i have a small question

when i use this SQL bellow

select EmpId,Type,Description from Employee
it will return me this out put

EmpId Type Description
01 Private Private company owner
02 Self Home based
03 Self Home based

Now my requirement is to get this out put

EmpId Type Description
01 Private
02 Self Home based
03 Self Home based

i need to get the description of the employee when employee type only 'Self'
rest of the employee Type should leave it blank ?
how do i do this task ?

regards
suis

Try the example below.

Chris

Code Snippet

DECLARE @.MyTable TABLE

(

[EmpID] CHAR(2),

[Type] CHAR(10),

[Description] VARCHAR(50)

)

INSERT INTO @.MyTable([EmpID], [Type], [Description])

SELECT '01', 'Private', 'Private company owner' UNION ALL

SELECT '02', 'Self', 'Home based' UNION ALL

SELECT '03', 'Self', 'Home based'

SELECT [EmpID],

[Type],

CASE WHEN [Type] = 'Self' THEN [Description]

ELSE ''

END AS [Description]

FROM @.MyTable

|||Hi chris
thank you very much for u r quick response,
i could manage to sort out my problem using u r comments,
thank you very much again for this forum
regards
suis

How to get the stored procedure return type

I am having a problem finding the stored procedure return type. I am having no problem with the getting the stored procedure parameters collection and processing as needed or determining if the parameter is an output column. But if the stored procedure returns a value, how to I determine that return value type with SMO. I would much appreciate it if someone could answer this...

Steve Graddy
Orgbrat Consulting

Stored Procedures return multiple values in the form of output parameters. Thus the return types would be the datatype of StoredProcedure.Parameters which have IsOutputParameter as true.
Hope that helps.

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

Ok I am confused.. I understand about the ISOutputParameter property. let me give you an example and maybe you can explain. Using the Pub's database and the "reptq3" stored procedure as an example. if you look at this stored procedure definition there is no output parameter, but when you look at the parameters list in the object tree of the SQL Server 2005 Management Studio the last parameter or item is "Returns Integer". But when you get the stored procedures parametr collaection with SMO, you only get the first three parametrs that are in the stored procedure parameter list. Where is the SQL Server 2005 Management Studio coming up with that last item or does it always assume that if there are no output parametrs declared, there is always a last item "Returns Integer"? Here is a quick and dirty code section and as you see, I am asking for every paramter and then handling if it is input or output in the application. Probklem is, I only get three parametrs back from this call.

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;

if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;

dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

A stored procedure always returns an integer whose value is 0 if the execution was successful and non-zero in case of any failure. The return parameter which is mentioned above is different from this. From BOL - A stored procedure Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

How to get the stored procedure return type

I am having a problem finding the stored procedure return type. I am having no problem with the getting the stored procedure parameters collection and processing as needed or determining if the parameter is an output column. But if the stored procedure returns a value, how to I determine that return value type with SMO. I would much appreciate it if someone could answer this...

Steve Graddy
Orgbrat Consulting

Stored Procedures return multiple values in the form of output parameters. Thus the return types would be the datatype of StoredProcedure.Parameters which have IsOutputParameter as true.
Hope that helps.

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

Ok I am confused.. I understand about the ISOutputParameter property. let me give you an example and maybe you can explain. Using the Pub's database and the "reptq3" stored procedure as an example. if you look at this stored procedure definition there is no output parameter, but when you look at the parameters list in the object tree of the SQL Server 2005 Management Studio the last parameter or item is "Returns Integer". But when you get the stored procedures parametr collaection with SMO, you only get the first three parametrs that are in the stored procedure parameter list. Where is the SQL Server 2005 Management Studio coming up with that last item or does it always assume that if there are no output parametrs declared, there is always a last item "Returns Integer"? Here is a quick and dirty code section and as you see, I am asking for every paramter and then handling if it is input or output in the application. Probklem is, I only get three parametrs back from this call.

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;

if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;

dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

A stored procedure always returns an integer whose value is 0 if the execution was successful and non-zero in case of any failure. The return parameter which is mentioned above is different from this. From BOL - A stored procedure Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

How to get the stored procedure return type

I am having a problem finding the stored procedure return type. I am having no problem with the getting the stored procedure parameters collection and processing as needed or determining if the parameter is an output column. But if the stored procedure returns a value, how to I determine that return value type with SMO. I would much appreciate it if someone could answer this...

Steve Graddy
Orgbrat Consulting

Stored Procedures return multiple values in the form of output parameters. Thus the return types would be the datatype of StoredProcedure.Parameters which have IsOutputParameter as true.
Hope that helps.

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

Ok I am confused.. I understand about the ISOutputParameter property. let me give you an example and maybe you can explain. Using the Pub's database and the "reptq3" stored procedure as an example. if you look at this stored procedure definition there is no output parameter, but when you look at the parameters list in the object tree of the SQL Server 2005 Management Studio the last parameter or item is "Returns Integer". But when you get the stored procedures parametr collaection with SMO, you only get the first three parametrs that are in the stored procedure parameter list. Where is the SQL Server 2005 Management Studio coming up with that last item or does it always assume that if there are no output parametrs declared, there is always a last item "Returns Integer"? Here is a quick and dirty code section and as you see, I am asking for every paramter and then handling if it is input or output in the application. Probklem is, I only get three parametrs back from this call.

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;

if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;

dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

A stored procedure always returns an integer whose value is 0 if the execution was successful and non-zero in case of any failure. The return parameter which is mentioned above is different from this. From BOL - A stored procedure Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

How to get the return/execution value of a package from a parent?

Hi there,

I'm trying to get the return value of a package. I see there is a ForcedExecutionValue property which I set using an expression (variable). What I'm executing are 2 packages, Package1 contains an Execute Package Task that calls Package 2. Package 2 contains a Script Task that sets the value of variable Max. I want to get the value of Max in Package 1 then how can I do this?

My first approach is toset the return value of Package 2 = Max and then I thought I could retrieve this value from Package 1 but I'm not able to do that yet.

Any thoughts?

Thanks for any help!The way I would approach this is to use a Script Task (surprise, surprise) to load and execute the package instead of the Execute Package Task. Your script has the ability to read the child packages variables after it has executed, thus allowing child variables to be passed back to the parent.
http://blogs.msdn.com/jamesk/archive/2005/12/21/506463.aspx

Alternatively, you can also have the child package set the parent's variable directly.
http://blogs.conchango.com/jamiethomson/archive/2005/03/17/1151.aspx|||Hi JayH,

Yes, I did the first approach and it worked fine. I'm loading the package from a Script Task and getting the Executables.Count and store this value in a local variable.

Thanks for the suggestion!.

Ricardo

How to get the return value when using a TableAdapter access a Stored Procedure

I have a Stored Procedure

CREATE PROCEDURE test
AS
BEGIN
SELECT Count(*) FROM dbo.test
END

I can using the unbox get the return value

but if i direct return a value form a Stored Procedure like this

CREATE PROCEDURE test
AS
BEGIN
return 100
END

I can not get the VALUE
I do not know how to
Please Help Me
thx

You can use such T-SQL statements to get the result from the stored procedure in SQL Server:

declare @.s int
exec @.s=test
select @.s

How to get the return value when using a TableAdapter access a Stored Procedure

I have a Stored Procedure

CREATE PROCEDURE test
AS
BEGIN
SELECT Count(*) FROM dbo.test
END

I can using the unbox get the return value

but if i direct return a value form a Stored Procedure like this

CREATE PROCEDURE test
AS
BEGIN
return 100
END

I can not get the VALUE
I do not know how to
Please Help Me
thx

Hi,

and welcome to the ASP.NET forums.

You can use aSqlParameter with Direction set toParameterDirection.ReturnValue. Pleasetake a look at this little code sample.

Grz, Kris.

How to get the return value of a stored procedure

I have a Stored procedure (sql 2000), that inserts data into a table. Then, I add this, at the end:
Return Scope_Identity()

I have the parameters for the sProc defined and added to the Command, but I'm having a really lousy time trying to figure out how to get the return value of the Stored PRocedure. BTW - I'm using OleDB instead of SQL due to using a UDL for the connection string.

I have intReturn defined as an integer

I've tried :
Dim retValParam As OleDbParameter = cmd.Parameters.Add("@.RETURN_VALUE", OleDbType.Integer)
retValParam.Direction = ParameterDirection.ReturnValue
intReturn=cmd.Parameters("@.RETURN_VALUE").Value

whenever I add this section - I get an error that there are too many arguments for the sProc.

I've tried:
intreturn=cmd.ExecuteNonquery - tried adding a DataReader - using ExecuteScalar - I've tried so many things and gotten so many errors - I've forgotten which formations go with which errors.

What is the best way to do this in the code part (VB.Net)?

Thanks ahead of time

check the second part ofthis article

|||

Turned out, I didn't even use a parameter for the Returnvalue -

I ended up using something I'd tried before :
lg=cmd.ExecuteScalar....and it worked.

sql

how to get the results of sp_stored_procedures in C# app?

The result of sp_stored_procedures and many others is a result set, a table, but this procedure and others return only an integer. How do I get the result set in C# code? I need a general idea how it is done.

Thanks.

Are you looking for something like this:

using (SqlConnection cxn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=Master;Integrated Security=True"))
{
cxn.Open();
SqlCommand cmd = new SqlCommand("sp_stored_procedures", cxn);
SqlDataReader rdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
Console.Write(rdr.ToString() + "\t");
}
Console.WriteLine();
}

cmd.Dispose();
}

|||

If the procedure provides more than one resultsset, you will have to switch between the resultsets to retrieve the resultset you want to get. ( As a procedure can have more than one returned table ) E.g. If you use a datareader you can switch to the next next result.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jeff Papiez - MSFT wrote:

Are you looking for something like this:

using (SqlConnection cxn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial ();
}

Thank you very much, Jeff.

|||

Jens K. Suessmeyer wrote:

If the procedure provides more than one resultsset, you will have to switch between the resultsets to retrieve the resultset you want to get. ( As a procedure can have more than one returned table ) E.g. If you use a datareader you can switch to the next next result.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Jens, thanks a lot.

how to get the primary key from the field of the row ive just inserted

I need to insert a row of data and return the value of the primary key id of the row.
I thought that something like this would work

int Key = (int)command.ExecuteScalar();

where command is SqlCommand object.

It doesn't work, maybe I've misunderstood the usage of ExecuteScalar.ExecuteScalar() returns the first row/first column of the resulet set. This should work IF part of the command contains something like SELECT Scope_IDentity() or SELECT @.@.IDENTITY after the insert, and the table has an IDENTITY column.sql

How to get the numbers of row in a table using function. Simple but there is a problem i have

Here is the code. Trying to get the number of the rows in a table. If it is greater than 0 return false else return true. Later i changed this code with if else but i am wondering why it is giving this error?

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.ProductNum =COUNT(*)

FROM tblProduct

WHERE ProductID = @.ProductPrm

-- Return the result of the function

CASE WHEN @.ProductNum >= 1 THEN @.Status = 'False' ELSE @.Status = 'True' END

RETURN @.Status

END

Code Snippet

Msg 156, Level 15, State 1, Procedure RestoranHazirMi, Line 25

Incorrect syntax near the keyword 'CASE'.

Msg 102, Level 15, State 1, Procedure RestoranHazirMi, Line 29

Incorrect syntax near 'END'.

CASE WHEN is a statement level construction, not flow control

You could use

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.Status = CASE WHEN COUNT(*)>=1 THEN 'False' ELSE 'True' END

FROM tblProduct

WHERE ProductID = @.ProductPrm

RETURN @.Status

END

OR (IF/ELSE for flow control):

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.ProductNum =COUNT(*)

FROM tblProduct

WHERE ProductID = @.ProductPrm

-- Return the result of the function

IF (@.ProductNum >= 1)

SET @.Status = 'False'

ELSE

SET @.Status = 'True'

RETURN @.Status

END

|||thanks dude. I've learned it now.sql

how to get sqlcmd return values

I'm having a hard time getting a non-zero return value from sqlcmd when a
error in the sql script it is executing occurs.
I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
is relevant) but regardless ERRORLEVEL is always 0
Here is a sample of the sqlcmd and the os syntax I'm running from within a
sql agent job step:
sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
input file.sql"
echo %ERRORLEVEL%
or
set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
"C:\Admin\bcp table space input file.sql"
also, I've tried different iterations with and without spaces between -V and
10
any help or suggestions is greatly appreciated.What error occurs on the script you run? I just tried with RAISERROR and it
work find. Here's the
bat file:
sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
ECHO %ERRORLEVEL%
pause
And here's the contents of a.sql:
RAISERROR('Ouch', 15, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
> I'm having a hard time getting a non-zero return value from sqlcmd when a
> error in the sql script it is executing occurs.
> I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
> is relevant) but regardless ERRORLEVEL is always 0
> Here is a sample of the sqlcmd and the os syntax I'm running from within a
> sql agent job step:
> sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
> input file.sql"
> echo %ERRORLEVEL%
> or
> set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> also, I've tried different iterations with and without spaces between -V a
nd
> 10
>
> any help or suggestions is greatly appreciated.
>|||Here is the error message:
Msg 945, Level 14, State 2, Server servername, Line 1
Database 'databasename' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
Basically I iterate through different databases and select from some sys
tables. The problem here is one particular db is either in a loading state
or in a suspect state and therefore causes the error. Nonetheless, resolvin
g
that issue is not hard and not as important as just catching the error itsel
f.
Thanks
"Tibor Karaszi" wrote:

> What error occurs on the script you run? I just tried with RAISERROR and i
t work find. Here's the
> bat file:
> sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> ECHO %ERRORLEVEL%
> pause
> And here's the contents of a.sql:
> RAISERROR('Ouch', 15, 1)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
>|||Try the "-b" command line option.
"paul" <paul@.discussions.microsoft.com> wrote in message
news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
> I'm having a hard time getting a non-zero return value from sqlcmd when a
> error in the sql script it is executing occurs.
> I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
> is relevant) but regardless ERRORLEVEL is always 0
> Here is a sample of the sqlcmd and the os syntax I'm running from within a
> sql agent job step:
> sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
> input file.sql"
> echo %ERRORLEVEL%
> or
> set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> also, I've tried different iterations with and without spaces between -V
> and
> 10
>
> any help or suggestions is greatly appreciated.
>|||Tried this but didn't work. Is my dos look right?
!!sqlcmd -U username -P password -S servername -b -V 10 -h-1 -i
"C:\Admin\bcp table space input file.sql"
!!echo %ERRORLEVEL%
with or without -V doesn't work?
"Mike C#" wrote:

> Try the "-b" command line option.
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
>
>|||It work just fine for me with such an error as well. Here's what I did:
CREATE DATABASE x
ALTER DATABASE x SET OFFLINE
And here's what I have in the bat file:
sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
ECHO %ERRORLEVEL%
pause
And what is in a.sql:
SELECT 'Hello'
SELECT * FROM x..sysobjects
SELECT 'Hello again'
And below is output from executing the bat file:
C:\>sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
Msg 942, Level 14, State 4, Server TIBWORK\RTM, Line 2
Database 'x' cannot be opened because it is offline.
C:\>ECHO 14
14
C:\>pause
Press any key to continue . . .
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:7B3754E7-C857-4BE3-9DFD-14BED25FD33B@.microsoft.com...
> Here is the error message:
> Msg 945, Level 14, State 2, Server servername, Line 1
> Database 'databasename' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for detail
s.
>
> Basically I iterate through different databases and select from some sys
> tables. The problem here is one particular db is either in a loading stat
e
> or in a suspect state and therefore causes the error. Nonetheless, resolv
ing
> that issue is not hard and not as important as just catching the error its
elf.
> Thanks
>
> "Tibor Karaszi" wrote:
>|||Tibor,
Thanks for following through. Can you try one more thing that I'm wondering
may be having an effect.
Change you select sql to be a dynamically executed string.
Like this:
declare @.sql varchar(255)
set @.sql = 'select * from x..sysobjects'
select 'Hello'
exec(@.sql)
select 'Hello again'
If that works on your machine, I would say it is something local to mine,
else I'm wondering if exec() behaves the way I'm expecting it to.
Thanks again.
"Tibor Karaszi" wrote:

> It work just fine for me with such an error as well. Here's what I did:
> CREATE DATABASE x
> ALTER DATABASE x SET OFFLINE
> And here's what I have in the bat file:
> sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> ECHO %ERRORLEVEL%
> pause
> And what is in a.sql:
> SELECT 'Hello'
> SELECT * FROM x..sysobjects
> SELECT 'Hello again'
>
> And below is output from executing the bat file:
> C:\>sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> Msg 942, Level 14, State 4, Server TIBWORK\RTM, Line 2
> Database 'x' cannot be opened because it is offline.
> C:\>ECHO 14
> 14
> C:\>pause
> Press any key to continue . . .
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:7B3754E7-C857-4BE3-9DFD-14BED25FD33B@.microsoft.com...
>|||Same result. I still managed to catch the error with errorlevel.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:1AB3EA52-EAB2-4C11-82A2-E0A9A97A0B19@.microsoft.com...
> Tibor,
> Thanks for following through. Can you try one more thing that I'm wonderi
ng
> may be having an effect.
> Change you select sql to be a dynamically executed string.
> Like this:
> declare @.sql varchar(255)
> set @.sql = 'select * from x..sysobjects'
> select 'Hello'
> exec(@.sql)
> select 'Hello again'
>
> If that works on your machine, I would say it is something local to mine,
> else I'm wondering if exec() behaves the way I'm expecting it to.
> Thanks again.
>
> "Tibor Karaszi" wrote:
>|||I've been able to run it ok and get the return error value as well... I'm
running SQL 2K5 SP 1 if that makes a difference.
"paul" <paul@.discussions.microsoft.com> wrote in message
news:6897329C-EEFC-4756-A1DA-46E366EAF3EA@.microsoft.com...
> Tried this but didn't work. Is my dos look right?
> !!sqlcmd -U username -P password -S servername -b -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> !!echo %ERRORLEVEL%
> with or without -V doesn't work?
>
>
> "Mike C#" wrote:
>|||Tibor / Mike,
Thanks for all your help, I believe I have solved the riddle. As contorted
as my logic might seem, I am actually calling numerous sqlcmd from inside th
e
same transact sql bactch.
To do this I am prefixing it of course with '!!'. So there in lies the
problem, each !!sqlcmd must be spawning its own dos session so if I do the
following:
!!sqlcmd ....
!!echo %errorlevel%
error level will always be 0 in the second dos session.
thanks for helping with that guys
"Tibor Karaszi" wrote:

> Same result. I still managed to catch the error with errorlevel.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:1AB3EA52-EAB2-4C11-82A2-E0A9A97A0B19@.microsoft.com...
>

How to get Row Numbers from SQL Express 2005 query?

Hi,

I'm using SQL Express 2005 and VIsual Studio 2005, and this sounds like it should be easy. I'm trying to return the row numbers of my queries, but if I use the Row_Number() command I get the following error:

"The OVER SQL construct or statement is not supported."

So, is Row_Number() not supported in SQL Express 2005? If not, how can I return row numbers with my queries? Or, more specifically, how can I return a limited result set from a query (i.e. Return only row number 10-20)?

My current command is as follows:

SELECT (SELECT Row_Number()OVER (ORDER BY UserName)As RowNumber), *
FROM Users
ORDER BY UserName

Thanks for any advice you can offer!

like this

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30

Hope this helps

|||

SELECT ROW_NUMBER() OVER (ORDER BY ProductID) as RowNumber,ProductID, ProductName, UnitPrice FROM Products WHERE RowNumber BETWEEN 20 AND 30;

|||

Thank you both for your replies. I'm sorry, I think I my question may have been a bit misleading. I know that you can return a limited result set by using the statements you have provided, but the error message I am getting is as follows:

"The OVER SQL construct or statement is not supported."

Both your suggestions use the OVER construct, and this does not appear to be supported bySQL Server Express 2005. If I run your suggested queries inVisual Studio 2005 I get the aforementioned error and the query will not run (though the SQL validation check says it's fine).

So, is the "Row_Number() OVER" command supported by SQL Express 2005 or not? If not, what other ways can I return a limited result set?

|||

i think you are using the designer, go in the sql server management studio and to this

Select File / New / Query and type your queries in the editing window instead.

Hope this helps

|||

Run this in SQL SERVER 2005 Management Studio Express:

EXEC sp_dbcmptlevel yourDataBase

If the current compatibility level of your database is 80, then run this:

EXEC sp_dbcmptlevel yourDataBase, 90

You need the compatibility level at 90 to run the Row_Number() OVER() query and other new features.

|||

Hi Limno,

That sounds promising. I've tried getting my website database into SQL Server Management Studio Express in the past but without success. My database is held as a .MDF file within my project - is there a way of importing this directly into Management Studio? The "Connect To Server" dialog displayed on startup does not allow me to browse to a specific .MDF file, and the File -> Open dialog has no option to open .MDF files.

Thanks

|||

I just found out how to import an MDF here:

http://forums.asp.net/p/1147899/1871779.aspx#1871779

And my database shows a compatibility rating of 90. Using the Row_Number() command works within SQL Server Management Express Studio! In that case, how can I perform a query in Visual Studio 2005 that uses this command if Visual Studio does not support this command?

I'm using strongly typed Table Adapters and Data Tables using the DAL component in Visual Studio. Is it impossible? If not, can I at least perform this query programmatically (C#) and cast the results to my strongly typed Data Table? If it can be done programmatically, does anyone have any examples of how to do this?

Thanks again!

|||

In Visual Studio 2005, you will see that not support message. Have you tried to ingnore it and see what will happen? It seems is should work fine. Another way, you can wrap your query in a Stored Procedure to work with.

How to get return value for the number of rows affected by update command

Hi,

i read from help files that "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. " Anyone know how to get the return value from the query below?

Below is the normal way i did in vb.net, but how to check for the return value. Please help.

========
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub 'CreateMySqlCommand
========

Thank you.you can add either of these statements to the SQL being called
[BOL} @.@.rowcount
[BOL] Rowcount_big

the difference is in the datatypes rowcount _big returns a bigint
and @.@.rowcount returns int

if you have over 2 billion rows user rowcount_big|||Hi Ruprect, thanks for your reply. My sql statement is a very simple insert query without using any parameters just like the one below:

sql = "INSERT INTO [Subscriber] ([SubID], [SubName], [SubEmail], [Status], [MailID], [SubscribeDate]) VALUES (SubID, SubName, SubEmail, 'Pending', MailID ,getDate())"

I'm unsure of how to include the " [BOL} @.@.rowcount ". Do you mean that i should add a parameter to return @.@.rowcount or there is other way to do it? I'm new to this, would you please give me an example.

Thanks for your time.|||@.@.Rowcount stored the number of records affected by the immediately prior statement. The value is lost as soon as another statement is executed, so you must either use it immediately or store it in a procedure variable:

declare @.RecordsAffected Int
.
.
.
.
.
Update/Select/Delete some records from somewhere...
set @.RecordsAffected = @.@.RowCount

Look up @.@.Rowcount in Books Online for more details.|||thanks BLIND MAN
i didnt getthis until late
[BOL] stands for Books Online it's the sql server help file
i was giving you the article title

and since blindman got it exactly i've no need to reiterate
good luck.|||see if there is something like mycommand.rowsaffected property.|||Thanks Blindman and Thanks Ruprect. I'll study BOL ;) for details of @.@.rowcount.|||You should also follow ms_sql_dba's suggestion to see if there is a method to return the value via VB.

It might be more appropriate if you are going to use the value in your VB code.|||Hi ms_sql_dba, there isn't any rowsaffected property, however there is this UpdatedRowSource and others ..

Thanks for your suggestion, although i'm unsure of their usage, i'll look into it and see if i can find something which stores the value of number of rows affected!|||Sure Blindman, i'll study both ways and see which one is more applicable for my situation. You have a great day.|||Hi Everyone,

I managed to find another solution to my question. Just simply assign the value like this line:-

rowsAffected = myCommand.ExecuteNonQuery()|||see, it was simple!|||Yea. Lesson learned! Cheers!!!

How to get record count only from FTS

Hi,
Is there a way to get the FTS system to return you the count of matches
only, without passing back all the key ids.
Basically if a user doesn't find what they are looking for with their
initial multi term search, i.e '10k resistor tomatoes'.
I want to display a list of the counts for each word. 10k = 500,
resistor = 46000, tomatoes = 5. This will help them refine their
search.
select count(*) from freetexttable([MyCatName],*,'resistor') seems
quite slow where there are a large number of matches as I assume it's
passing the data back for SQL to count. Is there a way to just get the
count it came up with.
Thanks,
No, there is no way to do this using SQL FTS, some of the Microsoft Search
engines return a hitcount value which is the raw number of hits for all
search tokens.
You could maintain a count should you shred all documents in an inverted
file index - this will require a lot of work however.
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
"Martin" <bigmarts@.hotmail.com> wrote in message
news:1169052866.809003.281110@.a75g2000cwd.googlegr oups.com...
> Hi,
> Is there a way to get the FTS system to return you the count of matches
> only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get the
> count it came up with.
> Thanks,
>
|||Hello Martin,
Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
this.
We cache keyword counts to achieve something similar
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi,
> Is there a way to get the FTS system to return you the count of
> matches only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get
> the count it came up with.
> Thanks,
>
|||Hi, it's 2005.
It's not a major issue, current method of counting the results is
actually performing ok.
Shredding inverted file indexes sounds interesting. Any pointers of
where to look for info on what you were suggesting..?
I assume you
On 18 Jan, 20:11, Simon Sabin <SimonSa...@.noemail.noemail> wrote:
> Hello Martin,
> Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
> this.
> We cache keyword counts to achieve something similar

how to get only weekdays

Using SS2000, in QA I want to return data for only days Monday thru Friday.
Is there a way to do this? Is there some function that will give me only the
weekdays?
Thanks,
Dan D.
Dan,
Check http://www.aspfaq.com/show.asp?id=2519.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
> Using SS2000, in QA I want to return data for only days Monday thru
> Friday.
> Is there a way to do this? Is there some function that will give me only
> the
> weekdays?
> Thanks,
> --
> Dan D.
|||Thanks Dejan. I'll take a look at that.
Dan D.
"Dejan Sarka" wrote:

> Dan,
> Check http://www.aspfaq.com/show.asp?id=2519.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
>
>
|||You could use the DAY function to extract the numeric day of the week
select DAY(getdate())
This would give you all Sundays, for example.
SELECT * FROM Table1 where DAY(YourDateField) = 1
If you need to account for holidays or other more complex date calculations
the calendar table is the way to go.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
> Using SS2000, in QA I want to return data for only days Monday thru
Friday.
> Is there a way to do this? Is there some function that will give me only
the
> weekdays?
> Thanks,
> --
> Dan D.
|||SELECT * FROM Table1 where DATEPART(dw, YourDateField) between 2 and 6
http://sqlservercode.blogspot.com/
|||I discovered that function. I also discovered datepart which is what I'm
using. Thanks Terri.
Dan D.
"Terri" wrote:

> You could use the DAY function to extract the numeric day of the week
> select DAY(getdate())
> This would give you all Sundays, for example.
> SELECT * FROM Table1 where DAY(YourDateField) = 1
> If you need to account for holidays or other more complex date calculations
> the calendar table is the way to go.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
> Friday.
> the
>
>
|||I came across datepart after some more looking. It works. Thanks SQL.
Dan D.
"SQL" wrote:

> SELECT * FROM Table1 where DATEPART(dw, YourDateField) between 2 and 6
> http://sqlservercode.blogspot.com/
>

how to get only weekdays

Using SS2000, in QA I want to return data for only days Monday thru Friday.
Is there a way to do this? Is there some function that will give me only the
weekdays?
Thanks,
--
Dan D.Dan,
Check http://www.aspfaq.com/show.asp?id=2519.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
> Using SS2000, in QA I want to return data for only days Monday thru
> Friday.
> Is there a way to do this? Is there some function that will give me only
> the
> weekdays?
> Thanks,
> --
> Dan D.|||Thanks Dejan. I'll take a look at that.
--
Dan D.
"Dejan Sarka" wrote:

> Dan,
> Check http://www.aspfaq.com/show.asp?id=2519.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
>
>|||You could use the DAY function to extract the numeric day of the week
select DAY(getdate())
This would give you all Sundays, for example.
SELECT * FROM Table1 where DAY(YourDateField) = 1
If you need to account for holidays or other more complex date calculations
the calendar table is the way to go.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
> Using SS2000, in QA I want to return data for only days Monday thru
Friday.
> Is there a way to do this? Is there some function that will give me only
the
> weekdays?
> Thanks,
> --
> Dan D.|||SELECT * FROM Table1 where DATEPART(dw, YourDateField) between 2 and 6
http://sqlservercode.blogspot.com/|||I discovered that function. I also discovered datepart which is what I'm
using. Thanks Terri.
--
Dan D.
"Terri" wrote:

> You could use the DAY function to extract the numeric day of the week
> select DAY(getdate())
> This would give you all Sundays, for example.
> SELECT * FROM Table1 where DAY(YourDateField) = 1
> If you need to account for holidays or other more complex date calculation
s
> the calendar table is the way to go.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
> Friday.
> the
>
>|||I came across datepart after some more looking. It works. Thanks SQL.
--
Dan D.
"SQL" wrote:

> SELECT * FROM Table1 where DATEPART(dw, YourDateField) between 2 and 6
> http://sqlservercode.blogspot.com/
>

how to get only weekdays

Using SS2000, in QA I want to return data for only days Monday thru Friday.
Is there a way to do this? Is there some function that will give me only the
weekdays?
Thanks,
--
Dan D.Dan,
Check http://www.aspfaq.com/show.asp?id=2519.
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
> Using SS2000, in QA I want to return data for only days Monday thru
> Friday.
> Is there a way to do this? Is there some function that will give me only
> the
> weekdays?
> Thanks,
> --
> Dan D.|||Thanks Dejan. I'll take a look at that.
--
Dan D.
"Dejan Sarka" wrote:
> Dan,
> Check http://www.aspfaq.com/show.asp?id=2519.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
> > Using SS2000, in QA I want to return data for only days Monday thru
> > Friday.
> > Is there a way to do this? Is there some function that will give me only
> > the
> > weekdays?
> >
> > Thanks,
> > --
> > Dan D.
>
>|||You could use the DAY function to extract the numeric day of the week
select DAY(getdate())
This would give you all Sundays, for example.
SELECT * FROM Table1 where DAY(YourDateField) = 1
If you need to account for holidays or other more complex date calculations
the calendar table is the way to go.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
> Using SS2000, in QA I want to return data for only days Monday thru
Friday.
> Is there a way to do this? Is there some function that will give me only
the
> weekdays?
> Thanks,
> --
> Dan D.|||SELECT * FROM Table1 where DATEPART(dw, YourDateField) between 2 and 6
http://sqlservercode.blogspot.com/|||I discovered that function. I also discovered datepart which is what I'm
using. Thanks Terri.
--
Dan D.
"Terri" wrote:
> You could use the DAY function to extract the numeric day of the week
> select DAY(getdate())
> This would give you all Sundays, for example.
> SELECT * FROM Table1 where DAY(YourDateField) = 1
> If you need to account for holidays or other more complex date calculations
> the calendar table is the way to go.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:B7E42041-0ADA-4AFA-8512-8EF4806E33DC@.microsoft.com...
> > Using SS2000, in QA I want to return data for only days Monday thru
> Friday.
> > Is there a way to do this? Is there some function that will give me only
> the
> > weekdays?
> >
> > Thanks,
> > --
> > Dan D.
>
>|||I came across datepart after some more looking. It works. Thanks SQL.
--
Dan D.
"SQL" wrote:
> SELECT * FROM Table1 where DATEPART(dw, YourDateField) between 2 and 6
> http://sqlservercode.blogspot.com/
>

How to get last record ?

Dear all,
I am writing a store procedure which should return the last entry whcih has
been written in a database table.
That store procedure will always return the last recorded entry from a table
How to do that ?
regards
sergeHi Serge,
What is the "last" record in your case ?
SQL Server doesn=B4t care about the order of data unless you specify an
Order in a Select query or you can determine the last entry via an
identity column or a column which store the modified date.
HTH, Jens Suessmeyer.|||You need to add datetime column and insert the value when you add a record.
Then you can select the most recent record in the table.
something like
select top 1 <column list>
from table
order by Time_inserted DESC
Thats offcourse if you mean 'last' by time of insert. If you want to handle
updates ('LastChanged') read about timestamp in the BOL.
MC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EB64CB5C-40D0-42E2-A56B-31205AB24C7D@.microsoft.com...
> Dear all,
> I am writing a store procedure which should return the last entry whcih
> has
> been written in a database table.
> That store procedure will always return the last recorded entry from a
> table
> How to do that ?
> regards
> serge|||If you don't record the information about which row was the last then
SQL Server won't do it for you. The answer is something like:
SELECT ,,,
FROM your_table
WHERE created_date =
(SELECT MAX(created_date)
FROM your_table) ;
David Portas
SQL Server MVP
--|||Hi Jens,
nased on that, I have a column in my database table Identified as START_TIME
which is of type DataTime.
Based on that column, the "last" record will be the one who have the biggest
DateTIme value.
any tip ?
"Jens" wrote:

> Hi Serge,
> What is the "last" record in your case ?
> SQL Server doesn′t care about the order of data unless you specify an
> Order in a Select query or you can determine the last entry via an
> identity column or a column which store the modified date.
> HTH, Jens Suessmeyer.
>|||Thnaks it works
"MC" wrote:

> You need to add datetime column and insert the value when you add a record
.
> Then you can select the most recent record in the table.
> something like
> select top 1 <column list>
> from table
> order by Time_inserted DESC
> Thats offcourse if you mean 'last' by time of insert. If you want to handl
e
> updates ('LastChanged') read about timestamp in the BOL.
> MC
>
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:EB64CB5C-40D0-42E2-A56B-31205AB24C7D@.microsoft.com...
>
>

How to get last payment and amt of all

This will get me what I need based on an entered client id, but what is I want it to return the last payment date and amt for all loans? I tried removing the two where clauses and it only returned the last payment entered but not for all loans.

SELECT dbo.tblLoan.Client_ID,MAX(dbo.tblPayments.PaymentDate)AS [Last Pay Date],SUM(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE (dbo.tblLoan.Client_ID = @.Client_ID)AND dbo.tblPayments.PaymentDate = (SELECT TOP 1 p.PaymentDateFROM dbo.tblPayments pINNERJOIN dbo.tblLoan lON l.Loan_ID = p.Loan_IDWHERE l.Client_ID = @.Client_IDORDER BY p.PaymentDateDESC)GROUP BY dbo.tblLoan.Client_ID

Can you give the exact design of the two tables, which fileds are what ...i mean primary key etc?

|||

Considering the Loan_ID will be unique in tblLoan

SELECT l.Client_ID,MAX(p.PaymentDate)AS'Last Pay Date',SUM (p.AmountPaid)AS'Total Amount Paid'FROM tblPayments pINNERJOIN tblLoan lON p.Loan_ID = l.Loan_IDWHERE l.Client_ID = 1GROUP BY l.Client_ID

|||

Addie,

I tried your suggestion but I need to see the last pay date and amt for all clients. This only shows if there is a clientid of 1.

I also tried removing the Where clause, that gave me the last pay date but it gave me a total sum of payments and I need just the amount of the last payment and date.

Then I remved the Sum but that made the query return all payments for all clients.

How can I make it show each client and there last pay date and last pay amt?

|||

Post some sample data from each of the tables in question and the expected output. Sometimes it is easier to see what you are trying to do just by looking at the data than your explanation.

|||

ndinakar,

I'm not able to provide data, however, below is an example of what I need:

ClientID Last Pay Date Last Pay Amt10010001 2/2/2007 $10010002001 3/2/2007 $200Eachof the clients may have made several paymentsover time but I needto seeonly these three fieldsfor each client,for their LAST payment made.

|||

Try this:

I have added Loan_ID to the results row; it seems possible one client might have two loans?

If you are absolutely sure that cannot happen, then simply remove Loan_ID from the first and last lines.

If you happen to have two or more payments for the same loan on the same day, you'll get the total. 

SELECT dbo.tblLoan.Client_ID, dbo.tblLoan.Loan_ID, max(dbo.tblPayments.PaymentDate)AS [Last Pay Date], sum(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE dbo.tblPayments.PaymentDate = (SELECT max( p.PaymentDate)FROM dbo.tblPayments pWHERE p.Loan_ID = dbo.tblLoan.Loan_ID)

Group by dbo.tblLoan.Client_ID, dbo.tblLoan.LoanID

|||

I think we are making progress, but that is not it yet. I'm going to make it easier for us.

Clients make payments on loans, they are inserted into tblPayments. I need to return a list of the LAST PAYMENT made by each client. I need to see clientID, PaymentDate, and PaymentAmount.

client1 2/2/2007 $100 < this is the last payment they made, they may have made other payments but this was the last.

client2 3/2/2007 $50 < this is the last payment they made, they may have made other payments but this was the last.

client3 4/1/2007 $1000 < thi si the last payment they made, they may have made other payments but this was the last.

etc...

|||

Without providing any table structure or sample data to work on your query it will only be a guessing game. Its just a matter of luck as to who takes the best guess. From your initial query it looks like the two tables are related by LoanId. But what is the relation between LoanId and ClientId?

|||

Did you look at my last post? It shows a simplified version of what I need. I have taken the loan table out of the query, it is not needed.

I cannot beleive that this is so hard to do in SLQ Server. I just want a list of the last payment made by each client that is in tblPayments.

tblPayments

ClientID, PaymentDate, PaymentAmount

|||

Yes I did look at your last post. Stating what you want can help if you also provide the source data from which you intend to achieve the result. As I mentioned after making the assumptions based on the info you provided here's a sample:

Declare @.paymentstable (PaymentIdint, Paymentdatedatetime , amountdecimal(10,2), ClientIdint)Insert into @.paymentsSelect 1,'01/01/2007', 100.00,10010001unionallSelect 2,'02/01/2007', 125.00,10010001unionallSelect 10,'02/01/2007', 125.00,10002001unionallSelect 11,'03/01/2007', 125.00,10002001unionallSelect 12,'04/01/2007', 255.00,10002001select P.*from @.payments PJoin (select Clientid,Max(PaymentDate)as MaxDatefrom @.paymentsGroup by Clientid) P2ON P.ClientId = P2.clientidAND P.Paymentdate = P2.maxdate

And, what you are trying to do is very simple, if you provided all the required info.

|||I think I may have worded my post in a way that you did not like, for that I apologize. That is never my intention.|||

SELECT dbo.tblLoan.Client_ID, dbo.tblPayments.PaymentDateAS [Last Pay Date], dbo.tblPayments.AmountPaidAS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE dbo.tblPayments.PaymentDate = (SELECT max( p.PaymentDate)FROM dbo.tblPayments pWHERE p.Loan_ID = dbo.tblLoan.Loan_ID)

order by dbo.tblLoan.Client_ID

|||

I mis understood the question, below is what you are perhaps looking for:

SELECT dbo.tblLoan.Client_ID,dbo.tblLoan.Loan_ID,MAX(dbo.tblPayments.PaymentDate)AS [Last Pay Date],SUM(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE dbo.tblPayments.PaymentDate = (SELECT MAX( p.PaymentDate)FROM dbo.tblPayments pWHERE p.Loan_ID = dbo.tblLoan.Loan_ID)GROUP BY dbo.tblLoan.Client_ID, dbo.tblLoan.Loan_IDORDER BY Client_ID