Getting Subsequent Records From Same Table

Feb 2, 2015

I was trying to use inline views, then sub-query etc. Still can't get quite what I want.

So for example take a personID where they are registered, processed and deregistered. They can have one or more registrations, one or more related processes (for each registration) and then deregistration, the processes are always tied to a registration and deregistration period.

for a given time-period I want to find the first registration and use that first registration to find subsequent registration records within 20 days - I don't want to return processes related to the next registration record, just the next registration record.

I already have the query built that returns all the raw data for those registrations, processes and deregistrations but need to set up something that finds subsequent registrations. So here is a sample of raw data:

Person Id RowID Reg_Date Reg_Time Process_First_Date Process_First_Time Process_last_Date Process_Last_Time De_Reg_Date De_Reg_Time
========= ======= ======== ======== ================== ================== ================= ================= =========== ===========
000000001 00001/8 01/01/2014 12:00 01/01/2014 12:00 02/01/2014 11:40 03/01/2014 16:44
000000001 00001/10 01/01/2014 12:00 02/01/2014 13:22 03/01/2014 09:56 03/01/2014 16:44

[Code] ....

firstly need to use all first registrations - that would be row where reg-date = process first date and also reg time and process first time are equal - and use those for finding the next reg_date of that personID. PersonID can have more than one registration in a period so need to do something like partition, which although it worked on it's own, seemed to become a tangled mess when I started using the subset in a inline view or sub-query. Not sure how to return earliest reg date in period for certain PersonID for multiple IDs.

So for above I would want to return these rows:

Person Id RowID Reg_Date Reg_Time Process_First_Date Process_First_Time Process_last_Date Process_Last_Time De_Reg_Date De_Reg_Time
========= ======= ======== ======== ================== ================== ================= ================= =========== ===========
000000001 00002/12 04/01/2014 10:36 04/01/2014 10:36 05/01/2014 15:12 08/01/2014 12:33
000000002 00004/13 04/01/2014 10:36 04/01/2014 10:36 05/01/2014 15:12 08/01/2014 12:33

And this is based on these two rows:

Person Id RowID Reg_Date Reg_Time Process_First_Date Process_First_Time Process_last_Date Process_Last_Time De_Reg_Date De_Reg_Time
========= ======= ======== ======== ================== ================== ================= ================= =========== ===========
000000001 00001/8 01/01/2014 12:00 01/01/2014 12:00 02/01/2014 11:40 03/01/2014 16:44
000000002 00003/9 01/01/2014 12:00 01/01/2014 12:00 02/01/2014 11:40 03/01/2014 16:44

I have made the dates the same in last 4 rows, this isn't intentional and doesn't mean anything it's because I copied and pasted the first 4 rows.

How to tie this together using first reg of each personID and then comparing with other records to find subsequent first registrations. I'm not too fussed about finding them within a certain time period from the first registration, more that I want to just get some sort of query set up then I can put in specifics.

View 3 Replies


ADVERTISEMENT

How To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.

Sep 13, 2006

Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables.  I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key.  Is there a way to automatically create new records in the foreign tables that will have the new id?  Would this be a job for a trigger, stored procedure?  I admit I haven't studied up on those yet--I am learning things as I need them. Thanks. 

View 4 Replies View Related

SQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1

Aug 11, 2015

Table1 contains fields Groupid, UserName,Category, Dimension

Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)

So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)

In Table1 There might be 10 Groupid records all of which are different.

View 9 Replies View Related

Transact SQL :: Retrieve All Records From Parent Table And Any Records From Child Table

Oct 21, 2015

I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.

SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY

[Code] ...

It's only returning two rows (the students where they have a row in the HomeworkLogLine table). 

View 3 Replies View Related

DTS/SSIS Too Many Tables Warning And Subsequent Error

Nov 23, 2006

I need to bring over a large number of tables' records (200+ tables) with the Import/Export Wizard. The tables are being imported from MS Access. A separate script run previously will create the tables, so the DTS wizard is only to bring over the data from the Access tables into the empty SQL ones.

First, I get the warning that indicates "a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".

Well, I proceed with the DTS and it tries to validate and takes a fair bit, but then it errors indicating:

"Error 0xc0202009: {2F0FABA0-5F4B-4310-97C0-76EA19893547}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)"

Can anyone shed any light on why I receive an "unspecified error" when tring to DTS a larger number of tables. It does not error, if I import 40 or so tables.

This was never an issue with SQL 2000 DTS.

Thanks

View 4 Replies View Related

How Do I Select All Records In One Table That Have NO Related Records In Another Table?

Jul 20, 2005

I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks

View 2 Replies View Related

SQL Server 2012 :: Calculate EndDate From Subsequent StartDate

Jul 28, 2014

Here is sample data I am working with:

Create table cattimelines (categoryID int, EffectiveDate datetime, CategoryValue varchar(11))

INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-01-01', 'A')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-02-01', 'B')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-04-01', 'C')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-07-01', 'A')

I need to calculates a term date for each record which will be 1 day before the effective date of any new record, thus:

CATEGORYIDEFFECTIVEDATETERMDATECATEGORYVALUE

10002014-01-012014-01-31A
10002014-02-012014-03-21B
10002014-04-012014-06-30C
10002014-07-01NULLA

View 3 Replies View Related

Modifying Repeating Group Headers So That They Are Different On Subsequent Pages

Jan 7, 2008

It is using a table to display a very large amount of data (a basic list of information) with groups. Each of the groups can span multiple pages, so the user may not realize where the grouping actually began (I am repeating the group headers). Therefore, I would like to modify the group header when it is repeated across multiple pages by simply adding the text, "(cont.)" to the header so the reader can easily see that the group started on a prior page. There does not appear to be any standard test function for this condition. Using Previous() for this case does not work. This cannot be a new issue and must have been solved; any help with this would be greatly appreciated. Sorry if this has been asked before, I did search the forum but could not find an answer to this question...

View 3 Replies View Related

Can Decrease To Accomodate Contents Not Working For Subsequent Pages?

Feb 13, 2008

This is an example of my data:

Col1 Col2 Col3
1 LONGDESCRIPTION1 111
1 LONGDESCRIPTION1 222
1 LONGDESCRIPTION1 333
1 LONGDESCRIPTION1 444
2 LONGDESCRIPTION2 XXX
2 LONGDESCRIPTION2 YYY
2 LONGDESCRIPTION2 XYZ
2 LONGDESCRIPTION2 999

This is an example of what I want my report to look like (ill put row numbers to help identify)

Col1 Col2 Col3 Row Num
1 LONGDESCRIPTION1 111 1
222 2
333 3
444 4
Subtotal Col3Sum (PAGEBREAK)
2 LONGDESCRIPTION2 XXX 5
YYY 6
XYZ 7
999 8
Subtotal Col3Sum

The Settings:
I'm currently using a single TABLE DETAIL ROW with HIDE DUPLICATES in Col1 and Col2.
The LONGDESCRIPTION field can be expected to take 2 row heights worth of space because it wont fit.
I've also got CAN INCREASE TO ACCOMODATE CONTENTS and CAN DECREASE TO ACCOMODATE CONTENTS selected.

The Problem:
When Printing/PrintPreviewing Page1, the Height of Rows 2/3/4 ARE THE CORRECT SIZE (only 1 row high, meaning that can decrease to accomodate contents works).
BUT when Printing/PrintPreviewing Page2, the Height of Rows 6/7/8 FOLLOW THE HEIGHT of ROW 5 (two rows high, meaning that can decrease to accomodate contents DOES NOT WORK).

Rows 2/3/4 has the same Row Height as Row 1 ONLY ON PAGES after Page1.
Rows 6/7/8 has the same Row Height as Row 5 ONLY ON PAGES after Page1.

Can decrease to accomodate contents does not seem to be working for Pages after page 1.


View 3 Replies View Related

User Defined Function Gives Different Results On Subsequent Runs

Feb 25, 2008



Hi

I have a UDF
---------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UDF_AlphaNumeric]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[UDF_AlphaNumeric]
GO

CREATE function UDF_AlphaNumeric (@string nvarchar(max)) returns nvarchar(max)
AS
-- select dbo.UDF_AlphaNumeric('a[]#b`c,;"1$%^2"!3')
begin
while @@rowcount > 0
select @string = replace(@string, substring(@string, patindex('%[^0-9a-zA-Z]%', @string), 1), '')
where patindex('%[^0-9a-zA-Z]%', @string) <> 0

if @string = ''
select @string = null

return @string
end

---------------------

After creating the function I open a new query window, put in the below two calls to the function, and run them


select dbo.ADE_AlphaNumeric('a432[]#b`c,;gfd23$%^789')
select dbo.ADE_AlphaNumeric('a432[]#b`c,;gfd23$%^789')

The results then look like this:

a432[]#b`c,;gfd23$%^789
a432bcgfd23789

The first time it runs, it's not having the desired effect. Any subsequent calls to the function perform as expected.


I am using SQL Server 2005 SP2. Is this a known issue? Or is there some setting I am missing?

Cheers

Neil

View 7 Replies View Related

Deleting Old Records Is Blocking Updating Latest Records On Highly Transactional Table

Mar 18, 2014

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 ..

View 3 Replies View Related

T-SQL (SS2K8) :: Renumbering Remaining Records In A Table After Some Records Deleted

Dec 3, 2014

I have a table with about half a million records, each representing a patient in my county.

Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.

I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.

I can see what I want to accomplish by using ROW_NUMBER, thus:

SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank

I see the numbers in the RecNumber column falling behind the RRank as I scan down the results

My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:

UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);

but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).

View 5 Replies View Related

Remember Expansions/drilling Level Between Subsequent Runs Of A Report.

Mar 9, 2007

Hi all,

I have a report in which the user can drill down on data. However when the user changes a parameter and reruns the report, all nodes are collapsed again. Is it possible to let the report keep the expansion state of nodes after pressing the 'view report' button again?

Regards,
Henk

View 1 Replies View Related

Transact SQL :: How To Update Subsequent Columns If Proceeding Column Is Not Null

Jul 7, 2015

I've got a table with 6 fields :

EmployeeAccess
(MasterID, LoginID, AccessID, Storage1, Storage2, Storage3)
that needs to be updated using the data in the following spreadsheet
NewEmployeeAccessData
(ID, MasterID, AccessID1, LoginID1)

There is a 1:1 relationship between the two tables..I'm trying to code a pair of update statements on the EmployeeAccess table (1 for LoginID, 1 for AccessID) with the following logic:

If LoginID is NULL, then Update LoginID with new LoginID1 value,
If LoginID is not null, and Storage1 is NULL then Update Storage1 with New LoginID1 values
If LoginID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New LoginID1 values
etc etc...

The same applies when trying to populate the AccessID column

If AccessID is NULL, then Update AccessID with new AccessID1 value,
If AccessID is not null, and Storage1 is NULL then Update Storage1 with New AccessID1 values
If AccessID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New AccessID1 values
etc etc.

I have no control over the schema of this table  so I'm trying to work the logic on how to update the columns in my table only if the corresponding column data is NULL, else update the next non NULL Storage column.

View 7 Replies View Related

SQL Server 2005 Log Shipping And Subsequent FULL Backups That Are Needed

Dec 4, 2007

Afternoon,

I have a few Log Shipped DBs that are working great.

Currently they are set to fire off every 15 minutes 24/7.

My question is this ... I need to get FULL backups of the source DBs in order to restore them on certain Dev boxes.

If I were to execute the full backup on one of these Log Shipped DBs ... how would it affect the log shipping process?

Is there a special method to accomplish this?

As a side note, what would be some concerns/issues if in being able to create the FULL backups and not interupt log shipping, I were to create the backup using a 3rd party tool like Quest LiteSpeed?

I sure wish we were on Enterprise, then I could create a mirror and then snapshot off it to create my backups BUT ... that is not the case as we stand today.

Thanks

View 9 Replies View Related

SQL Server 2008 :: How To Improve Speed Of Initial Query Vice Subsequent Queries

Apr 23, 2015

I have a pretty large DB and a fairly complex query. If I drop buffers and clear cache the query runs in 20 seconds returning 25K rows. Subsequent runs are 2 seconds. Is this the result of the results being cached, execution being cached, other? Are there good ways to close the gap between the initial and later runs? Does the cache stay present until the service restarts or does SQL recycle the memory and if so, based on what criteria?

View 5 Replies View Related

Delete Records From A Table Using Records In Another Table.

Jul 20, 2005

I have been searching many postings and I cant seem to find anyonethat has this answer so I decided to post. I am using SQL(Transact-SQL).If I have 2 tables with columnsacct_num,activity_date,and pay_amt and I want to delete one instanceof a record in table 1 for every instance of that record in table 2how could I do that. For example.Table 1-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00Table 2-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00I need a delete statement that will find 2 of the 5 records(It doesn'tmatter which 2) and delete them.Leaving table one looking like this.Table 1-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00How can I do this??

View 7 Replies View Related

Automatically Adding Records To Child Table When Record Added To Parent Table

Aug 19, 2006

In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks

View 1 Replies View Related

SQL Server 2012 :: Join To Find All Records From One Table That Do Not Exist In Other Table

Apr 29, 2014

I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.

What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:

'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002

So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".

View 7 Replies View Related

Query To Return Records Where One Table Is Void Of Linked Data In Another Table

Feb 13, 2008

I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.

Thanks in advance,

Mark

View 5 Replies View Related

Transact SQL :: Avoid Same Table Multiple Times Rather Than Put Records In Single Table And Use It Throughout

Nov 19, 2015

There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule

SELECT  
 PE.PropertyExternalReferenceValue  [BAReferenceNumber]
, PA.DescriptionCode
    [PSDCode]
, PV.ValuationEffectiveDate
    [EffectiveDate]
, PV.PropertyListAlterationDate
    [ListAlterationDate]

[code]....

Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set  tables multiple times 

View 11 Replies View Related

Transact SQL :: Delete Records From Table (Table1) Which Has A Foreign Key Column In Related Table (Table2)?

Jun 29, 2015

I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).

Table1 columns are: table1Id; Name.  Table2 columns include Table2.table1Id which is the foreign key to Table1.

What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?

View 11 Replies View Related

How To Use Value Calcuated In Query In Subsequent Query, All Based On Value In Querystring?

Jun 5, 2008

I have a vb.net page that I need to display a list of employees who work in a specific office, based on a MatterID passed in a query string.  But, I don't know how to get a value returned from one sql statement into a second.  Here's what I'm trying to do...
From the QueryString, we know that the MatterID = 4  ( xxx.aspx?MatterID=4)
Knowing that the Matterid=4, I query the database to get the OfficeId for that MID  (Select OfficeID from tMatter where Mid=4)   ~This returns an OfficeID of 6
So, then I need to do another query to get the employees where OfficeID = 6   (Select EmployeeID from tEmployees where OfficeID = 6)
How do I do these in one query, or how do I use the Calculated Value for the OfficeID in the 2nd statement? 
 

View 3 Replies View Related

Automating A Query And The Saving Saving Of Subsequent Results

Dec 13, 2007

Hi Guys,

I am trying to automate a basic task using SQL Server 2005 Express.

Currently I have a query script that I run and then save the results as a CSV file. I need to do this on a daily basis and so I am looking to find out how best to go about this. There are a multitude of third party tools that claim to be able to do this - can anyone recommend this or enlighten me of the best way to set up this automation.

All ideas gratefully received!

View 1 Replies View Related

What's The Accepted Way To Retrieve Records In A SQL Table With Null Values Using A Visual Studio 2005 Table Adapter?

Jan 21, 2008

I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
 I have a very simple dilemma.  In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND   (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null>  (accounting for about 95% of the records in the database). Thanks in advance Simon
 

View 9 Replies View Related

SQL Server Admin 2014 :: Few Record Loss In Table Primary Key Where Same Records Exists In Foreign Key Table?

Jun 21, 2015

Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.

View 3 Replies View Related

T-SQL (SS2K8) :: Pulling Records From A Table Between Date Ranges In Another Table

Mar 17, 2014

This seems simple enough but for some reason, my brain isn't working.

I have a lookup table:

Table A: basically dates every 30 days

1/1/2014
2/3/2014
3/3/2014
4/3/2014

I have Table b that has records and dates created assocated with each record

I want all records that fall between the 1st 30 days to have an additional column that indicates 30

union

records with additional column indicating 60 days that fall between the 30 and 60 day

union

records with additional column indicating 90days that fall between the 60 and 90 day mark.

Is there an easy way to do this?

View 6 Replies View Related

SQL Server 2008 :: Update A Table With Date When There Are New Records In Another Table

Sep 16, 2015

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?

View 8 Replies View Related

Update One Table When Records Inserted In Another Table - Variables In Trigger

May 19, 2014

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)

[Code] ....

View 4 Replies View Related

Selecting ONLY Records From One Table Having ALL Data Contained In Other Table (GROUP BY?)

Jul 20, 2005

Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz

View 2 Replies View Related

HOW TO: Delete All Records From A Table Where A Child Record From Another Table Does Not Exist.

Mar 17, 2008

I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.

Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime

Child Table: TBL_VERIFICATION
PK: Serial Number

Thanks,
Sean

View 1 Replies View Related

Transact SQL :: Inserting Records Into Table 2 From Table 1 To Avoid Duplicates

Nov 2, 2015

INSERT
INTO [Table2Distinct]        
([CLAIM_NUMBER]        
,[ACCIDENT_DATE]

[code]....

I used the above query and it still inserts all the duplicate records. What is wrong with my statement?

View 5 Replies View Related

Show Changes Between Records In Main Table And Audit Table

Aug 2, 2006

Hallo
i have two tables, MainTable and MainTableAudit: the second one keeps DML auditing via triggers.
I'm trying to build a query to highlight changes occurred to fields between the MainTable record and its audited records in MainTableAudit, for example, let's suppose i entered an item with some wrong attributes, and edited it three times:
MainTable record contains the latest and current version
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|240 pages
MainTableAudit contains edited ID002 versions
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|232 pages|2006-07-08 08:32:12
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|212 pages|2006-05-08 10:54:02
ID002|Hitchhikers Guide to Galaxy|Sci-fi|222 pages|2006-07-04 11:42:16

I would like to build a report like this:
first insertion: Hitchhikers Guide to Galaxy|Sci-fi|222 pages
modified on 2006-07-04 11:42:16: field "Title" changed from "Hitchhikers Guide to Galaxy" to "Hitchhikers Guide to the Galaxy", field "PageNo" changed from "222" to "212"
modified on 2006-05-08 10:54:02: field "PageNo" changed from "212" to "232"
modified on 2006-07-08 08:32:12: field "PageNo" changed from "232" to "240"
current version: Hitchhikers Guide to the Galaxy|Sci-fi|240 pages

i'd prefer to use T-SQL and keep all into a single place (a view or storedprocedure), or at least to use reporting services; btw i would like to avoid coding web pages or hosted applications.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved