Query Help

I have query with following column names:
cust_no, Firm_no, Name, homeaddress, homecity, homestate, homezip,
officeaddress, officecity, officestate, officezip
I want to be able to pull it this way that whenever there is a certain
unique Firm_no it should populate the homeaddress, homecity, homestate,
homezip for this customer.
What do I have to do to accomplish this?
ThanksU can achieve this by the following way:
Use the following expresson to display Homeaddress:
=IIF(IsNothing(Previous(Fields!Firm_no.Value)),
Fields!homeaddress.Value,
IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
"",
Fields!homeaddress.Value))
In the same way implement the same logic to display homecity, homestate,
homezip.
Regards,
SaraS|||saras,
I have a unique firm_no that I want to use when my report runs it should
look like:
Custo_no Firm_no Name Address City State Zip
123 1111 Jon Doe Main Dallas TX 75000
321 1122 Jon Doe Globe Pano TX 75001
So when anytime the Firm_no is '1122' I want the address to print their
homeaddress.
Thanks for your help.
"saras" wrote:
> U can achieve this by the following way:
> Use the following expresson to display Homeaddress:
> =IIF(IsNothing(Previous(Fields!Firm_no.Value)),
> Fields!homeaddress.Value,
> IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
> "",
> Fields!homeaddress.Value))
>
> In the same way implement the same logic to display homecity, homestate,
> homezip.
> Regards,
> SaraS
>|||Thanks Saras, It worked I just dint have it in the correct place. Stupid
typo mistake. Thanks a bunch have a great day!
"saras" wrote:
> U can achieve this by the following way:
> Use the following expresson to display Homeaddress:
> =IIF(IsNothing(Previous(Fields!Firm_no.Value)),
> Fields!homeaddress.Value,
> IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
> "",
> Fields!homeaddress.Value))
>
> In the same way implement the same logic to display homecity, homestate,
> homezip.
> Regards,
> SaraS
>|||Saras,
Sorry to bother again...but now its printing all the homeaddress I still
have this questions:
I have query with following column names:
cust_no, Firm_no, Name, homeaddress, homecity, homestate, homezip,
officeaddress, officecity, officestate, officezip
I want to be able to pull it this way that whenever there is a certain
unique Firm_no it should populate the homeaddress, homecity, homestate,
homezip for this customer.
What do I have to do to accomplish this?
Thanks
"Shan" wrote:
> I have query with following column names:
> cust_no, Firm_no, Name, homeaddress, homecity, homestate, homezip,
> officeaddress, officecity, officestate, officezip
> I want to be able to pull it this way that whenever there is a certain
> unique Firm_no it should populate the homeaddress, homecity, homestate,
> homezip for this customer.
> What do I have to do to accomplish this?
> Thanks|||Ok. I have these fields.
Cust_no, Firm_name, Firm_no, Name, OfficeAddr, OffCity, OffSt, OffZip,
HomeAddr, Homecity, HomeSt, HomeZip
In my report I only want to show based on my query the following format
Custo_no Firm_name Firm_no Name Address City State Zip
123 Keller 1111 Jon Doe Main Dallas TX 75000
321 Ebby 1122 Jon Doe Globe Pano TX 75009
102 Cold 1234 Jon Doe Holly Plano TX 75002
103 Nonmember 1000 Jon Doe Trench Allen TX 75001
109 Nonmember 1000 Jon Doe Trail Prosper TX 75003
Now I want to populate the Address field with customer's homeAddr anytime
the firm_no is 1000 which is a Non member otherwise if the firm_no is
anything else then populate the Address with their OfficeAddr. The Firm_no
1000 doesn't have an OfficeAddr so we want to populate it with customer's
homeaddr.
Thanks for your help.
"Shan" wrote:
> saras,
> I have a unique firm_no that I want to use when my report runs it should
> look like:
> Custo_no Firm_no Name Address City State Zip
> 123 1111 Jon Doe Main Dallas TX 75000
> 321 1122 Jon Doe Globe Pano TX 75001
> So when anytime the Firm_no is '1122' I want the address to print their
> homeaddress.
> Thanks for your help.
> "saras" wrote:
> > U can achieve this by the following way:
> >
> > Use the following expresson to display Homeaddress:
> >
> > =IIF(IsNothing(Previous(Fields!Firm_no.Value)),
> > Fields!homeaddress.Value,
> > IIF(Fields!Firm_no.Value = Previous(Fields!Firm_no.Value),
> > "",
> > Fields!homeaddress.Value))
> >
> >
> > In the same way implement the same logic to display homecity, homestate,
> > homezip.
> >
> > Regards,
> > SaraS
> >

query help

I have a name column that contains both first and last
names:
Col1
--
John Doe
I'd like to split it into two columns, a first and
lastname:
firstname lastname
-- --
John Doe
Anyone have any easy way to do this?Do you *always* have two words, separated by a space? I.e., what does your data look like?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
> I have a name column that contains both first and last
> names:
> Col1
> --
> John Doe
> I'd like to split it into two columns, a first and
> lastname:
> firstname lastname
> -- --
> John Doe
> Anyone have any easy way to do this?|||For the most part. There are some names that contain a
middle inital..
the data looks like this:
John Doe
Jane Doe
George W Bush
Bill Clinton
John Kerry
Jim Bob Smith
etc....
I'm not overly concerned with getting everything perfect.
To be honest, I'd be cool with just the first names.
>--Original Message--
>Do you *always* have two words, separated by a space?
I.e., what does your data look like?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:c07e01c47a31$4e2ec290$a601280a@.phx.gbl...
>> I have a name column that contains both first and last
>> names:
>> Col1
>> --
>> John Doe
>> I'd like to split it into two columns, a first and
>> lastname:
>> firstname lastname
>> -- --
>> John Doe
>> Anyone have any easy way to do this?
>
>.
>|||This should get you started:
CREATE TABLE Presidents (
FullName varchar(50)
)
GO
INSERT INTO Frog VALUES ('George W Bush')
INSERT INTO Frog VALUES ('Bill Clinton')
INSERT INTO Frog VALUES ('Ronald Reagan')
INSERT INTO Frog VALUES ('George H Bush')
INSERT INTO Frog VALUES ('Gerald Ford')
INSERT INTO Frog VALUES ('Richard Nixon')
GO
SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
CASE
WHEN PATINDEX('% _ %', FullName) > 0
THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1, 1)
ELSE ''
END AS 'MI',
RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last Name'
FROM Presidents
You can look up the various pieces used.
CHARINDEX
PATINDEX
SUBSTRING
REVERSE
CASE
Rick Sawtell
MCT, MCSD, MCDBA|||Ummm. Change the INSERT INTO commands to reflect the Presidents table...
Sorry bout that.
Rick
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> This should get you started:
> CREATE TABLE Presidents (
> FullName varchar(50)
> )
> GO
> INSERT INTO Frog VALUES ('George W Bush')
> INSERT INTO Frog VALUES ('Bill Clinton')
> INSERT INTO Frog VALUES ('Ronald Reagan')
> INSERT INTO Frog VALUES ('George H Bush')
> INSERT INTO Frog VALUES ('Gerald Ford')
> INSERT INTO Frog VALUES ('Richard Nixon')
> GO
>
> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1) AS 'First Name',
> CASE
> WHEN PATINDEX('% _ %', FullName) > 0
> THEN SUBSTRING(FullName, CHARINDEX(' ', FullName) +1,
1)
> ELSE ''
> END AS 'MI',
> RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS 'Last
Name'
> FROM Presidents
>
> You can look up the various pieces used.
> CHARINDEX
> PATINDEX
> SUBSTRING
> REVERSE
> CASE
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Cool, that did it. One other thing though... Could the
same be used for an address column? I used the same
syntax, but ran into an issue...
The column has a street address:
123 N. Main St.
I used the SQL and pulled the house number, directional,
and suffix, but lost the street name. Any help?
Thanks!
>--Original Message--
>Ummm. Change the INSERT INTO commands to reflect the
Presidents table...
>Sorry bout that.
>
>Rick
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
>> This should get you started:
>> CREATE TABLE Presidents (
>> FullName varchar(50)
>> )
>> GO
>> INSERT INTO Frog VALUES ('George W Bush')
>> INSERT INTO Frog VALUES ('Bill Clinton')
>> INSERT INTO Frog VALUES ('Ronald Reagan')
>> INSERT INTO Frog VALUES ('George H Bush')
>> INSERT INTO Frog VALUES ('Gerald Ford')
>> INSERT INTO Frog VALUES ('Richard Nixon')
>> GO
>>
>> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1)
AS 'First Name',
>> CASE
>> WHEN PATINDEX('% _ %', FullName) > 0
>> THEN SUBSTRING(FullName, CHARINDEX
(' ', FullName) +1,
>1)
>> ELSE ''
>> END AS 'MI',
>> RIGHT(FullName, CHARINDEX(' ', REVERSE
(FullName)) - 1) AS 'Last
>Name'
>> FROM Presidents
>>
>> You can look up the various pieces used.
>> CHARINDEX
>> PATINDEX
>> SUBSTRING
>> REVERSE
>> CASE
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>
>.
>|||Ummm..
Use the SUBSTRING function to get everything to the right of your
directional. Then apply the same CHARINDEX or PATINDEX functions to the
return value you are looking for from the return value of the SUBSTRING
function.
On a separate note... SQL really isn't the best choice to be doing
procedural language things like this.
If you dumped everything to a text file and used VBScript, you could
probably get this thing hashed out more quickly.
Rick
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:c45a01c47a48$9c9aef50$a301280a@.phx.gbl...
> Cool, that did it. One other thing though... Could the
> same be used for an address column? I used the same
> syntax, but ran into an issue...
> The column has a street address:
> 123 N. Main St.
> I used the SQL and pulled the house number, directional,
> and suffix, but lost the street name. Any help?
> Thanks!
> >--Original Message--
> >Ummm. Change the INSERT INTO commands to reflect the
> Presidents table...
> >
> >Sorry bout that.
> >
> >
> >Rick
> >
> >
> >
> >"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> >news:%23FfBMvjeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> >> This should get you started:
> >>
> >> CREATE TABLE Presidents (
> >> FullName varchar(50)
> >> )
> >>
> >> GO
> >>
> >> INSERT INTO Frog VALUES ('George W Bush')
> >> INSERT INTO Frog VALUES ('Bill Clinton')
> >> INSERT INTO Frog VALUES ('Ronald Reagan')
> >> INSERT INTO Frog VALUES ('George H Bush')
> >> INSERT INTO Frog VALUES ('Gerald Ford')
> >> INSERT INTO Frog VALUES ('Richard Nixon')
> >>
> >> GO
> >>
> >>
> >> SELECT LEFT(FullName, CHARINDEX(' ', FullName) -1)
> AS 'First Name',
> >> CASE
> >> WHEN PATINDEX('% _ %', FullName) > 0
> >> THEN SUBSTRING(FullName, CHARINDEX
> (' ', FullName) +1,
> >1)
> >> ELSE ''
> >> END AS 'MI',
> >> RIGHT(FullName, CHARINDEX(' ', REVERSE
> (FullName)) - 1) AS 'Last
> >Name'
> >> FROM Presidents
> >>
> >>
> >>
> >> You can look up the various pieces used.
> >> CHARINDEX
> >> PATINDEX
> >> SUBSTRING
> >> REVERSE
> >> CASE
> >>
> >> Rick Sawtell
> >> MCT, MCSD, MCDBA
> >>
> >>
> >
> >
> >.
> >

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'
>
>