Query question

Hi,
I am logging process data every 5 min, every record has a datetime field in
addition to the process data one of the requierements of the application is
to avg the data every 3 hours. is there any simple way to accomplish this
with a query or will it be better create a job that runs every 3 hrs and
average the last 3 hrs and insert the results into a new table.
ThanksJulio
If you need to agregate the data every three hours , so yes , create a job
"Julio Delgado" <jdelgado89@.hotmail.com> wrote in message
news:OWLI7ip4GHA.3400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am logging process data every 5 min, every record has a datetime field
> in addition to the process data one of the requierements of the
> application is to avg the data every 3 hours. is there any simple way to
> accomplish this with a query or will it be better create a job that runs
> every 3 hrs and average the last 3 hrs and insert the results into a new
> table.
> Thanks
>

Query question

Hi,
I am logging process data every 5 min, every record has a datetime field in
addition to the process data one of the requierements of the application is
to avg the data every 3 hours. is there any simple way to accomplish this
with a query or will it be better create a job that runs every 3 hrs and
average the last 3 hrs and insert the results into a new table.
ThanksJulio
If you need to agregate the data every three hours , so yes , create a job
"Julio Delgado" <jdelgado89@.hotmail.com> wrote in message
news:OWLI7ip4GHA.3400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am logging process data every 5 min, every record has a datetime field
> in addition to the process data one of the requierements of the
> application is to avg the data every 3 hours. is there any simple way to
> accomplish this with a query or will it be better create a job that runs
> every 3 hrs and average the last 3 hrs and insert the results into a new
> table.
> Thanks
>

query question

I have a table where one of the fields is named SHIPPER
in the field, it will have the names of various shippers. ShipperA,
ShipperB ShipperC and ShipperD as an example
I need to make a query where it will return all the records where the
SHIPPER is ShipperA, ShipperB, ShipperD
All help would be great!
ThanksSELECT *
FROM YourTable
WHERE Shipper IN ('ShipperA', 'ShipperB', ShipperD')
?
If this doesn't work, please post DDL and sample data (
http://www.aspfaq.com/etiquette.asp?id=5006 )
"johnfli" <john@.here.com> wrote in message
news:OTiQIf8rEHA.3588@.tk2msftngp13.phx.gbl...
> I have a table where one of the fields is named SHIPPER
> in the field, it will have the names of various shippers. ShipperA,
> ShipperB ShipperC and ShipperD as an example
> I need to make a query where it will return all the records where the
> SHIPPER is ShipperA, ShipperB, ShipperD
> All help would be great!
> Thanks
>|||Thank you for the quick reply.
I wasn't able to get it to work, I am sure it has something to do with the
wildcards.
I modified your line to read:
WHERE Shipper IN ('%ShipperA%', '%ShipperB%', '%ShipperD%')
as for some reason, the names were inputted with partial address
thanks again if you're able to fix this.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23i6goi8rEHA.3848@.TK2MSFTNGP14.phx.gbl...
> SELECT *
> FROM YourTable
> WHERE Shipper IN ('ShipperA', 'ShipperB', ShipperD')
> ?
> If this doesn't work, please post DDL and sample data (
> http://www.aspfaq.com/etiquette.asp?id=5006 )
>
> "johnfli" <john@.here.com> wrote in message
> news:OTiQIf8rEHA.3588@.tk2msftngp13.phx.gbl...
>|||Sorry, IN does not support wild cards. You'll have to use LIKE with OR:
WHERE Shipper LIKE '%ShipperA%'
OR Shipper LIKE '%ShipperB%'
OR Shipper LIKE '%ShipperD%'
"johnfli" <john@.here.com> wrote in message
news:uz2VMo8rEHA.1644@.tk2msftngp13.phx.gbl...
> Thank you for the quick reply.
> I wasn't able to get it to work, I am sure it has something to do with the
> wildcards.
> I modified your line to read:
> WHERE Shipper IN ('%ShipperA%', '%ShipperB%', '%ShipperD%')
> as for some reason, the names were inputted with partial address
> thanks again if you're able to fix this.
>|||perfect!
thank you
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OWbQUr8rEHA.556@.tk2msftngp13.phx.gbl...
> Sorry, IN does not support wild cards. You'll have to use LIKE with OR:
> WHERE Shipper LIKE '%ShipperA%'
> OR Shipper LIKE '%ShipperB%'
> OR Shipper LIKE '%ShipperD%'
>
> "johnfli" <john@.here.com> wrote in message
> news:uz2VMo8rEHA.1644@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
>

Query problem splitting field into two comumns

Hi I'm new to reporting services. I'm desinging a query for a report.
I have one filed "ship_yw" that contains year and week numbers, for example,
200701, 200702. I want to display the year in one column and the week in
another. I'm connected to a Progress database via ODBC. I have accomplished
this with no problem in Access using the following code:
SELECT LEFT([Ship_yw],4), RIGHT([Ship_yw],2)
This will not work in Query Builder or if I manually type it in the string
window.
I have also tried:
SELECT
LEFT(Ship__yw,4),
RIGHT(RTRIM(Ship__yw),2)
FROM
oe_head
SELECT
SUBSTRING(Ship__yw,1,4),
SUBSTRING(Ship__yw,5,2)
FROM
oe_head
Query Builder puts unwanted ' around the field name and causes the query to
return "ship" in every row in one column and "yw" in every row of the second.
Any suggestions?Try giving names for the fildes , Use AS
"Twaterman" wrote:
> Hi I'm new to reporting services. I'm desinging a query for a report.
> I have one filed "ship_yw" that contains year and week numbers, for example,
> 200701, 200702. I want to display the year in one column and the week in
> another. I'm connected to a Progress database via ODBC. I have accomplished
> this with no problem in Access using the following code:
> SELECT LEFT([Ship_yw],4), RIGHT([Ship_yw],2)
> This will not work in Query Builder or if I manually type it in the string
> window.
> I have also tried:
> SELECT
> LEFT(Ship__yw,4),
> RIGHT(RTRIM(Ship__yw),2)
> FROM
> oe_head
> SELECT
> SUBSTRING(Ship__yw,1,4),
> SUBSTRING(Ship__yw,5,2)
> FROM
> oe_head
> Query Builder puts unwanted ' around the field name and causes the query to
> return "ship" in every row in one column and "yw" in every row of the second.
> Any suggestions?
>
>

query problem

i have a msde 2000 database, and one of the field are filled by datetime with the format "yyyy-mm-dd hh:mm:ss".
I make a query like this:
"select * from mytable where myfield like '%2006-06-15%'"
but i didn't get any record for the result and i have make it sure that there are lot of records with that value in myfield. The main problem is the char "-" that separate the date value, becouse if i make a query :
"select * from mytable" then i get all of my records.
Is there any suggest to solf my problem?why u want a like clause on a date time field.

Instead try myfield >= '2006-06-15' and myfield < '2006-06-16'|||Is your date field a datetime datatype, or is it a string?|||Your date is actually formatted more like 0x000097E2008BE35B even though SQL Server displays it as 2006-06-16 13:29 when it returns a value to the client machine. You can use string patterns on it, and SQL Server will happily convert for you, but it isn't efficient and worse yet it isn't always predictable.

The suggested syntax that Ronin gave is both efficient and predictable.

-PatP|||0x000097E2008BE35B?

Is is that late already? I missed my 0x000097E200A4CB80 meeting!

Query problem

Hello Friends

I want to convert my float field (named Qty) to a float value with precision 2.
e.g. my Qty field hold 15.10000000000000001 and I wish to convert it into 15.10
I'm using SQL server 2000.

How could I write the query for this conversion.

Plz help me

Thanks

Declare @.float float
SET @.float = 2515.11111000000001
-- the optional third parameter of convert if 1 or 0
-- when applied to smallmoney or money will have only 2 numbers on right side of decimal
-- you can also add commas i.e. 12000.393939
-- if 1 would be 12,000.39
-- if 0 would be 12000.39
SELECT CONVERT(varchar, CONVERT(smallmoney, @.float), 1)

|||you can also do it in the design itself and not worry abt converting everytime you use it...set the column type to decimal and set the precision to 2. that way you dont have to worry abt the conversion everytime and also not lose any values when you do a reverse conversion...

hth|||Acutally I want to store the original value in the table but when i need it somewhere else then it has to be in such required conversion.

I knew ur solution but due to my requirement I'm expecting the proper query for that.

If u have the solution then plz reply me soon.

Thanks|||you can say

SELECT CONVERT(varchar, CONVERT(smallmoney, <column name>), 1) as [<column name>] from the table.

this will display the data without effecting the data base.

Query problem

I want to restrict the results of a query only where the field 'email' has
something in it. I had a record with data in 'email' and deleted it and my
query still returns the record even thoug part of my statements reads:
Select * from Users
Where email is NOT NULL OR email <> ''
Even though I deleted what was in the 'email' field I still get the
recordset and the email field is blank.
Any clues ?
AleksHow can we reproduce the problem?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Aleks" wrote:

> I want to restrict the results of a query only where the field 'email' has
> something in it. I had a record with data in 'email' and deleted it and my
> query still returns the record even thoug part of my statements reads:
> Select * from Users
> Where email is NOT NULL OR email <> ''
> Even though I deleted what was in the 'email' field I still get the
> recordset and the email field is blank.
> Any clues ?
> Aleks
>
>|||Aleks wrote:
> I want to restrict the results of a query only where the field
> 'email' has something in it. I had a record with data in 'email' and
> deleted it and my query still returns the record even thoug part of
> my statements reads:
> Select * from Users
> Where email is NOT NULL OR email <> ''
> Even though I deleted what was in the 'email' field I still get the
> recordset and the email field is blank.
>
Change "OR" to "AND".
When you deleted the contents, you likely did not set the field to Null, so
"email is NOT NULL" evaluates to True, resulting in the OR expression
evaluating to True.
Better yet, do something like this:
Where email > CHAR(1)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Query Problem

I am using the query shown below but I am not getting the result I expect. I
want to get "Elected" only when the count of LastName field is 75% or more
of the count of when the "CG" field is equal to "Y". What did I mess up
here?
============================ =iif(Count(Fields!LastName.Value) < Count(( Fields!CG.Value)="Y")*0.75, "Not
Elected", "Elected")
============================================Hi Wayne
Count(( Fields!CG.Value)="Y"), i think you can't do this,the
architecture of the RS is such a way that it calculates all the
aggregations and operations first and then will come to the
conditional Expressions next,
here it is messed up with Expressions first .where you are unable to
get the result
As an alternate you can do this
1) Create an extra column Extra1 by going to dataset-->Fields
it must be a calculated field (not dataset field), give like this
IIF( Fields!CG.Value="Y",1,0)
2)Add another column extra2 with sum(Extra1) .
2)Go to the layout ,add extra column to the right of the column
wherever you want,give the expression like this
=iif(Count(Fields!LastName.Value) <First(Extra2), "Not
> Elected", "Elected")
i am not that much confident that it will work, but readjusting all
this expressions should work
Regards,
Raj Deep.A
Wayne Wengert wrote:
> I am using the query shown below but I am not getting the result I expect. I
> want to get "Elected" only when the count of LastName field is 75% or more
> of the count of when the "CG" field is equal to "Y". What did I mess up
> here?
> ============================> =iif(Count(Fields!LastName.Value) < Count(( Fields!CG.Value)="Y")*0.75, "Not
> Elected", "Elected")
> ============================================|||Thanks for the suggestions. I'll give that a try.
Wayne
"RajDeep" <rajalapati@.gmail.com> wrote in message
news:1160558814.067206.178500@.h48g2000cwc.googlegroups.com...
> Hi Wayne
> Count(( Fields!CG.Value)="Y"), i think you can't do this,the
> architecture of the RS is such a way that it calculates all the
> aggregations and operations first and then will come to the
> conditional Expressions next,
> here it is messed up with Expressions first .where you are unable to
> get the result
> As an alternate you can do this
> 1) Create an extra column Extra1 by going to dataset-->Fields
> it must be a calculated field (not dataset field), give like this
> IIF( Fields!CG.Value="Y",1,0)
> 2)Add another column extra2 with sum(Extra1) .
> 2)Go to the layout ,add extra column to the right of the column
> wherever you want,give the expression like this
> =iif(Count(Fields!LastName.Value) <First(Extra2), "Not
>> Elected", "Elected")
> i am not that much confident that it will work, but readjusting all
> this expressions should work
> Regards,
> Raj Deep.A
>
>
> Wayne Wengert wrote:
>> I am using the query shown below but I am not getting the result I
>> expect. I
>> want to get "Elected" only when the count of LastName field is 75% or
>> more
>> of the count of when the "CG" field is equal to "Y". What did I mess up
>> here?
>> ============================>> =iif(Count(Fields!LastName.Value) < Count(( Fields!CG.Value)="Y")*0.75,
>> "Not
>> Elected", "Elected")
>> ============================================>sql

Query problem

Hi all,

I am trying to write a query to do the following in SQL server but am
struggling:

I have a table with a int number field in it and I want to find out
the lowest unused number in the table. Unfortunately, the numbers are
not necesarily sequential (due to deletes). So I may have the
following records:

num
--
1
2
3
4
6
8
9
10

I want a query to return me the value 5 - i.e. the lowest unused int
in the table (assuming counting starts at 1). I know I could do this
using a temporary table containing a full sequence of possible numbers
(given some ceiling) using something like:
select min(num) from temptable where num not in (select num from
maintable)
This would however mean creating a temporary table which I am trying
to avoid. I could also use a cursor to itterate through the records
above to find the lowest unused number but it seems overkill.

I'm sure there must be a way of doing this using a simple (possibly
nested) query. Any ideas?

Thanks in advance

--JamesSELECT COALESCE(MIN(T1.num)+1,1)
FROM YourTable AS T1
LEFT JOIN YourTable AS T2
ON T1.num = T2.num - 1
WHERE T2.num IS NULL

--
David Portas
SQL Server MVP
--|||David,
Just what I needed - cheers - nice bit of SQL too!

Regards

--James|||SELECT MIN(F1.num + 1)
FROM Foobar AS F1
UNION
VALUE (0) -- needs to be SELECT 0 in dialect
WHERE (T1.num +1)
NOT IN (SELECT num FROM Foobar);

This will give you an answer on the high end if the sequence is
complete.sql

Query performence about nText

Is there any difference between a nText field value=' and value=NULL ?You should compare Null with is

where value='' or value IS Null

Madhivanan

Cylix wrote:

Quote:

Originally Posted by

Is there any difference between a nText field value='' and value=NULL ?

|||How about the volume?
Is NULL value also use the harddisk.|||Cylix wrote:

Quote:

Originally Posted by

How about the volume?
Is NULL value also use the harddisk.

Surely this doesn't make a significant difference. Unless you're
dealing with an embedded system, in which case surely you should
use a SQL engine specifically designed for embedded systems.|||Cylix (cylix2000@.gmail.com) writes:

Quote:

Originally Posted by

How about the volume?
Is NULL value also use the harddisk.

A text value consists of a 16-byte pointer which is stored in the row,
and then the rest of the data is stored outside the row. (Unless you
have set the table option "text in row".

The 16 byte pointer takes up space also for a NULL value. If any space
is allocated for the empty string, I will have to confess that I don't
know. You could examine this by creating two tables with ntext columns,
and fill one with NULL values and one with empty strings, and then use
sp_spaceused to see if there is any difference.

Permit me also to norice that ntext is a data type that is on its way out.
In SQL 2005 there is a new data type nvarchar(MAX) which just like ntext
permits 2GB of data, but works like regular nvarchar, and does not have
the many quirks of ntext.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Query out an xml element from a field containing xml data

'message' Field Data:
<xml>
...
<test>ABC</test>
...
</xml
How do I query out 'ABC' from this xml field data in a SQL Server?

Assume message field is in table 'Persons'What you could do, is write a user defined function. Something like

create function [dbo].[fn_GetText]
(
@.Text as XML
)
returns varchar(50)
begin
declare @.Result as varchar(50)
declare @.Count as int
declare @.Start as int

set @.Count = 1
set @.Start = 0

while @.Count <= len(@.Text)
begin
if substring(@.Text, @.Count, 6) = '<test>'
set @.Start = @.Count + 6

if substring(@.Text, @.Count, 7) = '<\test>' and @.Start <> 0
set @.Result = substring(@.Text, @.Start, @.Count - @.Start - 1)

set @.Count = @.Count + 1
end

return @.Result
end

go|||Hi Mike,

thanks for the reply.

As my comfort level is much higher with C#, I just wrote some string
parsing code and got my result :)

Next time I need to spin off a UDF, I will remember this.

Ranjith|||Ranjith (ranjithvenkatesh@.hotmail.com) writes:
> 'message' Field Data:
><xml>
> ...
> <test>ABC</test>
> ...
></xml>
> How do I query out 'ABC' from this xml field data in a SQL Server?
> Assume message field is in table 'Persons'

create table Persons (message xml NOT NULL)
go
insert Persons (message) VALUES('<xml><test>ABC</test></xml>')
go
select message.value(N'(/xml/test)[1]', 'varchar(10)')
from Persons
go
Drop table Persons

This is for SQL 2005. You did not mention which server of SQL Server
you are using. This matters a lot when XML is involved, as the XML
support in SQL 2005 is greatly extended over what is in SQL 2000,
including a new query language, XQuery which I'm using above.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

query or stored procedure to insert values into pr. key field

Hello,
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
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
--|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

query or stored procedure to insert values into pr. key field

Hello,
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)
bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
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
|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.
|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

query on primary key field

Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
Andrew
Yes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would be
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this database
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:

> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records in
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew

query on primary key field

Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
AndrewYes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would be
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this database
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:
> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records in
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew

query on primary key field

Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
AndrewYes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would b
e
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this databas
e
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:

> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records i
n
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_t
id
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew

query on date field with AM/PM designation

I need to query a table that has the date field formatted as follows:

12/1/2000 12:33:16 PM

The query basically is looking for a range between two dates.

select * from area_formulae where create_dt > to_date('12/1/2000 12:33:15','mm/dd/yyyy hh:mi:ss')
and create_dt < to_date('12/1/2000 12:33:18','mm/dd/yyyy hh:mi:ss')

This query that will work but it ignores the AM or PM designation. How would I query for a range of dates that would cover both the AM and PM ranges?Can you do this?

'mm/dd/yyyy hh:mi:ss AM/PM'

Query needs to loop?

Is this possible?
A char variable length field contains this data: (for example)
ABC,XYZ,GEF,CAB
I need to query another table on the contents of each of these names that
are separated by comma.
I need to read ABC and then query another table for ABC in field1 and pick
up some fields.
Then, I need to do the same for XYZ, etc, reading across.
How can I parse the field that's comma delim, and in one continuous select
statment pick all the data from another table? Put a looping statement into
the Select statement to read through the comma delim field until done?
Thanks,
Don
On Thu, 25 Aug 2005 14:55:34 -0700, DonSQL2222 wrote:

>Is this possible?
>A char variable length field contains this data: (for example)
>ABC,XYZ,GEF,CAB
>I need to query another table on the contents of each of these names that
>are separated by comma.
>I need to read ABC and then query another table for ABC in field1 and pick
>up some fields.
>Then, I need to do the same for XYZ, etc, reading across.
>How can I parse the field that's comma delim, and in one continuous select
>statment pick all the data from another table? Put a looping statement into
>the Select statement to read through the comma delim field until done?
>Thanks,
>Don
Hi Don,
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)