Query.

SELECT TOP 10 *
FROM (
SELECT TOP 60 *
FROM Patient
ORDER BY PA_VPatientID DESC
)
ORDER BY PA_VPatientID DESC
Error :-
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ORDER'.
I did it through the following query
SELECT TOP 10 *
FROM (SELECT TOP 60 *
FROM Patient
ORDER BY PA_VPatientID DESC) a
ORDER BY PA_VPatientID
"Noor" <noor@.ngsol.com> wrote in message
news:eOrEnezXEHA.3044@.TK2MSFTNGP09.phx.gbl...
>
> SELECT TOP 10 *
> FROM (
> SELECT TOP 60 *
> FROM Patient
> ORDER BY PA_VPatientID DESC
> )
> ORDER BY PA_VPatientID DESC
>
> Error :-
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'ORDER'.
>
|||Why are you doing that? Simply using:
SELECT TOP 10 * FROM Patient ORDER BY PA_VPatientID DESC
would be more efficient.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:eOrEnezXEHA.3044@.TK2MSFTNGP09.phx.gbl...
>
> SELECT TOP 10 *
> FROM (
> SELECT TOP 60 *
> FROM Patient
> ORDER BY PA_VPatientID DESC
> )
> ORDER BY PA_VPatientID DESC
>
> Error :-
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'ORDER'.
>

Query, what's wrong here?

Hi
I get an error when I try to limit the "digits" I want to summerize, I have
only added the line
"and digits in (select digits from series)"
when it there the it fails and gives the errpr Msg 446, Level 16, State 9,
Line 8
The only thing I can imagine is that it's because I'm "joining" with another
table. the datatypes are identical.
declare @.site int
declare @.calltype char(1)
set @.site = 1
set @.calltype = 'E'
SELECT digits, COUNT(*) AS total, /* Line 9 */
COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC'
THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'RL'
AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND durationofcall >= 40
THEN 1 END) AS ubesvaret
FROM V2tickets WHERE
siteid = @.site AND calltype = @.calltype AND LEN(digits) = 4
/* adding this statement gives the errpr Msg 446, Level 16, State 9, Line 8
*/
and digits in (select digits from series)
GROUP BY digits
ORDER BY digits
regards
HenryThe error is due to a collation conflict. It looks like the Digits
column in Series must have a different collation to the Digits column
in V2tickets. Either change one of the collations or specify an
explicit collation in the query.
See Books Online for more information on collations.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> skrev i en
meddelelse news:1108503649.678190.90910@.z14g2000cwz.googlegroups.com...
> The error is due to a collation conflict. It looks like the Digits
> column in Series must have a different collation to the Digits column
> in V2tickets. Either change one of the collations or specify an
> explicit collation in the query.
> See Books Online for more information on collations.
Been there, done that, fixed the problem, a lot of stuff to keep track off
here.
Thanks for pointing me the right direction.
regards
Henry

Query!

Hi All,

Declare @.FirstNamenvarchar(60)

Declare @.LastNamenvarchar(60)

select @.FirstName=Usr_First_nm,@.LastName=Usr_Last_nmfrom cpmwhere login_id='dattEMEA_superuser'

Consider the above example. I want to print the varaiable values using select statement. What should I do?

I don't want to use PRINT statm.

Regards

Abdul

try with this procedure...

Declare @.FirstName nvarchar(60)

Declare @.LastName nvarchar(60)

@.FirstName= selectUsr_First_nm from cpm where login_id='dattEMEA_superuser'

@.LastName=selectUsr_Last_nmfrom cpmwhere login_id='dattEMEA_superuser'

select * fromcpmwhere Usr_First_nm =@.FirstName and Usr_Last_nm = @.LastName

|||

select @.FirstName , @.LastName

Query works in access, fails in new SQL Server db

I have the following query running in Cold Fusion and ODBC:
SELECT distinct
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name],
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
as FeederName,
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2].[Program_Web]
as FeederWeb FROM
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links] where
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name]
= 'Dental Assistant (Cert)' and
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
<> '' order by
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
which works using my Access DB, but when used against the SQL Server DB
returns the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
'Program_List_with_Feed_and_Exit_Programs_and_Web_Links.Healthcare_Catalog_2'
does not match with a table name or alias name used in the query.
I guess my question is how this ever worked in the first places, since
there is no
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2]
table... I can only see that table when I look at the access query in
design view, so it is the result of some kind of join.
How do I proceed with fixing this?Chris Lott wrote:
> I have the following query running in Cold Fusion and ODBC:
> SELECT distinct
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name],
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> as FeederName,
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2].[Program_Web]
> as FeederWeb FROM
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links] where
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name]
> = 'Dental Assistant (Cert)' and
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> <> '' order by
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> which works using my Access DB, but when used against the SQL Server DB
> returns the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
> 'Program_List_with_Feed_and_Exit_Programs_and_Web_Links.Healthcare_Catalog_2'
> does not match with a table name or alias name used in the query.
> I guess my question is how this ever worked in the first places, since
> there is no
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2]
> table... I can only see that table when I look at the access query in
> design view, so it is the result of some kind of join.
> How do I proceed with fixing this?
>
Your Access db must have a query with that big long name that joins two or more tables. Create a view in SQL joining
the same tables. If the view already exists, you may not have permission to access it. Or the view may be in another
schema and you will have to preface that view name with that.sql

Query works in 2000 but not 2005

I noticed that some queries work in 2000, but not in 2005, for example
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
Works for 2K, but for 2K5 i get an error message saying "columnD is
invalid in the select list because it is not contained...etc etc"
ANyone know of the change?It shouldn't work on SQL2000 either. Your CASE expression is not complete and
there should be a comma before sum(columnE).
The following should work on both versions:
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end,
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end
Linchi
"adauti@.gmail.com" wrote:
> I noticed that some queries work in 2000, but not in 2005, for example
>
> SELECT columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> sum(columnE)
> FROM TableA
> GROUP BY
> columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> Works for 2K, but for 2K5 i get an error message saying "columnD is
> invalid in the select list because it is not contained...etc etc"
> ANyone know of the change?
>

Query wizard

in sql server 2000, table view, i can right click any table and invoke query
select wizard. At any time I can select spetial icon on toolbar and change
type of the query: select, insert from, insert into, update, delete. I
missed this type of functionality in sql server 2005 and 2008
How about this?
Open up your SSMS and connect to your SQL Server instance.
Go and find your database and go to one of the tables. Right click on it and
go to Script Table as and so on...
Is this are you looking for?
Ekrem ?nsoy
"Aleks Kleyn" <AleksKleyn@.discussions.microsoft.com> wrote in message
news:29928A48-A418-49FE-8A08-835E9B1DD913@.microsoft.com...
> in sql server 2000, table view, i can right click any table and invoke
> query
> select wizard. At any time I can select spetial icon on toolbar and change
> type of the query: select, insert from, insert into, update, delete. I
> missed this type of functionality in sql server 2005 and 2008

Query wizard

in sql server 2000, table view, i can right click any table and invoke query
select wizard. At any time I can select spetial icon on toolbar and change
type of the query: select, insert from, insert into, update, delete. I
missed this type of functionality in sql server 2005 and 2008How about this?
Open up your SSMS and connect to your SQL Server instance.
Go and find your database and go to one of the tables. Right click on it and
go to Script Table as and so on...
Is this are you looking for?
--
Ekrem Ã?nsoy
"Aleks Kleyn" <AleksKleyn@.discussions.microsoft.com> wrote in message
news:29928A48-A418-49FE-8A08-835E9B1DD913@.microsoft.com...
> in sql server 2000, table view, i can right click any table and invoke
> query
> select wizard. At any time I can select spetial icon on toolbar and change
> type of the query: select, insert from, insert into, update, delete. I
> missed this type of functionality in sql server 2005 and 2008

Query wizard

in sql server 2000, table view, i can right click any table and invoke query
select wizard. At any time I can select spetial icon on toolbar and change
type of the query: select, insert from, insert into, update, delete. I
missed this type of functionality in sql server 2005 and 2008How about this?
Open up your SSMS and connect to your SQL Server instance.
Go and find your database and go to one of the tables. Right click on it and
go to Script Table as and so on...
Is this are you looking for?
Ekrem ?nsoy
"Aleks Kleyn" <AleksKleyn@.discussions.microsoft.com> wrote in message
news:29928A48-A418-49FE-8A08-835E9B1DD913@.microsoft.com...
> in sql server 2000, table view, i can right click any table and invoke
> query
> select wizard. At any time I can select spetial icon on toolbar and change
> type of the query: select, insert from, insert into, update, delete. I
> missed this type of functionality in sql server 2005 and 2008

Query within a query

Hi, I am having some logic trouble... I think I want to create a Left Join but I am not sure here is my query...

SELECT Nature.Nature, t.Apr
FROM Nature LEFT JOIN (SELECT NatureCountPerMonth.ComplaintNumber AS Apr, NatureCountPerMonth.Nature FROM NatureCountPerMonth WHERE Month = 4 AS t) ON Nature.Nature = t.Nature

But it doesn't work. How do I nest queries within queries? Is it a syntax problem or is this something that can't be done?

Thanks in advance for any help,Can you explain what you want to achieve?

Did you try this? Does this sql work?

SELECT Nature.Nature, t.Apr
FROM Nature,
(SELECT NatureCountPerMonth.ComplaintNumber AS Apr,
NatureCountPerMonth.Nature
FROM NatureCountPerMonth WHERE Month = 4) t
where Nature.Nature = t.Nature;|||Cheers,
That would be it... I figured you would change the name of a table the same way you change a field. This is what I needed.

Thanks,

Query with SELECT and SUBSTRING

Hello,
I'm a beginner in SQLServer and I'm trying to crite a query with a subst
ring but without success. I've got a field (String) in a table which con
tains a price formatted like that "AUD 2,000.10". I would like with a su
bstring (or something else) obtain something like "2000.10". Can somebod
y help me with that ?
Thanks a lot
Vincent
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Article poste via Voila News - http://www.news.voila.fr
Le : Tue Mar 30 02:55:05 2004 depuis l'IP : mail.ebycms.com.au [VIP 3500
978]Hi Vincent,
Is it always AUD ?
try
select convert(money,right(col1,len(col1)-3))
I hope this helps
--
Greg O
http://www.sql-scripts.com
"MOTTE" <liste@.france-dev.com> wrote in message
news:c4agh9$src$1@.news.x-echo.com...
> Hello,
> I'm a beginner in SQLServer and I'm trying to crite a query with a subst
> ring but without success. I've got a field (String) in a table which con
> tains a price formatted like that "AUD 2,000.10". I would like with a su
> bstring (or something else) obtain something like "2000.10". Can somebod
> y help me with that ?
> Thanks a lot
> Vincent
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Article poste via Voila News - http://www.news.voila.fr
> Le : Tue Mar 30 02:55:05 2004 depuis l'IP : mail.ebycms.com.au [VIP
3500978]

Query with OR never completes

This runs 'Instantly':
SELECT J.JobID, J.JobName, J.CustName
FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
WHERE J2.Jobid IN (SELECT jobid
FROM jobs WHERE jobname IN
(SELECT parent FROM Jobs WHERE
parent IS NOT NULL AND
schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
and this runs 'Instantly':
SELECT J.JobID, J.JobName, J.CustName
FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
WHERE J2.Jobid IN (SELECT jobid
FROM jobs WHERE jobname IN
(SELECT parent FROM Jobs WHERE
parent IS NOT NULL AND
schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
So why does this check ok but never complete when I run it:
SELECT J.JobID, J.JobName, J.CustName
FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
'11/4/2005')
OR J2.Jobid IN (SELECT jobid
FROM jobs WHERE jobname IN
(SELECT parent FROM Jobs WHERE
parent IS NOT NULL AND schedtdate
BETWEEN '1/1/2005' AND '11/4/2005')
Same exact where clauses OR'd
JobId is Identity and Primary
Bob Confused and StupidLook at the estimated execution plan. That should tell you what you need
to know.
one alternate way to do it, if the OR won't optimize is to UNION the two
queries together.
rvgrahamsevatenein@.sbcglobal.net wrote:

>This runs 'Instantly':
>SELECT J.JobID, J.JobName, J.CustName
>FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>and this runs 'Instantly':
>SELECT J.JobID, J.JobName, J.CustName
>FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>So why does this check ok but never complete when I run it:
>SELECT J.JobID, J.JobName, J.CustName
>FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
>'11/4/2005')
> OR J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND schedtdate
> BETWEEN '1/1/2005' AND '11/4/2005')
>Same exact where clauses OR'd
>JobId is Identity and Primary
>Bob Confused and Stupid
>
>|||SQL gets compiled into an execution plan (how the processor navigates
through tables and indexes), before it is run, and even seemingly
insignificant changes in the SQL can result in an entirely different plan.
Using the Show Execution Plan feature of Query Analyzer, see how the plan is
changed when you instroduce the OR condition. If table scans are being
performed, then you may need to implement a new index.
Graphically Displaying the Execution Plan Using SQL Query Analyzer
http://msdn.microsoft.com/library/d... />
1_5pde.asp
Tips on Optimizing SQL Server Indexes
http://www.sql-server-performance.c...ing_indexes.asp
<rvgrahamsevatenein@.sbcglobal.net> wrote in message
news:1131126018.213438.13380@.g43g2000cwa.googlegroups.com...
> This runs 'Instantly':
> SELECT J.JobID, J.JobName, J.CustName
> FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
> and this runs 'Instantly':
> SELECT J.JobID, J.JobName, J.CustName
> FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
> So why does this check ok but never complete when I run it:
> SELECT J.JobID, J.JobName, J.CustName
> FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
> '11/4/2005')
> OR J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND schedtdate
> BETWEEN '1/1/2005' AND '11/4/2005')
> Same exact where clauses OR'd
> JobId is Identity and Primary
> Bob Confused and Stupid
>|||I changed a couple of things and execution came down from 55 seconds (I
thought it was never completing, but it was) to about 5 seconds.
Changing the "Between" on the dates to ">=...and <+" seemed to result
in a completely different execution plan. Strange since in the
"Between" version Sql was using ">=...and <+" anyway!
Bob Graham|||My co-worker here had the same problem yesterday in ORACLE.
She had a query with a few ORs and NOT INs.
After running for about 7 minutes she cancelled the query and called me
over.
I suggested changing the NOT INs to NOT EXISTs but still the same problem.
The next suggestion was separate the query in 3 and UNION them.
It ran in under 2 seconds.
Go figure, Microsoft and Oracle agree on something...
"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:uOy8QhW4FHA.2888@.tk2msftngp13.phx.gbl...
> Look at the estimated execution plan. That should tell you what you need
> to know.
> one alternate way to do it, if the OR won't optimize is to UNION the two
> queries together.
> rvgrahamsevatenein@.sbcglobal.net wrote:
>|||Hmmm, Union worked well. Wish I had time to learn more about what was
wrong with the OR version, but must press on... Thank You!|||I've seen a lot of things like that. Looks like the optimizer likes to
go for a table scan when it encounters an OR.
Yet it seems to be more willing to find a good plan for every branch of
a UNION...

Query with multiple arguments?

Hi, I'm a bit stumped as to how to do this.
I have a string[] with a list of users, and I want to query my database to select only the users in this array and bind the datasource to a GridView, but I don't know how to write an SQL query to search for multiple results from the same field.

E.g. Say I have two results in my string[], fred and bob.
How can I select data from the database for just those two users - "SELECT * FROM tblUsers WHERE UserName='bob' AND ??";

IF this is possible, I also need to bind it to a gridview. I tried the following, but it didn't work as I needed it to:

for(int a = 0; a < userArray.Length; a++)
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn);
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close()
}

That 'worked', but as I'm sure you can see, the data that was bound to the gridview was only the last result found, not the whole result set.

Any help is greatly appreciated.

schuminator:

for(int a = 0; a < userArray.Length; a++)
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn);
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close()
}

try out as below

 String strUsers = String.Empty;for (int a = 0; a < userArray.Length; a++) strUsers = strUsers + @."'" + userArray[a] + @."',"; strUsers = strUsers.Substring(0, strUsers.Length - 1); conn.Open(); SqlCommand command =new SqlCommand("SELECT * FROM tblUsers WHERE UserName in (" + strUsers +")", conn); SqlDataReader reader = command.ExecuteReader(); grid.DataSource = reader; grid.DataBind(); conn.Close();

Good Luck./.

|||

Sorry, I tried to delete the thread but it was too late.
I got it sorted...it was so simple, I feel like an idiot!

"SELECT * FROM tblUsers WHERE UserName='fred' OR UserName='bob'" etc

I also wrote a little for loop to add another OR... to the end of the string when necessary.

|||

you can use above method also...

so instead of UserName='fred' OR UserName='bob'"......

if will formulate query using IN keywork as..

UserName in ('fred','bob')

|||

ahh ok awesome, thanks :)

Query with MAX funtion

Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lieThis is a multi-part message in MIME format.
--=_NextPart_000_033B_01C37935.CF55CC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_033B_01C37935.CF55CC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
select
=d.DeptName
, e.EmployeeName
from
Depts as =d
join
Employees as =e on e.Dept =3D d.Dept
where
e.Salary ==3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each =department butI would like to select the name of the employee who makes the =highest salary in each department.Can you help me to make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_033B_01C37935.CF55CC40--|||This is a multi-part message in MIME format.
--=_NextPart_000_0016_01C37937.E00063F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Tom's Query will work provided that the Salary field is declared as a =money or decimal field. I have seen many databases where (for =portability and the capability of storing very large numbers) that money =field are defined as "float" columns. If this is the case you should =use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, =anything with a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as e2 where
e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater Technology
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23QjHCdVeDHA.2680@.TK2MSFTNGP11.phx.gbl...
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_0016_01C37937.E00063F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Tom's Query will work provided that the =Salary field is declared as a money or decimal field. I have seen many =databases where (for portability and the capability of storing very large numbers) =that money field are defined as "float" columns. If this is the case =you should use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, anything with =a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as =e2 where
= e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater =Technology
"Tom Moreau" = wrote in message news:%23QjHCdVeDHA.=2680@.TK2MSFTNGP11.phx.gbl...
Try:

select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees =as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each department butI would like to select the name of the employee who =makes the highest salary in each department.Can you help me to =make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_0016_01C37937.E00063F0--|||This is a multi-part message in MIME format.
--=_NextPart_000_037D_01C37939.390DAC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Good point. I just assumed it was stored as money. When you assume, =...
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bruce A. Carson" <bcarson@.asgoth.com> wrote in message =news:eBBfolVeDHA.3576@.tk2msftngp13.phx.gbl...
Tom's Query will work provided that the Salary field is declared as a =money or decimal field. I have seen many databases where (for =portability and the capability of storing very large numbers) that money =field are defined as "float" columns. If this is the case you should =use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, =anything with a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as e2 where e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater Technology
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23QjHCdVeDHA.2680@.TK2MSFTNGP11.phx.gbl...
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_037D_01C37939.390DAC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Good point. I just assumed it =was stored as money. When you assume, ...
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bruce A. Carson" wrote in =message news:eBBfolVeDHA.3576=@.tk2msftngp13.phx.gbl...
Tom's Query will work provided that the =Salary field is declared as a money or decimal field. I have seen many =databases where (for portability and the capability of storing very large numbers) =that money field are defined as "float" columns. If this is the case =you should use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, anything with =a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as =e2 where = e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater =Technology
"Tom Moreau" = wrote in message news:%23QjHCdVeDHA.=2680@.TK2MSFTNGP11.phx.gbl...
Try:

select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees =as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each department butI would like to select the name of the employee who =makes the highest salary in each department.Can you help me to =make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_037D_01C37939.390DAC40--

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 with embedded SELECT

This is a request for help with a SELECT within SELECT. The following return
s
open occurances (no END_DATE) and selects the associated appointment with th
e
highest APPT_ID. I want to imbed another SELECT that will 1) read back to th
e
Appointment table (joining back on the APPT_ID) and return the APPT_DATE for
this appointment. 2) I want to JOIN to a table called Resource AS r on a
column in the Appointment table called a.RESOURCE_ID and get r. RESOURCE_NAM
E
from the resource table. Here is the original SELECT:
SELECT o.OCCURANCE_DESC AS CaseID, p.home_phone AS phone, RTRIM(p.first_name
+ ' ' + p.middle_name) + ' ' + p.last_name AS Patient, o.NOTE AS Comment,
MAX(a.APPT_ID) AS LastAppt FROM Occurance o INNER JOIN Patient p ON
o.CLIENT_ID = p.PatUniqueID JOIN Appointment a ON o.OCCURANCE_ID =
a.OCCURANCE_ID WHERE ISDATE(END_DATE) = 0 AND a.Status < 200 GROUP BY
o.OCCURANCE_DESC, p.home_phone, RTRIM(p.first_name + ' ' + p.middle_name) +
' ' + p.last_name, o.NOTE, o.OCCURANCE_ID ;
Thank you very muchrichardb
Please post DDL+ sample data + expected result. It's hard to suggest
something.
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:4D7B74D9-AD1C-40BB-94FF-EC18FF764920@.microsoft.com...
> This is a request for help with a SELECT within SELECT. The following
returns
> open occurances (no END_DATE) and selects the associated appointment with
the
> highest APPT_ID. I want to imbed another SELECT that will 1) read back to
the
> Appointment table (joining back on the APPT_ID) and return the APPT_DATE
for
> this appointment. 2) I want to JOIN to a table called Resource AS r on a
> column in the Appointment table called a.RESOURCE_ID and get r.
RESOURCE_NAME
> from the resource table. Here is the original SELECT:
> SELECT o.OCCURANCE_DESC AS CaseID, p.home_phone AS phone,
RTRIM(p.first_name
> + ' ' + p.middle_name) + ' ' + p.last_name AS Patient, o.NOTE AS Comment,
> MAX(a.APPT_ID) AS LastAppt FROM Occurance o INNER JOIN Patient p ON
> o.CLIENT_ID = p.PatUniqueID JOIN Appointment a ON o.OCCURANCE_ID =
> a.OCCURANCE_ID WHERE ISDATE(END_DATE) = 0 AND a.Status < 200 GROUP BY
> o.OCCURANCE_DESC, p.home_phone, RTRIM(p.first_name + ' ' + p.middle_name)
+
> ' ' + p.last_name, o.NOTE, o.OCCURANCE_ID ;
> Thank you very much|||I don't know what "post DDL+ sample data" means? Let me try to simplify the
request. Let's say I am selecting from a table of appointments the latest
appointment of each type for each patient as follows
SELECT p.PATIENT_NAME, a.APPT_TYPE, a.MAX(APPT_ID) AS LastestAppt FROM
Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID WHERE STATUS <
200 GROUP BY PATIENT_NAME, APPT_TYPE.
This would give:
PATIENT_NAME APPT_TYPE LastestAppt
-- -- --
JONES 90800 43
SMITH 81000 35
etc.
Each appointment row includes a code for the doctor. However, I did not want
to group on the doctor initially, because I only want one most recent
appointment for each patient. Now I want to know who is the doctor for that
appointment, so I think I need a second imbeded SELECT to go back into the
Appointment file and pick off the doctor's ID, resulting in:
PATIENT_NAME APPT_TYPE LastestAppt Doctor
-- -- -- --
JONES 90800 43 RMB
SMITH 81000 35 JDB
etc.
I cannot figure out how to do that and would appreciate help with an example
.
"Uri Dimant" wrote:

> richardb
> Please post DDL+ sample data + expected result. It's hard to suggest
> something.
>
>
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:4D7B74D9-AD1C-40BB-94FF-EC18FF764920@.microsoft.com...
> returns
> the
> the
> for
> RESOURCE_NAME
> RTRIM(p.first_name
> +
>
>|||Richardb
DDL means to post actual a table structure with some data.
CREATE TABLE xxx
(
col1 INT,
col2 INT,
blala...
)
INSERT INTO xxx VALUES ('data',data'.....)
Based on your narrotive I guess you need the following
SELECT p.PATIENT_NAME, a.APPT_TYPE,
a.MAX(APPT_ID) AS LastestAppt,D.Doctorid
FROM Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID JOIN
(SELECT max(doctoreID) Doctorid FROM
FROM 'SomeTable' GROUP BY Somthing) D ON
D.doctoreID=AnotherTable.Doctorid
WHERE STATUS <
200 GROUP BY PATIENT_NAME, APPT_TYPE.
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:8A2292C7-A997-47BA-A0A0-5173DDA61C26@.microsoft.com...
> I don't know what "post DDL+ sample data" means? Let me try to simplify
the
> request. Let's say I am selecting from a table of appointments the latest
> appointment of each type for each patient as follows
> SELECT p.PATIENT_NAME, a.APPT_TYPE, a.MAX(APPT_ID) AS LastestAppt FROM
> Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID WHERE STATUS <
> 200 GROUP BY PATIENT_NAME, APPT_TYPE.
> This would give:
> PATIENT_NAME APPT_TYPE LastestAppt
> -- -- --
> JONES 90800 43
> SMITH 81000 35
> etc.
> Each appointment row includes a code for the doctor. However, I did not
want
> to group on the doctor initially, because I only want one most recent
> appointment for each patient. Now I want to know who is the doctor for
that
> appointment, so I think I need a second imbeded SELECT to go back into the
> Appointment file and pick off the doctor's ID, resulting in:
> PATIENT_NAME APPT_TYPE LastestAppt Doctor
> -- -- -- --
> JONES 90800 43 RMB
> SMITH 81000 35 JDB
> etc.
> I cannot figure out how to do that and would appreciate help with an
example.
>
> "Uri Dimant" wrote:
>
with
to
APPT_DATE
a
Comment,
p.middle_name)sql

Query with another query input parameter

Dear Friends,

I have a long query with an input parameter. I want this input parameter be all teh values returned from another query.

SELECT DIR FROM DIRECCAO

BIG QUERY with DIR input parameter.

How can I do?

Thanks.

SELECT @.DIR = DIR FROM DIRECCAO

EXEC BIG_QUERY @.DIR

HTH,

Babu

|||

IT WORKS AND THE QUERY IS:

ALTER PROCEDURE [dbo].[GD_SP_FACTURA_GLOBAL]

AS

DECLARE @.DIR nvarchar(10)

SELECT @.DIR = DIR_NOME FROM Direccao

EXECUTE dbo.GD_SP_FACTURA_ValorTotal @.DIR

BUT How can I SUM all the values returned by the BIGQuery?

THANKS!!

|||How can I have the sum and it's possible to return a list of all values returned by the bigQuery? THANKS!!!|||

Could anyone help me?

Thanks!

Query VS Stored Procedure problem

Hello.

I am having a strange problem with SQL Server 2005. I have written a SELECT query that contains unions, joins and group functions. when the sql query is run using t-sql statements, the query completed execution in about 10-12 seconds. When the same query is written in a stored procedure without making any changes in the SELECT query (only adding a date parameter), it does not generate any result.

I waited for about 1 hour for the stored procedure to give me the result but it did not. Can anyone help me out with this problem?

Thanks in advance.

Raza:

Please provide a listing of your stored procedure.

Dave

|||

Definitely you need to provide the query, but also how much data is involved. Definitely look at the plans of the query (post them here too using set showplan_text on to get the plan) for clues as to what might be happening.

|||The data involved is huge (millions of rows) but regardless the sql statements copied from the proc and written in query window returns result in 5 - 7 seconds and when the same proc is executed it doesnot return any result.

I have MS Sql 2005 64 bit Enterprise Edition with SP1 installed.

here is the query.

SELECT sim.DEALER_CODE, sim.TRANSACTION_STAMP, sid.PRODUCT_CODE, dbo.REFERENCE_PRODUCT_CODES.SALE_PRICE AS UNIT_PRICE, sid.AMOUNT, sid.QUANTITY, sim.INVOICE_NUMBER, dbo.REFERENCE_TRANSACTION_TYPES.DESCRIPTION AS TRANSACTION_TYPE, sim.TRANSACTION_USER, icl.LOCATION_CODE, icl.REGION_NAME, icl.COUNTRY, (CASE WHEN dbo.REFERENCE_DEALER_CODES.DEALER_TYPE = 'I' THEN 'D' WHEN dbo.REFERENCE_DEALER_CODES.DEALER_TYPE = 'N' THEN 'D' ELSE 'E' END) AS DEALER_TYPE, sim.PARAMETER_1 AS ITEM_SERIAL FROM dbo.SALES_INVOICE_DETAIL AS sid INNER JOIN dbo.SALES_INVOICE_MASTER AS sim ON sid.INVOICE_NUMBER = sim.INVOICE_NUMBER INNER JOIN dbo.VIEW_USER_INFORMATION_COUNTRY_LEVEL AS icl ON sim.TRANSACTION_USER = icl.USER_ID INNER JOIN dbo.REFERENCE_TRANSACTION_TYPES ON sim.TRANSACTION_TYPE = dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE INNER JOIN dbo.REFERENCE_PRODUCT_CODES ON sid.PRODUCT_CODE = dbo.REFERENCE_PRODUCT_CODES.PRODUCT_CODE LEFT OUTER JOIN dbo.REFERENCE_DEALER_CODES ON sim.DEALER_CODE = dbo.REFERENCE_DEALER_CODES.DEALER_CODE WHERE (sim.TRANSACTION_STAMP BETWEEN CONVERT(CHAR(10), GETDATE() - 1, 101) AND CONVERT(CHAR(10), GETDATE() - 1, 101) + '

23:59:59') AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Thanks

|||

Some suggestions to identify the problem:

1- Limit the number of rows returned (maybe by adding an extra predicate) and see if the sproc returns any results at all. If the sproc is still hanging, it maybe an urelated issue with the query.

2- If the sproc returns results, try to open a cursor on the original query and print messages after every fetch to verify the query is returning results inside the proc.

Thanks.

|||

What do you mean "doesn't return any result" Do you mean it takes forever, or it returns no rows?

So the procedure is:

create procedure procName
as

<your query>

go

Or is there anything else? I don't know why that wouldn't use as good of a plan as an ad hoc query...especially if you recompile the procedure.

|||You are using between to compare a string. This NEVER works.

Try this:

WHERE (sim.TRANSACTION_STAMP BETWEEN CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) AS DATETIME) AND CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) + '

23:59:59') AS DATETIME) AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))|||

obviously probelm is with the date. remove the date from SP and verify.

Can you explain what is your requierment on date field

|||Could you verify whether there any records which satisfies the date condition mentioned in the where clause?|||

Tom Phillips wrote:

You are using between to compare a string. This NEVER works.

Try this:

WHERE (sim.TRANSACTION_STAMP BETWEEN CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) AS DATETIME) AND CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) + ' 23:59:59') AS DATETIME) AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Tom,

That is not true. BETWEEN works with string values, the problem is that it is more difficult to anticipate the results, and a greater reliance upon good indexing. For example, try these two queries:

USE Northwind
GO

SELECT
EmployeeID,
LastName,
FirstName
FROM Employees
WHERE LastName BETWEEN 'a' AND 'f'

SELECT
OrderID,
OrderDate
FROM Orders
WHERE OrderDate BETWEEN cast( convert( char(10), getdate() - 3850, 101 ) AS datetime )
AND ( cast( convert( char(10), getdate() - 3800, 101 ) AS datetime ) + ' 23:59:59' )

|||If the query works as an ad hoc call, but not in a procedure, it is unlikely that there is anything
"wrong" with the query itself. There is something missing that needs to be supplied before we can make a judgment. Maybe a param or something... Or an IF...THEN around the query. We need to see the entire proc...|||

RazaRana wrote:

The data involved is huge (millions of rows) but regardless the sql statements copied from the proc and written in query window returns result in 5 - 7 seconds and when the same proc is executed it doesnot return any result.

...

WHERE (sim.TRANSACTION_STAMP BETWEEN CONVERT(CHAR(10), GETDATE() - 1, 101) AND CONVERT(CHAR(10), GETDATE() - 1, 101) + ' 23:59:59') AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Thanks

I don't think that this WHERE clause is correct and will work to return data. I suggest the following alteration:

WHERE ( sim.TRANSACTION_STAMP BETWEEN convert( char(10), getdate() - 1, 101 )
AND convert( char(10), getdate() - 1, 101 ) + ' 23:59:59' )
AND dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE NOT IN ( '2', '7' )
)

|||You are correct, it technically "works". I should have said "NEVER gives the expected results". :)

This is the 3rd time in 3 months I have seen someone trying to do this exact same WHERE clause with BETWEEN a date and 2 strings.|||Hi

Sorry for late reply. The query works perfectly fine and returns results (upto 15,000 rows) in less than 10 secs.

I use the same query in sproc, only the date is passed as a parameter. The sproc takes forever, i waited for 1 hr and 25 minutes and still no results.

The interesting thing is that i killed a few locks created on the tempdb and ran the sproc at midnight and it returned the results in about 10 secs.

Thanx

Query using datetime datatype

hello,
so i have a table with column name Date with datetime as it's datatype. I'm
trying to run a select statement on it that will give me all rows where my
Date column has a datetime of 30 days or more. Can anyone help? I tried using
datediff but can't get it to work.
Thanks in advance!"FS" <FS@.discussions.microsoft.com> wrote in message
news:6A6874E0-38C5-4257-BA2A-4EEFF4D3A181@.microsoft.com...
> hello,
> so i have a table with column name Date with datetime as it's datatype.
> I'm
> trying to run a select statement on it that will give me all rows where my
> Date column has a datetime of 30 days or more. Can anyone help? I tried
> using
> datediff but can't get it to work.
> Thanks in advance!
WHERE dt <= DATEADD(DAY,-30,CURRENT_TIMESTAMP);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql