Query Help

Help, please!
I have 3 columns with numeric values. i want to write a query that returns
the following:
If all 3 columns are populated, then the middle value (not the average);
If one colunn has a null, then the lesser value,
If 2 are null, then the remaining value.
For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
the result to be "10". If ColumnB were null, then the result would be "5".
Making sense?
Is there a way to do this, without having to create a bazillion Case When
statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
... and so on).
Just wondering.
Thank you!
Kyra
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBA
Kyra,
I think the bazillion CASEs is the only way to go. Here's one attempt:
CREATE TABLE T1 (T1ID INT NOT NULL IDENTITY, Col1 int, Col2 int, Col3 int)
GO
INSERT T1 VALUES (NULL, NUll, 1)
INSERT T1 VALUES (NULL, 2, NULL)
INSERT T1 VALUES (3, NUll, NULL)
INSERT T1 VALUES (NULL, 4, 5)
INSERT T1 VALUES (6, NULL, 7)
INSERT T1 VALUES (8,9, NULL)
INSERT T1 VALUES (10,11,12)
GO
SELECT
CASE
WHEN Col1 IS NULL AND Col2 IS NULL THEN Col3
WHEN Col2 IS NULL AND Col2 IS NULL THEN Col1
WHEN Col1 IS NULL AND Col3 IS NULL THEN Col2
WHEN Col1 IS NULL AND Col2 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col2 < Col3 THEN Col2 Else Col3 END
WHEN Col2 IS NULL AND Col1 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col1 < Col3 THEN Col1 Else Col3 END
WHEN Col3 IS NULL AND Col1 IS NOT NULL AND Col2 IS NOT NULL THEN
CASE WHEN Col1 < Col2 THEN Col1 Else Col2 END
WHEN Col1 <= Col2 AND Col2 <= Col3 THEN Col2
WHEN Col2 <= Col3 AND Col3 <= Col1 THEN Col3
WHEN Col3 <= Col1 AND Col1 <= Col2 THEN Col1
END
FROM T1
One thing your conditions left out: what if they're all NULL?
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:3147EC21-B5A3-4725-B156-6A40834E5561@.microsoft.com...
> Help, please!
> I have 3 columns with numeric values. i want to write a query that returns
> the following:
> If all 3 columns are populated, then the middle value (not the average);
> If one colunn has a null, then the lesser value,
> If 2 are null, then the remaining value.
> For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> the result to be "10". If ColumnB were null, then the result would be "5".
> Making sense?
> Is there a way to do this, without having to create a bazillion Case When
> statements? (case when columnA > ColumnB and ColumnB < ColumnC then
> ColumnB
> else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else .
> .
> .. and so on).
> Just wondering.
> Thank you!
> Kyra
> --
> Financial Systems Analyst
> CCNA, MCSE, MCSA, MCDBA
|||Ysandre,
Here is an alternative to Ron's solution. In general, you
would have an easier time if all the values were in one
column..
SELECT
T1ID,
CASE cntC
WHEN 1 THEN maxC
WHEN 2 THEN minC
WHEN 3 THEN sumC - maxC - minC
END AS C
FROM (
SELECT
T1ID,
SUM(C) as sumC,
MIN(C) as minC,
MAX(C) as maxC,
COUNT(C) as cntC
FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T
GROUP BY T1ID
) T
or
SELECT T1ID, MIN(C) FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T1
WHERE C IN (
SELECT TOP 2 C FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
If your table were in the form I used for the derived table above (one
value column instead of three), you could write
SELECT T1ID, MIN(C) FROM T1
WHERE C IN (
SELECT TOP 2 C FROM T1 AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
Steve Kass
Drew University
Ysandre wrote:

>Help, please!
>I have 3 columns with numeric values. i want to write a query that returns
>the following:
>If all 3 columns are populated, then the middle value (not the average);
>If one colunn has a null, then the lesser value,
>If 2 are null, then the remaining value.
>For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
>the result to be "10". If ColumnB were null, then the result would be "5".
>Making sense?
>Is there a way to do this, without having to create a bazillion Case When
>statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
>else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
>.. and so on).
>Just wondering.
>Thank you!
>Kyra
>
>
|||Thank you Ron and Steve, that was very helpful!!!!
Thanks,
ysandre
"Steve Kass" wrote:

> Ysandre,
> Here is an alternative to Ron's solution. In general, you
> would have an easier time if all the values were in one
> column..
> SELECT
> T1ID,
> CASE cntC
> WHEN 1 THEN maxC
> WHEN 2 THEN minC
> WHEN 3 THEN sumC - maxC - minC
> END AS C
> FROM (
> SELECT
> T1ID,
> SUM(C) as sumC,
> MIN(C) as minC,
> MAX(C) as maxC,
> COUNT(C) as cntC
> FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T
> GROUP BY T1ID
> ) T
> or
> SELECT T1ID, MIN(C) FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T1
> WHERE C IN (
> SELECT TOP 2 C FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> If your table were in the form I used for the derived table above (one
> value column instead of three), you could write
> SELECT T1ID, MIN(C) FROM T1
> WHERE C IN (
> SELECT TOP 2 C FROM T1 AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> Steve Kass
> Drew University
>
> Ysandre wrote:
>

Query help

CREATE TABLE [dbo].[stuff] (
[c1] [char] (10)
)
insert into stuff values ('a')
insert into stuff values ('b')
Is there an easy way to get this output ?
a 1
a 2
a 3
b 1
b 2
b 3
Thank you in advance for your helpYep:
select
s.c1
, x.a
from
stuff
cross join
(
select 1 union all
select 2 union all
select 3
) as x (a)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Jack" <jack@.jack.net> wrote in message
news:9uaje.13324$4d6.11003@.trndny04...
CREATE TABLE [dbo].[stuff] (
[c1] [char] (10)
)
insert into stuff values ('a')
insert into stuff values ('b')
Is there an easy way to get this output ?
a 1
a 2
a 3
b 1
b 2
b 3
Thank you in advance for your help|||> select
> s.c1
> , x.a
> from
> stuff
> cross join
> (
> select 1 union all
> select 2 union all
> select 3
> ) as x (a)
Wow, that's really badass. I as the VB.NET guy would have wasted precious
seconds slurping it into a DataTable object and then doing a whole bunch of
monkey business.
RESPECT!
This is the stuff that articles are made of!!
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare|||Well,
I did write one or two that involved a cross join. Here's one that
un-pivots a table:
create table Budgets
(
Contract int not null
, Nominal int not null
, Budget_01 int null
, Budget_02 int null
, Budget_03 int null
, primary key (Contract, Nominal)
)
go
insert Budgets values (1, 123, 1000, 1000, 2000)
insert Budgets values (1, 234, 500, 500, 1000)
insert Budgets values (2, 456, 3000, 4500, 3000)
insert Budgets values (2, 567, 800, 800, 800)
insert Budgets values (3, 789, 500, 500, 500)
insert Budgets values (3, 987, 5000, 500, NULL)
go
select
*
from
(
select
b.Contract
, b.Nominal
, x.Period
, case x.Period
when 1 then b.Budget_01
when 2 then b.Budget_02
when 3 then b.Budget_03
end as Budget
from
Budgets as b
cross join
(
select 1 as Period
union all
select 2
union all
select 3
) as x
) as y
where
Budget is not null
order by
Contract
, Nominal
, Period
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23l4YUHOXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> select
> s.c1
> , x.a
> from
> stuff
> cross join
> (
> select 1 union all
> select 2 union all
> select 3
> ) as x (a)
Wow, that's really badass. I as the VB.NET guy would have wasted precious
seconds slurping it into a DataTable object and then doing a whole bunch of
monkey business.
RESPECT!
This is the stuff that articles are made of!!
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare

Query Help

Help, please!
I have 3 columns with numeric values. i want to write a query that returns
the following:
If all 3 columns are populated, then the middle value (not the average);
If one colunn has a null, then the lesser value,
If 2 are null, then the remaining value.
For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
the result to be "10". If ColumnB were null, then the result would be "5".
Making sense?
Is there a way to do this, without having to create a bazillion Case When
statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
.. and so on).
Just wondering.
Thank you!
Kyra
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBAKyra,
I think the bazillion CASEs is the only way to go. Here's one attempt:
CREATE TABLE T1 (T1ID INT NOT NULL IDENTITY, Col1 int, Col2 int, Col3 int)
GO
INSERT T1 VALUES (NULL, NUll, 1)
INSERT T1 VALUES (NULL, 2, NULL)
INSERT T1 VALUES (3, NUll, NULL)
INSERT T1 VALUES (NULL, 4, 5)
INSERT T1 VALUES (6, NULL, 7)
INSERT T1 VALUES (8,9, NULL)
INSERT T1 VALUES (10,11,12)
GO
SELECT
CASE
WHEN Col1 IS NULL AND Col2 IS NULL THEN Col3
WHEN Col2 IS NULL AND Col2 IS NULL THEN Col1
WHEN Col1 IS NULL AND Col3 IS NULL THEN Col2
WHEN Col1 IS NULL AND Col2 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col2 < Col3 THEN Col2 Else Col3 END
WHEN Col2 IS NULL AND Col1 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col1 < Col3 THEN Col1 Else Col3 END
WHEN Col3 IS NULL AND Col1 IS NOT NULL AND Col2 IS NOT NULL THEN
CASE WHEN Col1 < Col2 THEN Col1 Else Col2 END
WHEN Col1 <= Col2 AND Col2 <= Col3 THEN Col2
WHEN Col2 <= Col3 AND Col3 <= Col1 THEN Col3
WHEN Col3 <= Col1 AND Col1 <= Col2 THEN Col1
END
FROM T1
One thing your conditions left out: what if they're all NULL?
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:3147EC21-B5A3-4725-B156-6A40834E5561@.microsoft.com...
> Help, please!
> I have 3 columns with numeric values. i want to write a query that returns
> the following:
> If all 3 columns are populated, then the middle value (not the average);
> If one colunn has a null, then the lesser value,
> If 2 are null, then the remaining value.
> For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> the result to be "10". If ColumnB were null, then the result would be "5".
> Making sense?
> Is there a way to do this, without having to create a bazillion Case When
> statements? (case when columnA > ColumnB and ColumnB < ColumnC then
> ColumnB
> else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else .
> .
> .. and so on).
> Just wondering.
> Thank you!
> Kyra
> --
> Financial Systems Analyst
> CCNA, MCSE, MCSA, MCDBA|||Ysandre,
Here is an alternative to Ron's solution. In general, you
would have an easier time if all the values were in one
column..
SELECT
T1ID,
CASE cntC
WHEN 1 THEN maxC
WHEN 2 THEN minC
WHEN 3 THEN sumC - maxC - minC
END AS C
FROM (
SELECT
T1ID,
SUM(C) as sumC,
MIN(C) as minC,
MAX(C) as maxC,
COUNT(C) as cntC
FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T
GROUP BY T1ID
) T
or
SELECT T1ID, MIN(C) FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T1
WHERE C IN (
SELECT TOP 2 C FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
If your table were in the form I used for the derived table above (one
value column instead of three), you could write
SELECT T1ID, MIN(C) FROM T1
WHERE C IN (
SELECT TOP 2 C FROM T1 AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
Steve Kass
Drew University
Ysandre wrote:

>Help, please!
>I have 3 columns with numeric values. i want to write a query that returns
>the following:
>If all 3 columns are populated, then the middle value (not the average);
>If one colunn has a null, then the lesser value,
>If 2 are null, then the remaining value.
>For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
>the result to be "10". If ColumnB were null, then the result would be "5".
>Making sense?
>Is there a way to do this, without having to create a bazillion Case When
>statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
>else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
>.. and so on).
>Just wondering.
>Thank you!
>Kyra
>
>|||Thank you Ron and Steve, that was very helpful!!!!
Thanks,
ysandre
"Steve Kass" wrote:

> Ysandre,
> Here is an alternative to Ron's solution. In general, you
> would have an easier time if all the values were in one
> column..
> SELECT
> T1ID,
> CASE cntC
> WHEN 1 THEN maxC
> WHEN 2 THEN minC
> WHEN 3 THEN sumC - maxC - minC
> END AS C
> FROM (
> SELECT
> T1ID,
> SUM(C) as sumC,
> MIN(C) as minC,
> MAX(C) as maxC,
> COUNT(C) as cntC
> FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T
> GROUP BY T1ID
> ) T
> or
> SELECT T1ID, MIN(C) FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T1
> WHERE C IN (
> SELECT TOP 2 C FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> If your table were in the form I used for the derived table above (one
> value column instead of three), you could write
> SELECT T1ID, MIN(C) FROM T1
> WHERE C IN (
> SELECT TOP 2 C FROM T1 AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> Steve Kass
> Drew University
>
> Ysandre wrote:
>
>sql

Query Help

How can I select the count of the distinct values of a
column '
Thanks.Try:
select
count (distinct MyCol)
from
MyTable
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
How can I select the count of the distinct values of a
column '
Thanks.|||SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
FROM YourTable
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.|||Thanks but I forgot to add that it is a char column with
the numbers in it '

>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>
>.
>|||It makes no difference. You wanted a count of the distinct values. From
Northwind, try:
select
count (distinct CustomerID)
from
Orders
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:503401c4c5c7$6ed7d9a0$a301280a@.phx.gbl...
Thanks but I forgot to add that it is a char column with
the numbers in it '

>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>
>.
>|||Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>|||Thanx much. It sure means a lot. I saw Itzik and his wife in April.
They're both doing well. :-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OBcRNPtyEHA.3820@.TK2MSFTNGP11.phx.gbl...
Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>

Query Help

Hi i need some help in a Query,
i have a table
Table1 ( field1) with values 1,2,3,4 in the field 1.
when i run,
select field1 from table1
the query returns
1
2
3
4
but instead i need to get these values comma seperated
like 1,2,3,4. i dont want to use cursors for this.
can somebody help me in this.
thanks in advance.
sathyaHi
The only safe way is to use a cursor, alternatively the better method it to
do it on the client or a third party tool such as http://rac4sql.net/
John
"sathya" <softwaremaniac@.hotmail.com> wrote in message
news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> Hi i need some help in a Query,
> i have a table
> Table1 ( field1) with values 1,2,3,4 in the field 1.
> when i run,
> select field1 from table1
> the query returns
> 1
> 2
> 3
> 4
> but instead i need to get these values comma seperated
> like 1,2,3,4. i dont want to use cursors for this.
> can somebody help me in this.
> thanks in advance.
> sathya|||Isn't this something cool:
declare @.List varchar(1000)
select @.List = coalesce(@.List + ',', '') + ltrim(rtrim(col1)) from table1
select @.List
hth
Quentin
"sathya" <softwaremaniac@.hotmail.com> wrote in message
news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> Hi i need some help in a Query,
> i have a table
> Table1 ( field1) with values 1,2,3,4 in the field 1.
> when i run,
> select field1 from table1
> the query returns
> 1
> 2
> 3
> 4
> but instead i need to get these values comma seperated
> like 1,2,3,4. i dont want to use cursors for this.
> can somebody help me in this.
> thanks in advance.
> sathya|||Hi
This solution has been suggested by many people in the past (including
myself) but it is not a safe solution.
John
"Quentin Ran" <ab@.who.com> wrote in message
news:%23AGtH7CjDHA.360@.TK2MSFTNGP10.phx.gbl...
> Isn't this something cool:
> declare @.List varchar(1000)
> select @.List = coalesce(@.List + ',', '') + ltrim(rtrim(col1)) from table1
> select @.List
> hth
> Quentin
>
> "sathya" <softwaremaniac@.hotmail.com> wrote in message
> news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> > Hi i need some help in a Query,
> >
> > i have a table
> >
> > Table1 ( field1) with values 1,2,3,4 in the field 1.
> >
> > when i run,
> >
> > select field1 from table1
> > the query returns
> > 1
> > 2
> > 3
> > 4
> >
> > but instead i need to get these values comma seperated
> > like 1,2,3,4. i dont want to use cursors for this.
> >
> > can somebody help me in this.
> > thanks in advance.
> >
> > sathya
>|||hi john,
What is the problem you are expecting
for me is in a loop, if i use it will slow down the
process.
it works fine for me
thanks
>--Original Message--
>Hi
>This solution has been suggested by many people in the
past (including
>myself) but it is not a safe solution.
>John
>"Quentin Ran" <ab@.who.com> wrote in message
>news:%23AGtH7CjDHA.360@.TK2MSFTNGP10.phx.gbl...
>> Isn't this something cool:
>> declare @.List varchar(1000)
>> select @.List = coalesce(@.List + ',', '') + ltrim(rtrim
(col1)) from table1
>> select @.List
>> hth
>> Quentin
>>
>> "sathya" <softwaremaniac@.hotmail.com> wrote in message
>> news:060901c38bfc$65979c00$a001280a@.phx.gbl...
>> > Hi i need some help in a Query,
>> >
>> > i have a table
>> >
>> > Table1 ( field1) with values 1,2,3,4 in the field 1.
>> >
>> > when i run,
>> >
>> > select field1 from table1
>> > the query returns
>> > 1
>> > 2
>> > 3
>> > 4
>> >
>> > but instead i need to get these values comma
seperated
>> > like 1,2,3,4. i dont want to use cursors for this.
>> >
>> > can somebody help me in this.
>> > thanks in advance.
>> >
>> > sathya
>>
>
>.
>

Query help

I have a table with one column that has all negative values.
IO
-70.72
-72.93
-67.38
-65.05
-62.31
-65.52
-69.98
now we have to find that how many samples of IO are between
-50 to -40 range
-60 to -70 range
-70 to -60 range
How can I accomplish this. Thanks for your help.This should give you something to think about.
SELECT CONVERT(int, ThatColumn / 10) * 10 as RangeStart,
count(*) as Rows
FROM TheTable
GROUP BY CONVERT(int, ThatColumn / 10)
ORDER BY RangeStart
Roy Harvey
Beacon Falls, CT
On Fri, 7 Sep 2007 10:00:08 -0700, Shan
<Shan@.discussions.microsoft.com> wrote:
>I have a table with one column that has all negative values.
>IO
>-70.72
>-72.93
>-67.38
>-65.05
>-62.31
>-65.52
>-69.98
>now we have to find that how many samples of IO are between
>-50 to -40 range
>-60 to -70 range
>-70 to -60 range
>How can I accomplish this. Thanks for your help.|||This doesn't work. Any other ideas.
"Shan" wrote:
> I have a table with one column that has all negative values.
> IO
> -70.72
> -72.93
> -67.38
> -65.05
> -62.31
> -65.52
> -69.98
> now we have to find that how many samples of IO are between
> -50 to -40 range
> -60 to -70 range
> -70 to -60 range
> How can I accomplish this. Thanks for your help.|||On Fri, 7 Sep 2007 15:02:02 -0700, Shan
<Shan@.discussions.microsoft.com> wrote:
>This doesn't work. Any other ideas.
CREATE TABLE TestRanges (d decimal (13,2) NOT NULL)
INSERT TestRanges VALUES (-70.72)
INSERT TestRanges VALUES (-72.93)
INSERT TestRanges VALUES (-67.38)
INSERT TestRanges VALUES (-65.05)
INSERT TestRanges VALUES (-62.31)
INSERT TestRanges VALUES (-65.52)
INSERT TestRanges VALUES (-69.98)
SELECT CONVERT(int, d / 10) * 10 as RangeStart,
count(*) as Rows
FROM TestRanges
GROUP BY CONVERT(int, d / 10)
ORDER BY RangeStart
RangeStart Rows
-- --
-70 2
-60 5
Is this not close?
Roy Harvey
Beacon Falls, CT

Query Help

How can I select the count of the distinct values of a
column '
Thanks.Try:
select
count (distinct MyCol)
from
MyTable
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
How can I select the count of the distinct values of a
column '
Thanks.|||SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
FROM YourTable
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.|||Thanks but I forgot to add that it is a char column with
the numbers in it '
>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>> How can I select the count of the distinct values of a
>> column '
>> Thanks.
>
>.
>|||Please ignore the message about the column being a char
column........
Thanks.
>--Original Message--
>How can I select the count of the distinct values of a
>column '
>Thanks.
>.
>|||It makes no difference. You wanted a count of the distinct values. From
Northwind, try:
select
count (distinct CustomerID)
from
Orders
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:503401c4c5c7$6ed7d9a0$a301280a@.phx.gbl...
Thanks but I forgot to add that it is a char column with
the numbers in it '
>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>> How can I select the count of the distinct values of a
>> column '
>> Thanks.
>
>.
>|||Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>|||Thanx much. It sure means a lot. I saw Itzik and his wife in April.
They're both doing well. :-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OBcRNPtyEHA.3820@.TK2MSFTNGP11.phx.gbl...
Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>

Query Help

Help, please!
I have 3 columns with numeric values. i want to write a query that returns
the following:
If all 3 columns are populated, then the middle value (not the average);
If one colunn has a null, then the lesser value,
If 2 are null, then the remaining value.
For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
the result to be "10". If ColumnB were null, then the result would be "5".
Making sense?
Is there a way to do this, without having to create a bazillion Case When
statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
.. and so on).
Just wondering.
Thank you!
Kyra
--
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBAKyra,
I think the bazillion CASEs is the only way to go. Here's one attempt:
CREATE TABLE T1 (T1ID INT NOT NULL IDENTITY, Col1 int, Col2 int, Col3 int)
GO
INSERT T1 VALUES (NULL, NUll, 1)
INSERT T1 VALUES (NULL, 2, NULL)
INSERT T1 VALUES (3, NUll, NULL)
INSERT T1 VALUES (NULL, 4, 5)
INSERT T1 VALUES (6, NULL, 7)
INSERT T1 VALUES (8,9, NULL)
INSERT T1 VALUES (10,11,12)
GO
SELECT
CASE
WHEN Col1 IS NULL AND Col2 IS NULL THEN Col3
WHEN Col2 IS NULL AND Col2 IS NULL THEN Col1
WHEN Col1 IS NULL AND Col3 IS NULL THEN Col2
WHEN Col1 IS NULL AND Col2 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col2 < Col3 THEN Col2 Else Col3 END
WHEN Col2 IS NULL AND Col1 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col1 < Col3 THEN Col1 Else Col3 END
WHEN Col3 IS NULL AND Col1 IS NOT NULL AND Col2 IS NOT NULL THEN
CASE WHEN Col1 < Col2 THEN Col1 Else Col2 END
WHEN Col1 <= Col2 AND Col2 <= Col3 THEN Col2
WHEN Col2 <= Col3 AND Col3 <= Col1 THEN Col3
WHEN Col3 <= Col1 AND Col1 <= Col2 THEN Col1
END
FROM T1
One thing your conditions left out: what if they're all NULL?
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:3147EC21-B5A3-4725-B156-6A40834E5561@.microsoft.com...
> Help, please!
> I have 3 columns with numeric values. i want to write a query that returns
> the following:
> If all 3 columns are populated, then the middle value (not the average);
> If one colunn has a null, then the lesser value,
> If 2 are null, then the remaining value.
> For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> the result to be "10". If ColumnB were null, then the result would be "5".
> Making sense?
> Is there a way to do this, without having to create a bazillion Case When
> statements? (case when columnA > ColumnB and ColumnB < ColumnC then
> ColumnB
> else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else .
> .
> .. and so on).
> Just wondering.
> Thank you!
> Kyra
> --
> Financial Systems Analyst
> CCNA, MCSE, MCSA, MCDBA|||Ysandre,
Here is an alternative to Ron's solution. In general, you
would have an easier time if all the values were in one
column..
SELECT
T1ID,
CASE cntC
WHEN 1 THEN maxC
WHEN 2 THEN minC
WHEN 3 THEN sumC - maxC - minC
END AS C
FROM (
SELECT
T1ID,
SUM(C) as sumC,
MIN(C) as minC,
MAX(C) as maxC,
COUNT(C) as cntC
FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T
GROUP BY T1ID
) T
or
SELECT T1ID, MIN(C) FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T1
WHERE C IN (
SELECT TOP 2 C FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
If your table were in the form I used for the derived table above (one
value column instead of three), you could write
SELECT T1ID, MIN(C) FROM T1
WHERE C IN (
SELECT TOP 2 C FROM T1 AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
Steve Kass
Drew University
Ysandre wrote:
>Help, please!
>I have 3 columns with numeric values. i want to write a query that returns
>the following:
>If all 3 columns are populated, then the middle value (not the average);
>If one colunn has a null, then the lesser value,
>If 2 are null, then the remaining value.
>For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
>the result to be "10". If ColumnB were null, then the result would be "5".
>Making sense?
>Is there a way to do this, without having to create a bazillion Case When
>statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
>else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
>.. and so on).
>Just wondering.
>Thank you!
>Kyra
>
>|||Thank you Ron and Steve, that was very helpful!!!!
Thanks,
ysandre
"Steve Kass" wrote:
> Ysandre,
> Here is an alternative to Ron's solution. In general, you
> would have an easier time if all the values were in one
> column..
> SELECT
> T1ID,
> CASE cntC
> WHEN 1 THEN maxC
> WHEN 2 THEN minC
> WHEN 3 THEN sumC - maxC - minC
> END AS C
> FROM (
> SELECT
> T1ID,
> SUM(C) as sumC,
> MIN(C) as minC,
> MAX(C) as maxC,
> COUNT(C) as cntC
> FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T
> GROUP BY T1ID
> ) T
> or
> SELECT T1ID, MIN(C) FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T1
> WHERE C IN (
> SELECT TOP 2 C FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> If your table were in the form I used for the derived table above (one
> value column instead of three), you could write
> SELECT T1ID, MIN(C) FROM T1
> WHERE C IN (
> SELECT TOP 2 C FROM T1 AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> Steve Kass
> Drew University
>
> Ysandre wrote:
> >Help, please!
> >
> >I have 3 columns with numeric values. i want to write a query that returns
> >the following:
> >If all 3 columns are populated, then the middle value (not the average);
> >If one colunn has a null, then the lesser value,
> >If 2 are null, then the remaining value.
> >
> >For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> >the result to be "10". If ColumnB were null, then the result would be "5".
> >Making sense?
> >
> >Is there a way to do this, without having to create a bazillion Case When
> >statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
> >else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
> >.. and so on).
> >
> >Just wondering.
> >Thank you!
> >Kyra
> >
> >
> >
>

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3
On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
> sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration=
'86400'
sd.Duration= '86400'

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value = sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value => sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration='86400'
sd.Duration= '86400'

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
> sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration=
'86400'
sd.Duration= '86400'

Query gurus please?

Hi,

I'm trying to put together a query which will combine a number of values from several database tables. Currently, there are two tables in question, one of products, and one of order details.

tblProducts:
ProductID
Product_Category
Product_Weight

tblDetails:
DetailID
Order_ID (linked to an order profile table)
Product_ID (linked to tblProducts.ProductID)
Product_Category
Detail_Quantity

What I need to achieve is a query (or several queries) that will retrieve the total weight of a detail record (ie tblProducts.ProductWeight * tblDetails.Detail_Category) and then group this by Product_Category. What you end up with is the total weight of each category of product - this will be used to calculate freight costs.

I can construct a query to retrieve total quantities by category, and total weight per detail row, but how can I combine these?

Retrieve total weight per detail row:

SELECT tblProducts.ProductID, tblProducts.Product_Weight, tblDetails.Detail_Quantity, (tblDetails.Detail_Quantity*tblProducts.Product_We ight) AS FreightWeight, tblDetails.DetailID, tblDetails.Order_ID
FROM tblProducts INNER JOIN tblDetails ON tblProducts.ProductID = tblDetails.Product_ID
WHERE (((tblDetails.Order_ID)= *xxx* ));

Retrieve total number of products ordered by category

SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity) AS TotalQuantity
FROM tblDetails
GROUP BY tblDetails.Product_Category;

Any suggestions would be great!

BenHow about:

SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity*tblProducts.Product _Weight) AS TotalWeight
FROM tblDetails
INNER JOIN tblDetails ON tblProducts.ProductID = tblDetails.Product_ID
GROUP BY tblDetails.Product_Category;|||Thanks Andrew,

When I try to run this query, I get a Syntax error in the JOIN statement. I've tried recreating this (unfortunately in Access) and I get an error which I can't understand:

'You tried to execute a query that does not include the specified expression 'Product_ID' as part of an aggregate function'

This error is repeated for any fields included, other than those in the SUM function. Is this just due to wiggy JetSQL? Your query structure makes sense to me, but I can't make it work.

Any other ideas?

Thanks again,

Ben|||No, that doesn't make sense to me. I presume you fixed the error in my code?

SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity*tblProducts.Product _Weight) AS TotalWeight
FROM tblDetails
INNER JOIN tblProducts ON tblProducts.ProductID = tblDetails.Product_ID
GROUP BY tblDetails.Product_Category;|||Andrew - you're a legend!

I thought I had fixed that error, but I succeeded only in compounding the problem. This query of yours works a treat. Thanks very much.

All the best,

Ben

Query From ACCCESS to MSDE

First of all...sorry for writing in english.
I'm trying to pass from access to msde.
I have a query that returns some values to populate a listview.
With access this query works in the right way, but in msde i have some
problems .
That's the query
rs.Open "select * from anagp inner join ingrp on anagp.incr = ingrp.rif
where ingrp.lotto='" & ListView2.SelectedItem.SubItems(1) & "' order by
anagp.data desc", cn, adOpenStatic, adLockOptimistic
I've checked out in the immediate window that the query works well but when
I populate the LW I have this problem
li.SubItems(2) = "" & rs("anagp.nome").Value
where li has the dimention of a listitem...
Error: cannot find anag.nome
Why'
Any suggestion will be very apprecitated
Thanx
IAKwhy dont to try adps
"PiantoTardivo" wrote:

> First of all...sorry for writing in english.
> I'm trying to pass from access to msde.
> I have a query that returns some values to populate a listview.
> With access this query works in the right way, but in msde i have some
> problems .
> That's the query
> rs.Open "select * from anagp inner join ingrp on anagp.incr = ingrp.rif
> where ingrp.lotto='" & ListView2.SelectedItem.SubItems(1) & "' order by
> anagp.data desc", cn, adOpenStatic, adLockOptimistic
> I've checked out in the immediate window that the query works well but whe
n
> I populate the LW I have this problem
> li.SubItems(2) = "" & rs("anagp.nome").Value
> where li has the dimention of a listitem...
> Error: cannot find anag.nome
> Why'
> Any suggestion will be very apprecitated
> Thanx
> IAK
>
>|||Looks like zour problem is the name of the column.
1. make sure that the column names are correct, you wrote once anagp.nome
and once anag.nome
2. instead of doing select * ... and then acessing anag(p).nome consider
someting like this:
select anagp.nome as a_nome, ...
and then you can use
rs("a_nome")
to access the column
HTH, Fred
"PiantoTardivo" wrote:

> First of all...sorry for writing in english.
> I'm trying to pass from access to msde.
> I have a query that returns some values to populate a listview.
> With access this query works in the right way, but in msde i have some
> problems .
> That's the query
> rs.Open "select * from anagp inner join ingrp on anagp.incr = ingrp.rif
> where ingrp.lotto='" & ListView2.SelectedItem.SubItems(1) & "' order by
> anagp.data desc", cn, adOpenStatic, adLockOptimistic
> I've checked out in the immediate window that the query works well but whe
n
> I populate the LW I have this problem
> li.SubItems(2) = "" & rs("anagp.nome").Value
> where li has the dimention of a listitem...
> Error: cannot find anag.nome
> Why'
> Any suggestion will be very apprecitated
> Thanx
> IAK
>
>

Query for multiple values

I was able to create a query that returns data based on a single order
number. Now I need to create a query that returns data based on
multiple order numbers. Obviously, when I run this query, it prompts me
for an order number. How do I change this so I can enter multiple order
numbers?
SELECT ORDNUMBE, CUSTNAME
FROM SOP10100
WHERE (ORDNUMBE = @.ordnumbe)
Thanks!http://www.sommarskog.se/arrays-in-sql.html
<2retread@.gmail.com> wrote in message
news:1126021459.621452.89010@.z14g2000cwz.googlegroups.com...
>I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||See if this helps:
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"2retread@.gmail.com" wrote:

> I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
In SQL Server, the VALUES() table constructor has to be faked with
(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).|||On 6 Sep 2005 20:19:28 -0700, --CELKO-- wrote:

>CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
>INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
>BEGIN
>SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL
> AND <other conditions> )
> AND <more predicates>;
><more code>;
>END;
>In SQL Server, the VALUES() table constructor has to be faked with
>(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).
Hi Joe,
And how exactly is your query better than
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (p1, p2, .., pN)
AND <more predicates>;
?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I usually wind up adding some CAST(), UPPER() and other procedures to
the SELECTs or in theVALUES() list.|||On 7 Sep 2005 12:49:10 -0700, --CELKO-- wrote:

>I usually wind up adding some CAST(), UPPER() and other procedures to
>the SELECTs or in theVALUES() list.
Hi Joe,
Assuming that you meant "functions", not "procedures", there's still no
reason to make it so complicated:
WHERE my_col IN (UPPER(p1), CAST(p2 AS verchar(10), ..., LOWER(pN))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Assuming that you meant "functions", not "procedures", there's still no r
eason to make it so complicated: <<
Opps! I grant that you can do a lot of edit work with CASE epxrssions,
too:
CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
But it is hard to do things that involve another table, such as
translating codes in the parameters to the target table format::
(SELECT shoesize_eur
FROM ShoeSizes.
WHERE UPPER (shoesize_usa) = @.p1)
or multiple-parameter look-ups:
(SELECT address_nbr
FROM StandardAddresseNumbers AS A
WHERE CAST (x_ord AS INTEGER) = @.p1
AND CAST (y_ord AS INTEGER) = @.p2)|||On 8 Sep 2005 07:57:01 -0700, --CELKO-- wrote:

>Opps! I grant that you can do a lot of edit work with CASE epxrssions,
>too:
>CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
>But it is hard to do things that involve another table, such as
>translating codes in the parameters to the target table format::
>(SELECT shoesize_eur
> FROM ShoeSizes.
>WHERE UPPER (shoesize_usa) = @.p1)
>or multiple-parameter look-ups:
>(SELECT address_nbr
> FROM StandardAddresseNumbers AS A
> WHERE CAST (x_ord AS INTEGER) = @.p1
> AND CAST (y_ord AS INTEGER) = @.p2)
Hi Joe,
Assuming that we're still discussing what can and can't be placed in an
IN (expression, expression, ...) string, I still fail to see how CASE
expressions and subqueries like the examples you posted would introduce
the need to move from IN with a list of expressions to IN with a
subquery, like the one you posted at the start of this thread.
I don't have the full SQL-92 standard at my disposal, but Books Online
says this:
Syntax
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
Arguments
(snip)
expression [,...n]
Is a list of expressions to test for a match. All expressions must be of
the same type as test_expression.
There are no further requirements for the expressions in the list of
expressions. So you should bne able to just slam the subqueries in
there.
Of course, the end result would be quite complicated - but adding an
extra layer of complexity by using a subquery with SELECT .. FROM VALUES
(...) would make it even more complicated, not less so!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query for multiple tables

Hi,

I have a query that returns values from three seperate data tables into a dataset:

SELECT [MediaFileData].[FileIdentifier], [AudioPCData].[RecorderID],
[AudioPCData].[Channel], [MediaFileData].[SubmittingUser], [MediaFileData].[DateTimeAsString],
[MetaData].[FieldText]

FROM [MediaFileData] INNER JOIN [AudioPCData] ON MediaFileData.FileIdentifier = AudioPCData.FileIdentifier
INNER JOIN [MetaData] ON MediaFileData.FileIdentifier = MetaData.FileIdentifier
INNER JOIN [SSRData] ON MediaFileData.FileIdentifier = SSRData.FileIdentifier

WHERE ([MediaFileData].[SubmittingUser] = '{0}') AND ([AudioPCData].[RecorderID] = '{0}')
AND ([MediaFileData].[MediaDescription] = '{0}') AND ([SSRData].[ModelUsed] = '{0}')

Each table has only got one 'FileIdentifier' apart from MetaData. This table has three columns 'FileIdentifer', 'FiledName' and 'FieldText'. One file can have more than one field and therefore It will have the same 'FileIdentifier' e.g.

FileIdentifier FieldName FieldText

1 Field 1 Hello

1 Field 2 Goodbye

The first problem is I only want to display the first and second field 'FiledText' in my results grid but still load all the other fields into my dataset.

The second problem is that it creates a new row for every field, whereas I want the fields with the same 'FileIdentifier' to be in the same row!

At the moment mt results gridlooks like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello Hello

1 MyPC 1 Me 03/05/07 14:24 GoodBye Goodbye

I need it to look like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello GoodBye

Thanks,

Guy

Depends on which version of SQL Server you are using. Using SQL 2k5 you should take a look on Cross apply.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Guy,

Could you be more clear as to what your goal is, and what the purpose of these fields might be, their scope, the number of overlapping entries, and generally how you view this thing to be structured? On first blush, this seems like a situation where a relational database setup could save you some storage and access time in the long run if you can identify the shared fields and refactor your tables. The problem with setting Field1 and Field2 appropriately is also interesting in that you want Field1 to have "hello" in it and field2 to have "goodbye" in it whereas other than that string nothing differs between the records. This behaviour seems highly specific to your problem, and so knowing more about what your goals are for this database might help us to identify how best to solve your immediate problems in such a manner as to have long-term benefits.

Some natural things to weigh might be:

How do these components logically relate, and what are valid values for things in the database? (e.g. identical entries with field1 and field2 values, should this happen?, should field1 and field2 be mirrored?, etc.).

Does this solution need to last, or is it a one-time only operation?

How important is it to retain the database structure?

Is there a rule to how you want this join to work in the long run that requires domain specific knowledge?

How important is the performance of your database in the long run?

How often do you intend to perform this operation, and how much development work is "worth it"?

I think that as we understand the constraints of your task and what you intend to accomplish, we can find a solution that best fits your needs.

Thanks,

John (MSFT)

Query example

Hi,

I have to select two different column values from two different tables based on one condition.

Like, I have two tables - orders and fulfillment. I have to select orders.order_id and fulfillment.fulfillment_id for product_id = 2 and date_added = '08/18/06'. The only common fields between the two tables are customer_id,order_id,product_id,date_added.

I tried doing this but I know that won't work because it is returning all the rows from orders and fulfillment.

select o.order_id ,f.fulfillment_id

from orders o,fulfillment f

where o.product_id = 2

and o.date_added = '08/18/06'

and f.product_id = 2

and f.date_added = '08/18/2006'

Can somebody please suggest me how to select records from these two tables based on thte above condition?

Thanks,

Reva,

The details about the relationships of the data between the two tables are a little vague, but let me give it a try:

SELECT
o.order_id,
f.fulfillment_ID
FROM orders AS o
JOIN fulfillment AS f
ON o.order_id = f.order_id
WHERE o.product_id = 2
AND o.date_added = '08/18/06'

You will note that I don't have criteria in the WHERE clause of the f.date_added and f.product_id fields. I am assuming that the date_added and product_id values for a fulfillment are the same as the date_added and product_id values for the related order. If that is the case, you only need to filter the values in one of the tables.

The previous query uses the JOIN and ON clauses to state the join criteria. This is the standars compliant syntax that makes JOINS more accurate and less likely to cause unexpected results.

The problem with your query is that you used the older "legacy" style join syntax of FROM orders,fulfillment. When you do that you MUST state the join criteria (o.order_id = f.order_id) for the rows to match. Otherwise you get what is called a "CROSS JOIN" where every row of the first table is joined to every row of the second table. That is why you are seeing all the extra rows you didn't want. The syntax I used above prevents that from happening by accident because the ON clause is REQUIRED unless CROSS JOIN is explicitly stated.

I hope that helps.

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 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 based parameter value changes back to default value

I have several parameters on my report, some have non-queried lists, and
some have queried lists. All of them have default values. When I ran
reports on the Report Manager, I changed the value of the parameters from
the drop-down list to produce different reports. My problem is whenever I
changed the value of a parameter(pA), the value of those queried-based
parameters that were listed after pA would switch back to their respective
default values. This didn't happen when I previewed reports on Visual
Studio .Net Development Environment. Is this a bug, limitation, or am I
missing something?
Any help would be appreciated.
Paul
--
Message posted via http://www.sqlmonster.comPaul,
It sounds like your default values are calculated values (begin with a '=').
The way that RS computes these is that whenever you change a parameter, it
recalculates all the calculated parameters after it. This behaviour is to
allow cascading parameters. As far as I know, there is no way to turn this
calculation off. However, I worked around it by creating another dataset
which had a column for each default value (I do a lot of default dates). You
can then set the default values for the parameters to the dataset value in
the parameters window. I have to use SQL to do the calculations, but for
most things, this should be an easy workaround. It gets rid of the changing
values and also the refresh that takes place when you change calculated
parameters
"Paul via SQLMonster.com" wrote:
> I have several parameters on my report, some have non-queried lists, and
> some have queried lists. All of them have default values. When I ran
> reports on the Report Manager, I changed the value of the parameters from
> the drop-down list to produce different reports. My problem is whenever I
> changed the value of a parameter(pA), the value of those queried-based
> parameters that were listed after pA would switch back to their respective
> default values. This didn't happen when I previewed reports on Visual
> Studio .Net Development Environment. Is this a bug, limitation, or am I
> missing something?
> Any help would be appreciated.
> Paul
> --
> Message posted via http://www.sqlmonster.com
>|||Hi David, that works great. Thank you very much!!
Paul
--
Message posted via http://www.sqlmonster.com