query with joins

Can someone please re-write this query for me using better
joins? Thank you very much.
select distinct(c.research), t.FAX, t.NAME, CAST
(exp_AUDITDATE AS varchar(11))
AS AUDITDATE from teachers t (nolock), cases c (nolock),
RecordLog l (nolock)
where l.postedDate > 120703 and
l.postedDate < GETDATE() and l.seqno = c.sequence_no and
l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
Ida
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
Here it is. It has distinct in it so it's probably broken, but without DDL
and a description of what you want, it's impossible to tell how broken.
Also it converts a date to a string, which it probably shouldn't do.
select distinct
c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
from
cases c (nolock)
join teachers t (nolock)
on c.research = t.resNo
join RecordLog l (nolock)
on l.seqno = c.sequence_no
and l.PostedDate = c.PostedDate
where l.postedDate > 120703
and l.postedDate < GETDATE()
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
David
|||Here's a start:
select distinct c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
from teachers t (nolock)
join cases c (nolock) ON c.research = t.resNo.
join RecordLog l (nolock) ON l.seqno = c.sequence_no
where l.postedDate > '20031207'
and l.postedDate < GETDATE()
and l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
|||Thank you so much
Ida

>--Original Message--
>Here's a start:
>select distinct c.research,
> t.FAX,
> t.NAME,
> CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
>from teachers t (nolock)
>join cases c (nolock) ON c.research = t.resNo.
>join RecordLog l (nolock) ON l.seqno = c.sequence_no
>where l.postedDate > '20031207'
> and l.postedDate < GETDATE()
> and l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
> or exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE'
>
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and
>
>.
>
|||Thank you.

>--Original Message--
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and
>Here it is. It has distinct in it so it's probably
broken, but without DDL
>and a description of what you want, it's impossible to
tell how broken.
>Also it converts a date to a string, which it probably
shouldn't do.
>select distinct
>c.research,
>t.FAX,
>t.NAME,
>CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
>from
>cases c (nolock)
>join teachers t (nolock)
> on c.research = t.resNo
>join RecordLog l (nolock)
> on l.seqno = c.sequence_no
> and l.PostedDate = c.PostedDate
>where l.postedDate > 120703
>and l.postedDate < GETDATE()
>and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
>AND DELIVERY <> 'INACTIVE'
>David
>
>.
>

query with joins

Can someone please re-write this query for me using better
joins? Thank you very much.
select distinct(c.research), t.FAX, t.NAME, CAST
(exp_AUDITDATE AS varchar(11))
AS AUDITDATE from teachers t (nolock), cases c (nolock),
RecordLog l (nolock)
where l.postedDate > 120703 and
l.postedDate < GETDATE() and l.seqno = c.sequence_no and
l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
Ida"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
Here it is. It has distinct in it so it's probably broken, but without DDL
and a description of what you want, it's impossible to tell how broken.
Also it converts a date to a string, which it probably shouldn't do.
select distinct
c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
from
cases c (nolock)
join teachers t (nolock)
on c.research = t.resNo
join RecordLog l (nolock)
on l.seqno = c.sequence_no
and l.PostedDate = c.PostedDate
where l.postedDate > 120703
and l.postedDate < GETDATE()
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
David|||Here's a start:
select distinct c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
from teachers t (nolock)
join cases c (nolock) ON c.research = t.resNo.
join RecordLog l (nolock) ON l.seqno = c.sequence_no
where l.postedDate > '20031207'
and l.postedDate < GETDATE()
and l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida|||Thank you so much
Ida

>--Original Message--
>Here's a start:
>select distinct c.research,
> t.FAX,
> t.NAME,
> CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
>from teachers t (nolock)
>join cases c (nolock) ON c.research = t.resNo.
>join RecordLog l (nolock) ON l.seqno = c.sequence_no
>where l.postedDate > '20031207'
> and l.postedDate < GETDATE()
> and l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
> or exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE'
>
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
> news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>|||Thank you.

>--Original Message--
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
> news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and[vbcol=seagreen]
>Here it is. It has distinct in it so it's probably
broken, but without DDL
>and a description of what you want, it's impossible to
tell how broken.
>Also it converts a date to a string, which it probably
shouldn't do.
>select distinct
>c.research,
>t.FAX,
>t.NAME,
>CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
>from
>cases c (nolock)
>join teachers t (nolock)
> on c.research = t.resNo
>join RecordLog l (nolock)
> on l.seqno = c.sequence_no
> and l.PostedDate = c.PostedDate
>where l.postedDate > 120703
>and l.postedDate < GETDATE()
>and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
>AND DELIVERY <> 'INACTIVE'
>David
>
>.
>

query with joins

Can someone please re-write this query for me using better
joins? Thank you very much.
select distinct(c.research), t.FAX, t.NAME, CAST
(exp_AUDITDATE AS varchar(11))
AS AUDITDATE from teachers t (nolock), cases c (nolock),
RecordLog l (nolock)
where l.postedDate > 120703 and
l.postedDate < GETDATE() and l.seqno = c.sequence_no and
l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
Ida"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
Here it is. It has distinct in it so it's probably broken, but without DDL
and a description of what you want, it's impossible to tell how broken.
Also it converts a date to a string, which it probably shouldn't do.
select distinct
c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
from
cases c (nolock)
join teachers t (nolock)
on c.research = t.resNo
join RecordLog l (nolock)
on l.seqno = c.sequence_no
and l.PostedDate = c.PostedDate
where l.postedDate > 120703
and l.postedDate < GETDATE()
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
David|||Here's a start:
select distinct c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
from teachers t (nolock)
join cases c (nolock) ON c.research = t.resNo.
join RecordLog l (nolock) ON l.seqno = c.sequence_no
where l.postedDate > '20031207'
and l.postedDate < GETDATE()
and l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida|||Thank you so much
Ida
>--Original Message--
>Here's a start:
>select distinct c.research,
> t.FAX,
> t.NAME,
> CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
>from teachers t (nolock)
>join cases c (nolock) ON c.research = t.resNo.
>join RecordLog l (nolock) ON l.seqno = c.sequence_no
>where l.postedDate > '20031207'
> and l.postedDate < GETDATE()
> and l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
> or exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE'
>
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
>> Can someone please re-write this query for me using
better
>> joins? Thank you very much.
>> select distinct(c.research), t.FAX, t.NAME, CAST
>> (exp_AUDITDATE AS varchar(11))
>> AS AUDITDATE from teachers t (nolock), cases c
(nolock),
>> RecordLog l (nolock)
>> where l.postedDate > 120703 and
>> l.postedDate < GETDATE() and l.seqno = c.sequence_no
and
>> l.PostedDate = c.PostedDate
>> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
>> exp_auditdate is null)
>> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>>
>> Ida
>
>.
>|||Thank you.
>--Original Message--
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
>> Can someone please re-write this query for me using
better
>> joins? Thank you very much.
>> select distinct(c.research), t.FAX, t.NAME, CAST
>> (exp_AUDITDATE AS varchar(11))
>> AS AUDITDATE from teachers t (nolock), cases c
(nolock),
>> RecordLog l (nolock)
>> where l.postedDate > 120703 and
>> l.postedDate < GETDATE() and l.seqno = c.sequence_no
and
>> l.PostedDate = c.PostedDate
>> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
>> exp_auditdate is null)
>> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>>
>> Ida
>Here it is. It has distinct in it so it's probably
broken, but without DDL
>and a description of what you want, it's impossible to
tell how broken.
>Also it converts a date to a string, which it probably
shouldn't do.
>select distinct
>c.research,
>t.FAX,
>t.NAME,
>CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
>from
>cases c (nolock)
>join teachers t (nolock)
> on c.research = t.resNo
>join RecordLog l (nolock)
> on l.seqno = c.sequence_no
> and l.PostedDate = c.PostedDate
>where l.postedDate > 120703
>and l.postedDate < GETDATE()
>and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
>AND DELIVERY <> 'INACTIVE'
>David
>
>.
>

query syntax help

Folks,
Hi, I have the following query:
SELECT cast([\\host\NetItfc(Intel[R] CardType\Bytes Sent/sec] AS float) AS
[BytesSentPerSec] FROM tblHostCapacity001
It returns the following syntax error:
Server: Msg 170, Level 15, State 1, Line 1. Line 1: Incorrect syntax near
'CardType'.
I understand the reasons for the error - i.e. I need to escape the square
brackets around the [R] in the fieldname...
BUT how do I accomplish this escaping of the [ and ] - normally these chars
delimit a space-contained fieldname. Haven't been able to find any solutions
on the web via google.
Appreciate any advice.
Cheers,
Neil Evans-Mudie
-. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
... -...- -- ..- -.. .. .
e: My@.myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudie...new.msnw?&pps=kYou should really consider renaming the column to something sensible
following the rules of indentifiers. As for a short term workaround, use
double quotes (") instead of square brackets.
The actual alternative to escaping square brackets is to add another closing
square bracket after the existing one like:
CREATE TABLE tbl ( [\\host\NetItfc(Intel [R]] CardType\Bytes Sent/sec] INT )
Note the addition of ']' after [R] but not before it.
Anith|||See if this helps:
-- cast([\\host\NetItfc(Intel[R]] CardType\Bytes Sent/sec] AS float)
create table t1 (
[a[b]]c] int
)
go
select
cast([a[b]]c] as varchar) as c1
from
t1
go
drop table t1
go
AMB
"Neil Evans-Mudie" wrote:

> Folks,
> Hi, I have the following query:
> SELECT cast([\\host\NetItfc(Intel[R] CardType\Bytes Sent/sec] AS float) AS
> [BytesSentPerSec] FROM tblHostCapacity001
> It returns the following syntax error:
> Server: Msg 170, Level 15, State 1, Line 1. Line 1: Incorrect syntax near
> 'CardType'.
> I understand the reasons for the error - i.e. I need to escape the square
> brackets around the [R] in the fieldname...
> BUT how do I accomplish this escaping of the [ and ] - normally these char
s
> delimit a space-contained fieldname. Haven't been able to find any solutio
ns
> on the web via google.
> Appreciate any advice.
> Cheers,
> Neil Evans-Mudie
> -. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
> ... -...- -- ..- -.. .. .
> e: My@.myorg.com address is a spam sink
> If you wish to email me, try neilevans underscore mudie at hotmail dot com
> w: http://groups.msn.com/TheEvansMudie...new.msnw?&pps=k
>
>