How To Make Automated Update On The Previous Record...
May 9, 2008
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 1 Replies
ADVERTISEMENT
Jan 14, 2015
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]...
View 2 Replies
View Related
Apr 6, 2007
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
View 5 Replies
View Related
Jul 20, 2005
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
May 2, 2007
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 2 Replies
View Related
Dec 5, 2005
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 Related
Feb 1, 2007
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 4 Replies
View Related
Jun 25, 2004
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 4 Replies
View Related
Aug 28, 2001
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 3 Replies
View Related
Apr 16, 2014
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
View 8 Replies
View Related
Mar 31, 2015
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?
View 16 Replies
View Related
Apr 20, 2007
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 3 Replies
View Related
Apr 9, 2008
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 3 Replies
View Related
May 19, 2008
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 3 Replies
View Related
May 22, 2008
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
View 5 Replies
View Related
Mar 3, 2014
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)
View 5 Replies
View Related
Jul 20, 2005
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 Related
Sep 22, 2015
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() ??
View 3 Replies
View Related
May 22, 2008
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 5 Replies
View Related
Mar 16, 2008
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
View 3 Replies
View Related
Nov 29, 2000
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
View 5 Replies
View Related
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.
View 9 Replies
View Related
Dec 10, 2007
May 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
View 3 Replies
View Related
Jun 2, 2014
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
View 5 Replies
View Related
Nov 20, 2007
I have an SSIS package (SQL Server 2005) that gathers statistics (ie total records created, items created, items shipped, etc). I have made a UNION query to get the data (because it has to look at different date fields) that returns the following results:
ID QTY
Created 20
Shipped 30
Received 35
What I what to do is store this data as 1 record like this:
Date Qty_Created Qty_Shipped Qty_Received
11/20/2007 20 30 35
How can I accomplish this?
Thanks
View 4 Replies
View Related
Dec 14, 2007
Hello,
I'm a bit new to MS-SQL so i thought maybe you could help me.
This is my prob. I have an Access application that i have upsized to a MS-SQL server. I have a query based on 3 tables: Customers, Companys and Payreminders. When i run this query, i can't add new records or make any changes.
If i only run the query with the tables: companys and payreminders i can add new records and edit them. If i run a query that's based on companys and customers, i can edit and add new records, but when i run the query based on companys and customers i can't add or edit records.
companys, payreminders: Editable
payreminders, companys: Editable
Companys, customers: Non editable
Companys, payreminders, customers: Non editable
Something must go wrong when i use the companys and customers table. Can anybody help or give a suggestion what to do.
Thanks.
View 1 Replies
View Related
Jun 22, 2007
I have a table of magazine issues. The table are defined as below:
issueID int Unchecked
name varchar(50) Unchecked
title varchar(100) Checked
description varchar(500) Checked
crntIssue bit Checked
archived bit Checked
navOrder int Checked
dateCreate datetime Checked
And here is what I want. Is there a way when inserting/updating or on the table itself to make sure that there is only one record that is marked as the current issue? The way I have it here in my table, any records can have the current issue (crntIssue) field checked. I only want one crntIssue field checked regardless of how many records or issues are in the table. If there is no way to automatically have SQL Server to manage that then that means I must check all the records before hand before the update/insert query, correct?
View 23 Replies
View Related
Jun 22, 2007
I have a table of magazine issues. The table are defined as below:
issueID int Uncheckedname varchar(50) Uncheckedtitle varchar(100) Checkeddescription varchar(500) CheckedcrntIssue bit Checkedarchived bit CheckednavOrder int CheckeddateCreate datetime Checked
And here is what I want. Is there a way when inserting/updating or on the table itself to make sure that there is only one record that is marked as the current issue? The way I have it here in my table, any records can have the current issue (crntIssue) field checked. I only want one crntIssue field checked regardless of how many records or issues are in the table. If there is no way to automatically have SQL Server to manage that then that means I must check all the records before hand before the update/insert query, correct?
View 9 Replies
View Related
Feb 14, 2008
HI if I have a recrod in a plain text file , and it has some colums information including FromDate and ThruDates if the date range is over 30 days, Then the record should split and becomes 2 records rather then one.
for example:
if i have MemberID=255 FromDate 07/01/06 and ThruDate = 08/25/06
than
that single record should be saved as two records
like
Record#1 FromDate ThruDate MemberID
1 07/01/06 07/30/06 225
2 08/31/06 08/25/06 225
Well the file is a fixed length file.
On a web page. Page is uploaded.
when the fixed length file is uploaded it converted into the xml file.
After the xml file goes through the validation, it gets ready for the database.
Each field in the fixed length file was a xml node and sent to the database.
Now what I have to check from the sqlManagement studio. Insert a query with the test query as I decribed earlier, and instead of it get to the database it should be splited into two record instead of one and get in the database table. And if dates are in between 30 days, then the recrod go into table with out any other issue.
View 2 Replies
View Related
Oct 22, 2007
Hi.
I have a table where I need to set some rows to Read only (to protect the rows from being edited). Can I do this with a trigger checking against a column (where dataReadOnly = 1)?
Or is there a better way?
thankyou
View 6 Replies
View Related
Jul 8, 2004
I have a table full of items that have a "date_updated" field. I'd like this field to be set to GETDATE() whenever a record is updated. I've got this trigger:
CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
update cp_shiptos set date_updated = GETDATE()
Problem is, of course, there's no WHERE clause..yet. I don't know how to refer to the record that was updated.... for example:
CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
update cp_shiptos set date_updated = GETDATE()
where shipto_id = @THIS_ID
I imagine there's some kind of builtin variable or something like that. How is this done?
Thanks in advance.
View 2 Replies
View Related
Jul 23, 2005
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
View 5 Replies
View Related
Aug 16, 2006
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?
View 5 Replies
View Related
