Query works in QA, but NOT in SQL Server stored proc?!

Ok...below is a simple query that inserts some records into a temp
table then updates another table using the temp table. It works great
in Query Analyzer, but refuses to save in SQL Servers' stored procedure
area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
I'm banging my head against a wall here, please help! I've tried
placing single and double quotes around fvd_count to no avail...
CREATE PROCEDURE [Update_Counts]
AS
--counts the number of times that distinct doc/poe combo exists
SELECT
doc,
poe,
COUNT(equipment) AS fvd_count <<<<<--ERROR
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND
a.poe = b.poe
GOAre you creating the sp in EM?. Try creating the sp from SQL Query Analyzer.
AMB
"roy.anderson@.gmail.com" wrote:

> Ok...below is a simple query that inserts some records into a temp
> table then updates another table using the temp table. It works great
> in Query Analyzer, but refuses to save in SQL Servers' stored procedure
> area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
> I'm banging my head against a wall here, please help! I've tried
> placing single and double quotes around fvd_count to no avail...
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exists
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count <<<<<--ERROR
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = b.fvd_count
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>|||Did you cut/paste the error message? If so, then you have a typo somewhere
in your code because the error message references field 'fvd_cnt', while the
sp that you show us names it 'fvd_count'.
If that is just a typo in your post...then...another thought is to use
[square brackets] around the field name. It shouldn't be necessary in this
case, but worth a try.
<roy.anderson@.gmail.com> wrote in message
news:1107276839.749893.138630@.c13g2000cwb.googlegroups.com...
> Ok...below is a simple query that inserts some records into a temp
> table then updates another table using the temp table. It works great
> in Query Analyzer, but refuses to save in SQL Servers' stored procedure
> area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
> I'm banging my head against a wall here, please help! I've tried
> placing single and double quotes around fvd_count to no avail...
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exists
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count <<<<<--ERROR
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = b.fvd_count
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>|||I dont think this is the full sproc, i have just tested this in QA & EM
without any error
USE Northwind
GO
CREATE TABLE firstvd (doc varchar(10), poe varchar(10), equipment varchar(10
))
CREATE TABLE lla (doc varchar(10), poe varchar(10), fvd_count int)
GO
CREATE PROCEDURE [Update_Counts]
AS
SELECT doc, poe, COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND a.poe = b.poe
GO
EXEC Update_Counts
GO
DROP TABLE firstvd
DROP TABLE lla
DROP PROCEDURE [Update_Counts]
GO
Instead of using a temp table why not do it in the query
UPDATE a
SET a.fvd_count = (SELECT COUNT(equipment)
FROM firstvd b
WHERE b.doc = a.doc AND b.poe = a.doc)
FROM lla a, firstvd
Andy
"CPK" wrote:

> Did you cut/paste the error message? If so, then you have a typo somewher
e
> in your code because the error message references field 'fvd_cnt', while t
he
> sp that you show us names it 'fvd_count'.
> If that is just a typo in your post...then...another thought is to use
> [square brackets] around the field name. It shouldn't be necessary in thi
s
> case, but worth a try.
> <roy.anderson@.gmail.com> wrote in message
> news:1107276839.749893.138630@.c13g2000cwb.googlegroups.com...
>
>|||Hey all, thanks for the input. I tried using QA to load it in and it
works like that... the first time... but when I call it after that it
ends up producing nothing (called from my asp.net page) or erroring out
in QA or EM.
Tried the square brackets, no go (and yes, it was a typo on my part).
I'll try your query idea next Andy, I don't know what to say regarding
your experiment except to say it just doesn't work in my EM. I did
clarify where the error occurs though. It happens at b.fvd_count below:
CREATE PROCEDURE [Update_Counts]
AS
--counts the number of times that distinct doc/poe combo exi=ADsts
SELECT
doc,
poe,
COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count =3D >>>>>>> b.fvd_count <<<<<<--ERROR HERE
FROM #FVD_Temp b, lla a
WHERE a.doc =3D b.doc AND
a=2Epoe =3D b.poe
GO=20
****************************************
***********************|||Roy
Just try this in QA first, forget about EM its a GUI and should be just
treated that way, i spend 90% of my time using QA
CREATE PROCEDURE [Update_Counts]
AS
SELECT doc, poe, COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND a.poe = b.poe
GO
Oh just one more thing does this field "fvd_count" exist in table lla as we
know it exists in the temp table #FVD_Temp
Andy
"roy.anderson@.gmail.com" wrote:

> Hey all, thanks for the input. I tried using QA to load it in and it
> works like that... the first time... but when I call it after that it
> ends up producing nothing (called from my asp.net page) or erroring out
> in QA or EM.
> Tried the square brackets, no go (and yes, it was a typo on my part).
> I'll try your query idea next Andy, I don't know what to say regarding
> your experiment except to say it just doesn't work in my EM. I did
> clarify where the error occurs though. It happens at b.fvd_count below:
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exi_sts
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = >>>>>>> b.fvd_count <<<<<<--ERROR HERE
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>
> ****************************************
***********************
>|||Yes, the create proc above works in QA and yes, fvd_count exists in
lla...|||Wild guess: Try adding SET NOCOUNT ON in the beginning of your proc code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<roy.anderson@.gmail.com> wrote in message
news:1107286986.218999.296680@.f14g2000cwb.googlegroups.com...
> Yes, the create proc above works in QA and yes, fvd_count exists in
> lla...
>|||Roy
have you sorted this out.
If the CREATE PROC statement returned "The command completed successfully"
then the procedure has been created. But does it execute & do what you
expect'
Andy
"roy.anderson@.gmail.com" wrote:

> Yes, the create proc above works in QA and yes, fvd_count exists in
> lla...
>|||Yes, the query proc works! Thanks much. Additonally, the suggestion to
create it in QA (the original stored proc) was accurate. The SP works
when called from my asp.net page. Even though both ways work (outside
of EM), I'm sticking with the query method because it seems less cpu
intensive.
The weird thing is still that the original SP won't save and the syntax
check fails when I open it (the original SP) in EM. But no matter, at
least I have a working now process now. Thanks everyone for all the
great suggestions!

Query with index?

Hi,

I have a table, when I execute query I want to display an index of retrieved records.

Assume table has data like:

id name phone
3 Alan1 5487411
5 Alan2 5487412
9 Alan3 5487413
10 Alan4 5487414
11 Alan5 5487415
12 Alan6 5487416

Select * from table where phone > 5487412 AND phone < 5487415

So result must be like this

index id name phone
1 9 Alan3 5487413
2 10 Alan4 5487414

Who can I get this result by SQL query?

I am not sure I understand your question; are you looking for a row number for each row returned? Something like:

declare @.mockup table
( id tinyint primary key, -- Needs to be changed
[name] varchar(7), -- Needs to be changed
phone varchar(7) -- Needs to be changed
)

insert into @.mockup values (3, 'Alan1', '5487411')
insert into @.mockup values (5, 'Alan2', '5487412')
insert into @.mockup values (9, 'Alan3', '5487413')
insert into @.mockup values (10, 'Alan4', '5487414')
insert into @.mockup values (11, 'Alan5', '5487415')
insert into @.mockup values (12, 'Alan6', '5487416')

select row_number ()
over ( order by id )
as [index],
id,
[name],
phone
from @.mockup
where phone > 5487412
and phone < 5487415

-- index id name phone
-- - - - -
-- 1 9 Alan3 5487413
-- 2 10 Alan4 5487414

Query with date question

I need to return all records in which the date 'lastmodified' +
'responsible' which is a number (of days) is the same as today's date.
I have the following condition in my sql but even though it should return
some records it doesnt:
WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
getdate())
Activities.responsible = Its the number of days being added
Activities.lasmodified = Its a date say (01/01/2005)
If the SUM of both is todays date the recordset should be returned, I don't
know if this has to do with the fact that seconds and minutes might be
involved ?
Any help is appreciated.
AleksThis should do it:
WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
AND GETDATE()
... Unfortunately, I believe this will force a table or index scan; I'm not
sure how to get around it given your current schema. If you can, instead of
keeping the number of days, keep the "end" date. Then you'll be able to
write a query that's capable of using an index.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I
don't
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>|||Much more efficient query (assuming an index on LastModified):
DECLARE @.d SMALLDATETIME
SET @.d = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= (@.d - a.responsible)
AND a.LastModified < (@.d +1 - a.responsible)
You always want the column with the index on its own on one side of the
equation. This will allow for an index s instead of a scan. And yes,
this is because LastModified has minutes and seconds, presumably, and
GETDATE() certainly does. The DATEADD/DATEDIFF trick I did up there
converted it to a time of midnight, which makes it easy to find date values
anywhere >= that day and < that day + 1. The query also takes advantage of
implicit integer math with datetime/smalldatetime values, but the purists
might want this instead:
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= DATEADD(DAY, 0 - a.responsible, @.d)
AND a.LastModified < DATEADD(DAY, 1 - a.responsible, @.d)
http://www.aspfaq.com/
(Reverse address to reply.)
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I
don't
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>|||Did not work
The sum of lastworked and responsible should be today's date. I tried that
sql and returned nothing.
Aleks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OULqxl2IFHA.588@.TK2MSFTNGP15.phx.gbl...
> This should do it:
> WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
> AND GETDATE()
>
> ... Unfortunately, I believe this will force a table or index scan; I'm
> not
> sure how to get around it given your current schema. If you can, instead
> of
> keeping the number of days, keep the "end" date. Then you'll be able to
> write a query that's capable of using an index.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> don't
>|||This seems to work though.
(Activities.LastModified+responsible) between (GETDATE()-1) and
(getdate())
Would that be alright ?
A
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OULqxl2IFHA.588@.TK2MSFTNGP15.phx.gbl...
> This should do it:
> WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
> AND GETDATE()
>
> ... Unfortunately, I believe this will force a table or index scan; I'm
> not
> sure how to get around it given your current schema. If you can, instead
> of
> keeping the number of days, keep the "end" date. Then you'll be able to
> write a query that's capable of using an index.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> don't
>|||"Aleks" <arkark2004@.hotmail.com> wrote in message
news:egjIvv2IFHA.3568@.TK2MSFTNGP10.phx.gbl...
> This seems to work though.
> (Activities.LastModified+responsible) between (GETDATE()-1) and
> (getdate())
> Would that be alright ?
Personally -- if you can't change the schema -- I would go with Aaron's
second query:
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= DATEADD(DAY, 0 - a.responsible, @.d)
AND a.LastModified < DATEADD(DAY, 1 - a.responsible, @.d)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Alek,
To explain your problem, You must understand theat getDate() function
returns date and Time, so your query will only return records where the
expression
DATEADD(day,Activities.responsible , Activities.Lastmodified )
is EXACTLY equal to the current date AND time Value, It is very unlikely
that any records will satisfy that...
The only way to do this is to write your predicate as a between, (or as a >=
AND < ), where all records are returned where the value in the dataabase
table is between 2 calculated BOUNDARY values, based on the current date and
time...
so you would either have
Activities.Lastmodified Between @.LowDate And @.HighDate, or
Activities.Lastmodified > @.LowDate And Activities.Lastmodified <= @.HighDate
(NOTE: BETWEEN implies >= AND <= )
Two questions need to be answered, to determine what those
1) When you say "= getdate()" What, exactly do you mean? - do you want,
a) ALl the records that occurred on that specific Calendar DAY?, or
b) All the records that occurred within exactly 12/(24?) hours of a
specific Date and Time?
If it's the former (which I suspect is the case), than your Boundary dates
will be Midnight, in the am, on a specific calculated date :
DateAdd(day, - Responsible, getdate()), converted to strip off the time...
Convert(VarChar(8), DateAdd(day, - Responsible, getdate()), 112)
and the high date would be one day later, again at midnigjt...
Convert(VarChar(8), DateAdd(day, 1 - Responsible, getdate()), 112)
or.
Where Activities.Lastmodified >= Convert(VarChar(8), DateAdd(day, -
Responsible, getdate()), 112)
AND Activities.Lastmodified < Convert(VarChar(8), DateAdd(day, 1 -
Responsible, getdate()), 112)
You have to split off the time portion if you only want those records from a
specific calendar day...
cannot "Aleks" wrote:

> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day ,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I don'
t
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>
>

query using commas

Hi,

I have table Article(ID,Title,FAID)

I need a query that will select all the Article.ID records where the FAID contains the number of the article ID

For exapmle, Article Table content is:

1, "Title1","2,6"2, "Title2",""3, "Title3","6,1"

6, "Title6","2"

Lets say I want to get all titles of article ID 1. I am going to its FAID which is "2,6"

So the query will return : "Title2", "Title6"

Can you advice how to write this? I can do a walk around solution where I will open a new table nameFAbut I rather not to.

You could use the CHARINDEX function in T-SQL. Something like this:

WHERE CHARINDEX(FAID, ID) > 0

This is untested, but will probably work for you.

BUT, this looks an awful lot like a non-normalized table, since you have multiple items in the FAID field. You're likely to be able to write much cleaner and more efficient queries if you normalize it.

Don

|||

the FAID fiels containd a related article IDs. this mean I will use this field only in one query. the one that I am building right now.

although at the moment I have normlized table ReleatedArticle (FAID,ArticleID). for each FAID, I have multiple ArticleIDs, and this is normlize.

do you think I should leave it like this normlized, and not to change to the CHARINDEX solution?

I don't like to open a new table when it seems like unneccessary one. please advice.

|||

Well, the normalization rules are not absolute, although there are people who treat them as such. Sometimes there are good reasons to break the rules. If you truly will not use the information in any other way, in any other queries, this might possibly be efficient for you. But if you ever find yourself writing any convoluted T-SQL or client code to work with the related article ID data, consider putting it into a separate table.

Don

query tunning

I have a dropdown box to running a query filtering mt records.
The dropdown has 4 option: ALL, Close, Open, Pause.
which filter the status of records.
When I change dropdown from all to others works fine, but when I change from
the Close, Open or Pause to All then it takes more than 10 seconds. It seems
that the SQL server needs more time to return the records from others to ALL
,
but it taks less time from ALL to others.
Any suggestions is great appreciated,As far as we dont know what code happens when you chenge the Combobox its
hard to help you in here. Perhaps you might run the profiler to watch the
incoming queries...
HTH, Jens Suessmeyer.
http.//www.sqlserver2005.de
--
"Souris" <Souris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>I have a dropdown box to running a query filtering mt records.
> The dropdown has 4 option: ALL, Close, Open, Pause.
> which filter the status of records.
> When I change dropdown from all to others works fine, but when I change
> from
> the Close, Open or Pause to All then it takes more than 10 seconds. It
> seems
> that the SQL server needs more time to return the records from others to
> ALL,
> but it taks less time from ALL to others.
> Any suggestions is great appreciated,
>
>|||Thanks for the message,
The dropdown box just run the query.
I will user profilter to watch it.
Thanks,
"Jens Sü?meyer" wrote:

> As far as we don′t know what code happens when you chenge the Combobox it
s
> hard to help you in here. Perhaps you might run the profiler to watch the
> incoming queries...
> HTH, Jens Suessmeyer.
> --
> http.//www.sqlserver2005.de
> --
>
> "Souris" <Souris@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>
>|||Chances are it is using an index and returning only a small portion of the
rows when it is other than ALL. When it is set to ALL it has to scan the
whole table and return a lot of rows.
Andrew J. Kelly SQL MVP
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>I have a dropdown box to running a query filtering mt records.
> The dropdown has 4 option: ALL, Close, Open, Pause.
> which filter the status of records.
> When I change dropdown from all to others works fine, but when I change
> from
> the Close, Open or Pause to All then it takes more than 10 seconds. It
> seems
> that the SQL server needs more time to return the records from others to
> ALL,
> but it taks less time from ALL to others.
> Any suggestions is great appreciated,
>
>|||Thanks!
You are right,
Are there any suggestions to resolve the issue?
Should I analyze the index fields?
Is it possible to change index fields will resolve the issue?
Thanks millions,
"Andrew J. Kelly" wrote:

> Chances are it is using an index and returning only a small portion of the
> rows when it is other than ALL. When it is set to ALL it has to scan the
> whole table and return a lot of rows.
> --
> Andrew J. Kelly SQL MVP
>
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:754D78B4-8A12-4664-9975-A876C9A37D1A@.microsoft.com...
>
>|||If there are no other columns which can be used in the WHERE clause then you
have no choice but to scan the table. Indexes won't help if there is no
WHERE clause. I don't know your business logic but you would probably be
best served by always requiring some field to be used as the search
criteria. Rarely is ALL a good choice. If there are thousands of rows or
more then the user usually can not sift through all of them effectively
anyway.
Andrew J. Kelly SQL MVP
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:64B6C003-1721-4DF8-9CC7-357E96311208@.microsoft.com...
> Thanks!
> You are right,
> Are there any suggestions to resolve the issue?
> Should I analyze the index fields?
> Is it possible to change index fields will resolve the issue?
> Thanks millions,
>
> "Andrew J. Kelly" wrote:
>|||Thanks millions,
Souris,
"Andrew J. Kelly" wrote:

> If there are no other columns which can be used in the WHERE clause then y
ou
> have no choice but to scan the table. Indexes won't help if there is no
> WHERE clause. I don't know your business logic but you would probably be
> best served by always requiring some field to be used as the search
> criteria. Rarely is ALL a good choice. If there are thousands of rows or
> more then the user usually can not sift through all of them effectively
> anyway.
> --
> Andrew J. Kelly SQL MVP
>
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:64B6C003-1721-4DF8-9CC7-357E96311208@.microsoft.com...
>
>sql

Query total number of records in a schema

Please help out the neophyte!

I'm trying to query a database for the total number of records in each table owned by a particular schema in SQL Server 2000. I can query INFORMATION_SCHEMA.TABLES for a list of the tables in a particular schema, but how do I pass these table names to variable that can be used to count(*) the records in each? Can anyone show me how this should be done using simple TSQL?

Thanks.

Chris

Give this a shot:

Code Snippet

DECLARE @.TableName varchar(100)

DECLARE @.SQL nvarchar(MAX)

DECLARE SchemaCounter CURSOR FOR

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN SchemaCounter

FETCH NEXT FROM SchemaCounter INTO @.TableName

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.SQL = 'SELECT COUNT(*) [' + @.TableName + ' COUNT] FROM [' + @.TableName + ']'

EXEC sp_executesql @.SQL

FETCH NEXT FROM SchemaCounter INTO @.TableName

END

CLOSE SchemaCounter

DEALLOCATE SchemaCounter

|||There is a couple of ways to get it through T-SQL, but I would not call them "simple" in neither case:

Code Snippet

select o.name, i.rowcnt
from sysindexes i
inner join sysobjects o on o.id = i.id
where o.type = 'U'
and i.indid in (0,1)

Or this one (still works in MSSQL2005):

Code Snippet

exec sp_MStablespace 'MyTableName'

The only drawback of these methods is that they used undocumented or deprecated features of SQL Server. Only hope you know what the word "undocumented" really means in MSSQL.


PS There is also a way to use sp_MSForEachTable system stored proc, but it's also undocumented .|||

Hey...

rowcnt in sysindexes

It is very dangerous to use. One can not assure it gives exact number of rows and I am not sure if we can use this or not?

Regards,

Subbu

|||

cannot trust rowcnt in sysindexes. what do u say ?

|||

Yes.. Here the stright forward query (without undocumented sps)

Code Snippet

Select

OBJ.NAME [TableName],

IND.RowCnt as [RowCount]

From

dbo.sysindexes IND

Join sysobjects OBJ on IND.ID = OBJ.ID and indid < 2 And OBJ.Type='U'

order By 1

|||

Hey, the neophyte thanks you for your help. A nice simple solution to my problem.

Chris

|||RowCnt is not updated in "real time". It not ever guaranteed to be 100% accurate. However, if you only want a very reliable "ballpark" number, it works really well. I use it all the time with the caviat that the number is a guidline, rather than "in-stone."

query to use

Hi
i have 2 tables. Table 2 can contain some values for each record in
table1. may vary for the no:of records in table2 for each record in
table1
table1
=====
id name
1 Arun
2 Hari
Table2
=====
id table1.id some_field
1 1 x
2 1 y
3 1 z
4 2 d
I want to get a display like the following
id name 1 2 3
1 Arun x y z
or
id name 1
1 Hari d
What query I have to use
?
Hi
--SQL Server 2000
create table #table1 (id int,name varchar(50))
insert into #table1 values(1,'Arun')
insert into #table1 values(2,'Hari')
create table #table2 (id int,anotherid int, some_field varchar(50))
insert into #table2 values(1,1,'x')
insert into #table2 values(2,1,'y')
insert into #table2 values(3,1,'z')
insert into #table2 values(4,2,'d')
select * from #table1
select * from #table2
select name,max(case when rn=1 then some_field end) as '1',
max(case when rn=2 then some_field end) as '2',
max(case when rn=3 then some_field end) as '3'
from
(
select t2.anotherid,t2.some_field,count(*)rn from #table2,#table2 t2
where t2.anotherid=#table2.anotherid and t2.id<=#table2.id
group by t2.anotherid,t2.some_field
) as d join #table1 on d.anotherid=#table1.id
group by name
--SQL Server 2005
select * from
(
select t1.id ,name,anotherid,some_field,ROW_NUMBER() OVER(
PARTITION BY anotherid
ORDER BY some_field) AS pos
from #table1 AS t1
join #table2 AS t2
ON t1.id = t2.anotherid
) as der
pivot
(
max(some_field)
FOR pos IN([1], [2], [3], [4])
) AS PVT
<arunonw3@.gmail.com> wrote in message
news:1176193651.677044.91870@.l77g2000hsb.googlegro ups.com...
> Hi
> i have 2 tables. Table 2 can contain some values for each record in
> table1. may vary for the no:of records in table2 for each record in
> table1
> table1
> =====
> id name
> 1 Arun
> 2 Hari
>
> Table2
> =====
> id table1.id some_field
> 1 1 x
> 2 1 y
> 3 1 z
> 4 2 d
> I want to get a display like the following
>
> id name 1 2 3
> 1 Arun x y z
> or
> id name 1
> 1 Hari d
> What query I have to use
> ?
>
|||Thank you very much for sending me such a useful answer
|||If you dont mind can you please explain the last 2 queries

query to use

Hi
i have 2 tables. Table 2 can contain some values for each record in
table1. may vary for the no:of records in table2 for each record in
table1
table1
===== id name
1 Arun
2 Hari
Table2
===== id table1.id some_field
1 1 x
2 1 y
3 1 z
4 2 d
I want to get a display like the following
id name 1 2 3
1 Arun x y z
or
id name 1
1 Hari d
What query I have to use
?Hi
--SQL Server 2000
create table #table1 (id int,name varchar(50))
insert into #table1 values(1,'Arun')
insert into #table1 values(2,'Hari')
create table #table2 (id int,anotherid int, some_field varchar(50))
insert into #table2 values(1,1,'x')
insert into #table2 values(2,1,'y')
insert into #table2 values(3,1,'z')
insert into #table2 values(4,2,'d')
select * from #table1
select * from #table2
select name,max(case when rn=1 then some_field end) as '1',
max(case when rn=2 then some_field end) as '2',
max(case when rn=3 then some_field end) as '3'
from
(
select t2.anotherid,t2.some_field,count(*)rn from #table2,#table2 t2
where t2.anotherid=#table2.anotherid and t2.id<=#table2.id
group by t2.anotherid,t2.some_field
) as d join #table1 on d.anotherid=#table1.id
group by name
--SQL Server 2005
select * from
(
select t1.id ,name,anotherid,some_field,ROW_NUMBER() OVER(
PARTITION BY anotherid
ORDER BY some_field) AS pos
from #table1 AS t1
join #table2 AS t2
ON t1.id = t2.anotherid
) as der
pivot
(
max(some_field)
FOR pos IN([1], [2], [3], [4])
) AS PVT
<arunonw3@.gmail.com> wrote in message
news:1176193651.677044.91870@.l77g2000hsb.googlegroups.com...
> Hi
> i have 2 tables. Table 2 can contain some values for each record in
> table1. may vary for the no:of records in table2 for each record in
> table1
> table1
> =====> id name
> 1 Arun
> 2 Hari
>
> Table2
> =====> id table1.id some_field
> 1 1 x
> 2 1 y
> 3 1 z
> 4 2 d
> I want to get a display like the following
>
> id name 1 2 3
> 1 Arun x y z
> or
> id name 1
> 1 Hari d
> What query I have to use
> ?
>|||Thank you very much for sending me such a useful answer|||If you dont mind can you please explain the last 2 queries

query to use

Hi
i have 2 tables. Table 2 can contain some values for each record in
table1. may vary for the no:of records in table2 for each record in
table1
table1
=====
id name
1 Arun
2 Hari
Table2
=====
id table1.id some_field
1 1 x
2 1 y
3 1 z
4 2 d
I want to get a display like the following
id name 1 2 3
1 Arun x y z
or
id name 1
1 Hari d
What query I have to use
?Hi
--SQL Server 2000
create table #table1 (id int,name varchar(50))
insert into #table1 values(1,'Arun')
insert into #table1 values(2,'Hari')
create table #table2 (id int,anotherid int, some_field varchar(50))
insert into #table2 values(1,1,'x')
insert into #table2 values(2,1,'y')
insert into #table2 values(3,1,'z')
insert into #table2 values(4,2,'d')
select * from #table1
select * from #table2
select name,max(case when rn=1 then some_field end) as '1',
max(case when rn=2 then some_field end) as '2',
max(case when rn=3 then some_field end) as '3'
from
(
select t2.anotherid,t2.some_field,count(*)rn from #table2,#table2 t2
where t2.anotherid=#table2.anotherid and t2.id<=#table2.id
group by t2.anotherid,t2.some_field
) as d join #table1 on d.anotherid=#table1.id
group by name
--SQL Server 2005
select * from
(
select t1.id ,name,anotherid,some_field,ROW_NUMBER() OVER(
PARTITION BY anotherid
ORDER BY some_field) AS pos
from #table1 AS t1
join #table2 AS t2
ON t1.id = t2.anotherid
) as der
pivot
(
max(some_field)
FOR pos IN([1], [2], [3], [4])
) AS PVT
<arunonw3@.gmail.com> wrote in message
news:1176193651.677044.91870@.l77g2000hsb.googlegroups.com...
> Hi
> i have 2 tables. Table 2 can contain some values for each record in
> table1. may vary for the no:of records in table2 for each record in
> table1
> table1
> =====
> id name
> 1 Arun
> 2 Hari
>
> Table2
> =====
> id table1.id some_field
> 1 1 x
> 2 1 y
> 3 1 z
> 4 2 d
> I want to get a display like the following
>
> id name 1 2 3
> 1 Arun x y z
> or
> id name 1
> 1 Hari d
> What query I have to use
> ?
>|||Thank you very much for sending me such a useful answer|||If you dont mind can you please explain the last 2 queriessql

Query to update 1 record in a duplicate set of records

How do I update a record that has duplicates. For example, I have 3612 orders some of these orders have multiple orderid's I want to update the record for each of these orders that was added most recently.

Without more info it's hard to tell, but you just need to qualify what you want to update:

UPDATE Table
SET column = 'New Value'
WHERE column = 'youNeedADateHere'

AND orderid = 'Whatever'
GO

|||Thanks for your response but I can't specify an orderid because there are 3612 records and within those 3612 records some of the id's are dups. The query above would do a specific orderid and a specific date. The dates are all different I just want the most recent per orderid. Thank you anyway.|||

Something along the lines of:

UPDATE Table1

SET NewCol = 1

FROM Table1 AS i

INNER JOIN (SELECT OrderID, COUNT(*) AS c, MAX(OrderDate) AS OrderDate

FROM Table1

GROUP BY OrderID

HAVING COUNT(*) > 1) AS Dupes

ON i.OrderID = Dupes.OrderID

AND i.OrderDate = Dupes.OrderDate

|||Thank you that is close enough to what I was looking for. I appreciate your response

Query to Search all fields in simple table

I am trying to write a simple search page that will searchall the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:

SELECT Table.*

FROM Table

WHERE * = @.USERINPUT

But thats not working. Can someone help.? Thanks..

Not a simple task, but this should get you started.

SELECT *

FROM Table

WHERE field1 LIKE '%' + @.UserInput + '%' OR field2 LIKE'%'+@.UserInput+'%' OR...

sql

Query to return duplicate records

I have a table with a column varchar(50), say colA.
How can I create a sql query that returns all records with duplicate colA ?
For example:
colA colB
1 A
2 B
2 B
3 C
2 is the duplicate records for colA. How can I return those records ?
Thanks.SELECT ColA, count(*) FROM TableName
GROUP BY ColA
HAVING COUNT(*) > 1
HTH. Ryan
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:BF9FCDE6-61C4-4CD0-AEA7-98DE6049CE59@.microsoft.com...
>I have a table with a column varchar(50), say colA.
> How can I create a sql query that returns all records with duplicate colA
> ?
> For example:
> colA colB
> 1 A
> 2 B
> 2 B
> 3 C
> 2 is the duplicate records for colA. How can I return those records ?
> Thanks.
>|||"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:BF9FCDE6-61C4-4CD0-AEA7-98DE6049CE59@.microsoft.com...
>I have a table with a column varchar(50), say colA.
> How can I create a sql query that returns all records with duplicate colA
> ?
> For example:
> colA colB
> 1 A
> 2 B
> 2 B
> 3 C
> 2 is the duplicate records for colA. How can I return those records ?
> Thanks.
>
SELECT T.cola, T.colb
FROM your_table AS T
JOIN
(SELECT cola
FROM your_table
GROUP BY cola
HAVING COUNT(*)>1) AS D
ON T.cola = D.cola ;
David Portas
SQL Server MVP
--|||Select colA,ColB
>From Sometable
Where colA in
(
Select colA
From SomeTable
Group by colA
Having count(*) >1
)
HTH, jens Suessmeyer.

Query to merge duplicate records

Hello,

I have the following Query:

1 declare @.StartDatechar(8)
2 declare @.EndDatechar(8)
3 set @.StartDate ='20070601'
4 set @.EndDate ='20070630'
5 SELECT Initials, [Position], DATEDIFF(mi,[TimeOn],[TimeOff])AS ProTime
6 FROM LogTableWHERE
7 [TimeOn]BETWEEN @.StartDateAND @.EndDateAND
8 [TimeOff]BETWEEN @.StartDateAND @.EndDate
9 ORDER BY [Position],[Initials]ASC

The query returns the following data:

Position Initials ProTime
---------------- --- ----
ACAD JJ 127
ACAD JJ 62
ACAD KK 230
ACAD KK 83
ACAD KK 127
ACAD TD 122
ACAD TJ 127

What I'm having trouble with is the fact that I need to return a results that has the totals for each set of initials for each position. For Example, the final output that I'm looking to get is the following:

Postition Initials ProTime

ACAD JJ 189
ACAD KK 440
ACAD TD 122
ACAD TJ 127

Any assistance greatly appreciated.

Use the GROUP BY syntax, and SUM() the time diffs.

Your query will look like this:

SELECT Initials, [Position], SUM(DATEDIFF(mi,[TimeOn],[TimeOff])) AS ProTime
FROM LogTableWHERE
[TimeOn]BETWEEN @.StartDateAND @.EndDateAND[TimeOff]BETWEEN @.StartDateAND @.EndDate

GROUP BY Initials, Position

Leave off the ORDER BY from your statement - you see that the SQL is grouping by the columns you wish to merge and summing the variable data.

|||

Try:

SELECT Initials, [Position], SUM(DATEDIFF(mi,[TimeOn],[TimeOff]))AS ProTime
FROM LogTable

WHERE
[TimeOn]BETWEEN @.StartDateAND @.EndDateAND
[TimeOff]BETWEEN @.StartDateAND @.EndDate

GROUP BY Initials, [Position]
ORDER BY [Position],[Initials]ASC

|||

That was it! I knew it was going to be simple, but I could not "see the forest for the trees".

Thanks

Dan

Query to get the last inserted records first

Hello,
I have a table where new rows are inserted on a regulary basis (one by one)
during the day. Several thousands of new rows are inserted every day. This
is an 'History Like' table. It contains a DateRecord column.
The table is queried so that last inserted records must appears first in the
result set. A maximum of n records should be returned. Other constraints may
be applied on other columns.
Is there a way to avoid the SORT (DateRecord DESC) which is very time
consuming ?
TIA.if possbile u can use a identity column and while selecting the records mark
the query are ORDER BY <FIELD NAME> DESC
"Olivier Matrot" <olivier.matrot@.online.nospam> wrote in message
news:eJ7bzMQCFHA.208@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table where new rows are inserted on a regulary basis (one by
one)
> during the day. Several thousands of new rows are inserted every day. This
> is an 'History Like' table. It contains a DateRecord column.
> The table is queried so that last inserted records must appears first in
the
> result set. A maximum of n records should be returned. Other constraints
may
> be applied on other columns.
> Is there a way to avoid the SORT (DateRecord DESC) which is very time
> consuming ?
> TIA.
>|||You can't avoid sorting the records if you want it in a certain order.
According to what you wrote, you'll need to use TOP N in the select
clause and order by clause. If you'll have an index on the DateRecord
column, then the ordering could be fast. Also consider making it
clustered index.
Adi|||> Is there a way to avoid the SORT (DateRecord DESC) which is very time
> consuming ?
As mentioned by the others in this thread, you need to specify ORDER BY
DateRecord DESC to return data in the desired sequence. An index on the
DateRecord column may help performance, depending on the particulars of your
queries/
Hope this helps.
Dan Guzman
SQL Server MVP
"Olivier Matrot" <olivier.matrot@.online.nospam> wrote in message
news:eJ7bzMQCFHA.208@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table where new rows are inserted on a regulary basis (one by
> one) during the day. Several thousands of new rows are inserted every day.
> This is an 'History Like' table. It contains a DateRecord column.
> The table is queried so that last inserted records must appears first in
> the result set. A maximum of n records should be returned. Other
> constraints may be applied on other columns.
> Is there a way to avoid the SORT (DateRecord DESC) which is very time
> consuming ?
> TIA.
>|||One last thing, I would recommend not using an IDENTITY based solution
-- the idea of using a DATETIME data type is how you want to go.
-Alan
Olivier Matrot wrote:
> Hello,
> I have a table where new rows are inserted on a regulary basis (one
by one)
> during the day. Several thousands of new rows are inserted every day.
This
> is an 'History Like' table. It contains a DateRecord column.
> The table is queried so that last inserted records must appears first
in the
> result set. A maximum of n records should be returned. Other
constraints may
> be applied on other columns.
> Is there a way to avoid the SORT (DateRecord DESC) which is very time
> consuming ?
> TIA.|||If you're only interested in retrieving the most recent changes to this
table, I would highly recommend you create a clustered index on the
DateRecord column in descending order:
CREATE CLUSTERED INDEX IXC_DateRecord ON TableName (DateRecord DESC)
A clustered index defines the physical ordering of records in the
table. If this is the primary type of query you'll be executing on this
table, I would recommend clustering on the DateRecord column.
You may want to look at your existing query's execution plan in Query
Analyzer (Tools >> Show Execution Plan or Ctrl+k).
If you're unfamiliar with indexes, here's a good way to think of them
at first (please don't be offended if you're already familiar w/ these
concepts):
Think of the white pages in a printed phone book where everyone is
listed in alphabetical order, along with their address and phone
number. Lets say you have a phone number (but no name) and want to find
out whose it is, you would have to read through every single phone
number until you found it (you'll see these types of operations usually
listed as table scans or clustered index scans in your query's
execution plan).
Searching by name is much faster because you know the phone book is
sorted that way. If you're searching for "Samet Alan A" in the local
listings, you would thumb through it until you find S, look at the
ranges at the tops of the pages and then navigate to Samet Alan A. Once
you're past it, you can be sure there are no more listings for Samet
Alan A.
The white pages are the physical equivalent of a table that would be
clustered on LastName, FirstName. If you frequently had to look up
people by phone number, you would create a non-clustered index on the
phone number column. This would be the equivalent of having another
phone book that had all of the phone numbers sorted, with the LastName
and FirstName (the clustering key) also listed with them.
The non-clustered index would be significantly faster than having to
read through each listing's phone number. If all you needed is the name
associated with the phone number, the non-clustered index would be
adequate. If you needed an address based on the phone number, with the
above indexes, you would first look up the phone number in the
non-clustered index, get the associated name, and then look up the
address based on the person's name in the clustered index (this is
shown as a Bookmark Lookup in your execution plan).

>From what I've seen, when you have a clustered index and you want to
return the TOP [n] records sorted in the same way as the index, I've
always observed that they're returned sorted even without an ORDER BY
clause. However, I would not recommend leaving the sorting off of your
statement as I haven't seen any documentation that states you will
always experience this behavior -- not to mention, you may later choose
how you want to index your table. In which case, your sorting will
definitely change.
I hope I've helped.
-Alan
Olivier Matrot wrote:
> Hello,
> I have a table where new rows are inserted on a regulary basis (one
by one)
> during the day. Several thousands of new rows are inserted every day.
This
> is an 'History Like' table. It contains a DateRecord column.
> The table is queried so that last inserted records must appears first
in the
> result set. A maximum of n records should be returned. Other
constraints may
> be applied on other columns.
> Is there a way to avoid the SORT (DateRecord DESC) which is very time
> consuming ?
> TIA.

Query to filter duplicate data from table.

i am having 3000 records in table. now i want take out the duplicate from that table.
for example: i want to find out the duplicates of 'CompanyNames'.
help needed to write query for this operation.Originally posted by pln_verma
i am having 3000 records in table. now i want take out the duplicate from that table.

for example: i want to find out the duplicates of 'CompanyNames'.

help needed to write query for this operation.

check this :
http://www.sqlteam.com/item.asp?ItemID=3331|||Does your table have a surrogate key or any method of uniquely identifying a record?

Query to extract the most recent information - help please

Hi all,

I have a table, three records of which look like this:

ID PersonID FirstName LastName PostCode
1 999 Barry White BW13 8GS
2 999 <null> <null> BW13 9GS
3 999 <null> Whites <null>

Both these records refer to the same "person". The records with ID of 2 and 3 represent updates to the record with an ID of 1. The problem is, only the updated data (along with the personID) is represented in records 2 and 3. I need to write query that will return a single record that looks like this:

PersonID FirstName LastName PostCode
999 Barry Whites BW13 9GS

in other words, the most recent information we have for that person.

Does anyone have any ideas? I'd be very grateful as this is proving to be a real pain in the butt!

Kind regards,

maccaPersonID FirstName LastName PostCode
999 Barry Whites BW13 9GS

Hi
Try this:

SELECT PersonID, FirstName, LastName, PostCode FROM YourTable
WHERE ID IN(SELECT MAX(ID) FROM YourTable WHERE PersonID = 999)|||Hi shaikh,

Unfortunately, that would just return

ID PersonID FirstName LastName PostCode
3 999 <null> Whites <null>

as it is only selecting the most recent record (or the record with the highest ID).

Thanks for posting though.|||Hi,

You can use the following query, perhaps using CTE may be also solve the problem.

declare @.fn varchar(10), @.ln varchar(10), @.pc varchar(10)
SELECT
@.fn = CASE WHEN firstname is not null THEN firstname ELSE @.fn END,
@.ln = CASE WHEN lastname is not null THEN lastname ELSE @.ln END,
@.pc = CASE WHEN postcode is not null THEN postcode ELSE @.pc END
from persons where personid = 999
select @.fn, @.ln, @.pc

Eralper
http://www.kodyaz.com|||Ohh sorry

Try this. Put this code in stored procedure

SELECT TOP 1
FirstName = (SELECT TOP 1 FirstName FROM Test WHERE FirstName IS NOT NULL ORDER BY [ID] DESC),
LastName = (SELECT TOP 1 LastName FROM Test WHERE LastName IS NOT NULL ORDER BY [ID] DESC),
PostCode = (SELECT TOP 1 PostCode FROM Test WHERE PostCode IS NOT NULL ORDER BY [ID] DESC)
FROM Test WHERE PersonID = 999|||Thanks eralper,

That's a smart solution and in testing it works like a dream. I'd love to understand how it works. Could you elaborate, just a little?

Cheers

Tim|||Hi macca,

The query just updates the values of parameters while reading the selected rows.

This method is also useful while updating data rows in a table.

You can look at the article named "How to use SQL variables in an Update Statements Where Variable is also Updated for each row during the Update Process" at http://www.kodyaz.com/articles/SQL-Variables-In-Update-Statements.aspx

Eralper|||Thanks eralper, that's great. Shaikh, yours worked too so thanks for that.|||declare @.fn varchar(10), @.ln varchar(10), @.pc varchar(10)
SELECT
@.fn = CASE WHEN firstname is not null THEN firstname ELSE @.fn END,
@.ln = CASE WHEN lastname is not null THEN lastname ELSE @.ln END,
@.pc = CASE WHEN postcode is not null THEN postcode ELSE @.pc END
from persons where personid = 999
select @.fn, @.ln, @.pc
That's interesting; I had never seen this construction before.

Is it wise to add an 'order by ID' to make sure the rows are processed in the correct order?|||Hi Ivon,

I've done loads of testing on this construct, rearranged my data and all sorts and it still gives me the right answer! I must say, I'm not entirely sure how but it's great!

macca|||Hi,

I agree that an ORDER BY clause will be better to ensure that the rows processed are in correct order.

I believe that since the default order is same with the insert order of the rows, we get the desired result without an Order By.

Eralper
http://www.kodyaz.com

Query to display most active records

Hello,

I have two tables, Promotion and Promolocation. The Promotion table is used to set up promotions or sales, and consists of a PromoID, StartDate, and EndDate. Each PromoID is referenced in the Promolocation table, which is used to assign items to a promotion for various locations or stores. The Promolocation table consists of PromoID, LocID, SkuID, PromoPrice, and DiscLevel.

There are times where an item or SkuID will exist in more than one promotion, however, our application is currently not intelligent enough to determine which promotion to use, so it sets the active promotion based on the StartDate being before other promotions' StartDate and the EndDate being after other promotions' EndDate.

I want to find all promoid's where a sku exists in more than one promotion. I want to signify which promotion is active, using 1 as the first active promotion, 2 as the next active, 3 as the next, etc. To determine which promotion is the first active promotion, the StartDate must be before any of the other promotions' StartDate, and the EndDate must be after other promotions' EndDate. If the promotions' StartDate is after the other promotions' StartDate but not before the other promotions' EndDate, and the EndDate is before or on other promotions' EndDate, then that's the second active promotion. If the StartDate is the same as other promotions' StartDate, but the EndDate is before other promotions' EndDate, then that's the third active promotion.

For example:

PromoID StartDate EndDate
--- --- ---
PROMO1 1/1/2004 1/1/2006 (1st Active Promotion)
PROMO2 2/1/2004 1/1/2006 (2nd Active Promotion)
PROMO3 1/1/2004 12/1/2005 (3rd Active Promotion)

Here's a query I am using to display all active promotions:

select
pl.promoid,
pr.startdate,
pr.enddate,
pl.locid,
pl.skuid,
pl.promoprice,
pl.disclevel
from
promolocation pl
inner join
promotion pr
on
pl.promoid = pr.promoid
where
pr.enddate >= getdate()

Thanks for your help.

DProvide DDL and sample data. For example, SKU is missing from your post.|||Here's the DDL:

CREATE TABLE [dbo].[Promotion] (
[PromoID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PromoLocation] (
[PromoID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LocID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SkuID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PromoPrice] [money] NULL ,
[DiscLevel] [float] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

And here's some of the output from my query:

promoid startdate enddate locid skuid promoprice disclevel
---- ---------------- ---------------- ------ ------ ------- ----------------
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 116 60.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 116 60.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 116 60.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 116 60.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 1 118B 99.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 2 118B 99.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 3 118B 99.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 6 118B 99.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 119 139.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 119 139.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 119 139.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 119 139.0000 0.20000000000000001
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 1 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 2 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 3 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 4 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 6 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 7 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 8 125 135.0000 0.59999999999999998
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 20 220.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 20 220.0000 0.20000000000000001

(25 row(s) affected)|||Can somone help me with this problem?

Thanks,

D|||Can somone help me with this problem?what was the question?

:)

Query to combine several "records/rows" into one "record/row"?

Im new to SQL, Ive been getting by building lists from our student database by building or teacher (etc.). These have been all records that exist on only one row, so its been easy (so far).
Heres what Im trying to do and I dont even know what to call it, so I'm not even sure what to search for...

Ive got a MS SQL 6.5 database with the following:
ACTIVE students: each student has ID_NUM[8 digits], NAME, GRADE, SCHOOL with one rowof 4 data items per student.

SCHEDULE of courses with (student) ID_NUM[8 digits], SEMESTER[S1 or S2], HOUR[1-7], COURSE_NAME, ROOM_NUM with 14 records (rows) with these 5 items, in this SCHEDULE database for each student.

My mission is to combine then in to one row using the student ID_NUM as the key. (This is to help me with several things, spreadsheets/database for others to easily use, export to simple databases for teacher handhelds.).

Id like one row of the 75 items combined, resulting in 32 items (ACTIVE 4 items + 14 * 2 SCHEDULE items [COURSE_NAME, ROOM_NUM]) since I want stuff plugged into the correct field, for each student. I'd refer to this as COMBINEDRECORD and Id turn the field names into the following:
ID_NUM[8 digits], NAME, GRADE, SCHOOL, S1HOUR1_NAME, S1HOUR1_ROOM_NUM, S1HOUR2_NAME, S1HOUR2_ROOM_NUM, S1HOUR3_NAME, S1HOUR3_ROOM_NUM, S1HOUR4_NAME, S1HOUR4_ROOM_NUM, S1HOUR5_NAME, S1HOUR5_ROOM_NUM, S1HOUR6_NAME, S1HOUR6_ROOM_NUM, S1HOUR7_NAME, S1HOUR7_ROOM_NUM, S2HOUR1_NAME, S2HOUR1_ROOM_NUM, S2HOUR2_NAME, S2HOUR2_ROOM_NUM, S2HOUR3_NAME, S2HOUR3_ROOM_NUM, S2HOUR4_NAME, S2HOUR4_ROOM_NUM, S2HOUR5_NAME, S2HOUR5_ROOM_NUM, S2HOUR6_NAME, S2HOUR6_ROOM_NUM, S2HOUR7_NAME, S2HOUR7_ROOM_NUM

I dont care if there are any blanks I just want to get the data if
SEMESTER='S2', HOUR='5' & COURSE_NAME='Basketweaving' & ROOM_NUM='Pool'
to end up being in the right spot (S2 and Hour 5) in the new COMBINEDRECORD row with
S2HOUR5_NAME='Basketweaving' & S2HOUR5_ROOM_NUM='Pool' for the correct student ID_NUM. Of course with the correct ACTIVE student info into the same "row"

Does that make sense? It might not be the best way, but itll make the data more accessible to everyone and some programs we already use with our old student system. Obviously theres more data than that but I think this is enough to explain my issue and give me enough to work with

Any help, directions to a webpage or book with the correct terms to look up would be very helpful.

Thank you for any help or direction you can give,
GarySounds like a join. Are you trying to creat a new table or just do a report?|||Or a view with a join if you want to leave the existing tables alone.|||Originally posted by barneyrubble318
Or a view with a join if you want to leave the existing tables alone.

I'll probably be doing two (similar) things:

1) An SQL query that just puts my COMBINEDRECORD table into an Excel Spreadsheet. (Why Excel? Everyone here knows how to merge from it, so they can then manipulate it how they want.)

2) An SQL query from Desktop2MobileDB which will convert the COMBINEDRECORD table into a Palm OS (MobileDB) database so principals and teachers can have more data on hand. (They can see where the kid in the hall is really supposed to be...)

I pretty much do the above two things with data now, the problem is the multi-line data from the SCHEDULE/

If I have to create a new table and then access it from there, I guess I can do that. I might not be able to automate it as easily though...

Thanks,
Gary

Query to another database

Dear all,

I have views in database that select records from a table in another database in the same server.
Can I set security in such a way for not to give direct access to the table. I know than it is possible if a table and views are in the same database and have the same owner, but I failed to get the same for different databases.

Thanks.

The following thread includes an example of how to achieve this via signed code:

controlling security through stored procedures -- 2005 behaviour

Also, for another option, search for "cross database ownership chaining" in Books Online. There is a server option that enables this. BOL contains information on it.

Thanks
Laurentiu

|||Thank you

Query timeout when rows returned < TOP (n)?

SQLServer 2005, ~7 million records, queries are using a clustered index keyed on the field "date".

Both queries below have the same execution plan, IO Cost, etc but Query 1 takes ~38 seconds whereas Query2 takes ~.3 seconds.

The only difference is in one of the where clauses (point=). It seems to have something to do with the fact that the first query is only returning 66 rows, but I'm at a loss as to why it's so slow. Query 1 is sub 1 second if I do a select top 66, but ~38 seconds with a top 67.

Obviously there's something I'm missing, but I'm completely clueless as to what it is.

Thanks - James

Query 1:

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')

ORDER BY date DESC

37759 ms

66 rows

IO Cost: 188.225

Returns rows 1-61 < 1 second, 62-66 @. ~38 seconds

Query 2:

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 1')

ORDER BY date DESC

334 ms

100 rows

IO Cost: 188.225

It really depends on how many records there are of Attenna 1 and Antenna 2.

Clearly out of all your records there are only 66 that match Atenna 2 so it probably had to look through every record taking 38 seconds. however, there seem to be a whole lot more Attenna 1 or they were toward the begging of your records. Because it filled the Top 100 you specified. Once that is filled there is no point for the query to keep executing and it popped back after only 3 seconds.

On top of that your index is no on any of the columns in your where clause. An index is not a magic item. You indexes on your WHERE criteria in order for it to take advantage of it.

|||

Query plans are your friend.

Look at the query plan and you will see the difference between both queries.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Yep query plans are the same for both. I've got indices for the other fields also.

The odd thing is with a if I give it a point name that doesn't exist like

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'xx')

ORDER BY date DESC

It uses the index for point then hits the clustered index, but the same query with a valid point name

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')

ORDER BY date DESC

it uses only the clustered index. I'm starting to wonder if it might be a problem with Stastics being out of synch for some reason.

Thanks again - James

|||

Query 1 required a scan of 100% of the table. Even after looking at the whole table, only 66 records were returned.

In the second query, it found 100 records very quickly so there was no need to continue.

If you were to remove the "Top 100" from these queries, the execution times would be very similar as both queries would be required to scan the whole table (with this caviat: If Query 2 returne 200,000,000 records, it's going to take longer...the table scan won't take longer to locate the records, but actually reading the disk and moveing the bits will take longer).