I think you experts must know how to do this.. I just can`t find in any book a good example or related information....
1) I have a time-logged table like this
Product Status Datetime Where
A Received 1/1/90 23:00 Chicago
A Sold 1/7/90 08:30 New York
A Returned 1/8/90 04:30 Paris
B Bought 1/12/91 06:39 Dallas
B Received 1/13/91 04:30 Chicago
B Returned 1/14/91 14:23 Chicago
C SendOut 1/15/91 07:45 Chicago
D Bought 1/25/92 06:30 London
etc..
All A`s are the same unique product as B and C are too, above is thus a history log of each product item. The records are not updated, just insertions only. (a DELETE on some record in time is likely, but no UPDATES...) I use the history log for several analisys purposes
2) Now, I want by one single SQL query get ONLY latest status of all products,
Like this (No I do not want to keep a separate "status" table each time I insert to the above table)
Product Status Datetime Where
A Returned 1/8/90 04:30 Paris
B Returned 1/14/91 14:23 Chicago
C SendOut 1/15/91 07:45 Chicago
D Bought 1/25/92 06:30 London
how to do such QUERY. It tried many things without exactly what I need.
I am not a good SQL programmer as U may know now..
the QUERY also should run on MS-ACCESS MDB
3) Replication
The history table is updated by each location and SQL server must keep all table in same condition using replication. Anybody has a suggestion how to setup replication in SQL server for this. It means each insertion into the log is send to each individual site. Can I publish to the same table from each site to each site , so that each change in the log gets reflected in the other site`s history table ?
on SQL Server 2000 They show only Create date but I need know update date because I install my system on customer's site and solve problem on customer site and I can't bring all stored procedure back to my office and restore all stored because of my database have two projects.
I am trying to create Replication Topology (Merge Replication) like below.
Subscriber1 --> Publisher <-- Subscriber2.
I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.
Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.
Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.
I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.
How to modify the below procedure in order to get the latest date ( the biggest value ) in the column “Deadline?, type smalldatetime, ( example: 24/02/2007 00:00:00 ) for a given customer “UserName? ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CREATE PROCEDURE GetLatestDate (@UserName VARCHAR(50) AS SELECT Deadline FROM CustomerItems WHERE UserName = @UserName
I want to have a query that will return me a single row for each user from a table where the table has many rows for each user. The single returned row for each user must be the most recently dated entry ([I7-Change-Date]) for that user.
An example of the code I have so far is as follows, but it obviously doesn;t work.select DISTINCT([I1-Customer-Ref]) AS Cust,([i7-w-fixed-amnt]) as WaterFixedAmt, ([i7-w-rv-amnt]) as WaterRVAmt, ([i7-s-fixed-amnt]) as SewerageFixedAmt, ([I7-Change-Date]) AS [Date]from r07UnMeasuredBills ORDER BY Cust, [I7-Change-Date] DESC I am using MS SQL Server 7 for this.
Dear All,Suppose in the program a record is added to a table whoseprimary key is a identity field. If I really want to get the lastestvalue for that field after the insertion, is it the best way to useIDENT_CURRENT() to obtain this value?Thanks for your kind attentionYours faithfully,Benny
I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as: select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID , all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this? I tried to create a function to get the latest sentence for a chargeID like the following: select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it? thanks,
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
I have to find the latest file in a folder and export data to a table in sql server. The code should be something that has to be incorporated into a t-sql stored procedure.
The file name would for example abc_defYYYYMMDD.xls. would i be able to find the latest file in the folder using the the datestamp (YYYYMMDD) in the filename.
Please note i would have files in other format and names with datestamp attached to it, so the code has to pick specific file for which the file name starts with 'abc_def'
I need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime) insert into #InputTable (ID_No,OccurMonth) select 10, '2007-11-01' Union all select 10, '2007-12-01' Union all select 10, '2008-01-01' Union all select 20, '2009-01-01' Union all select 20, '2009-02-01' Union all select 20, '2009-04-01' Union all select 30, '2010-05-01' Union all select 30, '2010-08-01' Union all select 30, '2010-09-01' Union all select 40, '2008-03-01'
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
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?
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this: KeyField Value1 Value2 Value3 The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
I have an update trigger which fires from a transactiion table to update a parent record in another table. I am getting no errors, but also no update. Any help appreciated (see script below)
create trigger tr_cmsUpdt_meds on dbo.medisp for UPDATE as
if update(pstat) begin update med set REC_FLAG = 2 from deleted dt where med.uniq_id = dt.uniq_id and dt.pstat = 2 and dt.spec_flag = 'kop' end
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem? Thanks.
This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
I have contract table which has built in foreign key constrains. How can I alter this table for delete/ update cascade without recreating the table so whenever studentId/ contactId is modified, the change is effected to the contract table.
Thanks
************************************************** ****** Contract table DDL is
create table contract( contractNum int identity(1,1) primary key, contractDate smalldatetime not null, tuition money not null, studentId char(4) not null foreign key references student (studentId), contactId int not null foreign key references contact (contactId) );
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc SET stc.[Talks] = fmt.found_talks_type FROM @Find_Missing_Talks fmt WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks) AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
I have a scenario where I have to Update a table with date when there are new records in another table
For example:
I load ODS table with the data from a file in SSIS. the file has CustomerID and other columns.
Now, when there is new record for any customerID in Ods, then Update the dbo table with the most recent record for every CustomerID(i.e. update the date column in dbo for that customerID). Also Include an Identifier that relates back to the ODS table. How do I do this?
I am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr] for insert as begin declare @liter as decimal(28,6)
Hi, Is this anyway to finding updated/ deleted recored using anyother data flow transformation tasks without using sql task. Can find the new records using merge join task.
Is there better way to merge master table using staging table?
We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.
