Retriving Previous 5 Rows And Next 5 Rows And The Searched Record
Dear All
I have a table with the following structure in sql server 2005
create table app(
sno int,
name varchar(50),
add varchar(50),
city varchar(50),
state varchar(50)
)
it contains the follwing data
------------------------------------------
sno name add city state
------------------------------------------
1 mark street no1 newcity newstate
2 mark street no1 newcity newstate
3 mark street no1 newcity newstate
4 mark street no1 newcity newstate
5 mark street no1 newcity newstate
6 mark street no1 newcity newstate
7 mark street no1 newcity newstate
8 mark street no1 newcity newstate
9 mark street no1 newcity newstate
10 mark street no1 newcity newstate
11 mark street no1 newcity newstate
12 mark street no1 newcity newstate
13 mark street no1 newcity newstate
14 mark street no1 newcity newstate
15 mark street no1 newcity newstate
16 mark street no1 newcity newstate
17 mark street no1 newcity newstate
18 mark street no1 newcity newstate
19 mark street no1 newcity newstate
20 mark street no1 newcity newstate
----------------------------------------
I want to retrive previous 5 records, next 5 records and the record that meet the where condition of a select query.
When I run
select sno,add,name,city,state from app where sno=7
I want the following result
------------------------------------------
sno name add city state
------------------------------------------
2 mark street no1 newcity newstate |
3 mark street no1 newcity newstate |
4 mark street no1 newcity newstate | -- previous 5 records
5 mark street no1 newcity newstate |
6 mark street no1 newcity newstate |
7 mark street no1 newcity newstate --- searched record
8 mark street no1 newcity newstate |
9 mark street no1 newcity newstate |
10 mark street no1 newcity newstate |--- next 5 records
11 mark street no1 newcity newstate |
12 mark street no1 newcity newstate |
----------------------------------------
if there is a method to get the above result set, kindly post the query.
View Complete Forum Thread with Replies
Related Forum Messages:
Total Of Previous Rows
I have a table like this. Name Amount CBank 5 InstLend 4 DTC 1 Now i want write a query which give me the out put like this. Name Amount CBank 5 InstLend 4 DTC 1 Total 10 can any one help me to write this please?
View Replies !
Total Of Previous Rows
Hi, I have a table like this. Portfolio Name Value ALL UP Charge Off 47 ALL UP CLTV 212 ALL UP DSR 441 ALL UP Exc Total 719 ALL UP FICO 11 I want to add a row to the bottom of the table so that it will give the total of all values above. It's something like this. SELECT Portfolio, Name, Value , Total FROM table Total is equal to sum of all above values. Can anyone help me to write this query? Thanks
View Replies !
Reference Previous Rows
I am doing some calculations with a table and would like to know how to reference a previous row without having to alter the dates in a query at the start of each month. For example I have to find a percentage change for a series of values. The table has three columns, is based on end of month dates, and the "return" column is the calculated column where the value is. In the example the return value for date 2006-04-30 is Index of (2006-04-30/ Index 2006-03-31)-1. Date Index return 2006-03-31 6535 .05130 2006-04-30 6949.29 .06333
View Replies !
Showing Record Rows In Group By SQL
I know I should know this but here goes...I have a table with the following rows:UniqueID int IDENTITYParentUniqueID intRecordLabel nvarchar(50)RecordText ntextRecordDate DateTimeWhat I'm trying to do is to group all records by ParentUniqueIDand display the RecordLabel, RecordText and RecordDate from the mostrecent row for each grouped ParentUniqueID.In other words I need to GroupBy ParentUniqueID and show only theRecordLabel, RecordText and RecordDate for the MAX of RecordDate:Records in Table:UniqueID ParentUniqueID Recordlabel RecordText RecordDate1 1 ThreadOne Blah-Abc 1/1/20052 1 ThreadOne Blah-Def 1/2/20053 3 ThreadTwo Blah-Ghi 1/2/30054 3 ThreadTwo Blah-Jkl 1/3/20055 1 ThreadOne Blah-Mno 1/4/20056 6 ThreadThree Blah-Pqr 1/5/2005What I want to return:UniqueID ParentUniqueID Recordlabel RecordText RecordDate4 3 ThreadTwo Blah-Jkl 1/3/20055 1 ThreadOne Blah-Mno 1/4/20056 6 ThreadThree Blah-Pqr 1/5/2005The problem is I can't use a groupby clause with RecordLabel,RecordText, RecordDate because then it just returns all records (plusRecordtext is an ntext and I have to deal with that issue as well...)Any help is appreciated!lq
View Replies !
Why I Can Not Delete Record Rows With OLE DB Interface?
In a C++ project, I implement a component of accessing database with programming in ole db com interface. I delete many rows in following style: IRowsets::RestartPosition( NULL ); IRowsets::MoveNext( ) do { IRowsetsChange :: DeleteRows( DB_NULL_HCHAPTER, 1, &(m_hRow), &nStatus ); }while( IRowSet::MoveNext( 0 ) ); The Problem: In this style, I only delete two records. while use ' IRowSet::MoveNext( 0 ) ' to get the third records, the result is END_OF_RECORDSET. Why I only delete two rows? Why I do not get the third row? Thank you!
View Replies !
Sql Question - Returning Multiple Rows As One Record
Hi,In the process of localizing the 'regions' table, we added three newtables. The localized data will be stored in the TokenKeys andTokenValues tables. It would be easier if we did away with theTokeyKeys/TokenValues tables and just added a localeid in the regionstable, but this is the desired schema by the client. Here's theschema:Table: regionsid nameabbreviation1 United StatesUSTable: localesid locale1 en_US2 fr_CATable: TokenKeysid key1 db.regions.name2 db.regions.abbreviationTable: TokenValuesid keyid valuelocaleid1 1 Etas Unis22 2 EU2The old sql was simply this:select name, abbreviation from regionswhich returns:United States, USBut the new sql needs to link in the localized data from the tokeykeysand tokenvalues tables using the localeid... I’m trying to figure outwhat the sql statement would look like to return this:Etats Unis, EU (This is supposed to be the French version)My confusion is we are trying to return multiple column values fromthe same column (TokenValues.value) and make them act as separatecolumns in the same record, like it was with the original.Thanks
View Replies !
Combine Multiple Rows Into Single SQL Record
Hello: I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on. productID SectionID statusID 10 1 0 10 2 1 10 3 2 10 4 1 10 5 3 10 6 1 11 1 0 11 2 1 11 3 2 11 7 3 11 8 3 Need to return two rows with the respective values for each section. productID section1 section2 section3 section4 section5 section6 section7 section8 10 0 1 2 1 3 1 11 0 1 2 3 3 Any information or if you can point me in the right direction would be appreciated. Thanks
View Replies !
Retriving The Id Of A New Record
Can anyone help with an effective way in retriving the id of the new record before input of any data into a form. We have a form where a few of the controls recordsource requires the new record id before they will display correctly. I have tried various ways to trigger the form afterupdate event in the hope that the id will be returned but get the error message "The data will be added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying recordsource" Thanks in advance
View Replies !
Retriving The ID Of The Last Record Inserted
I would appreciate help with retriving the ID of the last record inserted. Have spent considerable time in forums and google but can't find anything that works for me. Here is my VB Code Dim queryString As String = "INSERT INTO [DUALML] ([UseriD], [Company]) VALUES (@UseriD, @Company)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dbParam_useriD As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_useriD.ParameterName = "@UseriD" dbParam_useriD.Value = useriD dbParam_useriD.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_useriD) Dim dbParam_company As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_company.ParameterName = "@Company" dbParam_company.Value = company dbParam_company.DbType = System.Data.DbType.[String] dbCommand.Parameters.Add(dbParam_company) Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try Return rowsAffected End Function
View Replies !
Rows Skipped Out In Stored Procedure While Return All Rows If Query Executed Seprate
Hi All, I am using sql server 2005. I stuck out in a strange problem. I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468. But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this. There are indexes in the tables. Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving. But nothing is improving
View Replies !
To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal
Hi, When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message. How can we handle transactions in SSIS 1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS. Any sort of help would be highly appreciated. Thanks,
View Replies !
Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer): user1 answer1 answer2 answer3user2 answer1 answer2 answer3 How can this be done? How can all answers of a user appear on a single row Thanx,Danny.
View Replies !
Compare Values In Consecutive Rows And Print Rows Based On Some Conditions
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState. VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card). The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed. I also would like to add two derived variables. 1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID. 2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day. My report should look like: VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState 1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE 1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE Can someone please help me here? Thanks, Romakanta
View Replies !
Ssis Package Design To Load Only Rows Which Are Changed From Exisiting Rows.
Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID ) and the newly inserted row with a insert time stamp so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond. example of Data would be like exisiting data StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME 12 DDS 14 M XYZ ST 2/4/06 NULL 14 hgS 17 M ABC ST 3/4/07 NULL New row to insert would be 12 DDS 15 M DFG ST 4/5/07 the data should reflect StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME 12 DDS 14 M XYZ ST 2/4/06 4/5/07 12 DDS 15 M DFG ST 4/5/07 NULL 14 hgS 17 M ABC ST 3/4/07 NULL Please provide your input as much as you can even though it might not be a 100% solution.
View Replies !
Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source
Environment: Running this code on my PC via VS 2005 .Net version 2.0.50727 on the server (shown in IIS) Code is in ASP.NET 2.0 and is a VB.NET Console application SSIS 2005 Problem & Info: I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel. Desired Help: How to perform 1) stripping out all undesired rows 2) importing each column into sql table
View Replies !
Finding Rows With Missing Related Rows
Hi, I need to write a query which I have never attempted before and could do with some help.... I have a Groups table and a Users_Groups look up table. In this model, users can only be assigned to 1 group. If a group is deleted, a trigger should fire and delete any rows in User_Groups having a matching Groups.Ref. Unfortunately, the trigger hasn't been firing and I now have a load of defunct rows in Users_Groups relating users to groups which do not exist.I now need to find all of these defunct rows in Users_Groups so that I can delete them. How can I find rows in Users_Groups where the parent rows and refs in Groups are null? I've tried searching the net for something similar but don't even know how to word the search properly to get any half relevant results. Cheers PS, I do realise I need to tighten the constraints on my database
View Replies !
Select Distinct Rows From Duplicate Rows....
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
View Replies !
Help, Selecting Rows Based On Values In Other Rows...
I'm stuck. I have a table that I want to pull some info from that I don''t know how to. There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED'). i.e example, this is the existing data. Call_id Call_Status 555555 3 3RD RESPONDED 235252 7 SEC RESOLVED 555555 7 3RD RESOLVED 325252 6 PENDING 555555 6 PENDING 325235 3 SEC RESPONDED 555555 1 NEW This is the data I want... Call_id Call_Status 555555 3 3RD RESPONDED 555555 6 PENDING 555555 7 3RD RESOLVED The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help. Cheers, Chris
View Replies !
Can I Read Some Rows From The Middle Of Rows In DataReader?
helo.. I have 100,000 rows in the database and I want to read results for eg: from 5000 to 5050 by DataReader. I wrote this code to do this but its too slow: Dim SlctStr As String = "select * from topicstbl where partID like '" & PagePartID & "'" Dim ReadCom As New SqlClient.SqlCommand ReadCom.CommandText = SlctStr ReadCom.Connection = MainLib.MyConnection Dim MyReader As SqlClient.SqlDataReader = ReadCom.ExecuteReader() Dim StartTNum As Long = 5000 For IR As Long = 0 To StartTNum - 1 MyReader.Read() Next Do While MyReader.Read StartTNum += 1 If StartTNum > 5500 Then Exit Do ''''''''''''''''''' Loop MyReader.Close() is there another way to do the same thing better off than this code?
View Replies !
Update Fields With Searched First Date Record Fields
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View Replies !
Return Two Rows From One Rows Data
I know this table is designed wrong for what I am doing but I hope Ican do it. I have a table like this.Prod_A_Jan, Prod_A_Feb, Prod_B_Jan, Prod_B_FebI want a query that returns data like this (two rows of data)"ProdA", Prod_A_Jan, Prod_A_Feb"ProdB", Prod_B_Jan, Prod_B_FebI know two queries can get it but I want one. Any Help would begreat!!!Sheila T.
View Replies !
How To Substract New Rows From Old Rows In The Same Column?
Hi I'm having a bit troubble by creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this: Pricecalcid Date Price Itemid 2000 2006-12-12 3000 100 2488 2007-10-11 2800 100 3100 2008-08-07 2500 100 What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100. How do I do that? Thanks Morten
View Replies !
Fetch Odd Rows And Even Rows From A Table
Dear Friends, Is there any way to display a table data separately like odd rows and even rows?I dont know this is possible or not?If it is possible means how can i achieve it?Please guide me a proper way. Thanks all! kiruthika http://www.ictned.eu
View Replies !
New Rows Based On Existing Rows
Hi All, I have the following Table Type Name Value x M1 5 x M2 10 x M3 20 y M1 10 y M2 15 y M3 30 Now, i need to add four more rows to the table Type Name Value x M1 5 x M2 10 x M3 20 y M1 10 y M2 15 y M3 35 z1 Total 15 (xM1+XM2) z1 Diff 5 (xM3-xM1+XM2) z2 Total 25 (yM1+yM2) z2 Diff 10 (yM3-yM1+yM2) Please help me. Many Thanks,
View Replies !
Value Of A Record Based On A Previous Record
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 Replies !
DataSet Rows Being Deleted, But After The Update , The Sql Database Is Not Updated. The Delete Rows Still In The Database.
Stepping thru the code with the debugger shows the dataset rows being deleted. After executing the code, and getting to the page presentation. Then I stop the debug and start the page creation process again ( Page_Load ). The database still has the original deleted dataset rows. Adding rows works, then updating works fine, but deleting rows, does not seem to work. The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet. cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); } /* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
View Replies !
Fact Table Load - Will Receive 31968 Rows Vs. Will Receive 52428 Rows
I am getting different performance results when importing two different fact tables, and I'm wondering what I can do to tweak the other table so that it is faster. Both are using MSDAORA.1 OLE-DB source and SQL Server Destination. Both have 100,000 defaultbuffermaxrows and 10 enginethreads. Both are set to readcommitted. Can someone tell me what could be causing these two SSIS packages to have different results when reading from Oracle and writing to SQL Server?
View Replies !
Next/previous Record
Hi. Is it possible in SQL query to find record previous or next in comparison with record found with clause WHERE (example of query below)? I need to find record with ProblemID less than or greater than 10. Regards Pawelek. SELECT ProblemIDFROM dbo.tblProblemsWHERE (ProblemID = 10)
View Replies !
Get Previous Record
Please see DDL and INSERT statements below.Let's say that some process throws out the second row, where theClocktime = '02/01/2005 12:34'Without the use of a cursor, how can I retrieve the PREVIOUS value forthat employee? Pseudo SQL might be something like:SELECT*FROMtblTestWHEREfldCLocktime = THE-ONE-IMMEDIATELY-BEFORE '02/01/2005 12:34'ANDfldEmployeeID = 1TIAEdwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTest]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblTest]GOCREATE TABLE [dbo].[tblTest] ([fldTestID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldClocktime] [datetime] NULL ,) ON [PRIMARY]GOINSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'01/01/2005 12:34')INSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'02/01/2005 12:34')INSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'03/01/2005 12:34')
View Replies !
How To Get Previous Record Thru Sql Query
How to Get previous record thru sql query For the example my table: 1 usera item1 1.00 01/02/072 usera item1 2.00 02/02/073 userc item2 3.00 03/02/07 --how to use the query to make them join became like this (get/join with the next record) 1 usera item1 1.00 01/02/07 item1 2.00 02/02/073 userc item2 3.00 03/02/07 null null null >.<need help ... thanks alot
View Replies !
Selecting Next And Previous Record
I'm trying to add a next and previous button to a popup window I have on a photo gallery - Having trouble with the sql I know I would have 2 select statements that would each return 1 result set. I know how to get the top ( and bottom by ordering descending and doing Select Top 1) But how would I get the next one up! I would hate returning the entire image section and perform the calcuation in the business tier :(
View Replies !
Select Next And Previous Record
Hi! I need to select a spesific record using the recordkey and then select the previous and the next record as well. (which leaves me with a recordset containing three records) Is this possible?
View Replies !
Get Next Record In A Table Rather Than Previous
I'm creating a report in SSRS where we want to do a week-over-week or month-over-month analysis. We have each month and the metrics in a table. By sorting the table ascending, I can grab the previous record using the Previous function in a cell. However, when I order the table descending the previous record is not the correct record to get. In that case I want to grab the next record rather than the previous record. How can I invert the previous function? Or how can i grab the next record in the table rather than the previous? Thanks!
View Replies !
Getting Previous Record From Another Table
Hello just wondering, if I can get help with a table (Table A) , where there are few dates and numbers, I have to relate this table to another table (Table B) , where I have whole year working dates I want to write an expression while making dataset, that if date in table B matches table A, it just get the number from the next column from table A, if it does not matches - it should go back to the date (in table A) - where there is a number. Like table B has working dates for march, but there are only two dates in table A , for 3/2 (and number is 300) and 3/20 (number 200). So In dataset I want all dates from table B and from 3/2 to 3/19 300 in number column and 200 on 3/20. Thanks in advance for your help
View Replies !
How To Retrive 1st, Next, Previous & Last Record ?
Hi, I am new to using SQL. Currently, I'm using the following statemens to retrive a specific record from my MS Access DB via VB.net. SELECT * FROM table_name WHERE Field_Name = Criteria Can someone please tell me, after selecting this record, If I want to go to the FIRST, or NEXT or PREVIOUS of the record just retrived or the LAST record. Can someone please tell me how can write the SQL statment to achieve this ? Regards
View Replies !
Transform To Remove Rows From Data Set A That Match Rows In Data Set B On A Given Key?
Hi, I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input. E.g. If I have the following data: Main input: Key Name --- ---- 1 Steve 2 Jamie 3 Donald Second Input Key DontCareAboutThisField1 --- ----------------------- 1 ... 3 ... 4 ... Then I would like the following output: Main Output Key Name --- ---- 2 Jamie Second Output Key Name --- ---- 1 Steve 3 Donald Can I do this with a standard transform, or will I have to write my own? Any help on this would be greatly appreciated! Thanks in advance, Lawrie
View Replies !
Fast Way To Get Values From Previous Record
Hello,I know that I've seen this question asked on here before, but I can'tfind an answer that gives me the performance that I need.I have a table that stores events for users:CREATE TABLE Lead_Action_History (lead_action_seq INT IDENTITY NOT NULL,lead_action_date DATETIME NOT NULL,lead_seq INT NULL,operator_id VARCHAR(20) NOT NULL,call_time INT NOT NULL,CONSTRAINT PK_Lead_Action_History PRIMARY KEY (lead_action_seq) )GOThe table has a foreign key to another table through the lead_seqcolumn:CREATE TABLE Lead_Master (lead_seq INT IDENTITY NOT NULL,state CHAR(2) NOT NULL,CONSTRAINT PK_Lead_Master PRIMARY KEY (lead_seq) )GOI need to write a query that will give me a sum of call_time brokendown by a column that is in the table joined through the lead_seq.However, if the lead_seq for a row is NULL then I need to use thelead_seq for the previous row (based on lead_action_date) for the sameoperator.This is what I came up with:SELECT LM.state, SUM(call_time)FROM Lead_Action_History LAHINNER JOIN Lead_Master LM ON (LM.lead_seq = LAH.lead_seq)OR (LAH.lead_seq IS NULLAND LM.lead_seq = (SELECT TOP 1LAH2.lead_seqFROMLead_Action_History LAH2WHERELAH2.operator_id = LAH.operator_idAND LAH2.lead_seqIS NOT NULLORDER BYLAH2.lead_action_date DESC))GROUP BY LM.stateThe problem is that Lead_Action_History has millions of records andany solution that I've found involves one or more subqueries on itwhich kills performance. I am going to look at using a covering indexwith the solution above, but I thought that someone here might haveanother way of doing this.I can't really change the structure, but I can play with the indexing.I would still be curious though how other people model this type oftemporal data in a way that makes it easy to work with.Thanks!-Tom.
View Replies !
How To Check Previous And Next Record Values
Hi all, I wanted to check the previous and next record values. For example: sKey NextKey PreviousKey 1 2 Null 2 8 1 8 5 2 5 null 8 Ex : In the first record of the table, the NextKey is pointing to 2. So the next record of Skey will be 2. The Next Key for this record is 8. Like wise the next record of this should have the Skey as 8. Now I need to check whether the NextKey and SKey are correct for all rows. For that I need to check the previous record of "Next key" and next record of "Skey". Any idea? Regards Helen
View Replies !
How To Make Automated Update On The Previous Record...
Hello...i have a table that record all the reading meter....so when i change one of the reader meter data...in logical it will automatically change the normalized fields... Reading teble ------------------- -id[PK] -meter -normalized -date when i insert new record..i just insert data about date and meter...an normalized is automated calculate using my function..the problem is..when i have data more than one...when i try insert or update or delete data...it nee to be automatically calculate back the normalized..i know this is needed the temperory table and then reinsert back..how can i solve this problem???
View Replies !
|
TRACKER
