SQL Query - Duplicate Records - Different Dates - How To Get Only Latest Information?
Mar 17, 2006
I have a SQL query I need to design to select name and email addresses
for policies that are due and not renewed in a given time period. The
problem is, the database keeps the information for every renewal in
the history of the policyholder.
The information is in 2 tables, policy and customer, which share the
custid data. The polno changes with every renewal Renewals in 2004
would be D, 2005 S, and 2006 L. polexpdates for a given customer could
be 2007-03-21, 2006-03-21, 2005-03-21, and 2004-09-21, with polno of
1234 (original policy), 1234D (renewal in 2004), 1234S (renewal in
2005), 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 month
policy) 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 early
information.
My current query structure is:
select c.lastname, c.email, p.polno, p.polexpdate
from policy p, customer c
where p.polid = c.polid
and p.polexpdate between '2006-03-01 and 2006-03-31
and p.polno like '1234%s'
and p.trantype like 'rwl'
and c.email is not null
union
select c.lastname, c.email, p.polno, p.polexpdate
from policy p, customer c
where p.polid = c.polid
and p.polexpdate between '2006-03-01 and 2006-03-31
and p.polno like '1234%'
and p.trantype like 'nbs'
and c.email is not null
How do I make this query give me ONLY the polno 123%, or 123%S
information, and not give me the information on policies that ALSO
have 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 found
it was too restrictive, and I had a valid 2003 licence, so I upgraded,
and still could not do it (after updating the syntax - things like
using single quotes instead of double, etc)
I keep getting those policies that were due in the stated range and
HAVE been renewed as well as those which have not. I need to get only
those which have NOT been renewed, and I cannot modify the database in
any way.
*** Free account sponsored by SecureIX.com ***
*** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***
The following SQL query :-SELECT CardHolder.RecordID, History.GenTime, History.Link1FROM History FULL OUTER JOINCard ON History.Param3 =LTRIM(RTRIM(Card.CardNumber)) FULL OUTER JOINCardHolder ON Card.CardHolderID =CardHolder.RecordIDWHERE (Card.Deleted = 0) AND (History.GenTime IS NOT NULL)ORDER BY CardHolder.RecordID, History.GenTime DESCreturns :-RecordID GenTime Link12 04/06/2004 15:30:00 1232 01/06/2004 16:00:00 1232 01/06/2004 08:00:00 1101155 02/06/2004 11:30:00 1231155 02/06/2004 08:00:00 1103925 03/06/2004 09:00:00 1233925 03/06/2004 08:00:00 1104511 06/06/2004 11:30:00 1234511 06/06/2004 10:30:00 110Is there a way of modifying this query to just return the lastestgenTime for each RecordID ??? ie return just rows 1,4,6 & 8.I assume it is something to do with MAX, but I can't seem to get myhead around it.Any help, or pointers would be appreciated.Oh, running query on Microsoft SQL Server 2000.RegardsDave
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 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.
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
Can anyone help me to write a query to show customers who have duplicate accounts with Email address, first name, and last name. this is the table structure is Customer table
customerid(PK) accountno fname lname
Records will be
like this
customerid accountno fname lastname 1 2 lori taylor 2 2 lori taylor 3 1 randy dave
How can I made a query to show only my duplicate records ? For some reason that i do not know, i have duplicate entries in my clustered index 21 duplicate records in a table how can i query to know those 21 duplicate records ?
I�m trying to select records which are between two dates. I use the following statement.
qry = System:tring::Format("SELECT sum(breakfast), sum(colacao), sum(lunch), sum(snacks), sum(dinner) FROM alunos, logtable WHERE alunos.cad_matr=logtable.studentid and alunos.cad_matr="+tbStudentId->Text+" and dateofmeal >=#"+dt->ToString("dd/MM/yyyy 00:00:00" )+"# and dateofmeal <=#"+dt2->ToString("dd/MM/yyyy 00:00:00" )+"#" );
Although the records exists the query does not get these records. If I go to the Query Design and use the same query it works but only if I enter the dates manually (dateofmel >=?).
Hello, I have the following Query: 1 declare @StartDate char(8)2 declare @EndDate char(8)3 set @StartDate = '20070601'4 set @EndDate = '20070630'5 SELECT Initials, [Position], DATEDIFF(mi,[TimeOn],[TimeOff]) AS ProTime6 FROM LogTable WHERE 7 [TimeOn] BETWEEN @StartDate AND @EndDate AND8 [TimeOff] BETWEEN @StartDate AND @EndDate9 ORDER BY [Position],[Initials] ASC The query returns the following data: Position Initials ProTime -------------------------------------------------- -------- ----------- ACAD JJ 127 ACAD JJ 62 ACAD KK 230 ACAD KK 83 ACAD KK 127 ACAD TD 122 ACAD TJ 127
What I'm having trouble with is the fact that I need to return a results that has the totals for each set of initials for each position. For Example, the final output that I'm looking to get is the following: Postition Initials ProTime ACAD JJ 189ACAD KK 440ACAD TD 122ACAD TJ 127 Any assistance greatly appreciated.
How do I update a record that has duplicates. For example, I have 3612 orders some of these orders have multiple orderid's I want to update the record for each of these orders that was added most recently.
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 ..
Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..
SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER FROM VENDFIL A, VENDFIL B, VENDFIL C GROUP BY A.FEDTID, B.VENDOR
Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance
I'm trying to do some analysis on duplicate records based off of several match keys. I have a data set of approximately 30,000 people and the goal is to determine how many duplicate matches are in the system.
How would I write an SQL statement that looks for the following pieces of information. (I'm not using one person as an example; I need to do an analysis on the entire data set)
First name (exact match) Last name (exact match) Address line 1 (exact match) Postal code/zip (exact match)
First Initial (exact match) Last name (exact match) DOB exact match Postal code/zip (exact match)
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'
I am putting my problem in an example as I Feel it would be clear.
Assume my table PEOPLE is having 4 columns with 6 rows, the SlNo being primary key. SlNo Name LastName birthdate 1 A B x -- 2 C B x |-- 1 pair (A, B, x) 3 D E y --|------------ 4 A E y | | 5 A B x __| |-- 2'nd pair (D, E, y) 6 D E y --------------- In this scenario, I need to find SlNo values having similar values in other columns. The o/p for above must be: 1 5 0 3 6 0 (0 needs to include in output for distinction in the sets)
(a)IS THIS POSSIBLE TO DO IN ONE SELECT STATEMET? and HOW? (b)If I create another temp table tempPEOPLE and select distinct row information of the 2'nd, 3'rd and 4'th columns from the PEOPLE table and then selecting SlNo's where the information match, I am able to get o/p 1 5 3 6 without 0...and I cannot makeout the distinct sets in this. HOW DO I FIND THE DISTINCTION IN SETS?
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?
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 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?
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
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
I have a SQL database (10000 records / 50 users) that stores information on faxes that we receive. This will sound odd but the last few days some weird things have happened. Chunks of data change arbitrarily - no script that I have can make these changes. Couple examples:
1) the number of pages will change from 1 to 3 - not every entry just a group and usuall ones that have a particular status 2) the status will change from CTW to PFR - not every CTW will change just a chunk
There appears to be no pattern to these changes.
Again, no script that I have can make these changes. This happened 6 months ago and I reinstalled service packs (NT and SQL), reinstalled data components and defragged the hard drive. The problem stopped either because of one of these actions or either on it's own. I have done all of these things this time but nothing has worked. Anyone have any ideas?? Any help is appreciated.
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this: ID - DayNr - Transportation - TransOrder - Route 25 - 1 - Car - 1 - Text A 25 - 1 - Train - 1 - Text B 25 - 1 - Train - 2 - Text C 25 - 7 - Train - 1 - Text D 25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2 25/1/Car - Text A 25/1/Train - TextB;TextC 25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. Ideas anyone?
Thanks. RMG
P.S. ID is not my primary key and doesn't have to be unique
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this: ID - DayNr - Transportation - TransOrder - Route 25 - 1 - Car - 1 - Text A 25 - 1 - Train - 1 - Text B 25 - 1 - Train - 2 - Text C 25 - 7 - Train - 1 - Text D 25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2 25/1/Car - Text A 25/1/Train - TextB;TextC 25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. The big issue here is that I have to base my concatenation on 3 columns. Ideas anyone?
Thanks. RMG
P.S. ID is not my primary key and doesn't have to be unique
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.
I'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server. Looking to remove records from all the tables within a schema. I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.
