CREATE TABLE TEMP1 (REFID INT, REVISION INT, FIELDNAM VARCHAR(10), VALUE VARCHAR(10));
INSERT INTO TEMP1 VALUES(1001, 0, 'A', 'A2');
INSERT INTO TEMP1 VALUES(1001, 0, 'C', 'C2');
INSERT INTO TEMP1 VALUES(1001, 0, 'E', 'E2');
INSERT INTO TEMP1 VALUES(1002, 0, 'A', 'A3');
INSERT INTO TEMP1 VALUES(1002, 0, 'B', 'B2');
INSERT INTO TEMP1 VALUES(1002, 0, 'E', 'E3');
INSERT INTO TEMP1 VALUES(1001, 1, 'A', 'A4');
INSERT INTO TEMP1 VALUES(1001, 1, 'E', 'E4');
Here based on latest revision and refid I should get the fieldnam and value.
Expected output:
REFID FIELDNAM VALUE REVISION
1001 A A4 1
1001 E E4 1
1002 B B2 0
1001 C C2 0
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 ..
I have a table, where one of the columns is the date/timestamp of when each row was inserted. I want to be able to extract the most recently inserted rows.
With Sybase (a not so distant cousin of MS SQL) the following works:
select * from TABLE having date = max(date)
With MS SQL, however, the same query does not work:
Column 'TABLE.date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Let's say I have a data entry from a pool of employees: table is as follow: EmpNo Branch Date Amount 1 A101 11/30/2007 $0.90 1 A101 11/30/2007 $1.20 2 A101 11/30/2007 $0.90 3 A101 11/30/2007 $0.80
How can I select the whole table and only take in 1 unique latest entry if there are multiple entries for the same day, same branch under same employee number?
I have a question regarding SQL Server Performance and would be grateful for a tip. Let's say I have a DB with 50.000 records. These records belong to 1.000 different datasets, so there is 1 actual and 49 historical data records. For example a company with 1000 employees has a database where each year a new record is created for each employee so after 50 years they have 50.000 records (50 years x 1000 employees). 1 record is actual, and 49 are historical. What is the best way to store this? Main target is performance for the enduser, so when an employee clicks "See all my records" it should be fast. But on the other hand the application mainly works only with the current year. Additionally it should also be fast for the boss of business unit who wants to see the latest records of his e.g. 100 employees. I have some ideas and would like to get your opinion:
1. Retrieve by latest date Just store the records. To get the current year just select the record with the latest year. Disadvantage might be with larger databases: If the company switches to store the requests per month, each user would have 600 records (12 months x 50 years). Each time the latest record should be retrieved, 600 recards have to be compared regarding the latest date (or sorted by date descending using Top1, but this might be a problem for the boss then? Or could this be combined for a group if the boss wants to see all the latest records of his employees?).
2. Add a 'IsCurrent'-Flag Each time a new record is stored it should be compared to the latest record. If it is newer, the 'IsCurrent'-Flag should be removed and then checked on the new record. This should be fast processed (because on saving a new record it only needs to be checked against the currently 'IsCurrent'-flagged record), and for retrieving the current record no further comparison is necessary. But how could I do this? I need to update the "AddRecord"-SP with this comparison, but I don't know how to do this.
Currently number 2 is my favorite, I just don't know how to do it ;-) What is your opinion about it, and could you include an example?
I need to create a table that would be the result set of a comparisonbetween table a and table b? Table a and b first 2 fields will always bethe same (CustomerName and CustomerNumber). But if the Address1 fieldchanges in table a, I would like to throw that whole row into mycomparison table. Almost like a Select Into with a sub query that wouldinclude a WHERE TableA.field <> TableB.field. I would need to do thiscomparison for about 8 fields. Help appreciated for my syntax is prettybad. Thanks.Steve*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
select DayRank = ROW_NUMBER() OVER(ORDER BY a.datedel DESC), a.order,a.line,a.datedel,a.recpt,b.status, b.item,b.t_sup from historytbl a inner join order b on a.order = b.order and a.line = b.line and a.status =4 group by a.order,line,a.datedel,a.recpt,b.status,b.item,b.sup
I'm bulk loading employees into an etl table, each employee has a unique ID number, but they have multiple records in the data. Sometimes their name or birthdate will change and I want to identify those records and only insert the newest version into production. I can do this with a series of temp tables, but I'm sure there's a better way. The SQL below updates the etl table with the flag I want to mark the inserts, but it seems convoluted. (Jon's birthday changes, Jane's birthday changes, Bill's gender changes, Amy nothing changes(I handle those inserts later))
I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.
TransactionsImport (which is the destination table) TransactionsImportDelta
I need to do the following:
Get the records with the latest date and time in the destination table TransactionsImport Get the records with the latest date and time in the destination table TransactionsImportDelta table Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.
Problem is date & time are in separate columns:
Table structure:
Date Time ID 2011121305154107142201008300100 2011121305154122B1L13ZY0000A07YD 2011121304504735142201090002600 2011121304504737142201095008300 2011121304504737142201090002600
I have a SQL query I need to design to select name and email addressesfor policies that are due and not renewed in a given time period. Theproblem is, the database keeps the information for every renewal inthe history of the policyholder.The information is in 2 tables, policy and customer, which share thecustid data. The polno changes with every renewal Renewals in 2004would be D, 2005 S, and 2006 L. polexpdates for a given customer couldbe 2007-03-21, 2006-03-21, 2005-03-21, and 2004-09-21, with polno of1234 (original policy), 1234D (renewal in 2004), 1234S (renewal in2005), and 1235L (renewed in 2006).The policy is identified in trantype as either 'rwl' for renewal, or'nbs' for new business.The policies would have poleffdates of 2004-03-21 (original 6 monthpolicy) 2004-09-21 (first 6 month renewal) , 2005-03-21 (2nd renewal,1 year), 2006-03-21(3rd renewal, 1 yr).I want ONLY THE LATEST information, and keep getting earlyinformation.My current query structure is:select c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%s'and p.trantype like 'rwl'and c.email is not nullunionselect c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%'and p.trantype like 'nbs'and c.email is not nullHow do I make this query give me ONLY the polno 123%, or 123%Sinformation, and not give me the information on policies that ALSOhave 123%L policies, and/ or renewal dates after 2006-03-31?Adding a 'and not polexpdate > 2006-03-31' does not work.I am working with SQL SERVER 2003. Was using SQL Server 7, but foundit was too restrictive, and I had a valid 2003 licence, so I upgraded,and still could not do it (after updating the syntax - things likeusing single quotes instead of double, etc)I keep getting those policies that were due in the stated range andHAVE been renewed as well as those which have not. I need to get onlythose which have NOT been renewed, and I cannot modify the database inany way.*** Free account sponsored by SecureIX.com ****** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***
Itemlookup tableField names : index_id (primary key), itemno, description.It has a child table, which is ItemPriceHistory tableThe relationship to the child table is one (parent table)-to-many(child table). - It is possible to have no child record for some rowsin the parent table.ItemPriceHistory tableField names: index_id (primary key), itemlookupID (foreign key of theItemlookup table), date begin, priceIt is a child table of the itemlookup table.How can I get all records for both tables with the latest begin date ifexists?I also need to show the records in the parent table if there is norelated record in the child table.Please help
Greetings, I have setup a FormView which functions as it should but after the user input is updated, the table record stays unchanged, and when I trap the FormView1_ItemUpdated and look at the SqlDataSource1.UpdateCommand, it shows this: UPDATE [aspnet_test] SET first_name = '', last_name = '', email = '' WHERE id = @original_ID Here is most of the code I am using:<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="id, first_name, last_name" OnItemUpdating="FormView1_ItemUpdating" OnItemUpdated="FormView1_ItemUpdated" > .. // my ItemEditTempate is here.</asp:FormView> <EditItemTemplate>First Name: <asp:TextBox Text='<%# Bind("first_name") %>' runat="server" ID="author_name" Columns="20"></asp:TextBox><br />Last Name: <asp:TextBox Text='<%# Bind("last_name") %>' runat="server" ID="TextBox1" Columns="20"></asp:TextBox><br />E-mail: <asp:TextBox Text='<%# Bind("email") %>' runat="server" ID="TextBox2" Columns="20"></asp:TextBox><br /><br /><asp:Button ID="UpdateButton" runat="server" Text="Update" CommandName="Update" /><asp:Button ID="CancelButton" runat="server" Text="Cancel" CommandName="Cancel" /> </EditItemTemplate> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" SelectCommand="SELECT id, first_name, last_name, email FROM aspnet_test where id = 1"UpdateCommand="UPDATE [aspnet_test] SET first_name = '<%# first_name %>', last_name = '<%# last_name %>', email = '<%# email %>' WHERE id = @original_ID "> <UpdateParameters><asp:Parameter Name="original_ID" Type="Int32" /></UpdateParameters></asp:SqlDataSource> Any idea where the @original_ID is supposed to get its value from, or why does the SQL command shows blank fields?Thanks Eric.
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
guys help please...I have a table with 3 columns (TransactionID, CustomerID, TrasanctionDate) and there could be posibility that one or more rows could have the same value for CustomerID. Now my question is it posible to retrieve the row of latest TrasanctionDate made by a particular customer?
Ex:
TransactionID | Customer ID | TrasanctionDate
1 | 2 | 9/3/2007 12:00:00 AM 2 | 3 | 9/4/2007 12:00:00 AM 3 | 2 | 9/5/2007 12:00:00 AM 4 | 2 | 9/6/2007 12:00:00 AM
Say, i want to retive the latest trasanction of customer with CustomerID equals to 2. The output shoudl be is row 4 since it has the lates date with CustomerID equals to 2
4 | 2 | 9/6/2007 12:00:00 AM
Any help will be greatly appreciated. Thanks in advance!
We have licensed software from a 3rd party and we do not have the source code, nor can we do anything to change the database schema, write our own procs, triggers, etc.
With that said, we want to know which records in certain tables have been added or modified since a certain time (for example, in the past 24 hours). However, none of the tables has any datetime or timestamp columns on them for create date, update date, etc.
I tried to look into hacking thru DBCC log (dbname, 2) to see if that would help, what a mess. Using a tool like that from Luminent or Red Gate doesnt help either, because long term this needs to be automated if possible. Does anyone have any ideas on how to do this, or is the answer simply, "Sorry mate, you need to get them to allow you to change the database to add new columns and/or triggers". :)
I have table has column called [last modified date] it has time too , i need to get the [latest modified date] for particular ID , there are many updates to the ID and i just need the latest date.
this will be my first post to sqlteam.com. I've been trying to work out this problem for awhile, and figured I really need help. I have a set of data that shows the results of a number of tests that I have been doing on a software. Each test I have done records the test_ID, test_Result, and the date_Tested. An example of the columns would be like this:
Is there a way I can query this? I've tried using "distinct test_ID" but it would still show the repeated test_ID due to the date_Tested not being the same.. also Group By will not work. I'm not sure what to do here. Any ideas anyone?
I have a column which stores dates (datetime data type). I would liketo fetch one data which is the most latest date among them. So if thereare 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one Ineed to output.how can I write this in sql stmt?select datebeginfrom testtablewhere datebegin = ?????I don't think it is simple as I thought. I have no idea what I need towrite in where clause to make this work.thanks.
Hi all,I have the following tableName Date Wish ValidName is person's name, date defaults to getdate() and is neverassigned directly (datetime field), Wish is some message, and Valid isbit, 1 indicates if the wish is the latest, and therefore valid. Allprevious wishes are kept in database, and are "invalidated" by settingthe Valid to 0.So, a typical data set looks like:Name Date Wish ValidJoe 02/01/2007 Ice Cream 0Joe 02/04/2007 Bicycle 0Joe 02/06/2007 PS3 0Joe 02/22/2007 XBox 360 1Mary 02/02/2007 Barbie 0Mary 02/04/2007 Cindy 0Mary 02/06/2007 Barbie house 0Mary 02/20/2007 Get married 1My users want to see the initial wish at some point and another onesome time later (they provide dates). So, if someone wanted to seechanges in wishes between 02/03 and till 02/15, they would get thatJoe's initial wish was Bicycle and the latest that he wanted was PS3.As for Mary, she started wanting Cindy and ended up thinking about theBarbie house.I can do UNION, but is there another way to do that?Thank you.
Hi AllThis is a belter that my little brain can't handle.Basically I have 1 SQL table that contains the following fields:Stock CodeStock DescReferenceTransaction DateQtyCost PriceBasically this table stores all the transaction lines of when a userbooks stock items into stock so that they can look at a journal ofthis goods in as and when they please.My task is that the user wants a list of all the stock items with thelast cost price that they were booked in at.So I think I have to find the last transaction date used for eachstock code and then bring this back as 1 row per stock code with theabove fields of data.How the whats-its can I do this? Is it acutally possible?Any help you can give is much appreciated.RgdsLaphan
HiI'm trying to get some data fra a few tables, but I'm having a fewproblems.What I would like is this:The tables contain somn info on manuscripts and which process thatmanuascript has received.a manuscript is represented once in manuascript tablethat manuascript can have several records in the process table.What I want is to get data for each manuscript and the last process thatthe manuscript received from the process table.This SQL gets the manuscript for each process it has received. SO if amanuscript has 5 process records I will get 5 records back...SELECTManuscript.m_id, Manuscript.uniqueIDCountry,Manuscript.uniqueIDNo, Manuscript.m_title,Manuscript.country, Manuscript.m_receivedDate,Process.p_id, Process.m_id, Process.processDate,ProcessTypes.ps_id, ProcessTypes.processNameFROMManuscript,Process,ProcessTypesWHERE [Process].m_id = [Manuscript].m_idAND [Process].ps_id = [ProcessTypes].ps_idPlease help... I'm getting desperate..*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have the following situation: For a list of contacts I need to return all the contacts with the latest campaign that he is associated... Here is my tables..
And sample data -----Contatos---- 16228 61349031000147 91055652949 *** ATLETICA FLORESTA R PRIMITIVA VIANCO 405 OSASCO CENTRO SP 06010000 1136818676 1136826872 NULL NULL TATIANA DA SILVA NULL 1136853252 NULL NULL NULL NULL NULL 700 NULL NULL NULL
16229 60504586000153 00444817808 COOPERATIVA MISTA TRAB DA GDE S PAULO LTDA R ANTONIO AGU 751 OSASCO CENTRO SP 06013000 1136543715 1136816656 NULL NULL EVANDECI JORGE CERQ OLIVEIRA NULL 1136816743 NULL NULL NULL NULL NULL 700 NULL NULL NULL
16230 00124567000170 36903809520 CALCADOS GABRIELLA OSASCO LTDA R ANTONIO AGU 115 OSASCO CENTRO SP 06013006 1136996011 1136996018 NULL NULL GILSON BASTOS DOS SANTOS NULL 1136996305 NULL NULL NULL NULL NULL 700 NULL NULL NULL
16231 00182385000155 07929808831 PRYMUS BEGNINI COM DE CALCADOS E CONFECCOES LTDA R ANTONIO AGU 177 OSASCO CENTRO SP 06013006 1136995410 NULL NULL NULL CLAUDIO DE OLIVEIRA COSTA NULL NULL NULL NULL NULL NULL NULL 700 NULL NULL NULL
16232 53679098000111 00368397874 ANGELA MODAS LTDA R ANTONIO AGU 293 OSASCO CENTRO SP 06013006 1136854672 NULL NULL NULL JORGE MIGUEL DA SILVA NULL NULL NULL NULL NULL NULL NULL 700 NULL NULL NULL
---Campanhas---- 1 Teste 2 teste2 3 teste3 4 teste4
--The result for my query must be contatcs 16228, 16229, 16230, for campaigns 1, 1, 3 I need to see all fields from contatcs, the name of the campaign, and it's date...
It's like if the campaign number 2 has been disabled or expired Thanks
I have a table as part of a replicated database where i need to change the allow nulls value (set it to not allow nulls) but when i try to save the table i get this error.
'CONTACTS' table - Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.CONTACTS' because it is being used for replication.
will i have to stop replication to make the change or is it worse and i have to delete the publication and start all over again?
