Sql ANSI Padding: select

显示标签为“select”的博文。显示所有博文

2012年3月22日星期四

Anybody knows the syntax? Thanks

declare @.lastmonth char(7)
select comment from tbldescription
where themonth = code.getlastmonth() <-- syntax wrong
getlastmonth() is a function returning last month in form like '2006.02'
Anybody knows the right syntax, please email me back. Thanks!It is not completely clear what are you trying to get.
1. If you want to use you custom code function, then
just use general sql pane (command type=text) and as you data source use:
="select comment from tbldescription where themonth ='"+code.getlastmonth()
+"'"
2. use just a plain sql query (command type=table direct)
select comment from tbldescription where YEAR(yourdatetime_field) =YEAR(GETDATE()) and MONTH(yourdatetime_field) = MONTH (GETDATE()) -1
Hope it helps
Oleg Yevteyev,
San Diego, CA
It is OK to contact me with a contracting opportunity.
"myfirstname"001atgmaildotcom.
Replace "myfirstname" with Oleg.
--
"Henry Chen" <HenryChen@.discussions.microsoft.com> wrote in message
news:5A98BD40-5FB4-4383-B307-4D38D7507690@.microsoft.com...
> declare @.lastmonth char(7)
> select comment from tbldescription
> where themonth = code.getlastmonth() <-- syntax wrong
>
> getlastmonth() is a function returning last month in form like '2006.02'
> Anybody knows the right syntax, please email me back. Thanks!
>
>|||I am creating a data connection and the three line codes are in the dataset
panel, then I click on refresh tab, it generated an error, why? that is my
question.
"Henry Chen" wrote:
> declare @.lastmonth char(7)
> select comment from tbldescription
> where themonth = code.getlastmonth() <-- syntax wrong
>
> getlastmonth() is a function returning last month in form like '2006.02'
> Anybody knows the right syntax, please email me back. Thanks!
>
>|||Hi,
"Why?" That's because SQL server doesn't understand what you are trying to
write and which is not in the SQL SERVER scope. This is used inside the
report.(code.getlastmonth()) Instead use sqlserver commands to get the last
month in your format. Can take it from the last post.
Amarnath
"Henry Chen" wrote:
> I am creating a data connection and the three line codes are in the dataset
> panel, then I click on refresh tab, it generated an error, why? that is my
> question.
> "Henry Chen" wrote:
> > declare @.lastmonth char(7)
> >
> > select comment from tbldescription
> > where themonth = code.getlastmonth() <-- syntax wrong
> >
> >
> > getlastmonth() is a function returning last month in form like '2006.02'
> >
> > Anybody knows the right syntax, please email me back. Thanks!
> >
> >
> >|||the correct answer is
="select comment from tbldescription where themonth = '" +
code.getlastmonth() + "'"
in ONE LINE.
so you are right.
Thanks!
"Henry Chen" wrote:
> declare @.lastmonth char(7)
> select comment from tbldescription
> where themonth = code.getlastmonth() <-- syntax wrong
>
> getlastmonth() is a function returning last month in form like '2006.02'
> Anybody knows the right syntax, please email me back. Thanks!
>
>sql

Any way to use params in a SELECT...IN query?

I wish to execute a query like this:
SELECT name FROM Owner WHERE ownerID IN (@.ownerIDs)
Is there any way to accomplish that? I'm using ADO.NET to execute this..
Thanks,
Pablo
--
Sleep till the end, true soul and sweet!
Nothing comes to thee new or strange.
Sleep full of rest from head to feet;
Lie still, dry dust, secure of change.
-- Alfred Tennyson (1809-1892)
-- To J. S.
Pablo Montilla
www.odyssey.com.uyCheck out http://www.sommarskog.se/dynamic_sql.html#List
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.typkr1dkcj6shk@.chimera.odyssey.com.uy...
>I wish to execute a query like this:
> SELECT name FROM Owner WHERE ownerID IN (@.ownerIDs)
> Is there any way to accomplish that? I'm using ADO.NET to execute this..
> Thanks,
> Pablo
> --
>
> Sleep till the end, true soul and sweet!
> Nothing comes to thee new or strange.
> Sleep full of rest from head to feet;
> Lie still, dry dust, secure of change.
> -- Alfred Tennyson (1809-1892)
> -- To J. S.
> Pablo Montilla
> www.odyssey.com.uy|||Pablo,
I think Tibor meant:
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Pablo Montilla" wrote:
> I wish to execute a query like this:
> SELECT name FROM Owner WHERE ownerID IN (@.ownerIDs)
> Is there any way to accomplish that? I'm using ADO.NET to execute this..
> Thanks,
> Pablo
> --
>
> Sleep till the end, true soul and sweet!
> Nothing comes to thee new or strange.
> Sleep full of rest from head to feet;
> Lie still, dry dust, secure of change.
> -- Alfred Tennyson (1809-1892)
> -- To J. S.
> Pablo Montilla
> www.odyssey.com.uy
>|||On Sat, 15 Sep 2007 20:22:00 -0300, Alejandro Mesa
<AlejandroMesa@.discussions.microsoft.com> wrote:
> Pablo,
> I think Tibor meant:
> Arrays and Lists in SQL Server
> http://www.sommarskog.se/arrays-in-sql.html
> AMB
>
Many thanks for the links!
Regards,
Pablo
--
Don't vote. The government will get in.
-- Anarchist's Slogan
Pablo Montilla
www.odyssey.com.uy

2012年3月20日星期二

Any way to track if a person does a select on a table?

Profile the database, filter the
user, save the trace to a table and query the table for the results...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>
|||Consider creating a SQL Trace with the desired events and filters. You can
create such a trace using the Profiler GUI and then script/run the trace to
log to a file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>
|||network packet sniffing is the most efficient/effective way to do this.
There are several products on the market that do this now.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>

Any way to track if a person does a select on a table?

We have a situation where we would like to know who is accessing our data.
We know we could make a user read only but we'd also like to know what they
did to retrieve the data.
I guess what I need is Query Profiler but limit it to a specific user. Since
the user will have read only privileges, I won't have to worry about them
changing the data.
I just want to know that they accessed it.
BTW - they will be doing this through an ODBC connection in Access.
TIA - Jeff.I think you answered your own question :) Profile the database, filter the
user, save the trace to a table and query the table for the results...
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||Consider creating a SQL Trace with the desired events and filters. You can
create such a trace using the Profiler GUI and then script/run the trace to
log to a file.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||network packet sniffing is the most efficient/effective way to do this.
There are several products on the market that do this now.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>

Any way to track if a person does a select on a table?

Profile the database, filter the
user, save the trace to a table and query the table for the results...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||Consider creating a SQL Trace with the desired events and filters. You can
create such a trace using the Profiler GUI and then script/run the trace to
log to a file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||network packet sniffing is the most efficient/effective way to do this.
There are several products on the market that do this now.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>

2012年3月11日星期日

Any suggestions on how to revise code to accomodate order by issue

The code below needs to do the following: SELECT the TOP 5 most recent entries from the table and UNION that data with the next table which performs the same SELECT statement.

I am not getting the correct output due to: SQL does not allow ORDER BY between a UNION so I am not pulling the most recent entries.

Any suggestion on how to solve this problem?

Thanks!

SELECT TOP 5 GlobalBanking.dbo.GB_LTFLead.BankCode AS BANKCODE, GlobalBanking.dbo.GB_LTFLead.Date AS DATE,
GlobalBanking.dbo.GB_LTFLead.code AS CODE, GlobalBanking.dbo.GB_LTFLead.Deal AS DEAL,
ISNULL('Fee: $'+GlobalBanking.dbo.GB_LTFLead.Fee, '') AS FEE,
ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFLead.date,4)) AS YEAR
FROM GlobalBanking.dbo.GB_LTFLead WHERE GlobalBanking.dbo.GB_LTFLead.BankCode = 1

UNION ALL

SELECT TOP 5 GlobalBanking.dbo.GB_LTFSyndicate.BankCode AS BANKCODE, GlobalBanking.dbo.GB_LTFSyndicate.Date AS DATE,
'Syndicate' AS CODE, GlobalBanking.dbo.GB_LTFSyndicate.Deal AS DEAL,
ISNULL('Fee: $' + GlobalBanking.dbo.GB_LTFSyndicate.Fee, '') AS FEE,
ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFSyndicate.Date,4)) AS 'YEAR'
FROM GlobalBanking.dbo.GB_LTFSyndicate WHERE GlobalBanking.dbo.GB_LTFSyndicate.BankCode = 1

UNION ALL

SELECT TOP 5 GlobalBanking.dbo.GB_SecLead.BankCode AS BANKCODE, GlobalBanking.dbo.GB_SecLead.Date AS DATE,
GlobalBanking.dbo.GB_SecLead.code AS CODE, GlobalBanking.dbo.GB_SecLead.Deal AS DEAL,
ISNULL('Fee: $'+GlobalBanking.dbo.GB_SecLead.Fee, '') AS FEE,
ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_SecLead.date,4)) AS YEAR
FROM GlobalBanking.dbo.GB_SecLead WHERE GlobalBanking.dbo.GB_SecLead.BankCode = 1

UNION ALL

SELECT TOP 5 GlobalBanking.dbo.GB_SecSyndicate.BankCode AS BANKCODE, GlobalBanking.dbo.GB_SecSyndicate.Date AS DATE,
'Syndicate' AS CODE, GlobalBanking.dbo.GB_SecSyndicate.Deal AS DEAL,
ISNULL('Fee: $' + GlobalBanking.dbo.GB_SecSyndicate.Fee, '') AS FEE,
ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_SecSyndicate.Date,4)) AS 'YEAR'
FROM GlobalBanking.dbo.GB_SecSyndicate WHERE GlobalBanking.dbo.GB_SecSyndicate.BankCode = 1

ORDER BY YEAR DESChave you tried this:select * from
( SELECT TOP 5 GlobalBanking.dbo.GB_LTFLead.BankCode AS BANKCODE
, GlobalBanking.dbo.GB_LTFLead.Date AS DATE
, GlobalBanking.dbo.GB_LTFLead.code AS CODE
, GlobalBanking.dbo.GB_LTFLead.Deal AS DEAL
, ISNULL('Fee: $'+GlobalBanking.dbo.GB_LTFLead.Fee, '') AS FEE
, ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFLead.date,4)) AS YEAR
FROM GlobalBanking.dbo.GB_LTFLead
WHERE GlobalBanking.dbo.GB_LTFLead.BankCode = 1
order by GlobalBanking.dbo.GB_LTFLead.date desc
) as derivedtable1

UNION ALL

select * from
( SELECT ...|||Code works great! I only had to add an additional Select statement at the beginning in order to combine everything I.E.:

SELECT * FROM
(select * from
( SELECT TOP 5 GlobalBanking.dbo.GB_LTFLead.BankCode AS BANKCODE
, GlobalBanking.dbo.GB_LTFLead.Date AS DATE
, GlobalBanking.dbo.GB_LTFLead.code AS CODE
, GlobalBanking.dbo.GB_LTFLead.Deal AS DEAL
, ISNULL('Fee: $'+GlobalBanking.dbo.GB_LTFLead.Fee, '') AS FEE
, ISNULL(NULL, RIGHT(GlobalBanking.dbo.GB_LTFLead.date,4)) AS YEAR
FROM GlobalBanking.dbo.GB_LTFLead
WHERE GlobalBanking.dbo.GB_LTFLead.BankCode = 1
order by GlobalBanking.dbo.GB_LTFLead.date desc
) as derivedtable1

UNION ALL

select * from
( SELECT ...)) AS DERIVETABLE2
ORDER BY DATE DESC

Other than that it runs well. Your great!!!

Any structure on Sql Server like WITH ... SELECT structure on DB2

Hi,
I'm using DB2 UDB 7.2.
Also I'm doing some tests on SQL Server 2000 for some statements to
use efectively.
I didn't find any solution on Sql Server about WITH ... SELECT
structure of DB2.

Is there any basic structure on Sql Server like WITH ... SELECT
structure?

A Sample statement for WITH ... SELECT on DB2 like below
WITH
totals (code, amount)
AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code)
SELECT
code, SUM(amount)
FROM totals
GROUP BY code

......................

Note: 'creating temp table and using it' maybe a solution.
However i need to know the definition of the result set of Union
clause. I don't want to use this way.
CREATE TABLE #totals (codechar(10), amount dec(15))
GO
INSERT INTO #totals
SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code
GO
SELECT code, sum(amount) FROM #totals GROUP BY code
GO

Any help would be appreciated
Thanks in advance
MemduhIn SQL Server, you can use SELECT ... INTO to create a table using a SELECT
query as the source for schema and data. Untested example:

SELECT
code,
SUM(amount)
INTO #totals
FROM trans1
GROUP BY code
UNION ALL
SELECT
code,
SUM(amount)
FROM trans2
GROUP BY code

SELECT
code,
SUM(amount)
FROM #totals
GROUP BY code

You can also produce the result using a derived table instead of a temp
table:

SELECT
code,
SUM(amount)
FROM
(
SELECT
code,
SUM(amount) AS amount
FROM trans1
GROUP BY code
UNION ALL
SELECT
code,
SUM(amount)
FROM trans2
GROUP BY code
) AS totals
GROUP BY code

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Memduh Durmaz" <mdurmaz@.derece.com.tr> wrote in message
news:e0c9cfd5.0408291350.31b8585a@.posting.google.c om...
> Hi,
> I'm using DB2 UDB 7.2.
> Also I'm doing some tests on SQL Server 2000 for some statements to
> use efectively.
> I didn't find any solution on Sql Server about WITH ... SELECT
> structure of DB2.
> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code
> .....................
> Note: 'creating temp table and using it' maybe a solution.
> However i need to know the definition of the result set of Union
> clause. I don't want to use this way.
> CREATE TABLE #totals (code char(10), amount dec(15))
> GO
> INSERT INTO #totals
> SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code
> GO
> SELECT code, sum(amount) FROM #totals GROUP BY code
> GO
> Any help would be appreciated
> Thanks in advance
> Memduh|||On 29 Aug 2004 14:50:25 -0700, Memduh Durmaz wrote:

> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code

You can always use a derived table:

SELECT code, SUM(amount)
FROM (
SELECT code, SUM(amount) FROM trans1 GROUP BY CODE
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY CODE
) AS TOTALS
GROUP BY CODE

(the "AS" in "AS TOTALS" is optional, and is often omitted.)|||SQL-Server 2005 will support Common Table Expressions (the WITH ...
SELECT stuff). If you don't want to wait a year, then you would have to
work around in SQL-Server 2000, or get hold of a beta-version.

HTH,
Gert-Jan

Memduh Durmaz wrote:
> Hi,
> I'm using DB2 UDB 7.2.
> Also I'm doing some tests on SQL Server 2000 for some statements to
> use efectively.
> I didn't find any solution on Sql Server about WITH ... SELECT
> structure of DB2.
> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code
> .....................
> Note: 'creating temp table and using it' maybe a solution.
> However i need to know the definition of the result set of Union
> clause. I don't want to use this way.
> CREATE TABLE #totals (code char(10), amount dec(15))
> GO
> INSERT INTO #totals
> SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code
> GO
> SELECT code, sum(amount) FROM #totals GROUP BY code
> GO
> Any help would be appreciated
> Thanks in advance
> Memduh

--
(Please reply only to the newsgroup)

2012年3月8日星期四

Any SELECT Statement Gurus Out There?

Hi all,

I'm having real trouble wrapping my newbie brain around this problem. Can
someone please tell me the most efficient (or any!) way to write a SELECT
statement to return a set of rows from 5 tables:

These tables are part of our medical database. For example, a person seeking
healthcare may want to know the names and contact information for all
doctors in Reno, NV, who do cardiology. Sounds simple, but it isn't.

A medical group can have many doctors.
A doctor may be a member of more than one medical group.
A doctor or group can have multiple specialties.
A group can have multiple facilities (physical locations).

So the tables look like this...

Table: Doctors
-----
DoctorName
DoctorID

Table: Groups
-----
GroupName
GroupID

Table: Docs2Groups (provides many-to-many relationship between Doctors and
Groups)
-------
DoctorID
GroupID

Table: Specialties (provides many-to-many relationshop between Doctors,
Groups, and Specialties)
------
SpecialtyID
SpecialtyDesc
DoctorID
GroupID

Facilities
---
FacilityName (provides one-to-many relationship between Groups and
Facilities)
FacilityID
GroupID
Address
City
State
Zip
Phone
E-mail

Any help would be GREATLY appreciated.

--Eric Robinsonthis table confuses me:

Table: Specialties (provides many-to-many relationshop between Doctors,
Groups, and Specialties)
------
SpecialtyID
SpecialtyDesc
DoctorID
GroupID

Are you saying that a doctor can only specialize in something within
the context of a certain group? In other words Dr.Smith is a pediatric
oncologist, but can only work in pediatrics for Group A and oncology
for group B? Does that happen?

It would be easier if a Group was a collection of doctors who all had
specialties.

SELECT d.DoctorName
FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
JOIN Specialties s ON d.DoctorID = s.DoctorID
JOIN Facilities f ON f.GroupID = d2g.GroupID
WHERE s.SpecialityDesc = 'cardiology'
AND f.City = 'Reno' AND f.State = 'NV'

But, if a Doctor can only specialize in something in the context of a
group, then you could do this:

SELECT d.DoctorName
FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
JOIN Specialties s ON d2g.DoctorID = s.DoctorID
AND d2g.GroupID = s.GroupID
JOIN Facilities f ON f.GroupID = d2g.GroupID
WHERE s.SpecialityDesc = 'cardiology'
AND f.City = 'Reno' AND f.State = 'NV'

Untested.

HTH,
Stu|||Stu,

Each doctor has his or her own primary specialty and 0 or more additional
specialties. These specialties are asociated with the doctor, not the group.
A group's specialty is a function of its participating doctors. If all the
docs for Group A are cardiologists, then Group A is considered a cardiology
group. If the docs are of different specialties, then the group is
considered "multispecialty." In that sense, a group is, as you say, a
collection of doctors.

HOWEVER, there are unusual cases where the group consists of docs with
different specialties, but it still wants to to be known (for the purposes
of our directory) as one certain kind of group. In these cases, the group
itself gets an "overriding" specialty associated with it to keep from being
listed as "multispecialty."

Therefore the Specialties table does double-duty, but there are only a few
records with GroupID <> 0.

Does that clear things up?

--Eric

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126550993.851782.190410@.g43g2000cwa.googlegr oups.com...
> this table confuses me:
> Table: Specialties (provides many-to-many relationshop between Doctors,
> Groups, and Specialties)
> ------
> SpecialtyID
> SpecialtyDesc
> DoctorID
> GroupID
> Are you saying that a doctor can only specialize in something within
> the context of a certain group? In other words Dr.Smith is a pediatric
> oncologist, but can only work in pediatrics for Group A and oncology
> for group B? Does that happen?
> It would be easier if a Group was a collection of doctors who all had
> specialties.
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d.DoctorID = s.DoctorID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> But, if a Doctor can only specialize in something in the context of a
> group, then you could do this:
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d2g.DoctorID = s.DoctorID
> AND d2g.GroupID = s.GroupID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> Untested.
> HTH,
> Stu|||Oops, I mistated the Specialties table. It is actually three tables:

Specialty
---
SpecialtyID
SpecialtyDesc

Doc2Specialty
------
SpecialtyID
DoctorID

Group2Specialty
------
SpecialtyID
GroupID

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126550993.851782.190410@.g43g2000cwa.googlegr oups.com...
> this table confuses me:
> Table: Specialties (provides many-to-many relationshop between Doctors,
> Groups, and Specialties)
> ------
> SpecialtyID
> SpecialtyDesc
> DoctorID
> GroupID
> Are you saying that a doctor can only specialize in something within
> the context of a certain group? In other words Dr.Smith is a pediatric
> oncologist, but can only work in pediatrics for Group A and oncology
> for group B? Does that happen?
> It would be easier if a Group was a collection of doctors who all had
> specialties.
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d.DoctorID = s.DoctorID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> But, if a Doctor can only specialize in something in the context of a
> group, then you could do this:
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d2g.DoctorID = s.DoctorID
> AND d2g.GroupID = s.GroupID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> Untested.
> HTH,
> Stu|||Eric Robinson (eric @. pmcipa..{com}) writes:
> These tables are part of our medical database. For example, a person
> seeking healthcare may want to know the names and contact information
> for all doctors in Reno, NV, who do cardiology. Sounds simple, but it
> isn't.
> A medical group can have many doctors.
> A doctor may be a member of more than one medical group.
> A doctor or group can have multiple specialties.
> A group can have multiple facilities (physical locations).
> So the tables look like this...

It is always preferable to post CREATE TABLE scripts for the table.
That and test data in INSERT statemants, and the desired result of
the test data. That will give you a tested solution.

So this is an untested solution:

SELECT D.DoctorName
FROM Doctors D
WHERE EXISTS (SELECT *
FROM Doc2Groups DG
JOIN Facility F ON F.GroupID = DG.GroupID
WHERE DG.DoctorID = D.DoctorID
AND F.State = 'NV'
AND F.City = 'Reno')
AND EXISTS (SELECT *
FROM Doc2Specialiity DS
JOIN Specialtities S ON DS.SpecialityID = S.SpecialityID
WHERE DS.DoctorID = S.DoctorID
AND S.SpecialityDesc = 'Cardiology')
UNION
SELECT D.DoctorName
FROM Doctors D
WHERE EXISTS (SELECT *
FROM Doc2Groups DG
JOIN Facility F ON F.GroupID = DG.GroupID
JOIN Group2Specialiity GS ON GS.GroupID = DS.GroupID
JOIN Specialtities S ON GS.SpecialityID = S.SpecialityID
WHERE DG.DoctorID = D.DoctorID
AND F.State = 'NV'
AND F.City = 'Reno'
AND S.SpecialityDesc = 'Cardiology')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 12 Sep 2005 12:36:02 -0700, Eric Robinson wrote:

>Oops, I mistated the Specialties table. It is actually three tables:
(snip)

Hi Eric,

I'm glad you posted that, since I was just preparing to telll you how
you should change your design and all <grin>.

Anyway, since we now have doctors who do cardiology, groups that
specialize in cardiology and doctors that belong to groups that do
cardiology, it's not exactly clear what you want your query to return.
I'll assume you want to know what I would be interested in if my heart
starts acting funny while I'm in Reno - I'd like to know where to go
(and quick!).

The query below will return the name of the group and the details of the
location for each group with a location in Reno, NV that either has
cardiology as group specialization, or hosts at least one doctor who
specializes in cardiology.

SELECT g.GroupName, f.FacilityName, f.Address, f.Phone
FROM Groups AS g
INNER JOIN Facilities AS f
ON f.GroupID = g.GroupID
LEFT JOIN Group2Specialties AS g2s
ON g2s.GroupID = g.GroupID
CROSS JOIN (SELECT SpecialtyID
FROM Specialties
WHERE SpecialtyDesc = 'Cardiology') AS s(SpecialtyID)
WHERE f.City = 'Reno'
AND f.State = 'NV'
AND
( g2s.SpecialtyID = s.SpecialtyID
OR EXISTS
(SELECT *
FROM Docs2Groups AS d2g
INNER JOIN Doc2Specialties AS d2s
ON d2s.DoctorID = d2g.DoctorID
WHERE d2g.GroupID = g.GroupID
AND d2s.SpecialtyID = s.SpecialtyID))

(untested - see www.aspfaq.com/5006 if you prefer tested suggestions).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I was offline for a few hours and I see that I have some very throughtful
replies. I will take a closer look at them. In the meantime, based on Stu's
initial suggestion, I came up with the following, which SEEMS to work:

For simplicity's sake, I lied in my original message about the true names of
the tables, so the following query looks slightly different because it
contains real table names. (In the future I'll take Erland's suggestion and
post CREATE TABLE scripts instead.)

SELECT s.Specialty, p.LastName, p.FirstName, p.Degree, g.GroupName,
f.PhysAddr1, f.PhysCity, f.PhysAddr2, f.PhysState, f.Phone1
FROM tblProviders p JOIN tblBindProviderGroup p2g on p.ProviderID =
p2g.ProviderID
JOIN tblGroups g on g.GroupID=p2g.GroupID
JOIN tblFacilities f on f.GroupID=p2g.GroupID
JOIN tblBindProviderSpecialty p2s on p2s.ProviderID=p.ProviderID
JOIN tblSpecialties s on s.SpecialtyID=p2s.SpecialtyID
WHERE f.PhysCity='Reno'
ORDER BY p.LastName

I've run this against the actual data and it SEEMS to return correct
results.

I'm guessing that some of the other approaches people have posted are better
in ways I have not yet thought of.

--Eric

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CFEF79E2BE1Yazorman@.127.0.0.1...
> Eric Robinson (eric @. pmcipa..{com}) writes:
>> These tables are part of our medical database. For example, a person
>> seeking healthcare may want to know the names and contact information
>> for all doctors in Reno, NV, who do cardiology. Sounds simple, but it
>> isn't.
>>
>> A medical group can have many doctors.
>> A doctor may be a member of more than one medical group.
>> A doctor or group can have multiple specialties.
>> A group can have multiple facilities (physical locations).
>>
>> So the tables look like this...
> It is always preferable to post CREATE TABLE scripts for the table.
> That and test data in INSERT statemants, and the desired result of
> the test data. That will give you a tested solution.
> So this is an untested solution:
> SELECT D.DoctorName
> FROM Doctors D
> WHERE EXISTS (SELECT *
> FROM Doc2Groups DG
> JOIN Facility F ON F.GroupID = DG.GroupID
> WHERE DG.DoctorID = D.DoctorID
> AND F.State = 'NV'
> AND F.City = 'Reno')
> AND EXISTS (SELECT *
> FROM Doc2Specialiity DS
> JOIN Specialtities S ON DS.SpecialityID = S.SpecialityID
> WHERE DS.DoctorID = S.DoctorID
> AND S.SpecialityDesc = 'Cardiology')
> UNION
> SELECT D.DoctorName
> FROM Doctors D
> WHERE EXISTS (SELECT *
> FROM Doc2Groups DG
> JOIN Facility F ON F.GroupID = DG.GroupID
> JOIN Group2Specialiity GS ON GS.GroupID = DS.GroupID
> JOIN Specialtities S ON GS.SpecialityID =
> S.SpecialityID
> WHERE DG.DoctorID = D.DoctorID
> AND F.State = 'NV'
> AND F.City = 'Reno'
> AND S.SpecialityDesc = 'Cardiology')
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

2012年3月6日星期二

any order

quote:

> As far as the I know, the order was the same as you add the data.
> You can change the order bu add "order by" statement , I think you can get
> whole information from BOL
>

|||Hi ,
If you have a clusted index in that table database will be ordered based on
the Index key. So incase if you have a clustered index the
"select * from tablename" will return the result set based on the clustered
index key order.
If you donot have a clusted index the result set will be reurned based on
the way you inserted.
Thanks
Hari
MCDBA
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>

|||records are sorted in ascending order by default.
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>

|||Sorry

quote:

> I don't think that order depends on insertion. (unless you have clustered
> index on this column)

I meant if you don't have a clustered index on the column it depends on
insertion.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...

quote:

> Wei
> I don't think that order depends on insertion. (unless you have clustered
> index on this column)
>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
get[QUOTE]
>

|||The first reply you made me shocked

|||> If you have a clusted index in that table database will be ordered based on

quote:

> the Index key.

Not necessarily. See my other post.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uPL7gkW6DHA.2628@.TK2MSFTNGP10.phx.gbl...

quote:

> Hi ,
> If you have a clusted index in that table database will be ordered based o
n
> the Index key. So incase if you have a clustered index the
> "select * from tablename" will return the result set based on the clustere
d
> index key order.
> If you donot have a clusted index the result set will be reurned based on
> the way you inserted.
>
> Thanks
> Hari
> MCDBA
> "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
>

|||> I meant if you don't have a clustered index on the column it depends on

quote:

> insertion.

Not correct. I'm afraid. See my other post.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23qCR2tW6DHA.1936@.TK2MSFTNGP12.phx.gbl...[Q
UOTE]
> Sorry
> I meant if you don't have a clustered index on the column it depends on
> insertion.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...
> get
>[/QUOTE]|||A table, is in the relation model, an unordered set of rows. Imagine your ro
ws being notes on
paper-slips and you throw them into a bag. The back is shaken from time to t
ime. Now. try to pull
the paper-slips out of this bag "in order". The term "order" doesn't make se
nse in the relational
model.
To be more specific, if you don't have order by, the optimizer is free to re
turn the rows in any
order. It will pick the order it finds most cost-effective.
It doesn't have to be in the same sequence as the rows are inserted (read ab
out IAM page etc in the
physical database chapter in Books Online). That is regardless whether you h
ave a clustered index or
not.
It doesn't have to be in the same sequence as the clustered index. The index
can be heavy
fragmented, so the optimizer can decide to instead of jumping back and forth
on the disk, it will
scan the file in physical order, according to the IAM page.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"John" <spam@.spam.com> wrote in message news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:
[color
=darkred]
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>[/color]

any order

Hi,
Is there any sort order if we don't use order by? for example,
select * from tablename
what is sort order? each time, I get the same records with the same order,
any mystery?As far as the I know, the order was the same as you add the data.
You can change the order bu add "order by" statement , I think you can get
whole information from BOL|||Wei
I don't think that order depends on insertion. (unless you have clustered
index on this column)
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> As far as the I know, the order was the same as you add the data.
> You can change the order bu add "order by" statement , I think you can get
> whole information from BOL
>|||Hi ,
If you have a clusted index in that table database will be ordered based on
the Index key. So incase if you have a clustered index the
"select * from tablename" will return the result set based on the clustered
index key order.
If you donot have a clusted index the result set will be reurned based on
the way you inserted.
Thanks
Hari
MCDBA
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||records are sorted in ascending order by default.
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||Sorry
> I don't think that order depends on insertion. (unless you have clustered
> index on this column)
I meant if you don't have a clustered index on the column it depends on
insertion.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...
> Wei
> I don't think that order depends on insertion. (unless you have clustered
> index on this column)
>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > As far as the I know, the order was the same as you add the data.
> > You can change the order bu add "order by" statement , I think you can
get
> > whole information from BOL
> >
> >
>|||The first reply you made me shocked :)|||Incorrect, I'm afraid. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"harsh" <harshalmistry@.hotmail.com> wrote in message
news:%23RaX%23pW6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> records are sorted in ascending order by default.
>
> "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same order,
> > any mystery?
> >
> >
>|||> If you have a clusted index in that table database will be ordered based on
> the Index key.
Not necessarily. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uPL7gkW6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> If you have a clusted index in that table database will be ordered based on
> the Index key. So incase if you have a clustered index the
> "select * from tablename" will return the result set based on the clustered
> index key order.
> If you donot have a clusted index the result set will be reurned based on
> the way you inserted.
>
> Thanks
> Hari
> MCDBA
> "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same order,
> > any mystery?
> >
> >
>|||> I meant if you don't have a clustered index on the column it depends on
> insertion.
Not correct. I'm afraid. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23qCR2tW6DHA.1936@.TK2MSFTNGP12.phx.gbl...
> Sorry
> > I don't think that order depends on insertion. (unless you have clustered
> > index on this column)
> I meant if you don't have a clustered index on the column it depends on
> insertion.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...
> > Wei
> > I don't think that order depends on insertion. (unless you have clustered
> > index on this column)
> >
> >
> >
> > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > As far as the I know, the order was the same as you add the data.
> > > You can change the order bu add "order by" statement , I think you can
> get
> > > whole information from BOL
> > >
> > >
> >
> >
>|||A table, is in the relation model, an unordered set of rows. Imagine your rows being notes on
paper-slips and you throw them into a bag. The back is shaken from time to time. Now. try to pull
the paper-slips out of this bag "in order". The term "order" doesn't make sense in the relational
model.
To be more specific, if you don't have order by, the optimizer is free to return the rows in any
order. It will pick the order it finds most cost-effective.
It doesn't have to be in the same sequence as the rows are inserted (read about IAM page etc in the
physical database chapter in Books Online). That is regardless whether you have a clustered index or
not.
It doesn't have to be in the same sequence as the clustered index. The index can be heavy
fragmented, so the optimizer can decide to instead of jumping back and forth on the disk, it will
scan the file in physical order, according to the IAM page.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John" <spam@.spam.com> wrote in message news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||Tibor
I have just finished to read the article, you are absolutely right. Sorry
for giniven incorrect information.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> A table, is in the relation model, an unordered set of rows. Imagine your
rows being notes on
> paper-slips and you throw them into a bag. The back is shaken from time to
time. Now. try to pull
> the paper-slips out of this bag "in order". The term "order" doesn't make
sense in the relational
> model.
> To be more specific, if you don't have order by, the optimizer is free to
return the rows in any
> order. It will pick the order it finds most cost-effective.
> It doesn't have to be in the same sequence as the rows are inserted (read
about IAM page etc in the
> physical database chapter in Books Online). That is regardless whether you
have a clustered index or
> not.
> It doesn't have to be in the same sequence as the clustered index. The
index can be heavy
> fragmented, so the optimizer can decide to instead of jumping back and
forth on the disk, it will
> scan the file in physical order, according to the IAM page.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same
order,
> > any mystery?
> >
> >
>|||Tibor.
Greate Poster, let me learn many thing.
I do not think you are right after you poster, but after read the
information on BOL. Yes you are right.|||>The term "order" doesn't make sense in the relational
> model.
I understand that the order and PHYSICAL placement of the data is not a
concern in the relational model but surely the RM addresses returning
ordered data? Isn't this called sort sets in the RM?
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> A table, is in the relation model, an unordered set of rows. Imagine your
rows being notes on
> paper-slips and you throw them into a bag. The back is shaken from time to
time. Now. try to pull
> the paper-slips out of this bag "in order". The term "order" doesn't make
sense in the relational
> model.
> To be more specific, if you don't have order by, the optimizer is free to
return the rows in any
> order. It will pick the order it finds most cost-effective.
> It doesn't have to be in the same sequence as the rows are inserted (read
about IAM page etc in the
> physical database chapter in Books Online). That is regardless whether you
have a clustered index or
> not.
> It doesn't have to be in the same sequence as the clustered index. The
index can be heavy
> fragmented, so the optimizer can decide to instead of jumping back and
forth on the disk, it will
> scan the file in physical order, according to the IAM page.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same
order,
> > any mystery?
> >
> >
>|||As far as I know, RM does not have this concept, as this would mean that RM defines other concepts
than relations. I might be wrong, of course and I welcome (as always :-) ) pointers etc...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eNG0m9X6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> >The term "order" doesn't make sense in the relational
> > model.
> I understand that the order and PHYSICAL placement of the data is not a
> concern in the relational model but surely the RM addresses returning
> ordered data? Isn't this called sort sets in the RM?
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> > A table, is in the relation model, an unordered set of rows. Imagine your
> rows being notes on
> > paper-slips and you throw them into a bag. The back is shaken from time to
> time. Now. try to pull
> > the paper-slips out of this bag "in order". The term "order" doesn't make
> sense in the relational
> > model.
> >
> > To be more specific, if you don't have order by, the optimizer is free to
> return the rows in any
> > order. It will pick the order it finds most cost-effective.
> >
> > It doesn't have to be in the same sequence as the rows are inserted (read
> about IAM page etc in the
> > physical database chapter in Books Online). That is regardless whether you
> have a clustered index or
> > not.
> >
> > It doesn't have to be in the same sequence as the clustered index. The
> index can be heavy
> > fragmented, so the optimizer can decide to instead of jumping back and
> forth on the disk, it will
> > scan the file in physical order, according to the IAM page.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > Is there any sort order if we don't use order by? for example,
> > >
> > > select * from tablename
> > >
> > > what is sort order? each time, I get the same records with the same
> order,
> > > any mystery?
> > >
> > >
> >
> >
>

2012年2月23日星期四

Any ideas on how to combine records into one

Here is what I have,

select id, name from rss_user

gives me this

r604738 one
r604738 two
r604738 three
r604739 one
r604739 two
r604739 three
r604739 four

I would like to be able to pipe this into a @.temp table so it looks like this,

r604738 one,two,three
r604739 one,two,three,four

Any ideas, so far I am drawing a blank.Sure, the search feature is your friend! Check out this thread (http://www.dbforums.com/t989683.html) from a couple of days ago.

This is MUCH better if done on the client, but it can also be done on the server too.

-PatP

Any idea why OpenRowSet to open Excel file doesn''t work well in SQL 2005?

Maybe it worked once, but in most time it doesn't work, query like below

select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')

I got error

OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

but the same query can run without any problem on a SQL 2000 server run on a server in the same network.

Any idea?

After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

|||

Any idea?

this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,

Any help?

|||

any suggestion?

help please

|||I've seen this exact error, but only when the path to the XLS file was wrong (SQL couldn't find the file). Suggest you try a simple path (eg. C:\TEMP\XLSFILE.XLS) first. And of course make sure you have privileges to read that folder.|||

Something is not right, it didn't work and then started to work, after restart SQL server, it doesn't work again, even I copy file to C:\ of the SQL server, it gives me the some error, well before with the same account, it worked for me when the file is on remote server.

I am using the buildin/administrators to connect to SQL server

|||

I get following error while trying to query ACCESS data, any clues?

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

|||

Ashish,

I'm getting the same error you did. Did you ever resolve the problem?

In my case, I've built and deployed a SSRS 2005 report that's based on a view that connects to a linked server (an Access .MDB). I used my domain account to build and deploy the report, and if I try to view it locally (on the SSRS server -- using http://localhost/reports), it works perfectly without any errors.

But if I log in with my domain account from another PC or server, I get this error in the report server:

An error has occurred during report processing.

Query execution failed for data set 'FinanceLinkedServer'.

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".

My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.

I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.

Thanks!

|||

I was having a similar issue, and thanks to Systernals FileMon I found that the sqlserver process tries to access the %tmp% directory of the profile of the account the process is running under as the Windows Authentication credentials of the user. Don't think I stated that very well, so I'll give my example:

My sqlserver was running under an account called 'DOMAIN\SqlServiceAct'. The %tmp% directory of this account was the 'C:\Winnt\Profiles\SqlServiceAct\Local Settings\Temp' folder. By changing the setting of this folder to allow both Read and Write permissions to the built-in Domain Users group, all is well.

|||

Thanks, davery921. I tried that, but it didn't resolve the error.

Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?

|||First, to answer your question, the answer is yes, I've gotten it to work.

Next.... I need help too...

I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:

DECLARE @.RC int

DECLARE @.STARTDATE datetime

DECLARE @.ENDDATE datetime

-- TODO: Set parameter values here.

EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]

'07/01/2007','07/31/2007'

On my machine through SSMS, I get this error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my test server through Remote desktop, to the test database, it runs just fine!

Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.

The only difference is that my production machine that is pointed to the production database is interacting with Oracle 9i in the SP while my test database SP is interacting with Oracle 10g. They all point to the same access database on the same server, competing for the same resource. We are just testing 10g.

Any ideas?

Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?

Maybe it worked once, but in most time it doesn't work, query like below

select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')

I got error

but the same query can run without any problem on a SQL 2000 server run on a server in the same network.

Any idea?

After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get

|||

Any idea?

this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me

it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,

Any help?

|||

any suggestion?

help please

I am using the buildin/administrators to connect to SQL server

|||

I get following error while trying to query ACCESS data, any clues?

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

|||

Ashish,

I'm getting the same error you did. Did you ever resolve the problem?

But if I log in with my domain account from another PC or server, I get this error in the report server:

An error has occurred during report processing.

Query execution failed for data set 'FinanceLinkedServer'.

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".

My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.

I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.

Thanks!

|||

Thanks, davery921. I tried that, but it didn't resolve the error.

Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?

|||First, to answer your question, the answer is yes, I've gotten it to work.

Next.... I need help too...

I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:

DECLARE @.RC int

DECLARE @.STARTDATE datetime

DECLARE @.ENDDATE datetime

-- TODO: Set parameter values here.

EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]

'07/01/2007','07/31/2007'

On my machine through SSMS, I get this error:

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my test server through Remote desktop, to the test database, it runs just fine!

Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.

Any ideas?

Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?

Maybe it worked once, but in most time it doesn't work, query like below

select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')

I got error

but the same query can run without any problem on a SQL 2000 server run on a server in the same network.

Any idea?

After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get

|||

Any idea?

this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me

it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,

Any help?

|||

any suggestion?

help please

I am using the buildin/administrators to connect to SQL server

|||

I get following error while trying to query ACCESS data, any clues?

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

|||

Ashish,

I'm getting the same error you did. Did you ever resolve the problem?

But if I log in with my domain account from another PC or server, I get this error in the report server:

An error has occurred during report processing.

Query execution failed for data set 'FinanceLinkedServer'.

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".

My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.

I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.

Thanks!

|||

Thanks, davery921. I tried that, but it didn't resolve the error.

Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?

|||First, to answer your question, the answer is yes, I've gotten it to work.

Next.... I need help too...

I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:

DECLARE @.RC int

DECLARE @.STARTDATE datetime

DECLARE @.ENDDATE datetime

-- TODO: Set parameter values here.

EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]

'07/01/2007','07/31/2007'

On my machine through SSMS, I get this error:

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my test server through Remote desktop, to the test database, it runs just fine!

Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.

Any ideas?

Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?

Maybe it worked once, but in most time it doesn't work, query like below

select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')

I got error

but the same query can run without any problem on a SQL 2000 server run on a server in the same network.

Any idea?

After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get

|||

Any idea?

this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me

it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,

Any help?

|||

any suggestion?

help please

I am using the buildin/administrators to connect to SQL server

|||

I get following error while trying to query ACCESS data, any clues?

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

|||

Ashish,

I'm getting the same error you did. Did you ever resolve the problem?

But if I log in with my domain account from another PC or server, I get this error in the report server:

An error has occurred during report processing.

Query execution failed for data set 'FinanceLinkedServer'.

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".

My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.

I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.

Thanks!

|||

Thanks, davery921. I tried that, but it didn't resolve the error.

Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?

|||First, to answer your question, the answer is yes, I've gotten it to work.

Next.... I need help too...

I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:

DECLARE @.RC int

DECLARE @.STARTDATE datetime

DECLARE @.ENDDATE datetime

-- TODO: Set parameter values here.

EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]

'07/01/2007','07/31/2007'

On my machine through SSMS, I get this error:

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my test server through Remote desktop, to the test database, it runs just fine!

Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.

Any ideas?

2012年2月18日星期六

any help with my select ?

Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
...
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek
Don't multi-post. See my answer in .programming.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq0j$25cf$1@.news2.ipartners.pl...
Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
...
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek

any help with my select ?

Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
KrzysiekDon't multi-post. See my answer in .programming.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq0j$25cf$1@.news2.ipartners.pl...
Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek

any help with my select ?

Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
KrzysiekDon't multi-post. See my answer in .programming.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq0j$25cf$1@.news2.ipartners.pl...
Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek

2012年2月16日星期四

Any equivalent for NEXT_DAY fn of Oracle

Haii Friends,
Is there any equivalent for Oracle's NEXT_DAY function in sqlserver.I need it for the following query...
SELECT NEXT_DAY( SYSDATE ,'THURSDAY') FROM DUAL;
ur Help in this regard is really appreciated...
Regards,
VickyI'm thinking that this query returns you the upcoming Thursday? There's not an immediate function that will easily do this for you, however, either a proc or function can achieve this easily. This is assuming you always want the upcoming Thursday

CREATE PROCEDURE dbo.getNextThursday AS

DECLARE @.CurrentDate AS DATETIME
DECLARE @.DayOfWeek AS TINYINT

SELECT @.DayOfWeek = DATEPART(WEEKDAY, GETDATE())

IF (@.DayOfWeek < 5) -- Not equal to Thursday
BEGIN
SELECT @.CurrentDate = (GETDATE() + (5-@.DayOfWeek))
END
ELSE
BEGIN
SELECT @.CurrentDate = (GETDATE() + (7-(@.DayOfWeek-5)))
END

SELECT @.CurrentDate

GO

2012年2月13日星期一

Any alternative way to retreive data

Hi: Guys
Given the table below I want a select query that returns the AccountRepID with the largest single sale for each RegionID. In the event of a tie choose any single top AccountRepID to return.

CREATE TABLE [Sales] (
[SalesID] [int] IDENTITY (1, 1) NOT NULL ,
RegionID] [int],
[AccountRepID] [int],
[SalesAmount] [money]
)

If the data were
salesid,regionid,accountrepid,salesamount
1,101,31,$50
2,101,32,$25
3,102,31,$25
4,102,32,$25
5,102,31,$15

The query should return
regionid,accountrepid
101,31
102,31 or 102,32

Is there another way to get the data other than the following query:

select regionID,accountrepid FROM Sales
where salesamount in
(select max(salesamount) FROM Sales group by regionid)

ThanksI don't think that will actually get you what you need. What if one region has the exact same salesamount as another region, but it's not the max for that region. You've then returned duplicate rows for that region.

Try this:

SELECT sa1.regionID, MAX(sa1.accountrepid)
FROM
Sales sa1
INNER JOIN (
SELECT regionID, MAX(salesamount) AS salesamount
FROM Sales) sa2 ON sa1.regionID = sa2.regionID
AND sa1.salesamount = sa2.salesamount|||select a.RegionId,a.AccountRepId,a.SalesAmount From Sales a
Inner join
(select RegionId,Max(salesAmount) as SalesAmount from Sales group by RegionId) b
on a.RegionId = b.RegionId and a.SalesAmount = b.SalesAmount

订阅：博文 (Atom)

2012年3月22日星期四

Anybody knows the syntax? Thanks

Any way to use params in a SELECT...IN query?

2012年3月20日星期二

Any way to track if a person does a select on a table?

Any way to track if a person does a select on a table?

Any way to track if a person does a select on a table?

2012年3月11日星期日

Any suggestions on how to revise code to accomodate order by issue

Any structure on Sql Server like WITH ... SELECT structure on DB2

2012年3月8日星期四

Any SELECT Statement Gurus Out There?

2012年3月6日星期二

any order

any order

2012年2月23日星期四

Any ideas on how to combine records into one

Any idea why OpenRowSet to open Excel file doesn''t work well in SQL 2005?

Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?

Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?

Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?

2012年2月18日星期六

any help with my select ?

any help with my select ?

any help with my select ?

2012年2月16日星期四

Any equivalent for NEXT_DAY fn of Oracle

2012年2月13日星期一

Any alternative way to retreive data

Sql ANSI Padding

Blog Archive

About Me