Transact SQL :: Update With Most Recent Entry
Jul 24, 2015
We have 2 Tables, tblMain, which contains unique products and tblHistory, which contains a history of the product Records are added to tblHistory daily and then the prices in tblMain are updated with those prices. The problem that we have is that since there are multiple instances of the same product in tblHistory, we're not always updating using the most recent price. Below is the current SQL that we are using.
Update TM SET TM.Price = TH.PriceHist,
TM.DateUpdated = TH.DateUpdatedHist
FROM tblHistory TH LEFT JOIN tblMain TM ON TH.ProdCode = TM.ProdCode
Below is a sample of data in tblHistory and the records that I would need to be used for the update.
ProdIDPriceHistDateUpdateHist
115.257/20/2015
117.157/15/2015
120.357/23/2015
520.107/25/2015
With the above. I would need corresponding records in tblMain to be updated with the entry on 7/23/2015 for ProdID 1 and with ProdID 5
View 15 Replies
ADVERTISEMENT
Sep 29, 2015
Empid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries. What do I need to alter to make it so that only the most recent entry is returned not both entries?
Create Table #one
(
empid varchar(100)
,empbadgeid varchar(100)
,emplunchtime decimal(18,4)
,empbreaktime decimal(18,4)
,empworktime decimal(18,4)
[code]....
View 5 Replies
View Related
May 4, 2008
I know how to select the most recent row from a database:
SELECT TOP (1) Location, Date FROM Images ORDER BY Date DESC
But how do I select the second to most recent? or the third most recent? or the 4th, ect, ect, ect.
There must be some method to it, anyone have any suggestions?
View 5 Replies
View Related
Jul 16, 2013
There is a large data table called emp_history on an SQL Server, which contains the employment history of each employee. The significant columns are as follows:
entry_id employee_id start_date position
where entry_id is the primary key and auto-increments.What I need, is to create a query to extract the employee_id and position, based on the most recent record (most recent start_date) for each employee.
View 6 Replies
View Related
Sep 28, 2007
I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?
/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table
by Chuck King 20070928
***/
;WITH CTE
as
(
SELECT
ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID
,DischDate
,AdmDiagCode
,ID
,AdmDiagCodeDesc
,AdmittingDiagnosis
,AnyDx
,DischDx
,ECode
,IntOOS
,PrinDxAnesDesc
,PrinDxAnesInd
,PrinDxCode
,PrinDxCondDesc
,PrinDxCondInd
,PrinDxDesc
,PrinDxEqualsAdmDxYnu
,SecDx10AnesDesc
,SecDx10AnesInd
,SecDx10Code
,SecDx10CondDesc
,SecDx10CondInd
,SecDx10Description
,SecDx11AnesDesc
,SecDx11AnesInd
,SecDx11Code
,SecDx11CondDesc
,SecDx11CondInd
,SecDx11Description
,SecDx12AnesDesc
,SecDx12AnesInd
,SecDx12Code
,SecDx12CondDesc
,SecDx12CondInd
,SecDx12Description
,SecDx13AnesDesc
,SecDx13AnesInd
,SecDx13Code
,SecDx13CondDesc
,SecDx13CondInd
,SecDx13Description
,SecDx14AnesDesc
,SecDx14AnesInd
,SecDx14Code
,SecDx14CondDesc
,SecDx14CondInd
,SecDx14Description
,SecDx15Code
,SecDx15Description
,SecDx1AnesDesc
,SecDx1AnesInd
,SecDx1Code
,SecDx1CondDesc
,SecDx1CondInd
,SecDx1Description
,SecDx2AnesDesc
,SecDx2AnesInd
,SecDx2Code
,SecDx2CondDesc
,SecDx2CondInd
,SecDx2Description
,SecDx3AnesDesc
,SecDx3AnesInd
,SecDx3Code
,SecDx3CondDesc
,SecDx3CondInd
,SecDx3Description
,SecDx4AnesDesc
,SecDx4AnesInd
,SecDx4Code
,SecDx4CondDesc
,SecDx4CondInd
,SecDx4Description
,SecDx5AnesDesc
,SecDx5AnesInd
,SecDx5Code
,SecDx5CondDesc
,SecDx5CondInd
,SecDx5Description
,SecDx6AnesDesc
,SecDx6AnesInd
,SecDx6Code
,SecDx6CondDesc
,SecDx6CondInd
,SecDx6Description
,SecDx7AnesDesc
,SecDx7AnesInd
,SecDx7Code
,SecDx7CondDesc
,SecDx7CondInd
,SecDx7Description
,SecDx8AnesDesc
,SecDx8AnesInd
,SecDx8Code
,SecDx8CondDesc
,SecDx8CondInd
,SecDx8Description
,SecDx9AnesDesc
,SecDx9AnesInd
,SecDx9Code
,SecDx9CondDesc
,SecDx9CondInd
,SecDx9Description
,VisitTypeCode
,accountnumber
,DischVisitTypeCode
FROM ampfm.rpt_AdtVisitDiag
)
--Select * From CTE
Delete From CTE Where ROWID > 1
View 5 Replies
View Related
Oct 19, 2007
I'm trying to determine the oldest timestamp that meets given criteria which includes a specific action_type code. That code can legitimately be performed more than once on an item (all actions are recorded in an actions table, so the full history is available).
actions table (columns: action_id, request_id, action_type, action_time, action_reason)
I'm having trouble getting the most recent timestamp for the given action_type I'm looking for, when that action_type has been performed more than once.
My intent with the query below is to get the most recent action_time and it's request_id that meets the criteria
SELECT TOP 1 a.action_time, r.request_id FROM requests r JOIN incident i ON r.request_id = i.request_id LEFT JOIN actions a ON r.request_id = a.request_id WHERE i.refund_type = 1 AND (r.status_code = 3 OR r.status_code = 14) AND a.action_type = 3 ORDER BY a.action_time
So for example, if the item with request_id 1334 has had action_type 3 performed on it twice, once at 2007-10-15 13:30:00 and then again at 2007-10-17 14:40:00, it's picking up the former, when I want the later. That it had action_type 3 performed on it twice is legitimate. (larger context an item was approved by a manager, then rejected by a processor, and then approved by a manager again after resubmission)
Hopefully this has made sense so far.
So, how do I make sure I'm getting the most recent action time, when I don't know if the item in particular will have more than one of this action type performed on it?
View 5 Replies
View Related
Oct 2, 2015
This is my query, from my nonono table I need to return the most recent date from attempted (column name) How could I modify this to do such
Select *
FROM [nonono] td
WHERE (NOT EXISTS
(SELECT [First], [second]
FROM [yesyesyes] AS d
WHERE ([First] = td.[First]) AND ([second] = td.[second]) AND ([Worksite] = td.[Worksite])))
View 3 Replies
View Related
Jul 1, 2015
My goal is to show the most recent 12 months of data including the current month based on a patients discharge date (relative to the query execution time). There could be many years of data so I want to be sure I am pulling the most recent 12 monthsThis seems to work (on the surface anyway … not quite sure if this is the best logic to use).
DATEDIFF(month,PAT_ENC_HSP_discharged.HOSP_DISCH_TIME,
GEtDate())
< 13
View 5 Replies
View Related
May 25, 2015
I have a table with below structure, with a sample entry for 1 country:
ID Date Event CountryCode TxnID
1 22/05/2015 ABC 123 111
2 23/05/2015 XYZ 123 111
3 20/05/2015 XYZ 123 222
4 21/05/2015 ABC 123 222
So, I need to get a count like:
FOR COUNTRYCODE=123
ABC count= 1
XYZ count= 1
Which means, I want to count for each transaction, the latest entry based on date. And there can be many countries like this. And I need to count both entries for ABC & XYZ.Normal count will give me duplicate entries.
View 10 Replies
View Related
Oct 1, 2015
Got a view with a number of monthly tables joined with union all. The fields' structure is as follows:
Report_Date
Staff_ID
Staff_Name
...
Current_Department
In such a case any query to reflect all the columns with an additional column reflecting the said Staff's latest Department?
In other words, ones still working in the organization reflecting in the latest Department field, the Current Department and those already resigned/fired reflecting the last department they worked at with respect to the Report_Date.
View 6 Replies
View Related
Jun 18, 2015
I simply need to list the most recent value for each employee here... the value at each person's maximum date
Sample Detail Data:
EmpID Date Value
1 1/1/14 27
1 2/12/15 333
2 5/5/15 255
3 5/4/15 110
3 1/1/13 67
3 3/2/14 80
[Code] ....
What is the most efficient way to display the most recent value for each employee ID via MS SQL.?
View 4 Replies
View Related
Nov 19, 2015
I am wanting to create a query so that I can combine each of the found duplicates into one entry.
An example of this is:
Name |ID |Tag |Address |carNum
-------------------------------------------------------
Bob Barker |2054| 52377 |235 Some road |9874
Bill Gates |5630| 69471 |014 Washington Rd. |3700
Bob Barker |2054| 97011 |235 Some road |9874
Bob Barker |2054| 40019 |235 Some road |9874
Steve Jobs |8501| 73051 |100 Infinity St. |4901
John Doe |7149| 86740 |7105 Bull Rd. |9282
Bill Gates |5630| 55970 |014 Washington Rd. |3700
Tim Boons |6370| 60701 |852 Mnt. Creek Rd. |7059
In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:
Bob Barker |2054|52377/97011/40019|235 Some road |9874
Bill Gates |5630|69471/55970 |014 Washington Rd.|3700
Steve Jobs |8501|73051 |100 Infinity St. |4901
John Doe |7149|86740 |7105 Bull Rd. |9282
Tim Boons |6370|60701 |852 Mnt. Creek Rd. |7059
Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.
View 2 Replies
View Related
Mar 12, 2007
Finding the earliest datetime entry, and then updating the database on these reults.
I need to query a table of data that has multiple datetime entries in it relating to individual customer records. So one customer could have 1 entry, or it could have 10 entries. I need to be able to identify the earliest of these records, and then on this earliest record update a field value. Example:
My Fields in the table are as follows.
Log_ID, Cust_Ref_No, ActionDateTime, Action_Code
The Log_ID is the primary key, and I need to update the Action_Code field on only the earliest entry against a customer record i.e.
12345,ABCDEF,01/01/2007 00:00:01.000,6
12346,ABCDEF,01/01/2007 09:00:00.000,6
12347,ABCDEF,01/01/2007 17:00:00.000,2
In the above I need to change the first record Action_Code from a 6 to a 1, but leave all other records unaffected.
All help greatly appreciated, Thanks : o )
View 10 Replies
View Related
Oct 30, 2013
I have two tables T1 and T2
T1 is having User : Visiting Date
T2 IS having User : Last Visited Date
Now I have to update T2 with last visited date based on T1 Entry.
View 1 Replies
View Related
Jul 15, 2014
I need to get all customer records with the most recent tDate. A customer should never have duplicate tDate records, as only one record per day is allowed per customer, yet there somehow there are duplicates. Given this, I need to get the record with the Max date and Max ID. the ID column is an auto-incrementing Identity column.Below is the code without the Max ID column logic
SELECT tCustID, MAX(tDate) AS tDate--get MAX tDate records
FROM table1
GROUP BY tCustID
View 2 Replies
View Related
Apr 15, 2015
Have a table that list item#, date the standard cost went into effect and the standard cost. How do I find the difference in StdCost on the last EffectiveDate and second to last EffectiveDate. 5.59 (01/05/2015) minus 5.81 (09/29/.014) = -.22.
Item# EffectiveDate StdCost
1152 01/01/2009 5.50
1152 09/29/2014 5.81
1152 04/04/2011 5.56
[code]....
View 2 Replies
View Related
Jul 8, 2015
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
View 7 Replies
View Related
Aug 25, 2015
Updating the Table . I need to update Two Column .. DEPT and Product .
For a Same EmployeeName DEPT and Product Name should Repeat till Next Value in encountered.
Example For EmployeeName 'A' Dept Value 100 should Repeat till 20 comes, based on Date, because 100 comes before 20 and Project P1 should repeat till P2 comes.
Below is the Sample Source Table.
CREATE TABLE #MYTEMP3 (NAME VARCHAR(50), DEPT INT ,PROJECT VARCHAr(100), ORDERDT DATE)
INSERT INTO #MYTEMP3
SELECT 'A',100,'P1', '2015-01-01' UNION ALL
SELECT 'A','','', '2015-01-02' UNION ALL
SELECT 'A','','', '2015-01-03' UNION ALL
[Code] ....
Looking for UPDATE Query . I do not want to use Common table expression to achieve this .
View 4 Replies
View Related
Aug 27, 2015
I would like to make an after update trigger on a table (ARD TABLE) where depending on the criteria the trigger will either insert, update, or delete a row in the trigger table. Basically, in the trigger table there can only be rows where the column status is > 0. So, if the ARD TABLE has a row updated that used to have a status of 0 and now the status changed to 1 then insert it into the trigger table. If the ARD TABLE had a row that used to be 1 and now it's 0 then delete it from the trigger table. And if the ARD TABLE had a row that was 1 and now made any change to that row we need to update the corresponding row in the trigger table.
View 4 Replies
View Related
Oct 7, 2015
I am trying to update all records in #newtable that exist in #mastertable. I have been using Intersect to show me the duplicate records, but now I need to update a field in #newtable This was my syntax to show the records that exist in both tables, how can I change this to an update statement? The field in #newtable I want to update is [alreadyexists] and I want to update it with a yes value
So update #newtable set [alreadyexists] = 'yes'
select uno, mucha, pablo, company from #Newtable
intersect
select uno, mucha, pablo, company from #mastertable
View 5 Replies
View Related
May 19, 2015
I have a table that has two columns. One column has ID from 1 to 1800. The other column is null. I want to update the second column with values from 29 to 43. So for ID 1, value will be 29, ID value will 30 and it goes to 43. Then after it will start from 29 again and goes to 43. It goes all the way to the highest ID, i.e. 1800.I have added a script for better clarity
declare @t table (stuID int, valueID int)
insert into @t (stuID, valueID)
values (1,null), (2,null), (3,null), (4,null)
the null part is what I want to have from 29 to 43 .... all the way untill the ID reachs to 1800
View 5 Replies
View Related
Aug 24, 2015
I have a below table, I have RowOrder for master entity and need to update child entity,example : if master has RowOrder 1.00 and there are 2 child before 2.00 , then child entites RowOrder will be 1.01 & 1.02 and so on...
DECLARE @TBL TABLE (RowNum INT, DataId int, RowOrder DECIMAL(18,2) NULL)
INSERT INTO @TBL VALUES
(1, 105508, 1.00),
(2, 105717, NULL),
(3, 105718, NULL),
(4, 105509, 2.00),
(5, 105510, 3.00),
(6, 105514, NULL),
(7, 105513, 4.00),
(8, 105719, NULL),
(9, 105718, NULL),
(10,105718, NULL)
SELECT * FROM @TBL
I need to update RowOrder and looking for below output,
View 9 Replies
View Related
May 28, 2015
I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below
UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL
The above code is throwing an error as follows:
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.TCK_NR" could not be bound.
View 4 Replies
View Related
Dec 2, 2015
I am trying to do a simple update in one sql table from another where a certain condition in both tables are met.I am missing something basic and have tried numerous posted examples, without success.Here is my code (that does not update) :
opdragaliasnaaminsit.Connection = konneksie
opdragaliasnaaminsit.CommandText = "UPDATE vyfjaarontledings " & _
"SET aliasnaam = T2.aliasnaam" & _
" FROM vyfjaarontledings T1" & _
" INNER " & _
"JOIN blokke T2 " & _
" ON T1.plaasno = T2.plaasno " & _
"WHERE T1.plaasno = T2.plaasno"
opdragaliasnaaminsit.ExecuteNonQuery()
I am trying to update aliasnaam in vyfjaarontledings from blokke.
View 10 Replies
View Related
Jun 16, 2015
When I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
Declare
@count int,
@Assignment varchar(100),
@fullname varchar(100),
@timeworkedtoday decimal(18,2),
@badgeNum varchar(50),
@ticket varchar(50)
[Code] ....
View 5 Replies
View Related
Jul 15, 2015
I have following sample data
I want this data updated to different table based on PK value relationship like below,
What shall I do to make such update ? Here is the sample script I want ot use
DECLARE @Activity TABLE(PK BIGINT,Value1 NVARCHAR(10),Value2 NVARCHAR(10))
INSERT INTO @Activity(PK) VALUES(1)
INSERT INTO @Activity(PK) VALUES(2)
INSERT INTO @Activity(PK) VALUES(3)
[Code] .....
View 11 Replies
View Related
Jun 23, 2015
i want to display the max Date in place of NULL when Indicator is "1"
INPUT
Emp Num
Date
Indicator
1
Null
1
[code]....
View 3 Replies
View Related
Aug 13, 2015
I have following query which is created dynamically as -
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1889147436' AND SEND_DATE = '1941-03-04';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1115509374' AND SEND_DATE = '1991-09-01';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1515579671' AND SEND_DATE = '1941-05-24';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1795509670' AND SEND_DATE = '1958-01-14';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1915508672' AND SEND_DATE = '1961-09-07';
Here till " UPDATE BUILDTABLE SET BUILD_ID = '984137'" is the same clause for all queries, but "where" condition is different for all queries. I have to update more than 500 UPDATE statements(like above) in one call. Currently I am concatenating all the queries in string Builder which is time consuming.I want to increase performance of application.Any other class like BulkCopy ?
View 18 Replies
View Related
May 21, 2015
I have a csv file that contains numbers like
OtherName, AddNO
name1,NewV001
name2,NewV002
name3,NewV003
and there is sql table which contains below values
ID, AddN0
1, OldV001
2, OldV002
3, OldV003
I need a sql query that reads New AddNo v001..V003 and update in Sql table for given range ID 1-3
OUtput
ID, AddN0
1, NewV001
2, NewV002
3, NewV003
View 2 Replies
View Related
Oct 1, 2015
I keep getting an error of error in statement near = on this update statement. What is incorrect?
Update abcd
Set CAST(field1 As varchar(4000)) = 'Computers and 2-in-1s'
where CAST(field1 As varchar(4000)) = 'Computeres and and 2-in-1s'
View 11 Replies
View Related
Nov 15, 2015
I have two tables i have to update table2 using table1 without using while loop.
example given below.
Table1
rid
id
amt
firdate
lastdate
1
1
500
[code]....
View 7 Replies
View Related
Sep 28, 2015
I have following T-SQL statement:-
insert into [dbo].[tbl_FG_Alert_Count_All_Report] ([Date] ,[Count] ,[Rule Type])
SELECT TOP 10 [Date]
,[Count]
,[Rule Type]
FROM [dbo].[tbl_FG_Alert_Count_All] where Count <>'0' and DATEDIFF(dy,date,GETDATE()) = 1 order by Date desc
When I ran this T-SQL statement in SSMS; I don't get any error and as expected, I can see new data in [dbo].
[tbl_FG_Alert_Count_All_Report] table.
Now I created one job with same T-SQL Statement. Job completes successfully with out giving any error message; But unfortunately I don't see any new data in [dbo].[tbl_FG_Alert_Count_All_Report] table. What would be the reason that I don't see new data when job completes successfully but I can see new data after executing same T-SQL statement in SSMS?
View 3 Replies
View Related
Apr 21, 2015
I created this unique codes and
I need all [FRMDAT] field set to ‘12/31/2014’ in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > ‘12/31/2014’
VALUE LIST
PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994
View 2 Replies
View Related
