SQL Server 2012 :: Trigger For Updates On A Row Using Previous Record Value?
Mar 9, 2015
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.
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData ( recordID nvarchar(5)NOT NULL, itemID int NOT NULL, itemvalue decimal(18, 2) NULL, monthitemvalue decimal(18, 2) NULL ) The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData 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
I am basically trying to update a table which reflects account transactions. Accounts get paid in full but occasionally balance payments can be reversed and I want to update the table to show this - I need to show which period the account was previously paid in full.I've created a simplified version of the scenario and below are a couple of examples of things I've tried that do not work. I understand why they do not work but I'm struggling to figure out how to update the 'PeriodPrevPaidInFull' field.
create table Trans ( AccNo int, Transaction_Period_Index int, PeriodOpeningBalance money, DebtBalance money, PeriodPaidInFull int NULL, PeriodPrevPaidInFull int NULL,
I have a SQL Server at version Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor).
I need to apply a cumulative update, either CU2 or CU3 for a third party tool used for monitoring to work. When I try to apply CU2 or any subsequent CU there is nothing that need the update so I cannot advance the version to 11.00.5548.
I am working with a stored procedure that needs to roll up a week number column once a week - columns are numbered 1-10, 1 being this week, 2 being last week and so forth
Once a week the 10th column is deleted, the 9th becomes 10, the 8th becomes the 9th and so forth and the 1st is calculated the week numbers are getting all screwed up - and we think it's because one statement starts before the one before it completes the statements go like this:
delete theTable where week_num=10; update theTable set weeknum=10 where weeknum=9; update theTable set weeknum=9 where weeknum=8; and so forth
is that the reason? is there any way not to start one statement until the one before it finishes?
get the desired results for the following sample data set. I was able to come up with a query that returns the the expected results however only for a given day, so I'd need to union several select statements the get the desired results which is definitely not ideal. I'd like to pass a parameter in (number of days) instead of doing a unions for each select.
DECLARE @T TABLE (Id INT, Category VARCHAR(1), [Date] DATE) INSERT INTO @T SELECT 1 AS Id, 'A' AS Category, '2015-5-13' AS ActivationDate UNION ALL SELECT 1, 'A', NULL UNION ALL SELECT 1, 'A', '2015-5-13' UNION ALL SELECT 1, 'A', NULL UNION ALL
BEGIN TRANSACTION Copy records from live to archive END TRANSACTION with commit or rollback execute sproc to write audit log with success or fail IF transaction was committed BEGIN TRANSACTION Delete records from live the archive END TRANSACTION with commit or rollback execute sproc to write audit log with success or fail End IF
END TRANSACTION OUTERTXN with commit if both inner transactions were successful or rollback if either failed
If either inner transaction rolled back execute sproc to write audit log saying whole process is rolling back End IfMy problem is that if the outer transaction rolls back then I am losing the two audit records because they are part of the transaction scope. I want these executes to commit even if the master transaction fails.
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year], T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run) , (SELECT SUM(Stores) FROM #ABC WHERE [Year] = T.[Year] AND [Month] = T.[Month]) AS [Sum_Stores], (SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
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')
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 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)
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]
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 ?
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.
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?
Is there a way to update all of the records in a table all at once using the results of a select of a different table's data?
For example. There are two tables, each have a primary index of catnum. One table (invoice_items) contains the line items (catnum) of customer invoices. The other table (sales_history) is a sales history table. I want to select all data from the invoice_items and sum the sales for various time periods (sales_0to30days, sales_31to60days, etc.) I then want to update the sales_history table which has columns: catnum, sales_0to30, sales_31to60, etc.). I want to run this daily to update all records.
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)
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.
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
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".
I've a system of users and let's call em subusers. Every User becomes an automatic generated login when entered into the database. Every subuser has a reference to his user and no login, cause only thr root in the chain is able to login. But when the user gets deleted, all subusers become a new user. I've done this with a trigger changing the superUser Value=0:
create trigger abc on Users AFTER DELETE as declare @h int SELECT @h = id FOM inserted UPDATE Users SET superUser=0 WHERE superUser=@h
Furthermore the trigger deletes all additionally data of the user. Since every subuser of the deleted user becomes a user himself for every subuser a new Login must be created. I'm using an update triger for this task:
1: create trigger userUpdate on Users After Update 2: AS 3: DECLARE @superold int 4: @supernew int 5: @name nvarchar(55) 6: @date smalldatetime 7: if UPDATE(superUser) 8: begin 9: SELECT @superold= superUser FROM deleted 10: SELECT @supernew=superUser FROM inserted 11: 12: if @superold <> @supernew 13: begin 14: if @superold = 0 15: begin 16: DELETE FROM UserLogin WHERE id=@superold 17: end 18: else if @supernew=0 19: begin 20: SELECT @name=Name,@date=Date from inserted 21: execute createLogin @supernew,@name,@date 21: end 22: end 23: end
The problem is in line 20 and 21, cause the values @name and @date containing only the last updated user(the last entry in the inserted table) thus only for the last user a new Login is created whereas the others become the state user but no login was created. What i need is a method to loop over all entrys in the tables inserted btw. deleted. Does anybody know how to achieve this, looping the tables and executing a stored procedure for every entry?
Converting an existing application, I have a table:
create table problem (
building char(3), function char(4), sqft int, pct dec(5,2) )
[pct] is a problem because it depends on it's own row and the sum of all other rows for the same building:
pct = sqft / sum(sqft) over building
I want to create a trigger to update the pct column in the database instead of any business layer. But, because it's updating itself I will use an Instead Of trigger that is separate from the table's Insert & Delete triggers.
The table has a primary key defined such that 'In-Place' updates will be used, that was a technique for reducing disk activity way back when and I can find no reference to it in SQL2005 BOL.
My question is, does the 'Inserted' table still exist for 'In-Place' updates? Or more basically, does the In-Place update still exist?
Approximately once a month, a client of ours sends us a bunch ofcomma-delimited text files which I have to clean up and then importinto their MS SQL database. All last week, I was using a Cold Fusionscript to upload the cleaned up files and then import the records theycontained into the database, though obviously, the process tookfriggin' forever, and could have been done 500x quicker had I done itdirectly on the server. My SQL knowledge is somewhat limited, however,so I had no choice but to stick to what I know, which is Cold Fusionprogramming.In the process of cleaning up some of these comma-delimited text files,I inadvertently messed up some of the 10-digit zip codes, by applyingthe wrong Excel formula to the ZIP columns. These records were importedinto the database with obviously incorrect zip codes (ie: singledigit). So now, I have to find the best and quickest way possible tocompare these records in the database (that have the single digit zipcodes) with the unmodified data, and to update the zip codes with thecorrect data.I've had no luck setting up a TEXT file as an ODBC datasource, -- soI've ruled that out completely. I've also managed to import theunmodified data into an Access database, and to set it up as a ColdFusion datasource. But it seems this 2nd road I've been traveling downis not the ideal approach either.My question is, -- assuming that I'll be able to import the recordsfrom the Access database into their own table on the SQL server, -- howshould I go about the process of updating these records that have theincorrect zip codes?Here is the specific logic I would need to employ:* Here is a list of records, each of which contains an incorrect1-digit zip code (Database A / Table A)* Here is a much longer list of records (which contains all of therecords from Database A / Table A + thousands more), each of whichcontains a correct 5-digit zip code (Database B / Table B)* Compare both lists of records and run the following query/update:When a record in Database A / Table A has matching "name", "address1",and "address2" values as a record in Database B / Table B -- update therecord in Database B / Table B with the zip code from the matchingrecord in Database A / Table A.Would anyone care to write a sample query for me that I could rundirectly on the SQL server, or at least give me some pointers?The specific field names are as follows:name,address1,address2,city,state,zipThanks in advance!- yvan
