Can any one let me know as how can I query the first occurence of a record. I need to select all distinct Order Types from a table. If there is more than one record with the same Order Type, then I need to get the first record.
It would be a great help to me.
Thanks,
John
Hi all,
Is there a function in SQL that will return the number of occurence of 1 string to the other.
e.g.
string1 = "1"
string2 = "2131145"
function should return 3
How do i obtain this in SQL?
Thanks in advance
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
Hi all,How can I get a row that has only one occurence in a table? Notthrough 'distinct' because this gets a single row that might havemultiple occurences, I want to get only rows that have only oneoccurence in the table.I tried the group by and within the having clause I placed thecount(myfield) = 1, did not work...select f1, f2, count(f2)from table1inner join table2 on table1.id = table2.fidwhere .....group by f1, f2having count(f2) = 1gives me result like this: (basically in this situation, I dont want toreturn any results)f1 f2 count(f1)--- --- -----------1 a 12 a 1any ideas sql gurus?thanks
View 3 Replies View RelatedHi ...I have a weblog database where I want to count the occurences of atable of string values that appear in all the urls viewed.My tblWebLog as a field that contains the url ...tblWebLog.[cs-uri-stem]I have another table ... tblStrings ... that has a field [strSearch]for a string value and an integer field [intViewCount] to count theoccurence of the string in tblWebLog.[cs-uri-stem]I've been trying ...Update tblStringsSet [intViewCount] = (Select Count(*) From tblWebLog Where[cs-uri-stem] Like '%_' + tblStrings.[strSearch] + '.htm%').... but it doesn't fly and I'm stumped. Any thoughts?Cheers.
View 2 Replies View RelatedI have a table that tracks GPS records broadly speaking as followstsDATETIMEusernameVARCHAR(16)...GPS data...I want to select the most recent GPS data for each distinct user.That is, the table will have may records for a given username, but Ionly want the most recent for each one.For a single user I know I can doSELECT TOP 1 * from <table> order by ts descBut I want a set of results that effectively does this forall users, and I can't work out the necessary query/subquery Ishould be using.I'm sure I'm missing something fairly obvious, so usual newbiedisclaimers apply.--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
View 4 Replies View RelatedWe have a strange occurrence in one of our dev Sql server. Our platform is Sql Server 2000 on SP4. Build Number is 8.00.2039
TableA
Sid int, --Non-Clustered Index
Aid int,
Uid int,
€¦..
TableB
Iid int,
Sid int, ---No foreign key constraint to TableA.Sid but logically refers to that column. --Non-Clustered Index
€¦..
Now, we are working on schema changes, now TableB looks like
TableB
Iid int,
Uid int, ---Logically refers to TableA.Uid
€¦..
Sid in TableB is dropped and a new column Uid is added.
And there is no Sid in TableB.
We have an existing SP that refers to TableB.Sid like
Create proc sp_ab
As
Select Uid from TableA where Sid = (Select Sid from TableB where Iid = @Iid)
After making the schema changes listed above, if I try to compile this SP without making any changes to the procedure, it should fail. Surprisingly it passes compilation and also retrieves data using hash joins and doing table scans on TableA avoiding index seeks. This SP used to take less than second, now takes almost 90 seconds to retrieve data. I couldn't replicate this problem with another set of objects.
If I make changes to the SP sp_ab like
Create proc sp_ab
As
Select Uid from TableA where Uid = (Select Uid from TableB where Iid = @Iid), it runs less than a second as it should.
Whats surprising is, if I do 'Select Sid from TableB where Iid = 1 ' in Query analyzer, it throws error referring to invalid column Sid in TableB but couldn't throw this error when the sql is wrapped in a SP. Everything else in the DB and in the db server seems to be ok except this.
If I query Syscolumns table, I couldn't see any references to Sid in TableB after the schema chnages and not sure why the code in SP is not caught during compilation. Any clues.
Hi All,
I want to extract a numeric value from a string. Example, in a string like - Mgmt Pack: Processor Exception Threshold >80% Every 10 Minutes. - I want to extract that number 80. Since, later I'll want this number to plot a graph.
Since this is going to be an alert pulled from OnePoint (MOM Operational Database), the number will vary, and so I cannot look for the same number. So, can anyone help me how to get this working?
Thanks a lot in advance and let me know if the question is not clear.
Manoj Deshpande.
Hi Friends:
I usually run this query in Oracle to find out the position of a character in a string from first position to the nth occurence.
For example, I run the following queries in oracle to get the desire result.
SQL> select instr('DADADAQQQA','A',1,1) FROM DUAL;
INSTR('DADADAQQQA','A',1,1) -- First occurence of 'A' from start.
---------------------------
2
SQL> select instr('DADADAQQQA','A',1,2) FROM DUAL;
INSTR('DADADAQQQA','A',1,2) -- Second occurence of 'A' from start.
---------------------------
4
SQL> select instr('DADADAQQQA','A',1,3) FROM DUAL;
INSTR('DADADAQQQA','A',1,3) -- Third occurence of 'A' from start.
---------------------------
6
SQL> select instr('DADADAQQQA','A',1,4) FROM DUAL;
INSTR('DADADAQQQA','A',1,4) -- Forth occurence of 'A' from start.
---------------------------
10
Is there ay equivelant way in Transact- SQL? If not, can anybody suggest the solution?
--Raj
Hi Everyone-
i woder if someone know Built in function to search the occurence of char in string in T-SQL
and iam not talking about the Index of the first occurence (e.g CHARINDEX )
but it is more generic
i want to send to the function to ask about the index the second or the third or N occurence of the char.
and i wonder if it is built in for performance reasons
example
getseachIndex("M|d|d","|",2)==> the result is 4
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies View RelatedHi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
Hey all!
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
Thanks in advance,
Johan
Hi,
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks,
Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
Hi
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View 3 Replies View RelatedHi Everyone-
i have a matrix report
and i want to switch the record background color with each record in the value column in that matrix report
e.g 1st record background color is gray and next record background color is white
and then the next record background color is gray ... and so on
can anyone help?
thanx
Maylo
Could anybody help me with the following scenario:
Table 1 Table2
ID,Date1 ID, Date2
I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date.
Example:
Table1 data Table2 Data
ID Date1 ID Date2
31 1/1/2008 31 1/5/2008
34 1/4/3008 31 4/1/2008
31 3/2/2008
The first record in table2 would only link to the first record in table1
The second record in table2 would only link to the third record in table1
Any help would be greatly appreciated.
Thanks
I've been looking for examples online to write a SPROC to get some data. Here are the tables.
Album_Category
AlbumCategoryID (PK, int, not null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)
Albums
AlbumID (PK, int, not null)
AlbumCategoryID (int, null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)
I need to return:
-[Album_Category].[AlbumCategoryID]
-[Album_Category].[Caption]
-[Albums].[Single AlubmID for each AlbumCategoryID]
-[Count of Albums in each AlbumCategory]
I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.
I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.
View 4 Replies View Related
Hello. I have a database with a record that has two columns locked. descrip1 and descrip2. they are both nvarchar(max) columns. These are the only two columns of the record that remain locked. I am certain no user is accessing the record. I have even moved a backup of the database to my testing computer and the lock still exists. How do I remove the lock from these two columns on that particular record.
I can edit these two columns on other records. I have researched "Unlock" on MSDN but it doesn't seem to apply to t-sql. Any help would be greatly appreciated.
Thanks. Gary.
I am trying to update a record in a table based off of criteria of another record in the table.
So suppose I have 2 records
ID owner type
1 5678 past due
2 5678 late
So, I want to update the type field to "collections" only if the previous record for the same record is "past due". Any ideas?
I have not yet succeeded in getting an aswer to this in a previous post, so I'll try again and rephrase the question
I have 2 fields, 1 called 'Created' the other 'Updated'
I would like to create a function, user procedure ,whatever in SQL something along the lines of...
FUNCTION myUID()
RETURN Date(Today)+Time(Now)+USERNAME
END FUNCTION
so that the value returned by the above pseudo code is something like '20080526T21:01:05.620SamL' where the date part is in yyyy0m0d format
I have got this working fine for the default 'Created' field but the calculated (and persisted) field comes up with something about being non-deterministic and refuses to play
Clearly something along these lines is a very straightforward requirement (I have it working fine in my 20+ year old database) , but have spent a week trying to get an answer (even from this forum) without success, so would be grateful for any help that you can provide me with to get round this barrier
Hi All,
I am trying to create package something like that..
1- New Customer table as OleDB source component
2- Lookup component - checks customer id with Dimension_Customer table
3- And if same customer exist : I have to update couple fields on Dimension_Customer table
4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination
but How can I update the existing ones?
I have tried to use oledb command but somehow it didnt work
my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
I will be appreciated if you can help me...
Hi,
How can we insert multiple records in a OLEDB destination table for each entry from the source table.
To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record.
how is this achieved.
thanks.
I tried checking to see if the point at which the reader was, that if it was the record I am looking for to go ahead and add the table data to a label. But for some reason it's only taking the first record in the database and not the one I thought I was at.[CODE] public void UpdateMaleHistLbl() { SqlConnection conn = new SqlConnection("Server=localhost\SqlExpress;Database=MyFamTree;" + "Integrated Security=True"); SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = @usrnmeLbl ", conn); comm.Parameters.AddWithValue("@usrnmeLbl", usrnmeLbl.Text); conn.Open(); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { string usr = reader["username"].ToString(); usr = usr.TrimEnd(); string pss = reader["password"].ToString(); pss = pss.TrimEnd(); if (usrnmeLbl.Text == usr) { if (hiddenpassLbl.Text == pss) { maleHistLbl.Text = reader["GG_Grandfather"] + " > "; maleHistLbl.Text += reader["G_Grandfather"] + " > "; maleHistLbl.Text += reader["Grandfather"] + " > "; maleHistLbl.Text += reader["Father"] + " > "; maleHistLbl.Text += reader["Son"] + " > "; maleHistLbl.Text += reader["Grandson"] + " > "; maleHistLbl.Text += reader["G_Grandson"] + " > "; maleHistLbl.Text += reader["GG_Grandson"] + "<br /><br />"; } } break; //exit out of the loop since user found } reader.Close(); conn.Close(); }}[/CODE]Thanks in advance
View 1 Replies View RelatedConsider this scenario.
I have two database in the sql server and consider that i have a query which has 4 tables inner joined.
When i execute the query in the database1 , the query is returning rows, But when i execute the same query in the database2, the query is not retuning rows . I know that the
no rows are returned because of missing data in the database2. But have no idea how to trace what values are missing in the database2. Please note the tables is having a huge
list of records by which manually comparison is painfull. Please consider i dont have any background idea of the values in the tables but just using it. Any help would be
appericated.
I have 2 tables they are identical what i need to do is when i make a update on one table the other table needs to reflect that same table. so lets say i insert into table A i need to look in table B if the record exist if it doesnt insert it if it does update it and if it exist in table B and doesnt exist in table A delete the record but i have to do this record by record..can anyone help me with this
View 2 Replies View RelatedI need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.
Something like this maybe using a cursor or something else:
For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next
So below, all I need to do is figure out how to loop through each column for the current record in the cursor
AS
DECLARE Create_Final_Table CURSOR FOR
SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1
OPEN Create_Final_Table
FETCH FROM Create_Final_EBN_Table INTO @FieldName, @AcctNumber, @Screen, @CaseNumber, @BKYChapter, @FileDate, @DispositionCode, @BKUDA1, @RMSADD2, @RMSCHPNAME_1, @RMSADDR_1,
@RMSCITY_1, @RMSSTATECD_1, @RMSZIPCODE_1, @RMSWORKKPHN, @BKYMEETDTE, @RMSCMPNAME_2, @RMSADDR1_2, @RMSCITY_2, @RMSSTATECD_2,
@RMSZIPCODE_2, @RMSHOMEPHN, @BARDATE, @RMSCMPNAME_3, @RMSADD1_2, @RMSADD2_3, @RMSCITY_3, @RMSZIPCODE_3, @RMSWORKPHN_2
WHILE @@FETCH_STATUS = 0
BEGIN
@Chapter = chapter for this record
For each column in current record <---- not sure how to code this part is what I'm referring to
do some stuff here using sql for the column I'm on for this row
Next
Case @Chapter
Case 7
Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record
Case 13
Insert Record
Insert Record
Insert Record
Case 11
Insert Record
Insert Record
Insert Record
Case 12
Insert Record
Insert Record
Insert Record
END
close Create_Final_Table
deallocate Create_Final_Table
I hope you can help me. I posted this in the microsoft sql server newsgroupa few days ago and got no response so I thought I'd try here. If I canprovide any clarification I'll be glad to do so.I'm trying to calculate a column based on the value of the previous record.I'm not very experienced with SQL-Server.I'm using the following table:CREATE TABLE tblPayment([PaymentID] [int] IDENTITY (1, 1) NOT NULL ,[LoanID] [int] NULL ,[PaymentPeriod] [int] NULL ,[PaymentRecDate] [datetime] NULL ,[PaymentAMT] [money] NULL)I have a view based on this table. That view has the following calculatedcolumnsBeginningBalance: For the first record, this is equal to the loan amountfrom the loan table. For each additional record this is equal to the endingbalance from the previous payment record.Interest: BeginningBalance * the monthly interest rate from the loantablePrincipal: PaymentAMT - InterestEndingBalance: BeginningBalance - PrincipalIt might seem I could use a subquery to calculate the Beginning Balance asin:SELECT LoanID, PaymentPeriod, PaymentAMT,(SELECT SUM(PaymentAMT) FROM tblPayment AS tbl1WHERE tbl1.LoanID = tblPayment.LoanID AND tbl1.PaymentPeriod <tblPayment.PaymentPeriod) AS BeginBalanceFROM tblPaymentWHERE (LoanID = @LoanID)But this will not work, because the interest is calculated on the previousmonth's balance. I need to find a way to loop through the recordset. Isthis possible?Thank you,--Derek CooperDatabase9www.database9.com
View 5 Replies View Related