I'm trying to optimise a query for a SQLServerCE database running on a Windows Mobile device. The query is used to identify rows to delete in one table based on no longer being referenced from a different table. The original query used a DISTINCT to identify matching rows:
SELECT TestGroupId, TestNameId, ServiceTypeId
FROM ServiceTypeIn...
So I am really confused on how to write this proc. Here is what needs to be done.
I have 3 tables laid out as follows:
tblObject {ObjectId, MasterId}
tblAnotherObject {ObjectId}
tblFurtherObject {ObjectId}
I need a proc that can delete the rows in 'tblAnotherObject' and 'tblFurtherObject' that contain an 'ObjectId' that does NOT hav...
For some weird reason i can not run my report, when i try to preview i get this error:
(PLEASE RIGHT CLICK THE IMAGE AND CHOOSE VIEW IMAGE TO SEE IT LARGE)
Here is how my tablix property looks like:
Any idea what to do? The current .rdl was copied from another one. I changed the newly copied .rdl values to match my needs. I always...
I am working in Sql Server 2008 and there is a change of table in my database. So I need to know which all the queries I am using that table or I need to know which all queries will throw errors.I has changed some SP to work fine.But there is a lot of queries which may cause errors.So If there is possible to find which all queries will r...
hi there,
i've to downgrade a bunch of queries from sql server 2008 to 2000.
some functions are not supported in 2k, like cte or row_number().
i've copied the database to an sql-2008 server, setting compatibility level to 80, hoping this db would behave like in a 2k-server. but i can easily run a query using cte or "not-supported" func...
Hi,
I need to drop and recreate a table, which exists to "cache" an expensive view. The view may change and I want to make maintenance as easy as possible, so I want the new table to reflect the latest version of the view.
I also want to be able to prevent read errors should a procedure try to access the table while it is in the middl...
I'm fairly sure this is an easy thing to do, but I'm a newbie at SQL so be gentle. If I want to write a query, that adds up the total occurences of each process number and stores those values to a new column, what do I do? I thought some mixture of count(distinct ...) could get it down but I'm not sure. See the result table for what I'm ...
I have a query in access which i need to convert to a stored proc in sql server 2005.
the query in access is as follows:
UPDATE
tblitem,
tblFileSignature
SET
tblitem.strFileProcesstype = [tblFileSignature].[STRFILEPROCESSTYPE], tblitem.strFileSignatureType = [tblFileSignature].[strfilesignaturetype]
WHERE
(((tblitem.strFileSignatu...
Current query:
SELECT order_id AS OrderNumber, ordName, ordLastName, question, answer
from cart_survey
JOIN orders
ON cart_survey.order_id=orders.ordID
JOIN survey_answers
ON survey_answers.id=cart_survey.answer_id
JOIN survey_questions
ON survey_questions.id=cart_survey.question_id
Results:
OrderNumber ordName ordLastName qu...
Hi. I have a problem in T-SQL that I find difficult to solve.
I have a table with groups of records, grouped by key1 and key2. I order each group chronologically by date. For each record, I want to see if there existed a record before (within the group and with lower date) for which the field "datafield" forms an allowed combination wit...
I am getting different result set for these two queries and second result set seems to be correct. What is the difference in these queries.
What type of inner join query second is?
1)
FROM TABLE1 t1
INNER JOIN TABLE2 t2 ON t1.Id = t2.Id
WHERE
t1.StatusId = 12
2)
FROM TABLE1 t1
INNER JOIN TABLE2 t2 ON t1.Id = t2.Id
AND t1.Sta...
I need a T-SQL ranking approach similar to the one provided by NTILE(), except that the members of each tile would be on a sliding distribution so that higher ranking tiles have fewer members.
For example
CREATE TABLE #Rank_Table(
id int identity(1,1) not null,
hits bigint not null default 0,
PERCENTILE smallint null
)
--Slant the dist...
what is the best way to write a select statement with two table that have a one to many relation ship?
I have a table called broker with these fields
id companyname
then another table called brokerContact that has a foreign key to the broker table with these fields
id brokerid contact name phone
How can I write a select statement...
I have the following snippet:
CONVERT(varchar, FLOOR(ROUND((DATEDIFF(minute, shiftStart.timeEntered, shiftEnd.timeEntered) - DATEDIFF(minute,
lunchStart.timeEntered, lunchEnd.timeEntered)) * 1.0 / 60, 2, 1))) + ':' + CONVERT(varchar, ROUND(ROUND(DATEDIFF(minute, shiftStart.timeEntered,
shif...
I'm about to implement this function to calculate some numbers.
CREATE FUNCTION [dbo].[funLookupFTE] (@PFID int) RETURNS
VARCHAR(20) AS BEGIN
DECLARE @NumberOfFTE AS VARCHAR(20)
SET @NumberOfFTE = (SELECT SUM(CASE WHEN Hours <= 20 THEN 0.5 WHEN Hours > 20 THEN 1 END) AS FTECount
FROM tblPractitioners
...
Hi,
Assume there were 100 records in tableA and tableA contained a column named 'price'.
How do I select the first-n record if where sum of price > a certain amount (e.g. 1000) without using cursor?
thanks
...
Hi,
In my situation, xml data are saved in a text column, how to query this against this column? For example:
create table t1
(
id INT IDENTITY(1, 1) PRIMARY KEY,
content text
)
insert into t1(content) values ('<?xml version="1.0"?>
<people>
<person>
<firstName>ooo</firstName>
<lastName>ppp</lastName>
...
Hello. Using Sybase IQ v12.7. Executing sp_helptext ProcedureName shows the text of the stored procedure which is fine. However it wraps lines at 80 characters. The question is, how to show text text of a stored procedure without wrapping?
In Sybase Central Java Edition there is a feature see the text of a stored procedure (Tranact-...
I am using SQL Server 2005. I would like to update fields like Order BY MatchId orders as below query. But updeted fields not order by MatchId.
DECLARE @counter int
SET @counter = 10008
UPDATE Matches
SET @counter = MatchNumberCounter = @counter + 1
WHERE MatchId IN
(SELECT TOP (232) MatchId FROM Matches WHERE LeagueStatueId =...
i want to place negative value in bracket like -2000 to (2000)
for that i made a which is converting -1361236.75886298 to (1.36124e+006)
the function that i made for this is :
ALTER function [dbo].[IsNegative](
@Number NVARCHAR (500)
)
Returns nvarchar (200)
as
begin
Declare @Number2 FLOAT
set @Number2=Cast (@Number as float)
Declar...