finding minimum value

How to find the minimum value from time Timestamp column ?

I want get the eralier timestamp vlaues from the avaliable list

when iam using Aggregate transformation ..its again giving all the list of vlaues

Thanks
Niru

If you have only one minimum per data set I would use the Script component to get it manually.

Thanks.

|||no i have lot of set of duplicate values with in the same column....I have to get the Minimum ones in those sets ..like

5
5
3
3

Desired result:

5
5

|||

I still do not understand your scenario.

If you have a data like this:

A B

1 1

1 2

2 7

2 8

You can use the Aggregate component to group on column A and find a minimum on B, so it would produce:

A B

1 1

2 7

Is that what you are looking for?

Finding duplicate records using two columns

I'm trying to find duplicate records in a table. I know how to do that using
a single column value, but I need to do it using the combined values of two
columns, company_id and client_id. There multiple are records with the same
company_id value (e.g., 123) and multiple records with the same department_id
value (e.g., 456). But there should be only one record with a company_id
value of 123 and a department_id value of 456.
I've searched for data in one table that is not in another table on these
two columns using CAST to concatenate the values:
select * from company c
where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
not in
(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
from client r)
But I can't seem to figure out how to use CAST to find duplicate records in
a single table. Should I be doing this a different way?
Any and all help would be appreciated.
JohnSELECT company_id, client_id, count(*) as Duplicates
FROM Company
GROUP BY company_id, client_id
HAVING COUNT(*) > 1
This works great with one column or ten columns.
Roy Harvey
Beacon Falls, CT
On Tue, 30 Oct 2007 12:56:05 -0700, John Steen
<moderndads(nospam)@.hotmail.com> wrote:
>I'm trying to find duplicate records in a table. I know how to do that using
>a single column value, but I need to do it using the combined values of two
>columns, company_id and client_id. There multiple are records with the same
>company_id value (e.g., 123) and multiple records with the same department_id
>value (e.g., 456). But there should be only one record with a company_id
>value of 123 and a department_id value of 456.
>I've searched for data in one table that is not in another table on these
>two columns using CAST to concatenate the values:
>select * from company c
>where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
>not in
>(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
>from client r)
>But I can't seem to figure out how to use CAST to find duplicate records in
>a single table. Should I be doing this a different way?
>Any and all help would be appreciated.
>John|||Thank you, Roy! It worked perfectly.
John
"Roy Harvey (SQL Server MVP)" wrote:
> SELECT company_id, client_id, count(*) as Duplicates
> FROM Company
> GROUP BY company_id, client_id
> HAVING COUNT(*) > 1
> This works great with one column or ten columns.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 30 Oct 2007 12:56:05 -0700, John Steen
> <moderndads(nospam)@.hotmail.com> wrote:
> >I'm trying to find duplicate records in a table. I know how to do that using
> >a single column value, but I need to do it using the combined values of two
> >columns, company_id and client_id. There multiple are records with the same
> >company_id value (e.g., 123) and multiple records with the same department_id
> >value (e.g., 456). But there should be only one record with a company_id
> >value of 123 and a department_id value of 456.
> >
> >I've searched for data in one table that is not in another table on these
> >two columns using CAST to concatenate the values:
> >
> >select * from company c
> >where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
> >not in
> >(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
> >from client r)
> >
> >But I can't seem to figure out how to use CAST to find duplicate records in
> >a single table. Should I be doing this a different way?
> >
> >Any and all help would be appreciated.
> >
> >John
>

Finding duplicate based on records in same table

I need a help with a sql...
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...
paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected result?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:

> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>
|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join (select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...
|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...

>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack
|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegro ups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>

Finding duplicate based on records in same table

I need a help with a sql...
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected resul
t?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:

> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join ( select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...

>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack|||Hi
[url]http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html[/
url]
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegroups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>

Finding duplicate based on records in same table

I need a help with a sql...
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected result?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join ( select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...
>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegroups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>

Finding and updating all rows with the same value in a column

This integer column should be unique, s? prior to altering it to unique i
need to traverse all rows and update the rows with the same value. This tabl
e
has another colun which is the primary key and when 2 rows has the same valu
e
the row with the highest value of the primary key will be updated.
Any easy T-SQL way to do it?
Regards,
Olavupdate your_table
set your_column = whatever you wish to set it to
where exists(select 1 from your_table yt
where your_table.your_column = yt.your_column
and your_table.PK_column > yt.PK_column)
Just curious: what kind of information do you have in that column so
that you can easily update it just to enforse uniqueness?sql

Finding and Grouping Records off of a given field value

In the "tblEmailGroupLink" table...

I need to find all records with the "UnSubscribed" field having a "True" value. All these records will have a corresponding "Emailid" field.

In the "tblEmailAddress" table...

The same "Emailid" field has a corresponding "EmailAddress" field.

What needed is all the email addresses found in the "EmailAdddress" field of these records.

I'm very new at this so I hope I explained this right. I'd really appreciate any help I can get.

Thanks,

Bill

Something like this:

Code Snippet

SELECT ea.EmailAddress
FROM tblEmailAddress ea
JOIN tblEmailGroupLink eg
ON ea.EmailID = eg.EmailID
WHERE eg.UnSubscribed = 'True'

|||

Arnie,

Thank you very much, it works perfect.

Bill

Finding a value within a range using IN

Patients may be assigned up to 4 diagnoses for an encounter. I need to selec
t
patients (by their unique ID) when any one of the 4 diagnoses falls within a
range. So I wrote the SELECT as below. Now I am curious if there is a more
concise syntax using IN. Here's what I did:
SELECT PatUniqueID FROM Charges WHERE
diag_code1 BETWEEN '300.00' AND '399.99' OR
diag_code2 BETWEEN '300.00' AND '399.99' OR
diag_code3 BETWEEN '300.00' AND '399.99' OR
diag_code4 BETWEEN '300.00' AND '399.99' ;
Thanks...The problem is that the design of table Changes is not normalized and SQL
statements are more concise when used with normalized data models. For
example, the following would be normalized:
PatUniqueID
VisitDate
diag_code
ChargeAmt
If the clinic decides that a patient may be assigned up to 5 diagnoses,
would this involve a re-write of the application?
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:123AEEEE-91FE-42AD-8047-793FC3A6D797@.microsoft.com...
> Patients may be assigned up to 4 diagnoses for an encounter. I need to
> select
> patients (by their unique ID) when any one of the 4 diagnoses falls within
> a
> range. So I wrote the SELECT as below. Now I am curious if there is a more
> concise syntax using IN. Here's what I did:
> SELECT PatUniqueID FROM Charges WHERE
> diag_code1 BETWEEN '300.00' AND '399.99' OR
> diag_code2 BETWEEN '300.00' AND '399.99' OR
> diag_code3 BETWEEN '300.00' AND '399.99' OR
> diag_code4 BETWEEN '300.00' AND '399.99' ;
> Thanks...
>|||JT wrote:

> If the clinic decides that a patient may be assigned up to 5
> diagnoses, would this involve a re-write of the application?
Indeed, a good rule:
If the customer even thinks there might be more than one, make it an
unlimited. It'll save you lots of trouble afterwards.
Kind regards,
Stijn Verrept.|||JT, I like that I often learn things from my posts that are not what I
thought I was asking about. In standard medical billing only up to 4
diagnoses are allowed (adding more is unlikely) and they must be rank
ordered. The primary diagnosis goes to a specific place in a claim, secondar
y
to another place, etc. In addition, the reference list of diagnoses changes.
A 2005 diagnosis might be absent from the 2006 reference list. Unless the dx
is actually written to the claim, obsolete references would have to be
maintained forever. Given all that would you still normalize the diagnoses?
"JT" wrote:

> The problem is that the design of table Changes is not normalized and SQL
> statements are more concise when used with normalized data models. For
> example, the following would be normalized:
> PatUniqueID
> VisitDate
> diag_code
> ChargeAmt
> If the clinic decides that a patient may be assigned up to 5 diagnoses,
> would this involve a re-write of the application?
>
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:123AEEEE-91FE-42AD-8047-793FC3A6D797@.microsoft.com...
>
>|||>> In standard medical billing only up to 4 diagnoses are allowed (adding mo
re is unlikely) and they must be rank ordered. <<
Modifying the DDL you never posted, try something like this:
CREATE TABLE MedForms
(patient_id CHAR(20) NOT NULL,
diag_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
icd_1 CHAR(7) NOT NULL CHECK (icd_1 LIKE
'[0-9][0-9][0-9].[0-9][0-9][0-9]'),
icd_2 CHAR(7) CHECK (icd_1 LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'),
icd_3 CHAR(7) CHECK (icd_1 LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'),
icd_4 CHAR(7) CHECK (icd_1 LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'),
. );
I assume that you are using the ICD codes. You might also want to add
a REFERENCES clause to a table.
The primary diagnosis goes to a specific place in a claim, secondary
to another place, etc. In addition, the reference list of diagnoses
changes.
A 2005 diagnosis might be absent from the 2006 reference list. Unless
the dx
is actually written to the claim, obsolete references would have to be
maintained forever. Given all that would you still normalize the
diagnoses?|||Yes, I would still normalize the diagnosis codes in a seperate table, but I
come from a data warehousing background where retaining historical,
versioned, and even obsolete data is one of the primary requirements of the
database. The Charges table could have a column indicating it's rank.
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:FC270CD0-F38E-4E3C-ADD1-B0931A692FA5@.microsoft.com...
> JT, I like that I often learn things from my posts that are not what I
> thought I was asking about. In standard medical billing only up to 4
> diagnoses are allowed (adding more is unlikely) and they must be rank
> ordered. The primary diagnosis goes to a specific place in a claim,
> secondary
> to another place, etc. In addition, the reference list of diagnoses
> changes.
> A 2005 diagnosis might be absent from the 2006 reference list. Unless the
> dx
> is actually written to the claim, obsolete references would have to be
> maintained forever. Given all that would you still normalize the
> diagnoses?
> "JT" wrote:
>|||I work in a hospital and the dx codes are ordered in a separate table.
However, the issue with your query is that your where clause looks like
you are filtering a numeric field when you actually have a varchar
datatype. Between 300.00 and 399.99 is 33.0. I use something like the
following:
SELECT PatUniqueID FROM Charges WHERE
case when isnumeric(diag_code1) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 OR
case when isnumeric(diag_code2) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 OR
case when isnumeric(diag_code3) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 OR
case when isnumeric(diag_code4) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 .
Then case statement is necessary because some dx codes contain alpha
characters.
Alternatively, you could create a view of the table that contains the
the numeric dx code as well as the varchar code. I don't like this
option as much because you have to translate the columns even when they
are not needed.sql

Finding "Id" with "Username" selection and then Inserting

I have a simple insert statement that takes values from textboxes and inserts them into a sql server database. I would like to add a value that is selected from a dropdown list and find its corresponding unique "Id" value and insert into the database as well.

My sql statement looks like this:

string strSQL = "INSERT INTO aspnet_Expenses (ExpenseDate,RentalCar,ect..) VALUES (@.ExpenseUserId,@.ExpenseDate,@.RentalCar,ect..)";

I would like to add this to it: SELECT @.ExpenseUserId = UserId FROM aspnet_users WHERE Username = ExpenseUserName

1) How do I assign the value from the dropdown list to save as the "ExpenseUserName"

and

2) Am I on the right path with this at all?

To do this in the least number of trips to the DB, pass all the parameters you have to a stored proc. In your proc:

(1) Use the SELECT from aspnet_users to get the userid into a variable.

(2) Do your regular insert.

|||

How do I pass the values to a stored procedure? Will I need to create a blll and data access layer? I've tried learning those but I was hoping to do something a little more straight forward. I'm pretty new to all of this.

|||OK, you can start from here:Using Stored Procedures with a Command
Try it and if you have any trouble please feel free to post it.
sql

Find values from record that had a max value in a group by

I am looking for the MS SQL variant of the oracle 'KEEP DENSE_RANK' construction.

If you have a table like this:

key date item 1 20070101 Apple 1 20070202 Banana 1 20070303 Cherry

The query for retrieving the item from the last date should pick the Cherry, but what would be the most efficient way to do this (without using multiple queries, in oracle I don't need a sub query!).

You would like to do something like:

Select Key, max(Date) as LastDate, PickLastValue(Cherry) on Date desc

from Table

group by Key.

any suggestions?

hans

You can do the same funciton in SQL Server 2005..

Code Snippet

Create Table #data (

[key] Varchar(100) ,

[date] Varchar(100) ,

[item] Varchar(100)

);

Insert Into #data Values('1','20070101','Apple');

Insert Into #data Values('1','20070202','Banana');

Insert Into #data Values('1','20070303','Cherry');

Insert Into #data Values('2','20070101','Apple');

Insert Into #data Values('2','20070202','Banana');

Code Snippet

--Over all

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Order By [date] Desc) rank From #Data

)

Select [key],[date],[item] from CTE Where rank=1

Code Snippet

--For each Key

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Partition By [Key] Order By [date] Desc) rank From #Data

)

Select [key],[date],[item],rank from CTE Where rank=1

|||

If you use sql server 2000 then you have to use the subquery...

Code Snippet

--For Overall

Select * from #data Where [date] in (Select max(date) From #data)

--For Each Key

Select * from #data Data

Join (Select [Key], max(date) date From #data Group By [Key]) Sub On

Sub.[Key]=Data.[Key] and Sub.date=Data.date

|||

This will do!

thanx

hans

Find value match in SQL table

Find value match in SQL table

Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?

Taod for ms sql server has that feature to compare rows from 2 tables..

I nkow that are theare also others but Toad i did use it..

Sorry for my bad english

|||

There is a tablediff.exe that comes with SQL Server 2005, if you are using 2005.

find value based on max(date)

I know I have done this before, but cannot for the life of me remember how.

I am trying to determine return the current (last added) deduction amount for each deduction type for each employee

Sample Table:
employee|Deduction_type|Date_entered|Amount
1|MED|1/1/2007|50
1|DEPC|1/1/2007|100
1|MED|1/8/2007|50
1|DEPC|1/8/2007|100
1|MED|1/15/2007|150
2|MED|1/1/2007|35
2|DEPC|1/1/2007|100
2|MED|1/8/2007|35
2|DEPC|1/8/2007|75
2|MED|1/15/2007|35

Any suggestions?select t.employee
, t.Deduction_type
, t.Date_entered
, t.Amount
from Sample as t
inner
join (
select employee
, Deduction_type
, max(Date_entered) as max_date
from Sample
group
by employee
, Deduction_type
) as m
on m.employee = t.employee
and m.Deduction_type = t.Deduction_type
and m.max_date = t.Date_enteredsql

Find minimum value in a list

How do I find the minimum value in a list if provided in a function?
For example: FindMin('323', '29', '991')
The answer should be '29'.
I could pull this off by comparing each value to the other until I determine
which is the least value. Although I have experience in other languages, I'
m
a bit new to SQL and am a bit stumped on how to pull this one off. I'm
assuming it should be rather easy.
PatrickMin() function
e.g. SELECT MIN(field1) FROM Table1
-Jason
"Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I
determine
> which is the least value. Although I have experience in other languages,
I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Create a user defined function that split the list and return a table and us
e
it as the source to:
select min(colA)
from dbo.ufn_table_from_list('323, 29, 991')
go
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"Patrix317" wrote:

> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I determi
ne
> which is the least value. Although I have experience in other languages,
I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Jason:
Thanks, Jason, but this isn't quite what I'm looking for. I'm passing three
separate felds not one.
Example record:
Farmer: John
Apples: 323
Oranges: 29
Peache: 991
Of all of John's produce, he has the least in oranges.
FindMin(Apples, Oranges, Peaches)
Answer: Oranges
Hope this clears up my question.
Patrick
"Jason Mauss" wrote:

> Min() function
> e.g. SELECT MIN(field1) FROM Table1
> -Jason
> "Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
> news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> determine
> I'm
>
>|||What format is the data in? If it is a column in a database then
SELECT TOP 1 MyValue
FROM MyTable
ORDER BY MyValue
If it's in a string list, then it isn't properly normalized and you may have
issues with your database design. You should probably give a little more
information.
"Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I
> determine
> which is the least value. Although I have experience in other languages,
> I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Or that. DUH.
"Jason Mauss" <jason.mauss@.nospamgmail.com> wrote in message
news:egHAUAJNFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Min() function
> e.g. SELECT MIN(field1) FROM Table1
> -Jason
> "Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
> news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> determine
> I'm
>|||Patrick,
A UDF to split a delimited list and return a table, as suggested by Alej,
is:
-- *********************************
Create Function dbo.ParseString (
@.S VarChar(8000), @.delim Char(1))
Returns @.tOut Table
(ValNum Integer Primary Key Identity,
sVal VarChar(1000))
As
Begin
Declare @.sVal VarChar(1000)
Declare @.dPos Integer
Declare @.Start Integer Set @.Start = 1
-- --
If @.S = @.delim Or Len(@.S) = 0 Return
Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
-- --
Set @.dPos = CharIndex(@.delim, @.S, 1)
While @.dPos <> 0
Begin
Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
Insert @.tOut (sVal) Values (@.sVal)
Set @.Start = @.dPos + 1
Set @.dPos = CharIndex(@.delim, @.S, @.Start)
End
Return
-- ---
End
-- *********************************
Then you can just take the minimum of the values in the table returned by
this function..
Select Min(Cast(sVal as Integer))
From dbo.ParseString('323, 29, 991', ',')
"Patrix317" wrote:

> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I determi
ne
> which is the least value. Although I have experience in other languages,
I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Woops, sorry about that...didn't entirely understand what you were looking
for. Alejandro's suggestion is probably what you're looking for.
-Jason
"Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
news:C5DCE464-E3FB-4E3F-A02A-2D5FF3BDCED6@.microsoft.com...
> Jason:
> Thanks, Jason, but this isn't quite what I'm looking for. I'm passing
three
> separate felds not one.
> Example record:
> Farmer: John
> Apples: 323
> Oranges: 29
> Peache: 991
> Of all of John's produce, he has the least in oranges.
> FindMin(Apples, Oranges, Peaches)
> Answer: Oranges
> Hope this clears up my question.
> Patrick
> "Jason Mauss" wrote:
>
languages,
I'm|||Bob,
Please see my reply to Jason for more information.
"Bob Castleman" wrote:

> What format is the data in? If it is a column in a database then...|||CBretana,
Thanks for the code. It's not as cryptic as I had suspected. I'm going to
try this now and check it out. It's certainly a different way of handling i
t!
Patrick
"CBretana" wrote:
> Patrick,
> A UDF to split a delimited list and return a table, as suggested by Ale
j,
> is:
> -- *********************************
> Create Function dbo.ParseString (
> @.S VarChar(8000), @.delim Char(1))
> Returns @.tOut Table
> (ValNum Integer Primary Key Identity,
> sVal VarChar(1000))
> As
> Begin
> Declare @.sVal VarChar(1000)
> Declare @.dPos Integer
> Declare @.Start Integer Set @.Start = 1
> -- --
> If @.S = @.delim Or Len(@.S) = 0 Return
> Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
> -- --
> Set @.dPos = CharIndex(@.delim, @.S, 1)
> While @.dPos <> 0
> Begin
> Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
> Insert @.tOut (sVal) Values (@.sVal)
> Set @.Start = @.dPos + 1
> Set @.dPos = CharIndex(@.delim, @.S, @.Start)
> End
> Return
> -- ---
> End
> -- *********************************
>
> Then you can just take the minimum of the values in the table returned by
> this function..
> Select Min(Cast(sVal as Integer))
> From dbo.ParseString('323, 29, 991', ',')
> "Patrix317" wrote:
>

Find Duplicated value

I have a table having 50000 rows. One field(Name) of the table may have duplicated data. How can I get all the duplicated data?

Hi,

what about

SELECT SomeColumn
FROM SomeTable
GROUP BY SomeCOlumn
HAVING COUNT(*) > 1

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Find and Replace string value in a stored procedure.

Not sure if there is a way to do this programmatically. I am using SQL
Server 2005 and I need to find a specific string value in a stored
procedure and replace that string value with some other value.
I can perform the "Find" part of the process by querying the
Information_Schema.Routines view but not sure there is a way to
perform the replace, essentially ALTERING the stored procedure. Is
there a command I can use that can do this? I essentially want to do
a batch Find and Replace type process.
Thanks in advance!Safest way is to use the code files you have stored in your source code
control system. Check them all out, do a find/replace in files command
(using a tool such as Search and Replace), check them back in. Compile the
ones that had a replacement. S&R can provide a file listing that can be
used to drive a compile process using sqlcmd or numerous other mechanisms.
If you don't have a system like the above, take the time to set one up! :-)
You can easily script all sprocs to separate files using SSMS or many other
3rd party tools. I like ApexSQL's Script.
<lgalumbres@.gmail.com> wrote in message
news:1191591082.224636.49880@.o80g2000hse.googlegroups.com...
> Not sure if there is a way to do this programmatically. I am using SQL
> Server 2005 and I need to find a specific string value in a stored
> procedure and replace that string value with some other value.
> I can perform the "Find" part of the process by querying the
> Information_Schema.Routines view but not sure there is a way to
> perform the replace, essentially ALTERING the stored procedure. Is
> there a command I can use that can do this? I essentially want to do
> a batch Find and Replace type process.
> Thanks in advance!
>

Find a word in entire database

SQL server 2000
Hi
I want to search a field value in entire database.
Example: Search for word "Ontario" in all rows of all tables in the
database. I do not want to search column_names but basically all rows in all
columns of the database for a particular word.
Thanks
ontario, canada
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada
|||If you need to do rather convoluted search (e.g. with regular expressions),
you could also consider dump everything out to files and search files for
your string. Probably more troublesome than it's worth, but could be useful
for certain scenarios.
Linchi
"db" wrote:

> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in all
> columns of the database for a particular word.
> Thanks
> ontario, canada
|||IIRC ApexSQL Edit has this functionality built in. Pretty slick.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada

Find a word in entire database

SQL server 2000
Hi
I want to search a field value in entire database.
Example: Search for word "Ontario" in all rows of all tables in the
database. I do not want to search column_names but basically all rows in all
columns of the database for a particular word.
Thanks
ontario, canadahttp://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada|||If you need to do rather convoluted search (e.g. with regular expressions),
you could also consider dump everything out to files and search files for
your string. Probably more troublesome than it's worth, but could be useful
for certain scenarios.
Linchi
"db" wrote:
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in all
> columns of the database for a particular word.
> Thanks
> ontario, canada|||IIRC ApexSQL Edit has this functionality built in. Pretty slick.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada

Financial functions for SQL Server

Hi everybody,

Does anyone have financial functions to be run in SQL Server 2000? For example, future value, interest rate, payments, and so on. Or where can I find them on Internet?

Thanks in advance

Jaime

Here might be a good place to start?
http://www.google.se/search?hl=sv&q=future+value%2C+interest+rate%2C+payments+function+%2BSQL+Server&meta=

/Kenneth

|||

No so good...

I have found some commercial libraries but none (free) that lists all financial functions. At least, I have found future value implementation. That's why I asked if someone has already a list of functions ready to use.

Jaime

Filters Top 5 showing more than 5 if a tie

I have a filter with the top 5, and if I have 10 items with the same top
value, it will show all 10. Is there anyway to cut this off show it just
shows 5?Hi,
Maybe using the distinct operator in your query?
SELECT DISTINCT TOP 5 col1
FROM table1
HTH,
Eric|||Why not try using the TOP N in the SQL instead of the filter.. By default
SQL will NOT include duplicates unless you include TOP N WITH TIES...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:eUf3l%23m%23EHA.4092@.TK2MSFTNGP09.phx.gbl...
> I have a filter with the top 5, and if I have 10 items with the same top
> value, it will show all 10. Is there anyway to cut this off show it just
> shows 5?
>|||Problem I'm using MDX queries and want to limit the number of queries.
I have a Top 5/ Show all toggle button. So I need all results back from the
query.
Using 2 queries kills me timewise.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OAacjNo%23EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Why not try using the TOP N in the SQL instead of the filter.. By default
> SQL will NOT include duplicates unless you include TOP N WITH TIES...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:eUf3l%23m%23EHA.4092@.TK2MSFTNGP09.phx.gbl...
> > I have a filter with the top 5, and if I have 10 items with the same top
> > value, it will show all 10. Is there anyway to cut this off show it
just
> > shows 5?
> >
> >
>

filtering rows that already exist in the destination table

Hi All,

What is the most straighforward way of not importing rows that already exist in the destination table (as determined by a primary key value)? Thanks.

Regards,

Daniel

Method 2 here: http://www.sqlis.com/default.aspx?311

-Jamie

|||Excellent, thanks.