query for this purpose

We have a worktran table
WoNo Jobno Status
1 01 Fullfill
1 02 Pending
2 01 Fullfill
2 02 Fullfill
We need to display those WoNo having all its rows status=fulfill
In the example above, it should display only WoNo 2 because it contain
all Fullfill status
We tried - select distinct WoNo from worktran where status='Fullfill'
But it does not make sure that all of the rows for certian WoNo have
status='Fulfill'
Thanks in advance.Hello, MadhavC
You can use one of these queries:
SELECT WoNo FROM worktran
GROUP BY WoNo
HAVING COUNT(*)=SUM(CASE WHEN Status='Fulfill' THEN 1 ELSE 0 END)
SELECT DISTINCT WoNo FROM worktran a
WHERE NOT EXISTS (
SELECT * FROM worktran b
WHERE a.WoNo=b.WoNo
AND b.WoNo<>'Fulfill'
)
You should also take a look at the following article, by Joe Celko,
regarding Relational Division:
http://www.dbazine.com/ofinterest/oi-articles/celko1
Razvan|||Thanks for your reply it worked.

Query for tables.

how can i fetch somw rows having same field value?

my table is like:

name id

x 1

y 2

z 2

w 5

so how can i get rows y and z for id=id ?

This is a Transact-SQL question and should be asked over there... To save you some time though:

select name, id from [table] a, [table] b
where a.id = b.id and a.name <> b.name

Will return:

x 2
y 2|||

thx sir...

lemme modify for others...

select t1.* from table1 t1,table1 t2 where t1.id=t2.id and t1.name<>t2.name

Query for finding rows whose sum constitues percentage of total su

Hi.
Given a table that looks like:
unique_ID, some_amount
1 10
2 15
3 7
4 20
5 8
6 7
...
What i want is to find the rows that contribute most to the total sum of the
column and then cut off at a given percentage. IE if i'd like to find the top
30% contributers to the total sum in the example above the result would be
row 4 (total being 67 and row 4 contributes with 30% of that).
Any help very much appreciated.
/fr
DROP Table #SomeTable
CREATE TABLE #SomeTable
(
unique_id INT Identity(1,1),
some_Amount INT
)
INSERt INTO #SomeTable(Some_Amount)
SELECT 10
INSERt INTO #SomeTable(Some_Amount)
SELECT 15
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
INSERt INTO #SomeTable(Some_Amount)
SELECT 20
INSERt INTO #SomeTable(Some_Amount)
SELECT 8
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
DECLARE @.Percentage DECIMAL(20,2)
SET @.Percentage = 0.25
Select * from #SomeTable
where some_amount >=
(Select @.Percentage * SUM(some_amount) from #SomeTable)
|||hi ragnar,
here's a script
I put the amount on a table with id and amount fields
hope it helps
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
|||hi,
you must cast the numerator in some datatype that accept the decimal place.
In my case i make it money. Other wise you get zero and then mulitply it with
100 to get the percentage. the rest is up to you
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
|||hi,
here's an additional help
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1 x
where cast(amount as money)/(select sum(amount) from test1)*100 > 25
order by 3
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
|||Try,
select 30 percent *
from t1
order by some_amount desc
-- or
select 30 percent with ties *
from t1
order by some_amount desc
AMB
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
sql

Query for finding rows whose sum constitues percentage of total su

Hi.
Given a table that looks like:
unique_ID, some_amount
1 10
2 15
3 7
4 20
5 8
6 7
...
What i want is to find the rows that contribute most to the total sum of the
column and then cut off at a given percentage. IE if i'd like to find the top
30% contributers to the total sum in the example above the result would be
row 4 (total being 67 and row 4 contributes with 30% of that).
Any help very much appreciated.
/frDROP Table #SomeTable
CREATE TABLE #SomeTable
(
unique_id INT Identity(1,1),
some_Amount INT
)
INSERt INTO #SomeTable(Some_Amount)
SELECT 10
INSERt INTO #SomeTable(Some_Amount)
SELECT 15
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
INSERt INTO #SomeTable(Some_Amount)
SELECT 20
INSERt INTO #SomeTable(Some_Amount)
SELECT 8
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
DECLARE @.Percentage DECIMAL(20,2)
SET @.Percentage = 0.25
Select * from #SomeTable
where some_amount >=(Select @.Percentage * SUM(some_amount) from #SomeTable)|||hi ragnar,
here's a script
I put the amount on a table with id and amount fields
hope it helps
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
--
thanks,
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:
> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||hi,
you must cast the numerator in some datatype that accept the decimal place.
In my case i make it money. Other wise you get zero and then mulitply it with
100 to get the percentage. the rest is up to you
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:
> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||hi,
here's an additional help
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1 x
where cast(amount as money)/(select sum(amount) from test1)*100 > 25
order by 3
--
thanks,
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:
> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||Try,
select 30 percent *
from t1
order by some_amount desc
-- or
select 30 percent with ties *
from t1
order by some_amount desc
AMB
"Ragnar" wrote:
> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr

Query for finding rows whose sum constitues percentage of total su

Hi.
Given a table that looks like:
unique_ID, some_amount
1 10
2 15
3 7
4 20
5 8
6 7
...
What i want is to find the rows that contribute most to the total sum of the
column and then cut off at a given percentage. IE if i'd like to find the to
p
30% contributers to the total sum in the example above the result would be
row 4 (total being 67 and row 4 contributes with 30% of that).
Any help very much appreciated.
/frDROP Table #SomeTable
CREATE TABLE #SomeTable
(
unique_id INT Identity(1,1),
some_Amount INT
)
INSERt INTO #SomeTable(Some_Amount)
SELECT 10
INSERt INTO #SomeTable(Some_Amount)
SELECT 15
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
INSERt INTO #SomeTable(Some_Amount)
SELECT 20
INSERt INTO #SomeTable(Some_Amount)
SELECT 8
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
DECLARE @.Percentage DECIMAL(20,2)
SET @.Percentage = 0.25
Select * from #SomeTable
where some_amount >=
(Select @.Percentage * SUM(some_amount) from #SomeTable)|||hi ragnar,
here's a script
I put the amount on a table with id and amount fields
hope it helps
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of t
he
> column and then cut off at a given percentage. IE if i'd like to find the
top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||hi,
you must cast the numerator in some datatype that accept the decimal place.
In my case i make it money. Other wise you get zero and then mulitply it wit
h
100 to get the percentage. the rest is up to you
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of t
he
> column and then cut off at a given percentage. IE if i'd like to find the
top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||hi,
here's an additional help
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1 x
where cast(amount as money)/(select sum(amount) from test1)*100 > 25
order by 3
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of t
he
> column and then cut off at a given percentage. IE if i'd like to find the
top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||Try,
select 30 percent *
from t1
order by some_amount desc
-- or
select 30 percent with ties *
from t1
order by some_amount desc
AMB
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of t
he
> column and then cut off at a given percentage. IE if i'd like to find the
top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14),
just records where any value occurs more than once. Any suggestions?
Many thanks
Homer
Homer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer
|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14)
,
just records where any value occurs more than once. Any suggestions?
Many thanks
HomerHomer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=1
4),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14),
just records where any value occurs more than once. Any suggestions?
Many thanks
HomerHomer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer

Query doesnt find existing data

SQL Server 2000 Enterprise

While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.

For example:

SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;

would not find the data sought. However, if I put in:

SELECT address FROM tblPersonalInformation WHERE name = Doe, John;

the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but cant figure out a reason. The front-end application displays the data without any apparent problems.

Ideas please.On the surface, it doesn't seem plausable...

Post the DDL of the Table, so we can see...|||Service pack #?|||Trailing spaces, perhaps?

What is the datatype of [ID Number]?|||Do this and let us know what you get:

select * from tblPersonalInformation where charindex(char(160), [ID Number]) > 0|||I have seen corrupt indexes cause this. Try

dbcc dbreindex ('tblPersonalInformation')

Query doesnt find existing data

SQL Server 2000 Enterprise

While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.

For example:

SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;

would not find the data sought. However, if I put in:

SELECT address FROM tblPersonalInformation WHERE name = Doe, John;

the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but cant figure out a reason. The front-end application displays the data without any apparent problems.

Ideas please.What is the datatype of [ID Number]?

query difficulties

Hello All,
I hope you can help with this.
I have next kind of data in the database(80000 rows) and I
want to drop out the min and max values.
day number prod quantity
monday 411 12004 545
monday 411 12004 776
monday 411 12004 345
monday 411 12004 876
monday 411 12004 764
tuesday 411 12004 324
tuesday 411 12004 225
tuesday 411 12004 421
tuesday 411 12004 314
tuesday 411 12004 432
I want to get rid off: monday 411 12004 345
monday 411 12004 876
tuesday 411 12004 432
tuesday 411 12004 225
and so on.
Thanks!help
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 INT,
col3 INT,
col4 INT
)
INSERT INTO #Test VALUES ('monday',1,12004,500)
INSERT INTO #Test VALUES ('monday',1,12004,200)
INSERT INTO #Test VALUES ('monday',1,12004,2)
INSERT INTO #Test VALUES ('tuesday',1,12004,50)
INSERT INTO #Test VALUES ('tuesday',1,12004,10)
INSERT INTO #Test VALUES ('tuesday',1,12004,100)
SELECT * FROM #Test
WHERE col4 =(SELECT MAX(col4)FROM #Test t
WHERE t.col1=#Test.col1)
OR col4=(SELECT MIN(col4)FROM #Test t
WHERE t.col1=#Test.col1)
ORDER BY col1
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:041201c539dc$3f13d5a0$a601280a@.phx.gbl...
> Hello All,
> I hope you can help with this.
> I have next kind of data in the database(80000 rows) and I
> want to drop out the min and max values.
> day number prod quantity
> monday 411 12004 545
> monday 411 12004 776
> monday 411 12004 345
> monday 411 12004 876
> monday 411 12004 764
> tuesday 411 12004 324
> tuesday 411 12004 225
> tuesday 411 12004 421
> tuesday 411 12004 314
> tuesday 411 12004 432
> I want to get rid off: monday 411 12004 345
> monday 411 12004 876
> tuesday 411 12004 432
> tuesday 411 12004 225
> and so on.
> Thanks!
>|||Try,
use northwind
go
create table t (
[day] varchar(10),
number int,
prod int,
quantity int)
go
insert into t values('monday', 411, 12004, 545)
insert into t values('monday', 411, 12004, 776)
insert into t values('monday', 411, 12004, 345)
insert into t values('monday', 411, 12004, 876)
insert into t values('monday', 411, 12004, 764)
insert into t values('tuesday', 411, 12004, 324)
insert into t values('tuesday', 411, 12004, 225)
insert into t values('tuesday', 411, 12004, 421)
insert into t values('tuesday', 411, 12004, 314)
insert into t values('tuesday', 411, 12004, 432)
go
delete t
where
not exists (select * from t as a where a.[day] = t.[day] and a.number =
t.number and a.prod = t.prod and a.quantity < t.quantity)
or not exists (select * from t as a where a.[day] = t.[day] and a.number =
t.number and a.prod = t.prod and a.quantity > t.quantity)
go
select * from t
go
drop table t
go
AMB
"help" wrote:

> Hello All,
> I hope you can help with this.
> I have next kind of data in the database(80000 rows) and I
> want to drop out the min and max values.
> day number prod quantity
> monday 411 12004 545
> monday 411 12004 776
> monday 411 12004 345
> monday 411 12004 876
> monday 411 12004 764
> tuesday 411 12004 324
> tuesday 411 12004 225
> tuesday 411 12004 421
> tuesday 411 12004 314
> tuesday 411 12004 432
> I want to get rid off: monday 411 12004 345
> monday 411 12004 876
> tuesday 411 12004 432
> tuesday 411 12004 225
> and so on.
> Thanks!
>|||I understand that you want to delete rows having the minimum and
maximum values of Quantity for each combination of Day, Number and
Prod. Try:
DELETE FROM YourTable
WHERE quantity IN
((SELECT MIN(quantity)
FROM YourTable AS T
WHERE day = YourTable.day
AND number = YourTable.number
AND prod = YourTable.prod),
(SELECT MAX(quantity)
FROM YourTable AS T
WHERE day = YourTable.day
AND number = YourTable.number
AND prod = YourTable.prod))
Make sure you have a current backup and test this out before you delete
any actual data.
David Portas
SQL Server MVP
--|||Just curious what does the # sign mean in #test?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePwR61dOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> help
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 INT,
> col3 INT,
> col4 INT
> )
> INSERT INTO #Test VALUES ('monday',1,12004,500)
> INSERT INTO #Test VALUES ('monday',1,12004,200)
> INSERT INTO #Test VALUES ('monday',1,12004,2)
> INSERT INTO #Test VALUES ('tuesday',1,12004,50)
> INSERT INTO #Test VALUES ('tuesday',1,12004,10)
> INSERT INTO #Test VALUES ('tuesday',1,12004,100)
>
> SELECT * FROM #Test
> WHERE col4 =(SELECT MAX(col4)FROM #Test t
> WHERE t.col1=#Test.col1)
> OR col4=(SELECT MIN(col4)FROM #Test t
> WHERE t.col1=#Test.col1)
> ORDER BY col1
>
>
> "help" <anonymous@.discussions.microsoft.com> wrote in message
> news:041201c539dc$3f13d5a0$a601280a@.phx.gbl...
>|||Aaron
#--local temporary table
##--global temporary table
For more details please refer to the BOL
"Aaron" <kuya789@.yahoo.com> wrote in message
news:%23C3sxFeOFHA.3444@.tk2msftngp13.phx.gbl...
> Just curious what does the # sign mean in #test?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePwR61dOFHA.2748@.TK2MSFTNGP09.phx.gbl...
>|||Hi,
Try this :
delete t
where quantity in (select min(quantity) from t group by [day], number, prod)
or quantity in (select max(quantity) from t group by [day], number, prod)
"help" wrote:

> Hello All,
> I hope you can help with this.
> I have next kind of data in the database(80000 rows) and I
> want to drop out the min and max values.
> day number prod quantity
> monday 411 12004 545
> monday 411 12004 776
> monday 411 12004 345
> monday 411 12004 876
> monday 411 12004 764
> tuesday 411 12004 324
> tuesday 411 12004 225
> tuesday 411 12004 421
> tuesday 411 12004 314
> tuesday 411 12004 432
> I want to get rid off: monday 411 12004 345
> monday 411 12004 876
> tuesday 411 12004 432
> tuesday 411 12004 225
> and so on.
> Thanks!
>

Query dataset locally from code

Hi all,
I need to get 20 rows of data from the SQL Server DB to put in 20 Textboxes
always one item of one row.
It's about Text in various languages, so each language needs 20 textstrings.
I don't want to call the Stored Procedure 20 times.
So I thought in calling it one time, get the data out once of the DB and
refer to it in queries local to the report 20 times.
I tried it with an Assembly. This worked, but I needed to configure the
assembly's trust etc.
This is speaking against a copy/paste deployment to the client's Reporting
Server.
Is it possible to create a standard data set in the Report and refer in some
form to it locally?
E.g.: Dataset dsData takes one time the 20 rows.
In a textbox I call a function like =code.getValue("item17")
and in the code section I query the dsData with something like
function getValue (item as string) as string
return (select col2 from dsData where col1 = item)
end function
Thanks in advance, HenryHello Henry,
I have suggested the following article to you.
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
You do not need to configure the trust level if it not required.
I hope this will be some help.
The Assembly is more functional than the Embeded Code in Reporting Services.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
thank you for your answer.
But, as written above, I've already programmed an Assembly that works, but
the client DENIES the use of an Assembly.
I know also, that the <Code> part of the RDL-File is compiled implicitely
into an Assembly by ReportServer on load, so I cant access the outside world
(like the filesystem or the SQL-Server) from within the custom Code without
elevating its trustlevel.
I know also, that it is possible to handover e.g. Parameters to the code to
manipulate it programatically.
Like:
Function GetValue(reportParameters as Parameters) As Object
do sth with the Parameters
return sthelse
end function
The question was:
Since via a Data Source and a DataSet I've already the data available.
CAN I do anything like above with the Parameters, but here with the dataset?
E.g. giving a whole Dataset to a Function (best byRef) to do a select from?
Function GetValue(dsData as Dataset) As Object
return (select sth from dsData where ...)
end function
Thanks in advance, Henry|||Hello Henry,
Unfortunately, you could not refer the dataset in the code.
I would like to know the concern from your client of using the assembly.
You could only grant some proper trust for runing the report.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Dear Wei Lu,
it might be possible to access the file system, as in your
XML-Assembly-Example that you sent me, but getting data out from an
SQL-Server DB is a big trouble.
The info under your hint:
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
is far from sufficient.
Only after lots of hours and the following resources, I got it to work.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
http://forums.microsoft.com/msdn/showpost.aspx?postid=139787&siteid=1
Microsoft Official Course 2840A, Implementing Security for Applications
So, in the very end I needed to give:
- fulltrust via UrlMembershipCondition
Error message: "Cannot generate Hash"
- Give the assembly a strong name
Error message:
Request for the permission of type
'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0,
... failed. (rsRuntimeErrorI-nExpression)
- Adjust CAS
- Assert
System.Data.SqlClient.SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
It didn't work until now!!!
So, worse, I had to install the Assembly in the GAC.
and with this give: AllowPartiallyTrustedCallers()
With our client was agreed a copy/paste-deployment.
All this procedure is FAR from that!
Any ideas?
Thank you, Henry|||Hello Henry,
Yes, the render order is from the upper-left to bottom-right.
I would like to suggest you use a hidden textbox in the report to
initialize.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||If I understood you correctly you are passing a predefined number of
parameters to this function. This can also be done dynamically using a
little hack described in this page:
http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx
I took the code at this page and used to build a dynamic localization
function that reads the text id numbers, language codes and corresponging
texts from a dataset and stores them into a Hashtable object using text id +
language code as the key and text as the value.
Basic idea is that if an aggregate function like Sum is called for a certain
scope, it will go through all of the data in that scope and it can take a
function call that returns a number as a parameter.
Like this:
Sum(Code.LoadDictionary(Fields!TEXT_ID.Value , Fields!LANGUAGE_CODE.Value,
Fields!TEXT_TXT.Value), "DICTIONARY")
Where DICTIONARY is the dataset for dictionary. The LoadDictionary function
is called once for each row in the scope.
You can the have this 'loader' in a hidden field as Mr. Lu suggested.
Then for the localized field you just have for example
=Code.GetLocalizedText("TEXT_0001", Parameters!LanguagePar.Value)
I know that your solution works great. I just think tahat this way the
function calls are simpler. I hope this helps.
Juho Salo

query consecutive valid rows?

I have a question for the T-SQL gurus out there.
I need to find all Names where there are 10 consecutive "Yes"
Values. The table has upwards of 30 million records.
Does anyone have an idea how to structure this query?
CREATE TABLE (
Name varchar(50)
,Value char(1)
,dDate datetime)
Sample Data
Name Value dDate
David, Yes, 1/1/2004
David, Yes, 1/2/2004
David, No, 1/3/2004
David, Yes, 1/4/2004
David, Yes, 1/5/2004
David, Yes, 1/6/2004
Chris, Yes, 1/6/2004
David, Yes, 1/7/2004Can you be more precise? By consecutive, do you mean "on consecutive days",
or "on any days, but uninterrupted in time by a NO"?
Perhaps you can post a few more of your 30,000,000 rows, so we have
sample data for which there are some results? If possible, post
sample data as INSERT .. VALUES statements that match the
CREATE TABLE statement. Then we can paste it into Query
Analyzer and use it without correcting your errors. 'Yes' and 'No' do
not fit into a char(1) column, for example.
Finally, are the dates constrained to be time-less, and is (Name,dDate)
a key (or guaranteed to be unique)? And does 1/4/2004 mean January
4, 2004, or does it mean April 1, 2004?
Steve Kass
Drew University
Dave wrote:

>I have a question for the T-SQL gurus out there.
>I need to find all Names where there are 10 consecutive "Yes"
>Values. The table has upwards of 30 million records.
>Does anyone have an idea how to structure this query?
>
>CREATE TABLE (
> Name varchar(50)
> ,Value char(1)
> ,dDate datetime)
>Sample Data
>Name Value dDate
>David, Yes, 1/1/2004
>David, Yes, 1/2/2004
>David, No, 1/3/2004
>David, Yes, 1/4/2004
>David, Yes, 1/5/2004
>David, Yes, 1/6/2004
>Chris, Yes, 1/6/2004
>David, Yes, 1/7/2004
>
>|||Sorry for being unclear.
I mean 10 (lets just say 3 for simplicity) Yes values in a row based of
dDate (datetime). cName,nValue ,dDate form the primary key... for this
example) should be unique.
I hope that answers your questions, if not please restate and I will do
my best to be clear.
Thanks for your interest!
As you can see from
select *
from myTest
order by cName,dDate
David,Peter have 3 consecutive Yes values while Chris and Chad do not.
CREATE TABLE myTest(
cName varchar(50)
,nValue char(3)
,dDate datetime )
INSERT INTO myTest(cName,nValue ,dDate)
SELECT 'David','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 17:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 18:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-08 16:26:31.077'
UNION
SELECT 'Chris','No','2005-08-08 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-09 16:26:31.077'
UNION
SELECT 'David','No','2005-08-10 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-11 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-13 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-14 16:26:31.077'|||Sorry for being unclear.
I mean 10 (lets just say 3 for simplicity) Yes values in a row based of
dDate (datetime). cName,nValue ,dDate form the primary key... for this
example) should be unique.
I hope that answers your questions, if not please restate and I will do
my best to be clear.
Thanks for your interest!
As you can see from
select *
from myTest
order by cName,dDate
David,Peter have 3 consecutive Yes values while Chris and Chad do not.
CREATE TABLE myTest(
cName varchar(50)
,nValue char(3)
,dDate datetime )
INSERT INTO myTest(cName,nValue ,dDate)
SELECT 'David','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 17:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 18:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-08 16:26:31.077'
UNION
SELECT 'Chris','No','2005-08-08 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-09 16:26:31.077'
UNION
SELECT 'David','No','2005-08-10 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-11 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-13 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-14 16:26:31.077'|||Sorry for being unclear.
I mean 10 (lets just say 3 for simplicity) Yes values in a row based of
dDate (datetime). cName,nValue ,dDate form the primary key... for this
example) should be unique.
I hope that answers your questions, if not please restate and I will do
my best to be clear.
Thanks for your interest!
As you can see from
select *
from myTest
order by cName,dDate
David,Peter have 3 consecutive Yes values while Chris and Chad do not.
CREATE TABLE myTest(
cName varchar(50)
,nValue char(3)
,dDate datetime )
INSERT INTO myTest(cName,nValue ,dDate)
SELECT 'David','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 17:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 18:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-08 16:26:31.077'
UNION
SELECT 'Chris','No','2005-08-08 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-09 16:26:31.077'
UNION
SELECT 'David','No','2005-08-10 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-11 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-13 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-14 16:26:31.077'|||Sorry for being unclear.
I mean 10 (lets just say 3 for simplicity) Yes values in a row based of
dDate (datetime). cName,nValue ,dDate form the primary key... for this
example) should be unique.
I hope that answers your questions, if not please restate and I will do
my best to be clear.
Thanks for your interest!
As you can see from
select *
from myTest
order by cName,dDate
David,Peter have 3 consecutive Yes values while Chris and Chad do not.
CREATE TABLE myTest(
cName varchar(50)
,nValue char(3)
,dDate datetime )
INSERT INTO myTest(cName,nValue ,dDate)
SELECT 'David','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-02 16:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-03 17:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-05 16:26:31.077'
UNION
SELECT 'Peter','Yes','2005-08-05 18:26:31.077'
UNION
SELECT 'Chad','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-07 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-08 16:26:31.077'
UNION
SELECT 'Chris','No','2005-08-08 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-09 16:26:31.077'
UNION
SELECT 'David','No','2005-08-10 16:26:31.077'
UNION
SELECT 'David','Yes','2005-08-11 16:26:31.077'
UNION
SELECT 'Chad','No','2005-08-13 16:26:31.077'
UNION
SELECT 'Chris','Yes','2005-08-14 16:26:31.077'|||CREATE TABLE answers(
aName varchar(50)
,Value char(1)
,dDate datetime)
go
-- select only 3 consecutive Ys to keep it short
insert into answers values('Joe','Y','9/17/2001')
insert into answers values('Joe','Y','9/18/2001')
insert into answers values('Joe','N','9/19/2001')
insert into answers values('Joe','Y','9/20/2001')
insert into answers values('Joe','Y','9/21/2001')
insert into answers values('Jill','N','9/19/2001')
insert into answers values('Jill','Y','9/20/2001')
insert into answers values('Jill','Y','9/21/2001')
insert into answers values('Jill','Y','9/22/2001')
insert into answers values('Jack','Y','9/21/2001')
insert into answers values('Jack','Y','9/22/2001')
go
CREATE TABLE #answers( id int identity
,aName varchar(50)
,Value char(1)
,dDate datetime)
go
insert into #answers(aname,value,ddate)
select aname,value,ddate from answers
order by aname, ddate
go
select distinct aname from #answers a
where exists(select aname from #answers b
-- 3 consecutive Ys to keep it short
where a.id=b.id+2 and a.aname=b.aname)
and not exists(select aname from #answers b
-- 3 consecutive Ys to keep it short
where b.id between a.id-2 and a.id and a.aname=b.aname and b.value='N')
drop table answers
drop table #answers
it works, but it would be interesting to figure out if it performs well
enough in your invironment. Instead of using identity, you could use
row_number() if you have it|||You need keys and constraints on the data. Let me do this in Standard
SQL and leave it to you to translate into dialect. Is this what you
meant?
CREATE TABLE Quiz
(contestant_name VARCHAR(30) NOT NULL,
answer_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
answer_flag CHAR(1) DEFAULT 'Y' NOT NULL
CHECK (answer_flag IN ('Y', 'N')),
PRIMARY KEY (foo_name, answer_date));
SELECT DISTINCT Q1.person_name
FROM Quiz AS Q1
AND 'Y'
= ALL (SELECT Q2.answer
FROM Quiz AS Q2
WHERE Q2.answer_date
BETWEEN Q1.answer_date AND Q1.answer_date +
INTERVAL 9 DAY);|||>> SELECT DISTINCT Q1.person_name
FROM Quiz AS Q1
AND 'Y'
= ALL (SELECT Q2.answer
FROM Quiz AS Q2
WHERE Q2.answer_date
BETWEEN Q1.answer_date AND Q1.answer_date +
INTERVAL 9 DAY);
<<
this is a wrong answer:
1. the subquery does not take into account contestant_name. So id Joe
always answers Yes from 1/1 to 1/11, but Jill gives a No at 1/5, Joe
will not be selected.
2. INTERVAL 9 DAY clause implies that each contestant answers exactly 1
question a day, which is not the case, just look at the OP's sample data|||Here is one way to do it:
select
distinct T.cName
from (
select
T1.cName,
T1.dDate as YesDate,
coalesce(min(T2.dDate),'99991231') as NextNoDate
from myTest as T1
left outer join myTest as T2
on T2.cName = T1.cName
and T2.nValue = 'No'
and T2.dDate > T1.dDate
where T1.nValue = 'Yes'
group by T1.cName, T1.dDate
) as R
join myTest as T
on T.cName = R.cName
and T.dDate >= R.YesDate
and T.dDate < R.NextNoDate
group by T.cName, R.YesDate
having count(*) >= 3
I am assuming the key is (cName, dDate), without nValue,
but if I'm wrong, you will have to tweak this to make it do
what you want when there are Yes and No values at the
same time for the same person.
SK
Dave wrote:

>Sorry for being unclear.
>I mean 10 (lets just say 3 for simplicity) Yes values in a row based of
>dDate (datetime). cName,nValue ,dDate form the primary key... for this
>example) should be unique.
>I hope that answers your questions, if not please restate and I will do
>my best to be clear.
>Thanks for your interest!
>As you can see from
>select *
>from myTest
>order by cName,dDate
>David,Peter have 3 consecutive Yes values while Chris and Chad do not.
>
>
>CREATE TABLE myTest(
> cName varchar(50)
> ,nValue char(3)
> ,dDate datetime )
>INSERT INTO myTest(cName,nValue ,dDate)
>SELECT 'David','Yes','2005-08-02 16:26:31.077'
> UNION
>SELECT 'Peter','Yes','2005-08-02 16:26:31.077'
> UNION
>SELECT 'Chris','Yes','2005-08-02 16:26:31.077'
> UNION
>SELECT 'Chad','Yes','2005-08-03 16:26:31.077'
> UNION
>SELECT 'Peter','Yes','2005-08-03 16:26:31.077'
> UNION
>SELECT 'Peter','Yes','2005-08-03 17:26:31.077'
> UNION
>SELECT 'Peter','Yes','2005-08-05 16:26:31.077'
> UNION
>SELECT 'David','Yes','2005-08-05 16:26:31.077'
> UNION
>SELECT 'Peter','Yes','2005-08-05 18:26:31.077'
> UNION
>SELECT 'Chad','Yes','2005-08-07 16:26:31.077'
> UNION
>SELECT 'David','Yes','2005-08-07 16:26:31.077'
> UNION
>SELECT 'Chad','No','2005-08-08 16:26:31.077'
> UNION
>SELECT 'Chris','No','2005-08-08 16:26:31.077'
> UNION
>SELECT 'David','Yes','2005-08-09 16:26:31.077'
> UNION
>SELECT 'David','No','2005-08-10 16:26:31.077'
> UNION
>SELECT 'David','Yes','2005-08-11 16:26:31.077'
> UNION
>SELECT 'Chad','No','2005-08-13 16:26:31.077'
> UNION
>SELECT 'Chris','Yes','2005-08-14 16:26:31.077'
>
>

Query Column with letters

I have a database with a zip code column. I want to flag all rows that have letters in that column (a-z). How could I construct the where clause?
Is there a better alternative to:
WHERE ZipCode LIKE '%a%' OR ZipCode LIKE '%b%' ...if you're doing this because you want to limit what goes into the column, why not just create a constraint that won't accept letters?|||if you're doing this because you want to limit what goes into the column, why not just create a constraint that won't accept letters?

Well the data is already in there and we would rather have it disabled with the option of cleaning it up later rather than simply deleting such data.|||ok. Use the ISNUMERIC function... it'll return a 1 if the field is a valid number... 0 if it's not. That should be the easiest way to determine if there are letters in the postal code.|||Good idea, but I must allow for hyphens.|||Ya mean like

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTable99(Col1 varchar(8000))
GO

INSERT INTO myTable99(Col1)
SELECT '12345' UNION ALL
SELECT 'Brett' UNION ALL
SELECT 'North Dallas 40' UNION ALL
SELECT '40-20' UNION ALL
SELECT '123.45' UNION ALL
SELECT '123456789012345678901234567890'
GO

SELECT * FROM myTable99
WHERE ISNUMERIC(REPLACE(Col1,'-','')) = 1
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

QUERY BY USER

I have a datagrid with rows that pertain to multiple users. I want to
write a query statement so that whomever the CURRENT logged-in
user is, he can go to the datagrid page and it will show ONLY rows
that pertain to him (programmatic security). To accomplish this,
I have a column in the datatable that references each user's
username. So I need a query statement that will compare the table's
'username' column to the current logged-in user. Something like...

SELECT... FROM [VendorEquipment]
WHERE [UserName] = Context.Current.User.Name

...or something like this. Question is, can someone help on how to
syntax this. Also, do I need anything special in the login form or in
web.config to 'pave the way' for this query to work.

Thx much.
Reid C.after a successfull login,you can store the username in sessions...|||Thanks for the reply...but I am a novice and will probably need more to clear up
the picture...can you suggest how I can syntax my SELECT command to accomplish
the above-stated objective and is there something I need to do via sessions to
enable this--and where would I do that?

Thx much.
Reid C.|||i believe you are checking for user authorization when the user logs in right ?

just put the username into session after the validation is successfull

session("username")=username

and frm any page
you can get it back as

dim uname as string
uname=session("username")

so when you query the db for the records you can do something like

strsql=select * From table where username='" & uname & "'"

and bind the datagrid.

HTH|||Thanks again...this sounds like what I'm looking for...just two
more questions due to my relative inexperience:

1) the statement: session("username")=username
you suggested...does that need to be in the login form
like in the "sub loginBtn_click" subroutine? and if so where in
the subroutine does it need to go...is the placement of the
statement critical?

2) is "username" interchangeable in this operation with "userid" ?
because I have written my registration form and login with a
"userid" instead of a "username"...but I will change this if it is
important to have "username" for forms authentication.

Thx.
Reid C.|||yep you can have any variable in session you want..username,userid...doesnt matter.
right after you check the userid with the password and if the query returns as a valid user, store the userid in sessions...and you should be able to access it frm any page...

HTH|||Thank you ...do I place the session stmt in global.asax?

like this, for instance:

Sub Session_OnStart()
 Session("userId")=userId
End Sub

?

Thx.
Reid C.|||no...you put it in ur login.aspx where ever you validate the authenticity of the user...|||o.k., thanks again, ...but I am a complete novice and have been building pages
using WebMatrix, so sorry to be dense...but do I place the "session..." statement
in the "Function GetUser..." or in my "Sub LoginBtn_click..." subroutine, or after
that and right before the </script> tag ? I know placement of this statement is
important, I just don't know where?

Thx again.
Reid C.|||if you post your code for the login page..i can tell you where to place it...|||Thanks again for all your help...here is the code for the login (minus the html) and
I've got the statement placed right before the end </script> tag...tell me if you see
a glaring case of needing to move it to a more appropriate space.

<script runat="server">
Function GetUser(ByVal userId As String, ByVal userPassword As String) As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "SELECT [Users].* FROM [Users] WHERE (([Users].[UserId] = @.UserId) AND ([Users].[U"& _
 "serPassword] = @.UserPassword))"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_userId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userId.ParameterName = "@.UserId"
 dbParam_userId.Value = userId
 dbParam_userId.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userId)
 Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userPassword.ParameterName = "@.UserPassword"
 dbParam_userPassword.Value = userPassword
 dbParam_userPassword.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userPassword)
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
End Function
Sub LoginBtn_Click(Sender As Object, E As EventArgs)
 If Page.IsValid Then
 Dim userDS As New System.Data.DataSet
 userDS = GetUser(UserId.Text, UserPassword.Text)
 If userDS.Tables(0).Rows.Count = 1 Then
 FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
 Else
 Msg.Text = "Invalid UserId or Password: Please try again"
 End If
 End If
End Sub
session("UserId")=UserId
</script

Thx again.
Reid C.|||i think this should work...

If Page.IsValid Then
 Dim userDS As New System.Data.DataSet
 userDS = GetUser(UserId.Text, UserPassword.Text)
 If userDS.Tables(0).Rows.Count = 1 Then
 'valid user...
 session("UserId")=UserId
 FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
 Else
 Msg.Text = "Invalid UserId or Password: Please try again"
 End If
 End If
End Sub
session("UserId")=UserId

|||In your revision you have the "session..." statement right after
the "If userDS.Tables(0).Rows.Count = 1 Then" statement which
made sense as soon as I saw it. But you also have the same
"session..." statement again after the "End Sub" statement. Is
this just a typo on your part from copying and pasting what I
sent you, or does it need to be there?

I am assuming it is a copy&paste oversight...unless you answer
back that it needs to be repeated...

Thx much again for your help.
Reid C.|||See if you can review the code for my datagrid page and see where I'm going wrong.
One thing, the "UserId" I need to compare is in the datatable as "PropUserId" because
I also have a column in the table "VendorUserId" and need to reference both. So I
need to compare the session "UserId" with the table's "PropUserId" column and I've
added a statement in the "Function GetVendorEquipment..." that says the "PropUserId"
column is equivalent to "UserId"...but I am getting a blank table on the page when I
run it.

<script runat="server"
 Function GetVendorEquipment() As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 dim UserId as String
 dim PropUserId as String
 PropUserId=session("UserId")
 Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]='" & UserId & "'"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
 End Function
 Sub Page_Load(sender As Object, e As EventArgs)
 DataGrid1.DataBind()
 End Sub
</script>

Thx again for your help.
Reid C.|||ur userid seems to be a number...
so you have to use

Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]=" & PropUserId

also one other suggestion...bind your datagrid within if not ispostback...loop

Sub Page_Load(sender As Object, e As EventArgs)
 if not ispostback then
 DataGrid1.DataBind()
 end if
End Sub

HTH

Query by length of varchar column

Hi mates,

I have a Table:Test with column text:varchar(255). I want get rows where text length to be longer than 100. Is it possible?

Thx in advance,

Look at LEN and DATALENGTH functions in BOL.

select *

from TableName

where datalength(colName) > 100

|||Thx very much!

query by grouping

Hi,
I have a table with data that every 2 rows have same data for most of
cloumns (11), only 4 columns have different data. I would like to query this
table so that such rows appear as single row in the result ( I don't need th
e
4 cloumns), so I used that 11 columns in my group by clause.
A large group by will be perforance problem?
ThanksGroup by and Distinct do the same thing (well, not really - ones for
aggrigating - but anyway), and using distinct will make your code a little
cleaner, rather than having a group by statement with 11 columns in it, you
can just use the word distinct to return all rows without duplicates.
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:8167FA52-468D-48BC-8D35-8F0E7BD69E46@.microsoft.com...
> Hi,
> I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query
this
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns), so I used that 11 columns in my group by clause.
> A large group by will be perforance problem?
> Thanks|||If you don't need the 4 columns that have different values, just leave them
out of the query, and add the word Distinct immediatey after the Select
Select Distinct ....
That will do the trick...
"Jen" wrote:

> Hi,
> I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query th
is
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns), so I used that 11 columns in my group by clause.
> A large group by will be perforance problem?
> Thanks|||Hi,
How about when i use the word SUM, do i have to put all the columns in the
group by?
Thanks,
Bruno N
"Jen" <Jen@.discussions.microsoft.com> escreveu na mensagem
news:8167FA52-468D-48BC-8D35-8F0E7BD69E46@.microsoft.com...
> Hi,
> I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query
this
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns), so I used that 11 columns in my group by clause.
> A large group by will be perforance problem?
> Thanks|||If you are aggrigating, yes, you would use group by instead of distinct.
Simon Worth
"Bruno N" <nylren@.hotmail.com> wrote in message
news:eB$VmrOJFHA.3596@.TK2MSFTNGP14.phx.gbl...
> Hi,
> How about when i use the word SUM, do i have to put all the columns in the
> group by?
> Thanks,
> Bruno N
> "Jen" <Jen@.discussions.microsoft.com> escreveu na mensagem
> news:8167FA52-468D-48BC-8D35-8F0E7BD69E46@.microsoft.com...
> this
need
> the
>|||Thanks, I do use min() to get the amount since some rows have null value, so
I need to use group, is there performance issue? Is it a good way or I need
to get all the rows back and let client to massage the data?
"Simon Worth" wrote:

> If you are aggrigating, yes, you would use group by instead of distinct.
> --
> Simon Worth
>
> "Bruno N" <nylren@.hotmail.com> wrote in message
> news:eB$VmrOJFHA.3596@.TK2MSFTNGP14.phx.gbl...
> need
>
>|||Group by limits the amount of records returned to your client, so it is a
good thing in that regard. Less traffic on the highway so to speak.
There's no need for the client application to loop through the records to
find the minimum amount for each group of records - the functionality is
built into SQL to accommodate aggregating data and should be utilized as
such.
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:F65899CA-80FD-45F8-8401-92CAFECA272E@.microsoft.com...
> Thanks, I do use min() to get the amount since some rows have null value,
so
> I need to use group, is there performance issue? Is it a good way or I
need
> to get all the rows back and let client to massage the data?
> "Simon Worth" wrote:
>
the
of
query|||Jen,
From yr orig post
<<< I have a table with data that every 2 rows have same data for most of
cloumns (11), only 4 columns have different data. I would like to query this
table so that such rows appear as single row in the result ( I don't need th
e
4 cloumns),
Is the column you need the minimum from one of the 11, or one of the 4?
If it's one of the 11, then you can't group by the 11, you'll need t ogroup
by the remaining 10... The one you're aggregating on cannot be in the Group
By.
If it's one of the 4, then Iguess you DO need (at least one) of the 4, hmmm
?
"Jen" wrote:
> Thanks, I do use min() to get the amount since some rows have null value,
so
> I need to use group, is there performance issue? Is it a good way or I nee
d
> to get all the rows back and let client to massage the data?
> "Simon Worth" wrote:
>|||Yes, the minimum column if from one of 4 columns.
"CBretana" wrote:
> Jen,
> From yr orig post
> <<< I have a table with data that every 2 rows have same data for most of
> cloumns (11), only 4 columns have different data. I would like to query th
is
> table so that such rows appear as single row in the result ( I don't need
the
> 4 cloumns),
> Is the column you need the minimum from one of the 11, or one of the 4?
> If it's one of the 11, then you can't group by the 11, you'll need t ogrou
p
> by the remaining 10... The one you're aggregating on cannot be in the Grou
p
> By.
> If it's one of the 4, then Iguess you DO need (at least one) of the 4, hm
mm?
> "Jen" wrote:
>

Query and process performace with incremental update

hello all,

we are working on a project with a large scale of data (around 1000 rows per second).
we built a cube on this fact table.

this table will hold at most 90M rows.

we need the data in the cube to be "real time", that mean, up to date.

we are doing it by proactive caching- incremental update.

we also need a very good query performance.

that's why the storage mode is set to MOLAP.

we still get a low performace from the cube process and and the querys.

any suggestions how to solve this issues?

Thanks in advance,

Shy Engelberg - Certagon.

You might be seeing the results of the meta data locking (see http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx) If you are processing the cube very frequently. You probably need to profile the server to gather as much information as you can to figure out where the issues are.

Is it on the source system - selected only new records?

Is the system CPU, IO or memory bound?

Are you using partitions to isolate the processing to a smaller subset of the data?

|||

The SSAS 2005 Performance Guide is a good reference for these kinds of issues ( http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc).

I agree with Darren's idea of identifying whether the problem is occuring in retreiving source data records or in assembling the MOLAP structures. And partitioning may also be beneficial if you can isolate updates to a smaller partition.

You may also want to consider using HOLAP. HOLAP will give you excellent query performance for most queries with shorter processing times.

Bryan