Isolating Records When More Than One Row Exists....

Nov 8, 2007

I am trying to isolate a specific record (and a value within that record) where I have selected records based on a max(date). Some records come back duplicated because there are duplicate max dates.

For example:

SELECT

O.ID,
O.Date,
O.Amount = Case when o.Value in ('6','7','H','I') then 1 else 2 END
, O.Value


FROM Table O
WHERE o.Date = (

SELECT

MAX(O2.Date)
FROM Table O2
WHERE O2.ID = O.ID
)



The problem is that I get some ID's with Multiple records because they have 2 different O.Values. My table looks like this:


10/12/07 A $5.00
10/12/07 B $7.00
10/10/07 C $8.00

I want to find the Max(O.Date). Since there are two, then I want to find any records with that date that have an O.Value = B, if there isn't any, then I want any with an O.Value = A, if there isn't any of those, then any records with the O.Value = C. I basically need the O.Amount depending on the hierarchy of the O.Value.

I hope that makes sense. Any help would be appreciated.

- S

View 3 Replies


ADVERTISEMENT

Isolating A Processor For SQL 7

Dec 7, 2000

I have a rather intensive load on a server that has a data mapping application on the same server(I know this is a no no but I didn't have a say). It seems to be maxing the CPU. Is there a way on a dual or quad processor machine to tell SQL Server to only use one of the processors?

View 1 Replies View Related

Isolating SQL Query!

Apr 28, 2004

hello
Since the past couple of months we have been seeing the tempdb grow from 5GB to 22GB about once every week. The production database is about 35GB. Since there is quite a bit of activity on the server i am finding it difficult to try to isolate what caused this sudden increase in the tempdev size. The actual data in tempdev less then 2 MB but it holds on to the 22GB till i go in and shrink it.
This has led to us having serious space issues on the server.
Any advise on how i would be able to trap the query that might cause this behavior would be great.

thanks

View 4 Replies View Related

SQL CLR: Isolating A CAS Permissions Fail

Jun 15, 2007

Hi All,

I have a SQL CLR Trigger, which has bizarrely (to me anyhow) requested full UI rights from CAS, SQL understandably isn€™t permitting this.




Code Snippet

The protected resources (only available with full trust) were: All
The demanded resources were: UI

I understand why SQL is failing, but what I€™d like to find out is how can I debug / isolate the offending code so I can refractor accordingly.
The namespaces I€™m utilising are as listed below; quite how or why any of these need UI requests is beyond me.

System
System.Data
System.Data.SqlClient
Microsoft.SqlServer.Server
System.Transactions
System.Text
System.Security.Permissions
System.Text

Does anyone have any experience or tips on what could be tripping me up here or how to isolate the offending call which SQL is failing?

Many thanks in advance for any help you may be able to afford me.

View 1 Replies View Related

T-SQL (SS2K8) :: After Update Trigger - Only Insert Records Not Exists

Jul 30, 2014

I have an address table, and a log table will only record changes in it. So we wrote a after udpate trigger for it. In our case the trigger only need to record historical changes into the log table. so it only needs to be an after update trigger.The trigger works fine until a day we found out there are same addresses exist in the log table for the same student. so below is what I modified the trigger to. I tested, it seems working OK. Also would like to know do I need to use if not exists statement, or just use in the where not exists like what I did in the following code:

ALTER TRIGGER [dbo].[trg_stuPropertyAddressChangeLog] ON [dbo].[stuPropertyAddress]
FOR UPDATE
AS
DECLARE @rc AS INT ;

[code]....

View 2 Replies View Related

How Can I Get All Records For Both Tables With The Latest Begin Date If Exists?

Jun 15, 2006

Itemlookup tableField names : index_id (primary key), itemno, description.It has a child table, which is ItemPriceHistory tableThe relationship to the child table is one (parent table)-to-many(child table). - It is possible to have no child record for some rowsin the parent table.ItemPriceHistory tableField names: index_id (primary key), itemlookupID (foreign key of theItemlookup table), date begin, priceIt is a child table of the itemlookup table.How can I get all records for both tables with the latest begin date ifexists?I also need to show the records in the parent table if there is norelated record in the child table.Please help

View 4 Replies View Related

Get Records In Table1 Not In Table2 - Not Exists Get Null Result

Jun 9, 2015

I'm trying to get the records in table1 not in table2 the query is

Select * from table1 where not exists (select * from table2)
--table1 and table2 ,structure are same

It will get a null result

Strange thing , while I add a where condition in the subquery, it worked.

Select * from table1 where not exists (select * from table2 where table1.ID =table2.ID)

It will get the result as expected.

I just thought it would compare all the fileds for the outer query and inner query, but seems not. Why, I add a condition, it worked ?

View 4 Replies View Related

Checking To See If A Records Exists Before Inserting - 3 Million + Rows

Aug 21, 2007

I have 1+ CSV files (using a foreach loop) which I'm doing a lot of transform work on and then inserting into a SQL database table.
Each CSV file usually contains about 2 days worth of data (contains date stamps) - somewhere in the region of 60k records per day.
The destination table currently contains 3 million+ rows and will get bigger.
I need to make sure that before inserting into the destination table, the data doesn't already exist.

I've read the following article: http://www.sqlis.com/311.aspx
While the lookup method works, it takes ages and eats up memory as it caches the 3m+ records before running for each CSV. Obviously this will only get worse as the table grows in size.

To make things a little more efficient what I'd like to do, is first derive the dates I'm dealing with in the current file - essentially storing the max(date) and min(date) in variables. Then in the lookup SQL use those vars, to reduce the amount of data that needs to be brought into the transformation to check against before inserting into the destination table.
Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.

Ideally I'd use an aggregate transformation and then use the subsequent output from that either in the lookup query or store the output in vars, but I don't think you can do that and I get the feeling I'm approaching this with the wrong mindset.

Any thoughts would be great!

View 6 Replies View Related

T-SQL (SS2K8) :: Isolating A List With Pattern Search

Jul 14, 2014

I'm trying to pull up a report of restored databases from our bug tracking software to audit and see if any of them can be taken down, and having some trouble figuring out how to pull the list of databases out of the entire request text, since they usually come in via email. Some sample data is below with the paltry beginning of a solution that I came up with.

WITH bug (BugID, BugComment) AS (
SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'
UNION ALL
SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'
UNION ALL
SELECT 3, 'Urgent DB_001000 DB_001000 Glick'

[Code] ....

View 6 Replies View Related

T-SQL (SS2K8) :: Historical Data Where Number Of Records Exists Between Two Dates With Different Years

Jul 10, 2015

Ok, I'm looking to get counts on historical data where the number of records exists between two dates with different years. The trick is the that the dates fall in different years. Ex: Give me the number of records that are dated between 0ct 1, 2013 and July 1, 2014.

A previous post of mine was similar where I needed to get records after a specific date. The solution provided for that one was the following. This let me get any records that occured after May 1 per given Fiscal year.

SELECT
MAX(CASE WHEN DateFY = 2010 THEN Yr_Count ELSE 0 END) AS [FY10],
MAX(CASE WHEN DateFY = 2010 THEN May_Count ELSE 0 END) AS [May+10],
MAX(CASE WHEN DateFY = 2011 THEN Yr_Count ELSE 0 END) AS [FY11],
MAX(CASE WHEN DateFY = 2011 THEN May_Count ELSE 0 END) AS [May+11],
MAX(CASE WHEN DateFY = 2012 THEN Yr_Count ELSE 0 END) AS [FY12],

[Code] ....

I basically need to have CASE WHEN MONTH(OccuranceDate) between Oct 1 (beginning year) and July 1 (ending year).

View 4 Replies View Related

SQL Server Admin 2014 :: Few Record Loss In Table Primary Key Where Same Records Exists In Foreign Key Table?

Jun 21, 2015

Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.

View 3 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

IF NOT EXISTS (... - EXISTS TABLE : Nested Iteration. Table Scan.Forward Scan.

Sep 20, 2006

Hi,

This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).

I'm looking at these new databases and I'm seeing code similar to this all over the place:

if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end

There's a unique clustered in dex on t1.f1.

The execution plan shows this for this statement:

FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

It's not using my index!!!!!

It seems to be the case with EXISTS statements. Can anybody confirm?

I also hinted to use the index but it still didn't use it.

If the existence check really doesn't use the index, what's a good code alternative to this check?

I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.

SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0

Appreciate your help.

View 3 Replies View Related

Deleting Old Records Is Blocking Updating Latest Records On Highly Transactional Table

Mar 18, 2014

I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.

In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously

While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.

Is there any SQL Server hints to avoid blocking ..

View 3 Replies View Related

SQL Server 2008 :: Parent Records Ordering And Display Child Records Next To It?

Sep 7, 2015

declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2

[code]....

This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....

Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending

--Final output should be

PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4

View 2 Replies View Related

Data Flow Task To Delete Records And Then Insert Records In Transaction

Aug 6, 2007

HI,

I have been trying to solve the locking problem from past couple of days. Please help mee!!

Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.

The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.

I dont know how to come out of this locking. Please help.

Thanks ,
Sunil

View 7 Replies View Related

HOW To Select A Matrix (cross Join) With Empty Records To Retrieve The Same Amount Of Records For Each Cell

Nov 2, 2006

Hello

Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:

This is a simple matrix


test a

text b








text c








text d

text e

text f








text g










This is a matrix with all the same row-height.



test a

text b

.








text c

.
.









text d

text e

text f








text g

.

.









Thx you a lot

View 3 Replies View Related

T-SQL (SS2K8) :: Renumbering Remaining Records In A Table After Some Records Deleted

Dec 3, 2014

I have a table with about half a million records, each representing a patient in my county.

Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.

I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.

I can see what I want to accomplish by using ROW_NUMBER, thus:

SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank

I see the numbers in the RecNumber column falling behind the RRank as I scan down the results

My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:

UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);

but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).

View 5 Replies View Related

I Want To Transfer ONLY New Records AND Update Any Modified Records From Oracle Into SQL Server Using DTS

Jul 23, 2005

I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.

View 2 Replies View Related

Need Efficient Query To Partition Records By Type And Pull Top N Records From DB

Jan 18, 2008

I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.

1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.


;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC

Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?

Thanks in advance.

View 4 Replies View Related

Insert 9900 Records Out Of 10000 Records Using DTS

Nov 28, 2005

I tried to port 10000 records using DTS. After porting of 9900 records I got an error and comes out without any result. But I want to keep the records which has been ported till the error occured. Plz help me.

View 1 Replies View Related

Looping Thru Records To Find Related Records

Oct 31, 2007

Hi, I have had this problem for a while and have not been able solve it.

What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.

then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.

The table name is Adm_disc_Match_tbl

Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)

example of data


Code: ( text )
Index_key,ur_episode,Admission_datetime,discharge_ datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17


If anyone could help it would be much appreciated.

View 6 Replies View Related

IF NOT EXISTS

Jul 7, 2006

Hello,
I am trying to create a table if one with the same name does not exists.  My code is:
Dim connectionString As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|PensionDistrict4.mdf;Integrated Security=True;User Instance=True"
Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
Dim newTable As String = "CREATE TABLE [" + titleString + "Comments" + "] (ID int NOT NULL PRIMARY KEY IDENTITY, Title varchar(100) NOT NULL, Name varchar(100) NOT NULL, Comment varchar(MAX) NOT NULL, Date datetime NOT NULL)"
sqlConnection.Open()
Dim sqlExists As String = "IF EXISTS (SELECT * FROM PensionDistrict4 WHERE name = '" + titleString + "Comments" + "')"
Dim sqlCommand As New SqlCommand(newTable, sqlConnection)
If sqlExists = True Then
sqlCommand.Cancel()
Else
sqlCommand.ExecuteNonQuery()
sqlConnection.Close()
End If
I keep getting a "Input String was incorrect format" for sqlExists?  I am new to Transact-SQL statements, any help would be appreciated.
Thanks Matt

View 1 Replies View Related

Where Not Exists---help

May 27, 2007

ok i have 2 tables---one table is name CourseInformation with a field named Instructor and the data in there looks like this 'John Doe'.
My other table Instructors has 3 fields InstructorName, LastName, FirstName.
I am grabbing the Instructor field from CourseInformation and breaking up the names and inserting them into my instructors table as follows..
Insert into Instructors(InstructorName, LastName, FirstName)
(Select Distinct
ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor)))+', '+
SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1),
ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor))) as LName,
SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1) as FNamefrom CourseInformation
where NOT EXISTS(Select * from instructors where LastName = LName and FirstName = FName)
AND Instructor is not null)
 
Only problem is, I cant get the where not exists clause to work right(of course that wont work what i have cuz the LName and FName columns dont exist, i just did that for demo purposes).  I dont want duplicate instructors in there..how can i accomplish this..is there a better way to rewrite my query? Any help is appreciated.
 

View 3 Replies View Related

NOT EXISTS Vs NOT IN

Feb 7, 2008

HI All,Which is best among the two 1) NOT IN or 2) NOT EXISTS .If the query is Select col1 from tab1 where col2 NOT IN (Select col 3 from tab2 where cl3=0) OR Select col1 from tab1 where col2 NOT EXISTS (Select col 3 from tab2 where cl3=0)  

View 2 Replies View Related

If Not Exists

Oct 12, 2001

Hi,

I have a question on the if not exists. Can you do something like this? If request not exists in table one then insert values into table two?

Thank you in advance,
Anne

View 3 Replies View Related

If Exists..

Jul 15, 2004

Hi,
Is there any way to check whether a column is there in the table, if it is there i need to drop it through script.

i'm looking for the script, something like this..

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tbl_Product_Tbl_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tbl_Product] DROP CONSTRAINT FK_Tbl_Product_Tbl_Products
GO

In the same way i need to check for a column and drop it through script.
Any help would be greatly appreciated.
Thanks in advance.

View 2 Replies View Related

Help With EXISTS

Jun 15, 2007

hi guys! is there anyway to retrieve the row from the result of EXISTS function aside from using the code below?


if EXISTS (select * from rcps_useraccount where User_login = 'daimous')
BEGIN
select * from rcps_useraccount where User_login = 'daimous'
END

View 3 Replies View Related

Exists

May 16, 2008

Im having a problem with the following can anyone spot how i can fix it? I dont think it likes the begin statement but without it, it has a declare issue.




IF EXISTS (SELECT 1
FROM snapevent.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='FastEp_Snap_OD_Acc' + preports.dbo.f_filedate(getdate())
begin
declare @CMD nvarchar(300)
Set @CMD = 'drop table Snap_OD_Acc_' + preports.dbo.f_filedate(getdate())
--print @CMD
exec (@CMD)
end

View 1 Replies View Related

If Exists - SP

Jun 20, 2008

Hi

Please help me

I have to create a SP.

The secenario is that,
A application calls the SP with a parameter(login), and a string of datas ( Acctid level1 level2; Acctid level1 level2; .......)

UserID AcctID Level1 level2
test testee N Y

the SP have to get the first string of data and check if the Acctid exists or not. If yes then update else insert.Then get then the second string of data and check if the Acctid exists or not. If yes then update else insert.


After checking all the strings ,
it have to check if any Acctids other than acctid mentioned in the string exists in the table for that login, then delete those rows

View 1 Replies View Related

Where Exists

Feb 18, 2006

I'm trying to perform an insert query on a table, but I also want to check to see if the record exists already in the table. It should be fairly simple, but I'm having a time of it. Should be something like:

select * from users u
inner join miamiherald m
on u.emailaddress = m.advertiseremail
where not u.emailaddress not exists <<< (???)

If it does exist, I then want to retrieve two columns from it. HELP!!

View 1 Replies View Related

IF EXISTS

May 28, 2007

Hi,

The following works in SQL 2005 but NOT SQL 2005 Compact Edition:

IF EXISTS (SELECT ID FROM Court2 WHERE BookingDate = '2007-05-28')
UPDATE Court2 SET T1100 = 52 WHERE (BookingDate = '2007-05-28')
ELSE
INSERT INTO Court2 (BookingDate,T1100) VALUES ('2007-05-28',52)

In CE I get the following error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

I can't find where the problem is - can someone help.

Thanks,

View 5 Replies View Related

Which One Is Better In Or Exists

Dec 6, 2007

Dear all,
i am trying to improve the performance of stored procedures and functions in that in key word is there i have to replace with exists.which one will give better performance.


Thanks&Regards,

Msrs

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved