comparing two integers and returning a third

I am having difficulty trying to figure out how to compare two integers stored in a table to return a third. I have two integer fields in one table and two in another like this:

Table1.SomeNumber1 = 1

Table1.SomeNumber2 = 2

Table2.SomeNumber1 = 2

Table2.SomeNumber2 = 1

I need to be able to compare the first number from the first table to the first number in the second table. If the values are different I need to set a variable or field to 0. If the numbers are the same I need to set my variable or field to 1.

I need to follow the same procedure comparing the second number in the first table to the second number in the second table. In addition, I need to be able to do it in a single select statement.

Does anyone have any ideas on how this could be done? Thank you for any help you may be able to provide.

Gmz

CREATE TABLE [dbo].[num1](
[id] [int] NULL,
[number1] [int] NULL,
[number2] [int] NULL,
[flag1] [int] NULL,
[flag2] [int] NULL
)
CREATE TABLE [dbo].[num2](
[id] [int] NULL,
[number1] [int] NULL,
[number2] [int] NULL
)
--After the comparison, two flag fields in table num1 are updated
UPDATE num1
SET flag1=(SELECT (CASE WHEN a.number1 = b.number1 THEN 1 ELSE 0 END) FROM num1 a INNER JOIN
num2 b ON a.id = b.id and a.id=c.id), flag2=(SELECT (CASE WHEN a.number2 = b.number2 THEN 1 ELSE 0 END) FROM num1 a INNER JOIN
num2 b ON a.id = b.id and a.id=c.id)
FROM num1 c|||

I took what you posted and applied it to just return the values of the comparisons as results of my query which saved me from having to store the extra data in the table. It worked great.

Thank you!!!!

GMZ

sqlsql

comparing query results

1. What is the best way to compare efficiency (performance) of two different
queries that return the same result in general?
2. What if one contains user defined function one query and another without
user defined function? The reason I am asking this is that the optimizer's
cost model do not reflect the work done in UDF in the query execution plan.
ThanksJustin
Compare both exection plans first. In my experience ( sure it depends) that
queries which don't no UDF/s perform better
"Justin" <nospam@.nospam.com> wrote in message
news:O%23$Nz46jGHA.4828@.TK2MSFTNGP04.phx.gbl...
> 1. What is the best way to compare efficiency (performance) of two
> different queries that return the same result in general?
> 2. What if one contains user defined function one query and another
> without user defined function? The reason I am asking this is that the
> optimizer's cost model do not reflect the work done in UDF in the query
> execution plan.
> Thanks
>|||I'd like to use Profiler to see reads etc. Profiler *does* include the work
done inside an UDF
(something that execution plan or STATISTICS IO doesn't do).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <nospam@.nospam.com> wrote in message news:O%23$Nz46jGHA.4828@.TK2MSFTNGP04.phx.gbl.
.
> 1. What is the best way to compare efficiency (performance) of two differe
nt queries that return
> the same result in general?
> 2. What if one contains user defined function one query and another withou
t user defined function?
> The reason I am asking this is that the optimizer's cost model do not refl
ect the work done in UDF
> in the query execution plan.
> Thanks
>|||Justin a crit :
> 1. What is the best way to compare efficiency (performance) of two differe
nt
> queries that return the same result in general?
> 2. What if one contains user defined function one query and another withou
t
> user defined function? The reason I am asking this is that the optimizer'
s
> cost model do not reflect the work done in UDF in the query execution plan
.
> Thanks
>
The most effective parameters in order are :
1) IO, so execute your queries with this flag set :
SET STATISTICS IO ON /OFF
2) the time taken by CPU and other subsystems, so
SET STATISTICS TIME ON
The exection plan is not a good indicator because it is not quiclky
readable and the number of basic steps is not correlated with time or IO
consummation.
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Comparing fields within a select

Hi there,

Is it possible for a query to return all the records in a table which have the same value in a given column?

SELECT * FROM table1 WHERE field1 = 2

..would return all the records from table1 which have a field1 value of 2 but I don't want to specify the value - just that all the records have the same value.

Cheers,

WT.I'm not sure I understand what you're trying to do. Are you trying to return all records where the values in field1 are repeated? Can you provide an example to clarify?|||I think I understand what you are asking for. You're looking for all records where there are duplicates in field1.
Kind of the opposite of a DISTINCT query.
If you do a self join you can get what you want.
Here's the generic form...
SELECT * FROM table1 WHERE field1 IN (SELECT DISTINCT T1.field1 FROM table1.T1, table1.T2 WHERE T1.field1=T2.field1 AND T1.Field2<>T2.field2)

Here's an example using the Customers table from NorthWind that will return all the customers in cities where there is more than one customer in that city...
SELECT DISTINCT C1.CITY FROM Customers C1, Customers C2 WHERE C1.City = C2.City and C1.CustomerID <> C2.CustomerID|||An easier way to do this (if I understand your question):

SELECT CompanyID, COUNT(*)
Companies
GROUP BY CompanyID
HAVING COUNT(*) > 1

comparing DateTime in UK Format

Hello friends,

I am trying to return all records between 2 dates. The Date columns are in DateTime format, and i am ignoring the timestamp. The user should be able to input UK Date Format (dd/mm/yyyy) and return the rows. This sql code works fine for American date format, but i get an error: converting from varchar to datetime when i put in a UK format. eg. 22/11/06. Please advise on this problem! many thanks!

ALTER PROCEDURE SalaryBetweenDates
(

@.WeekStart datetime,

@.WeekEnd datetime
)
AS

BEGIN
SET @.WeekStart = (SELECT REPLACE(CONVERT(DATETIME,@.WeekStart ,103),' ','-'))
SET @.WeekEnd = (SELECT REPLACE(CONVERT(DATETIME,@.WeekEnd ,103),' ','-'))
END

BEGIN
SELECT s.StaffNo,s.StaffName,s.StaffAddress, s.HourlyRate,
sh.HoursWorked, CONVERT(varchar(12), sh.WeekStart, 103) AS StartDate, CONVERT(varchar(12), sh.WeekEnd, 103)As EndDate,(sh.HoursWorked * s.HourlyRate)"Salary"
From Staff As S INNER JOIN StaffHours As Sh
On S.StaffNo = Sh.StaffNo
WHERE sh.WeekStart >= (@.WeekStart)
AND sh.WeekEnd <= (@.WeekEnd)

FOR XML RAW ('paySlip'), root('Staff'), ELEMENTS XSINIL
END

ReturnYou need to convert the UK format date into a format that Sql can read.
I always use the following ones
'YYYY-MM-DD' for date
'YYYY-MM-DD HH:NN:SS' for date & time
use exactly as is... don't change the sperators

so '22/11/06' should be passed to sqlserver as '2006-11-22'|||

If you want to be able to call the procedure like this

EXEC SalaryBetweenDates '22/11/06', '1/12/06'

you're going to have to make the procedure parameters varchars and write some string handling code to figure out the strings that are passed in. I'd recommend that you leave it as it is and have the application pass dates in the format that SQL Server expects, if necessary have the application do the work at figuring out what date the user actually entered.

|||

thanks for your help guys. I set the parameters as strings in the end, and used REPLACE(CONVERT) to handle the function

:-)

Compare given period in current year / previous year

Hi
I want to write a function that can return a sum for a given date
range. The same function should be able to return the sum for the same
period year before.

Let me give an example:
The Table LedgerTrans consist among other of the follwing fields
AccountNum (Varchar)
Transdate
AmountMST (Real)

The sample data could be
1111, 01-01-2005, 100 USD
1111, 18-01-2005, 125 USD
1111, 15-03-2005, 50 USD
1111,27-06-2005, 500 USD
1111,02-01-2006, 250 USD
1111,23-02-2006,12 USD

If the current day is 16. march 2006 I would like to have a function
which called twice could retrive the values.
Previus period (for TransDate >= 01-01-2005 AND TransDate <=
16-03-2005) = 275 USD
Current period (for TransDate >= 01-01-2006 AND TransDate <=
16-03-2006) = 262 USD
The function should be called with the AccountNum and current date
(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.
How can I create a function that dynamically can do this ?

I have tried f.ex. calling the function with
@.ThisYear as GetDate()
SET @.DateStart = datepart(d,0) + '-' + datepart(m,0) +
'-'+datepart(y,@.ThisYear)
But the value for @.dateStart is something like 12-07-1905 so this
don't work.

I Would appreciate any help on this.
BR / Jan(jannoergaard@.hotmail.com) writes:
> Let me give an example:
> The Table LedgerTrans consist among other of the follwing fields
> AccountNum (Varchar)
> Transdate
> AmountMST (Real)
> The sample data could be
> 1111, 01-01-2005, 100 USD
> 1111, 18-01-2005, 125 USD
> 1111, 15-03-2005, 50 USD
> 1111,27-06-2005, 500 USD
> 1111,02-01-2006, 250 USD
> 1111,23-02-2006,12 USD
> If the current day is 16. march 2006 I would like to have a function
> which called twice could retrive the values.
> Previus period (for TransDate >= 01-01-2005 AND TransDate <=
> 16-03-2005) = 275 USD
> Current period (for TransDate >= 01-01-2006 AND TransDate <=
> 16-03-2006) = 262 USD
> The function should be called with the AccountNum and current date
> (GetDate() ?) and f.ex. 0 or 1 for this year / previous year.
> How can I create a function that dynamically can do this ?

I'm uncertain on want interface you want on your function (and I am
not sure that you should use a function anyway), but here is a
query for the task:

SELECT AccountNum, LastYearTroubles =
SUM(CASE WHEN Transdate BETWEEN
dateadd(YEAR, -1,
convert(char(4), @.date, 112) + '0101')) AND
dateadd(YEAR, -1, @.date)
THEN AmountMST
ELSE 0
END),
ThisYear =
SUM(CASE WHEN Transdate BETWEEN
convert(char(4), @.date, 112) + '0101')) AND
@.date)
THEN AmountMST
ELSE 0
END)
FROM Ledger
WHERE TransDate BETWEEN dateadd(YEAR, -1,
convert(char(4), @.date, 112) + '0101')) AND
@.date
GROUP BY AccountNum

As for the date conversion, format 112 is essentail for playing with
dates. This format is YYYYMMDD, and this is one of the formats that
always converts back to date in the same way.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi There

Thank you very much. I modified your query a lillte bit, and it worked
as wanted in the function.

CREATE FUNCTION GuruInvoicedPeriodNew (@.AccountNum as
VarChar(10),@.Period AS Int, @.ThisY as DateTime)
RETURNS Float AS
BEGIN

DECLARE @.LedgerTrans AS Float
SET @.LedgerTrans = 0

IF @.Period = 0 /*this year*/
BEGIN
SELECT @.LedgerTrans =
SUM(AmountMST) FROM dbo.LedgerTrans
WHERE (DATAAREAID = dbo.GuruDataArea())
AND (TransDate >= (convert(char(4), @.ThisY, 112) + '0101')
AND TransDate <= @.ThisY)
AND AccountNum = @.AccountNum
END

IF @.Period = 1 /*previous year*/
BEGIN
SELECT @.LedgerTrans = SUM(AMOUNTMST) FROM dbo.LEDGERTRANS
WHERE (DATAAREAID = dbo.GuruDataArea())
AND Transdate >= (dateadd(YEAR, -1, convert(char(4), @.ThisY,
112) + '0101'))
AND TransDate <= dateadd(YEAR, -1, @.ThisY)
AND AccountNum = @.AccountNum
END

RETURN @.LedgerTrans
END

BR/Jan|||(jannoergaard@.hotmail.com) writes:
> Thank you very much. I modified your query a lillte bit, and it worked
> as wanted in the function.
> CREATE FUNCTION GuruInvoicedPeriodNew (@.AccountNum as
> VarChar(10),@.Period AS Int, @.ThisY as DateTime)
> RETURNS Float AS
> BEGIN

Yellow alert! How are you going to use this function? If you are going
to say something like:

SELECT AccountNum, dbo.InvoicedPeriod(AccountNum, 1, getdate()),
dbo.InvoicedPeriod(AccountNum, 0, getdate())
FROM accounts

It's not going to perform well. Scalar UDFs is something you should use
with care, and not the least scalar UDFs that perform table access.
There is quite an overhead for calling a UDF once per row, and when you
do table access, you have essentially created a disguised cursor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Compairing Data From 2 Tables

Table A

ID Product_code
1 aa
2 bb
3 cc
3 dd

Table B

ID Product_code
1 aa
2 bb
3 cc
3 dd
3 ee
4 aa

I need to compair both tables and return a list ID and Product_code
which is not in both tables ie

Result set

3 ee
4 aa

Thanks

Richard

Here it is,

Code Snippet

Create Table tablea (

[ID] int ,

[Product_code] Varchar(100)

);

Insert Into tablea Values('1','aa');

Insert Into tablea Values('2','bb');

Insert Into tablea Values('3','cc');

Insert Into tablea Values('3','dd');

Create Table tableb (

[ID] int ,

[Product_code] Varchar(100)

);

Insert Into tableb Values('1','aa');

Insert Into tableb Values('2','bb');

Insert Into tableb Values('3','cc');

Insert Into tableb Values('3','dd');

Insert Into tableb Values('3','ee');

Insert Into tableb Values('4','aa');

Code Snippet

select

isnull(a.id,b.id) id,

isnull(a.product_code, b.product_code) product_code

from

tablea a

full outer join tableb b

on a.id=b.id and a.product_code = b.product_code

where

a.id is nullor b.id is null

or

a.product_code is null or b.product_code is null

|||

SELECT * FROM TableA A
WHERE NOT EXISTS
(
Select * from TableB B WHERE A.Id = B.Id AND A.Product_code = B.ProductCode
)
UNION

SELECT * FROM TableB A
WHERE NOT EXISTS
(
Select * from TableB B WHERE A.Id = B.Id AND A.Product_code = B.ProductCode
)

otherwise you could also use INTERSECT / EXCEPT if you are using SQL Server 2005.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Commandtext problem

I am trying to get the return value from the select statement and store it into a variable
does anyone know whats wrong with my code?

Private Function check_login(ByVal login As String) As String

dim dbCon As SqlConnection = New SqlConnection
Dim dbCmd As SqlCommand = New SqlCommand

Dim returnuser As Integer

dbCon.ConnectionString = _
"Data Source=localhost;" + _
"Initial Catalog=registeruser;" + _
"User ID=int422;" + _
"Password=int422"

dbCon.Open()

dbCmd.Connection = dbCon
dbCmd.CommandText = "SELECT count(login_id) from users where login_id=@.login"
dbCmd.CommandType = CommandType.Text

returnuser = dbCmd.ExecuteScalar

Return returnuser

dbCon.Close()

SELECT count(login_id) from users where login_id=@.login

I don't see where you are setting the value for the @.login parameter. Also the dbCon.Close() should be immediately after the ExecuteScalar() call and, in particular, before the return statement.

Command Timeout in web config

I'm having timeout issues with a few stored procedures which do calculations and take a while to return data (like 2-3 minutes).

I'm getting timeouts when I try to run them in my asp.net page.

From my research it seems that I need to increase the Command Timeout setting. I'm storing my connection string in my web.config file and I can't seeem to get the syntax correct for increasing the command timeout setting.

Can someone show my how it's done.

My connection string (from web.config):

<appSettings>
 <add key="DataGold" value="server=localhost;uid=datauser;pwd=qu3ry;database=DataGold" />
</appSettings>

TIA.If you are working with command object, then you can increase timeout for it by

myCommand.CommandTimeout = Time

Or if Dataadapter using command object then it wll go as

DataAdapter.SelectCommand.CommandTimeout = Time

Difference between command & connection Timeouts:
CommandTimeout Property Gets or sets the wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.

ConnectionTimeout Property Gets the time to wait while trying to establish a connection before terminating the attempt and generating an error. The default value is 15 seconds.

command text question?

i have one question in my select statement i'm selecting two things username, password will the dbCmd.ExecuteScalar return username,password in one row or together in the returnuser variable. i wanna return them both in two separate variables or do i need to different select statements?

thanks

Private Function check_login(ByVal login As String) As String

Dim dbCon As SqlConnection = New SqlConnection
Dim dbCmd As SqlCommand = New SqlCommand

Dim returnuser As Integer

dbCon.ConnectionString = _
"Data Source=localhost;" + _
"Initial Catalog=registeruser;" + _
"User ID=int422;" + _
"Password=int422"

dbCon.Open()

dbCmd.Connection = dbCon
dbCmd.CommandText = "SELECT login,password from users where login_id= @.login"
dbCmd.CommandType = CommandType.Text

With (dbCmd.Parameters)
.Add("@.login", SqlDbType.VarChar, 64).Value = login

End With

returnuser = dbCmd.ExecuteScalar

dbCon.Close()

Return returnuser

End FunctionExecuteScalar will only return the first row of the first column. You will either need separate ExecuteScalar calls with separate queries (not very efficient), use a datareader, fill a datatable, or use a stored procedure and have the username and password returned as output parameters. The easiest is probably a datareader:

// Sorry C# but you should get the idea for VB.Net
string returnuser = "";
string password = "";
SqlDataReader dr = dbCmd.ExecuteReader();
while( dr.Read() ){
returnuser = dr.GetString(0);
password = dr.GetString(1);
break; // just to make sure you only get one record
}

The stored procedure with output parameters is probably the most efficient (fastest) but it probably makes no practical difference.