How to remove the records

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
LEFT JOIN PTList
ON
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
Order by Name

The above will select all the records from InPatient and will retrieve either the status code ('A') or blank ('') from PTList, depending on whether or not the john has marked the record as "his".

What I want to do is to return the same dataset, but WITHOUT any records belonging to john.

I can't use <>'John' because 14 others might have the silly thing as active at the same time. What I'd really like to do is

Select * from
(the above)
Where Status=Null

I know it can be done, but I don't know how. Any ideas?I am guessing you want one of the following.

Tim S

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
LEFT JOIN PTList
ON
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
WHERE PTList.Status IS NULL
Order by Name

Second Guess

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
WHERE NOT EXISTS
( SELECT * FROM PTList WHERE
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
)
Order by Name|||yup, it was the where not exists.

I'd already tried a variant on the other one, it returned nothing, but I probably set it up incorrectly.

Just tried the other way, also. Worked like a charm. I'm not positive, butg from somewhere I got the idea that the Exists works something like an IN statement? That would imply that the first way would be faster ... at least in machine terms.

Thank you

How to regenerate a stored proc (drop & create or alter procedure) ??

Hi all.
Cf my other news: How to change or alter a user defined data type?
I need to regenerate (not only "sp_recompile") a list of stored procedures.
With the "SQL Server Enterprise Manager" it's very easy: just open each
Stored proc and save it. But how to do that by transact SQL script '
Thanks.
Lilian.What purpose does "regenerating" these serve? There are 2 ways to do this
depending on what your after. One way is to use the DROP PROCEDURE xxx
command and then issue the CREATE PROCEDURE xxx ... command. The other is
to use the ALTER PROCEDURE xxx.... command. Drop will not keep any
existing permissions and ALTER will.
--
Andrew J. Kelly
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u7Os%23CHQDHA.2228@.tk2msftngp13.phx.gbl...
> Hi all.
> Cf my other news: How to change or alter a user defined data type?
> I need to regenerate (not only "sp_recompile") a list of stored
procedures.
> With the "SQL Server Enterprise Manager" it's very easy: just open each
> Stored proc and save it. But how to do that by transact SQL script '
> Thanks.
>
> Lilian.
>sql

how to reduce PAGEIOLATCH_SH waits?

Hi,
I have heavy updates and the most frequent problem is the PAGEIOLATCH_SH
wait type.
The update is blocked by himself (using the sp_who1, I see the lock)
what can I do?
there is any hint or option to test?
which counter of performance I have to follow?
thanks.
Jerome.Hi
Have a look at the answer from Santeri Voutilainen [MSFT] on:
http://groups.google.ch/group/microsoft.public.sqlserver.server/browse_thread/thread/b8e9970a1feb49f6/b8154738537bacaf?lnk=st&q=spid+Voutilainen&rnum=1&hl=en#b8154738537bacaf
In effect, your disk subsystem is not coping with what SQL server is
throwing at it.
Have a look at disk times, queue lengths and the like to identify the IO
problem.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jéjé" wrote:
> Hi,
> I have heavy updates and the most frequent problem is the PAGEIOLATCH_SH
> wait type.
> The update is blocked by himself (using the sp_who1, I see the lock)
> what can I do?
> there is any hint or option to test?
> which counter of performance I have to follow?
> thanks.
> Jerome.
>
>|||unfortunatly my HD subsystem is bad and I can't do anything.
I'm on a SAN and the results are really bad and we don't know why.
The guys in charge of the SAN work on it, but I don't see any improvement.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:D5AEA1D4-D2F8-47D5-83A0-FF224C74137B@.microsoft.com...
> Hi
> Have a look at the answer from Santeri Voutilainen [MSFT] on:
> http://groups.google.ch/group/microsoft.public.sqlserver.server/browse_thread/thread/b8e9970a1feb49f6/b8154738537bacaf?lnk=st&q=spid+Voutilainen&rnum=1&hl=en#b8154738537bacaf
> In effect, your disk subsystem is not coping with what SQL server is
> throwing at it.
> Have a look at disk times, queue lengths and the like to identify the IO
> problem.
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Jéjé" wrote:
>> Hi,
>> I have heavy updates and the most frequent problem is the PAGEIOLATCH_SH
>> wait type.
>> The update is blocked by himself (using the sp_who1, I see the lock)
>> what can I do?
>> there is any hint or option to test?
>> which counter of performance I have to follow?
>> thanks.
>> Jerome.
>>

how to reduce PAGEIOLATCH_SH waits?

Hi,
I have heavy updates and the most frequent problem is the PAGEIOLATCH_SH
wait type.
The update is blocked by himself (using the sp_who1, I see the lock)
what can I do?
there is any hint or option to test?
which counter of performance I have to follow?
thanks.
Jerome.
Hi
Have a look at the answer from Santeri Voutilainen [MSFT] on:
http://groups.google.ch/group/micros...154738537bacaf
In effect, your disk subsystem is not coping with what SQL server is
throwing at it.
Have a look at disk times, queue lengths and the like to identify the IO
problem.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jéjé" wrote:

> Hi,
> I have heavy updates and the most frequent problem is the PAGEIOLATCH_SH
> wait type.
> The update is blocked by himself (using the sp_who1, I see the lock)
> what can I do?
> there is any hint or option to test?
> which counter of performance I have to follow?
> thanks.
> Jerome.
>
>
|||unfortunatly my HD subsystem is bad and I can't do anything.
I'm on a SAN and the results are really bad and we don't know why.
The guys in charge of the SAN work on it, but I don't see any improvement.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:D5AEA1D4-D2F8-47D5-83A0-FF224C74137B@.microsoft.com...[vbcol=seagreen]
> Hi
> Have a look at the answer from Santeri Voutilainen [MSFT] on:
> http://groups.google.ch/group/micros...154738537bacaf
> In effect, your disk subsystem is not coping with what SQL server is
> throwing at it.
> Have a look at disk times, queue lengths and the like to identify the IO
> problem.
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Jj" wrote:

how to reduce PAGEIOLATCH_SH waits?

Hi,
I have heavy updates and the most frequent problem is the PAGEIOLATCH_SH
wait type.
The update is blocked by himself (using the sp_who1, I see the lock)
what can I do?
there is any hint or option to test?
which counter of performance I have to follow?
thanks.
Jerome.Hi
Have a look at the answer from Santeri Voutilainen [MSFT] on:
38537bacaf" target="_blank">http://groups.google.ch/group/micro... />
38537bacaf
In effect, your disk subsystem is not coping with what SQL server is
throwing at it.
Have a look at disk times, queue lengths and the like to identify the IO
problem.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jéjé" wrote:

> Hi,
> I have heavy updates and the most frequent problem is the PAGEIOLATCH_SH
> wait type.
> The update is blocked by himself (using the sp_who1, I see the lock)
> what can I do?
> there is any hint or option to test?
> which counter of performance I have to follow?
> thanks.
> Jerome.
>
>|||unfortunatly my HD subsystem is bad and I can't do anything.
I'm on a SAN and the results are really bad and we don't know why.
The guys in charge of the SAN work on it, but I don't see any improvement.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:D5AEA1D4-D2F8-47D5-83A0-FF224C74137B@.microsoft.com...[vbcol=seagreen]
> Hi
> Have a look at the answer from Santeri Voutilainen [MSFT] on:
> 4738537bacaf" target="_blank">http://groups.google.ch/group/micro...>
4738537bacaf
> In effect, your disk subsystem is not coping with what SQL server is
> throwing at it.
> Have a look at disk times, queue lengths and the like to identify the IO
> problem.
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Jj" wrote:
>sql

How to query xml data in column type xml

How can I query xml data in column type xml using an xml schema from the same table row in the xmlschema column and return values(not xml) to insert into a seperate table

XML Data Table

C1 =RecordID (int)

C2 =XMLData (xml)

C3 = XMLSchema(xml)

How can I query the XML data using the xmlshema column

and return values(not xml) to insert into table 2

Table 2

C1 = RecordID

C2 = User

C3 = date

thanks

I think you are looking for the XQuery "nodes()" function if I understand you correctly. It sounds like you want to reach into the column that is storing the schema, extract scalar values from that schema and store it in a relational table. If this is what you are trying to do, then the nodes() function should work for you.

Here is an example using the nodes() function on the xml data type.

http://msdn2.microsoft.com/en-us/ms188282.aspx

Once you get the data into relational form, you can CURSOR over it and call your INSERT statement with parameters, use INSERT INTO ... SELECT, or generate dynamic SQL.

How to query in same 2 table

my table look like this

ID | Name | Weight | Type
--
1 | A | 5 | out
2 | A | 4 | in
3 | B | 10 | out

i want to query data if same Name have Type = in
Show Name and weight of Type = in
if it have Type = out and don't have Type = in (same Name)
Show Name and Weight of Type = out

like this:

Name | Weight
A | 4
B | NULL

Here it is,

Create Table #data (

[ID] Varchar(100) ,

[Name] Varchar(100) ,

[Weight] int ,

[Type] Varchar(100)

);

Insert Into #data Values('1','A','5','out');

Insert Into #data Values('2','A','4','in');

Insert Into #data Values('3','B','10','out');

select [Out].[Name],[In].[Weight] from #Data [out]

Left Outer Join #Data [in] on [Out].Name = [In].Name and [In].[Type]='in'

Where

[Out].[Type]='Out'

How to query data type of XML?

I want to query XML datas.

I have one table T.this T has two columns,id int , datasXML xml.

the datasXML has some XML datas, there into one data is:

<root>
<item val="true"></item>
<others ... />
</root>

Now,I want to query this table's datasXML column,and root/item value is true.

How to design this SQL string?

SELECT * FROM T WHERE datasXML = "<root>.." -- or other?

Please give me some samples.thanks!

Ray Lynn

In SQL2005, XQuery is great for this. There are several good articles on MSDN, for example:

http://msdn.microsoft.com/library/en-us/dnsql90/html/sql2k5_xqueryintro.asp?frame=true

|||Thank you very much!!!!!!!!!!!!!!

How to query by keyword for image field.

If the data type of field is "varchar",we can use "like" to query if it has
some substring.
Such as "where custom.valuevariant like '%Verizon%' ", it will query out all
records that contains string "Verizon".
But how to do when data type of field custom.valuevariant is "image"?
ThanksIt would depend what type of data you've stored in the image column,
but full-text search supports querying text and Office (Word etc.
documents) data in an image column - see "Filtering Supported File
Types" in Books Online.

Simon|||Thank you very much.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1122017552.084995.312000@.f14g2000cwb.googlegr oups.com...
> It would depend what type of data you've stored in the image column,
> but full-text search supports querying text and Office (Word etc.
> documents) data in an image column - see "Filtering Supported File
> Types" in Books Online.
> Simon