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
I wanted to check the value of NextKey of Prev record and Skey of Next record.
Any idea?
Regards
Helen
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
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 4 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 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 RelatedHi...
Is there any way to check previous row in SQL Query?
I have a table with these column :
Name1
Name2
Audit_Time (datetime)
Changes
I want to delete record from database in which the Audit_time is <'01/05/2004'.
However before deletion, I want to check, if the Changes value is 'OLD' And the previous value is 'NEW', I will check the Audit_time of the NEW instead of OLD.
Table :
Row Name1 Name2 Audit_Time(mm/dd/yyyy) Changes
1 ABCD EFGH '01/01/2004' ADD
2 ABCD EFGHIJ '01/04/2004' NEW
3 ABCD EFGH '01/04/2004' OLD
4 Klarinda Rahmat '02/08/2004' NEW
5 Klarinda Rahmat '01/04/2004' OLD
In this case, I want to delete row 1,2,3 Where the audit_time are < '01/05/2004'.
Row 5 the audit_time also < '01/05/2004', however the changes='OLD' and the previous value changes='NEW', so I will check the Audit_Time of row 4 which is not < '01/05/2004'.
So I can't delete row5.
Is there any way to check previous row or the row before a specific row in SQL.
Any suggestion is welcomed.
Thank you in advanced.
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)
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 3 Replies View RelatedI have two tables. Users and records. I need to select only the users that not has lines recorded in the other table. How could I do that?
Example:
ID| Name| Access 1|Access 2|
----------------------------
1 | Axel | True |False |
2 | Ivan | False |False |
3 | Bob | True |False |
4 | Sue | False |False |
ID| Points| Month| Year|User_1|User_2|
--------------------------------------
1 | 2 | 5 | 2015| 2 | 1 |
2 | 5 | 5 | 2015| 2 | 1 |
3 | 1 | 5 | 2015| 3 | 1 |
Then I want to run a select in the users table, only with the users that hasn't records in the second table.
In the example, the second table has User_1 as the user who receives the points and the User_2 is the user who give the points. Then I would know what user didn't receive 'points' yet.
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
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 :(
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?
I need to get the previous price for all my PROMOTION records but not when the previous record is a type PROMOTION also it needs to keep going back to get the price.
I have created a table with RANK in which works OK to get previous price for all but how can I say if previous price is type PROMOTION go to next previous prices...
Bets way to show an example is with a jpeg image I have but having trouble inserting into this message...
SELECT a.[StartPrice]
,a.[ProductID]
,a.[Colour]
,ISNULL(b.[Price],a.[Price]) AS [Price Before]
,a.[Price] AS [Promotion Price]
[Code] ...
Table
PriceCodeStartPriceEndPriceProductIDColourPriceRank
RETAIL21-Oct-1324-Dec-13Bike15BLUE39.001
PROMOTION29-Nov-1301-Dec-13Bike15BLUE31.202
PROMOTION12-Dec-1323-Dec-13Bike15BLUE31.203
MARKDOWN25-Dec-1314-Jan-14Bike15BLUE31.204
[Code] ....
Want I'm trying to do select PriceCode PROMOTION and get previous price:
Get previous price
PROMOTION29-Nov-1301-Dec-13Wheel1BLACK31.2039.00
But in this example it picks up the PROMOTION before and I need to ignore this and get rank 1 price for both
PROMOTION29-Nov-1301-Dec-13Bike15BLUE31.2039.00
PROMOTION12-Dec-1323-Dec-13Bike15BLUE31.2039.00
I have a query that shows me a list of what employees that are on site assuming that employee badged in correctly. My problem is I need to know when an employee has two entries in a row that are "In" without have an "Out" entry.
For example, if John badges in at 8:00 Am and leaves without badging out, when he arrives the next day at 8:00 AM and badges in the system simply would show him as in with no record of him ever leaving correctly.
I am not sure how to return the correct result. Can I formulate a query that would display a Who's In list where the previous entry was NOT and Out?
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
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
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!
My current code returns account_number with multiple start_date regardless of the value is same or not. However, I would like to get only the account number when the value on start_date is different within same account_number.
select
acct_number
count(start_date) from table_A
group by acct_number, start_date
having(count(start_date) > 1)
I have one table :
file _target
which has below records..
file_target_ID is identity column which will repeat per files_ID
Now, i just shown Target log for file_ID 77796 see the last Target Date i want another column which returns a previous log Target date for each files beside Target date column
Like this ..
Target Date New Column
2015-09-09 00:00:00.000 2015-09-16 00:00:00.000
2015-09-16 00:00:00.000 2015-09-25 00:00:00.000
2015-09-25 00:00:00.000 New Target date after 25-9-2015
how to do this in SQL Server ??
using Cross Apply ?? Row_Number() ??
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???
Was wondering if there is easy anyway to autopopulate a field by pulling a value from a field in the previous record?
For example, I have a table with fields name "Dist_From" and "Dist_To". When I add a new record I would like it to populate the "Dist_From" field with the "Dist_to" value from the previous record.
TIA
Todd
Data from as400 imports into SQL with blank fields which is the way as400 outputs records. How can you insert previous record of data null or blank field. ex:
ONETWO
a1
2
3
b1
2
3
Would want:
ONETWO
a1
a2
a3
b1
b2
b3
I am looking to update a record from a previous row. So if there is a value of total goods in week 1, i want that value to carry forward to the value of goods in week 2. Is there any SQL as an example of the best way to accomplish this? I can query it using lag() which works great but i need the source data itself to update as the end-users are accessing the data via lightswitch, so when they save a change, i want the trigger (or whatever you recommend) to update the source table.
View 9 Replies View RelatedMay I know how to use a "date" to select out previous 14 days record from the table? and find the duplicated records?
-- sort out duplicate order from tblOrder
Select * FROM tblOrder
WHERE DDay > @prmDDay("day", -14, getDate())
Group by DDay
Many thanks~~~~~
Fr New Learner
Hi,
I need your help for my SQL query. I have a table like this
ClmA ClmB ClmType
------+------+----------
1 | 10 | 0
2 | 20 | 0
3 | 30 | 1
4 | 40 | 0
5 | 50 | 1
And its the result that i want to get.
ClmA ClmB ClmType ClmResult
------+------+---------+-------
1 | 10 | 0 | 10
2 | 20 | 0 | 30
3 | 30 | 1 | 30
4 | 40 | 0 | 70
5 | 50 | 1 | 80
Let me explain. When retrieving a row, an extra column should be added.It's value should be the sum of previous rows whose type is the same with the encountered one. I made it with a function but it's performance was terible with large tables. I have tables larger then fifty housands rows.
Having below data. SHCOMP (Customer ID), CBLNAM ( Customer name), SHDESC (service description), SHAMT (service charge), SHTYPE (F-> From T-> To)
CREATE TABLE #Temp(
[SHCOMP] [numeric](7, 0) NOT NULL,
[SHDESC] [char](35) NOT NULL,
[SHTYPE] [char](1) NOT NULL,
[SHAMT] [numeric](9, 2) NOT NULL,
[Code] ....
I need to remove records that have the same ID, Name, Description and Amount summary = 0, For example
SHCOMP CBLNAM SHDESC SHAMT SHTYPE
123 cust1 desc1 45 F remove
123 cust1 desc1 -45 T remove
123 cust1 desc1 45 F remove
123 cust1 desc1 -45 T remove
123 cust1 desc1 45 F
[Code] ....
Results
SHCOMP CBLNAM SHDESC SHAMT SHTYPE
123 cust1 desc1 45 F
123 cust1 desc1 -35 T
234 cust3 desc2 30 F
here is what I did but didn't work because sum is <> from 0 so is leaving all records
select SHCUST, SHDESC, ABS(SHAMT) SHAMT
into #t1
FROM #Temp
group by SHCUST, SHDESC, ABS(SHAMT)
having SUM(SHAMT)=0
order by SHCUST
delete S from #Temp S
join #t1 T on T.SHCUST = S.SHCUST and T.SHDESC = S.SHDESC
Hi,In oracle I have a LAG function using which I could get the previousvalue of a field.Do we have anything similar to that in SQL Server or access?ThanksDevi
View 4 Replies View RelatedWow, this board has gotten really busy lately - maybe 2007 is the year that a lot more people start using SSIS :)
Anyway my question is this: If I have an ordered set of data in the data flow and I want to add a column, lets just say "previousID" that basically has the ID value of a column from the row immediately before it - what is the most effecient way of doing that?
I've done much more complicated things with running averages, mean, etc by creating an asynchronous script transformation, pushing the data into a datatable in memory and looping through row by row using variables etc to do the calcs... but I just have this feeling that there is a "lighter, faster, easier" way for just getting previous row's value (with some special rows like first row has a null etc) than looping through a datatable row by row.
Can you push the buffer into an array (if so anyone have an example script) and use simple "n-1" logic? (ie using the array index)
I'm guessing this is a fairly straight forward need, but want to make sure I am using the correct set of tasks:
In the dataflow, some values I need to carry forward from the previous row, such as a balance that I need to carry forward for the current customer record. This is similar to a running total, only I am not summing anything, but just carrying over from the previous records value (assuming dataset is sorted correctly, first by customer #, then by date).
Do I need the Dervied Column transform, and use a variable to store the previous value, or is there another transform that would be better suited?
Thanks
Kory
How to write Stored Procedure to convert All seconds to Minutes AND finding average.
Ex:
My Table1:
SYMBOL TIME PRICE
EUR A0-FX 2008-05-09 11:37:31.203 1.54035
EUR A0-FX 2008-05-09 11:37:30.030 1.54034
EUR A0-FX 2008-05-09 11:37:28.860 1.54033
EUR A0-FX 2008-05-09 11:37:41.673 1.54032
EUR A0-FX 2008-05-09 11:37:59.720 1.54031
EUR A0-FX 2008-05-09 11:38:09.000 1.54033
EUR A0-FX 2008-05-09 11:38:35.877 1.54032
EUR A0-FX 2008-05-09 11:38:59.767 1.54041
OutPut:
SYMBOL TIME PRICE
EUR A0-FX 11:37 1.54031
EUR A0-FX 11:38 1.54041
I know this how to write ..
;WITH cte
AS
(
SELECT
SYMBOL,
[Time],
Price,
ROW_NUMBER() OVER(PARTITION BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(Time AS DATETIME) ASC) AS rn_1,
ROW_NUMBER() OVER(PARTITION BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(Time AS DATETIME) DESC) AS rn_2
FROM
Table1 WHERE SYMBOL='EUR A0-FX'
)
SELECT SYMBOL='EUR A0-FX',CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) AS [Time],MAX(CASE WHEN rn_2 = 1 THEN Price ELSE NULL END) AS [Close] FROM cte
GROUP BY
CONVERT(CHAR(5), CAST(Time AS DATETIME), 114)
ORDER BY
CAST(CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) AS DATETIME);
But I want to add some additional code in my procedure like AVERAGE of Previous 5 Price Values.
Ex:
SYMBOL TIME PRICE
EUR A0-FX 11:37 1.54031 ß1
EUR A0-FX 11:38 1.54041 ß2
EUR A0-FX 11:39 1.54021 ß3
EUR A0-FX 11:40 1.54081 ß4
EUR A0-FX 11:41 1.54071 ß5 Previous 5 Average PRICE Values.
(1.54061)
EUR A0-FX 11:42 1.54091 ß6
EUR A0-FX 11:43 1.54021 ß7
EUR A0-FX 11:44 1.54081 ß8
My Final
Out Put:
SYMBOL TIME PRICE AVERAGE
EUR A0-FX 11:42 1.54091 ß6 (1.54061)
EUR A0-FX 11:43 1.54021 ß7 (1.54091)
EUR A0-FX 11:44 1.54081 ß8 (1.54071)
At 11.42 time average is 1-5 price values
At 11.43 time average is 2-6 price values
At 11.44 time average is 3-7 price values
My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.
While trying to get to the correct previous record, I wrote the query below.
P.S. The attached .txt file includes a create and insert tbl_tmp sampling.
select top 1
a.ID,
a.WorkOrder,
a.MachineNo,
a.Operator,
a.PriorInsert,
[code]...
I've tried all sorts of code i.e. cross apply, running totals, etc. Cannot get this to work. I am trying to add a previous row value but only doing it for each group.
Source records
DECLARE @tbl table (Item int, Sequence int, StartTime datetime, Duration int)
INSERT INTO @tbl (Item,Sequence,StartTime, Duration) VALUES (1,1,'2/25/2015 12:00 am',10),(1,2,null,20),(1,3, null,22),(2,1,'2/25/2015 1:00 am',15),(2,2,null,30),(2,3, null,45),(2,4, null,5)
select * from @tbl
ItemSequenceStartTimeDuration
1102/25/15 0:0010
12null 20
13null 22
2102/25/15 1:0015
22null 30
23null 45
2 4 null 5
I would like to set the start time of the next row to be equal to the previous row time + duration. I know the start time of each group of 'Items' when the 'Sequence' number = 1. The last 'duration' value in the group would be ignored.
My expected output would be:
ItemSequenceStartTimeDuration
1102/25/15 0:0010
1202/25/15 0:1020
1302/25/15 0:3022
2102/25/15 1:0015
2202/25/15 1:1530
2302/25/15 1:4545
2402/25/15 2:305
hi,
it is my first post on this forum, please be patient if i miss any important bit of information.
i am transporting data from a legacy system into mssql 2k5 using SSIS.
among those column of a dataset there are 13 columns, all necessary for operational reasons, that i need to ensure data consistance.
i believe i could do this check using the lookup data flow item, but surely there must be a way to do it in a more streamlined fashion.
since column names contain numbers to distinguish the version, eg; col01, col02, col03 .. col13.
i thought i could include the lookup within a loop and use a couple of variables to do this trick, but since i have not done it before i am asking for some sort of guidance from a guru among you folks.
please let me know if further clarification is necessary.
regards,
nicolas