Transact SQL :: How To Find A Non Matched Record In 2 Tables

Jul 15, 2015

I have 2 tables .Lets Say tableA and tableB.Both Have Columns ClaimNumber,Amount. Now, to get the matched records for these 2 tables, i wrote the following query Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount = B.Amount This query works perfectly fine and gives me only matching records, however if i want to have records which match with ClaimNumber and not with Amount i wrote something like this

Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount <> B.Amount.

And this query produces wrong results, its giving me match and also non match records.

how to write a query for my non match condition?

View 5 Replies


ADVERTISEMENT

Find Not Matched

Oct 17, 2006

I am very new to MsSQL having only used Access before.
I am trying to find records in the second select statement that aren't in the first select statement. Using the

dbo.Tbl_01035_Tour_Players.TourTypeId (1st select stat)
Tbl_01020_Tour_Types.TourTypeId (2nd select stat)

Have been working on it for 15 hours now and still can't find a way to do it. Thanxs in advance

@_MemberIdint


AS
BEGIN
SET NOCOUNT ON;
Begin
SELECT dbo.Tbl_01035_Tour_Players.TourTypeId, dbo.Tbl_01035_Tour_Players.MemberId, dbo.Tbl_01030_Tour_Schedule.Sch_TourId,
dbo.Tbl_01030_Tour_Schedule.Sch_TourStartDate, dbo.Tbl_01030_Tour_Schedule.Sch_TourEndDate,
dbo.Tbl_01030_Tour_Schedule.Sch_TourGame
FROM dbo.Tbl_01035_Tour_Players INNER JOIN
dbo.Tbl_01030_Tour_Schedule ON dbo.Tbl_01035_Tour_Players.TourId = dbo.Tbl_01030_Tour_Schedule.Sch_TourId
WHERE (dbo.Tbl_01030_Tour_Schedule.Sch_TourEndDate >= GETDATE()) and dbo.Tbl_01035_Tour_Players.MemberId = @_MemberId
End



Begin
SELECT Tbl_01020_Tour_Types.TourTypeId, Tbl_01020_Tour_Types.TourGame, Tbl_01020_Tour_Types.TourDescr, Tbl_01020_Tour_Types.TourDaysDur,
Tbl_01020_Tour_Types.TourMinPot
FROM Tbl_01020_Tour_Types CROSS JOIN
Tbl_01035_Tour_Players
WHERE (NOT (Tbl_01020_Tour_Types.TourTypeId = db.Tbl_01035_Tour_Players.TourTypeID))
End

View 20 Replies View Related

Tables Need To Be Matched Up...

Feb 28, 2000

Hello there,

I have a table called employee which identifies each employee by the field 'empId'. In this table there are 2 fields called 'Fname' and 'Lname' - I have another table called 'Training' that I imported from Lotus Notes that holds all the training taken by the employees.

Unfortunately, the only identifying field that imported is called 'Name' and Just contains the First Name, a space and the Surname

Joe Smith
Susan Jones

I am at a loss to find a way of joining these tables. I need to put the field 'empId' into the Training Table. Is this possible and what about the case where there may be more than 1 Susan Jones?

Please, any help or suggeestions would be greatly appreciated.

Thanks in advance,

Anthony

View 1 Replies View Related

SQL Server 2012 :: Get Unmatched And Matched Values From Two Tables

Apr 10, 2015

I have two tables category and lu_category.

The category table has columns [CategoryId], [CategoryName], [TotalCategoryRiskScore] and the lu_category has columns [CategoryId], [CategoryName].

I want a sql query that will list all values from lu_category table and category table and if a categoryid is not available in lu_category table but available in category table,
i need that too in the result.

Below is the screenshot of the data and my desired output

I have attached the data as spreadsheet.

View 6 Replies View Related

Transact SQL :: Find Unmatching Columns Between Two Tables While Applying A Change Column Prefix Table

Nov 16, 2015

For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.

In the below example, want to end up with three records:

 1, View1, Column4
 2, View2, Column4
 2, View2, Column5

I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.

I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.

I want to end up with these three records as non-matching:

View1, Column4
View2, Column4
View2, Column5

View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.

/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;

[Code] ....

View 3 Replies View Related

Grabbing First Record Rather Than The Record I Am Trying To Find.

Mar 24, 2007

I tried checking to see if the point at which the reader was, that if it was the record I am looking for to go ahead and add the table data to a label. But for some reason it's only taking the first record in the database and not the one I  thought I was at.[CODE]     public void UpdateMaleHistLbl()    {        SqlConnection conn = new SqlConnection("Server=localhost\SqlExpress;Database=MyFamTree;" + "Integrated Security=True");        SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = @usrnmeLbl ", conn);        comm.Parameters.AddWithValue("@usrnmeLbl", usrnmeLbl.Text);        conn.Open();        SqlDataReader reader = comm.ExecuteReader();        while (reader.Read())        {            string usr = reader["username"].ToString();            usr = usr.TrimEnd();            string pss = reader["password"].ToString();            pss = pss.TrimEnd();            if (usrnmeLbl.Text == usr)            {                if (hiddenpassLbl.Text == pss)                {                    maleHistLbl.Text = reader["GG_Grandfather"] + " > ";                    maleHistLbl.Text += reader["G_Grandfather"] + " > ";                    maleHistLbl.Text += reader["Grandfather"] + " > ";                    maleHistLbl.Text += reader["Father"] + " > ";                    maleHistLbl.Text += reader["Son"] + " > ";                    maleHistLbl.Text += reader["Grandson"] + " > ";                    maleHistLbl.Text += reader["G_Grandson"] + " > ";                    maleHistLbl.Text += reader["GG_Grandson"] + "<br /><br />";                }            }            break; //exit out of the loop since user found        }        reader.Close();        conn.Close();     }}[/CODE]Thanks in advance

View 1 Replies View Related

Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?

Jul 26, 2005

I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you

View 4 Replies View Related

Lookup - No Matched Records

Aug 9, 2007



Hi All,

I have two tables:TableA and TableB, both of the two tables have two fields: c_IDA char(10) and c_IDB char(10); A import text file includes the ID data, the data will insert into TableB only when the ID existed in TableA
The line in the import text file like this:
00000023450000012345


in the text file: 1-10 is the c_IDA and 11-20 is the c_IDB

In the Derived Column transformation,
set the column name IDA as expression: SUBSTRING(LINE,1,10)
set the column name IDB as expression: SUBSTRING(LINE,11,10)

In the followed Lookup transfornation, I created the reference table with a sql: Select c_IDA,c_IDB from TableA, the IDA and IDB in the pipeline linked to the reference table's c_IDA and c_IDB, i then setup the error output to another log file.

The problem is even though the ID existed in the TableA, the Lookup always generate the error out put, that means the ID not been found in the TableA at all.


In the sample above, if i run the sql in the SSMS:
Select * from TableA where c_IDA = '0000002345' and c_IDB = '0000012345'

there is one record retrived.


Any idea?

TIA

View 9 Replies View Related

How To Find The Odd Record Out....

Mar 22, 2007

Code:


"SELECT " & _
"Asset_Number, Inv_ID " & _
"FROM " & _
"tbl_Asset "



OKay looking at this highly simplified query here is my problem.
We have a ton of Asset numbers that are the same and for each asset number the inv_id should also be the same.....

However, since this data gets entered by humans - sometimes the inv_id is wrong....
I need to figure out a query that fleshes out the rows where asset_nums are the same but inv_id is different than other rows...?

Of course this may be impossible.....
I mean what if there was only two records by the same asset_num and each inv_id was different?
How could the query know which was the right one......

I need to run a report that finds the invalid inv_id's so that they can corrected.....


any thoughts on how this is done?

My thoughts are - using an inner select...
something to the effect of :

SELECT ....
FROM
(SELECT * WHERE (inv_id = inv_id) > 1)
GROUP BY asset_num.........


I just dont know exactly what the correct syntax is....

muchas gracias....

(btw this is actually ms-sql but mysql forum is so much more popular and the syntaxes are similar enough....thank you...)

View 2 Replies View Related

Find Last Record ........

Apr 14, 2008

sno Empid Address Date
------------------------------------------------
1100bhopal2008-01-02 00:00:00.000
2100indore2008-02-02 00:00:00.000
3100gwalior2008-03-03 00:00:00.000
4101gwalior2008-01-02 00:00:00.000
5101Indore2008-02-02 00:00:00.000
6102bhopal2008-01-01 00:00:00.000
------------------------------------------------
I want last update Address of employee from this table like

100 Gwalior
101 Indore
102 Bhopal

pls help me out ...............


Yaman

View 2 Replies View Related

Find X Record

Mar 20, 2007

hi all,

if we have 100 same records, how can we select any 50 of it? thanks


~~~Focus on problem, not solution~~~

View 20 Replies View Related

How To Find Who Has Changed The Record

Aug 14, 2001

Is there any method so that I can find that the particular user has changed the record in the particualr table..?

View 1 Replies View Related

Need To Find Where In Db Record Is Stored

Feb 8, 2005

I have a mystery database on my hands...it is part of an application that my company bought, and it uses SQL server for its backend. The reporting features built in are not good enough, so I need to write some queries by hand...trouble is I am having a hard time figuring out how the schema works...using the front end they gave us I put a value of "12345" for a field I need to get to, but I can not locate where in the db it gets stored....can anyone tell me a way to query that will look at every single record and every single field in the db to find the value "12345"??

View 14 Replies View Related

Find A Record Out Of Place

Oct 15, 2013

I have a table called Register that has the following in it

Policy_number, Policy_date_time, Portfolio_set, Status..The rule for the table is that the last record for each portfolio_set for a policy the status needs to be 'A' but there have been instances that the last record status is 'I'

I need to identify the record that is out of place..In the example below record number 2.

example

policy_number Policy_date_time Portfolio_set, status
12345 1/1/2011 1 I
12345 1/2/2011 1 A
12345 1/3/2011 1 I
12345 1/4/2011 1 I

I need to identify that the second record is in the wrong place...

View 4 Replies View Related

How To Find Updating A Record In .net

Sep 23, 2007

Hello
I need an alarm or raise an event from SQL Server after updating to get it in .net.
I use SQL Server 2005 Express Edition and vb.net2 for my programming language.
Suppose that in a windows form I have a grid and I'm working with this page
another client is working with this same page .He is editing the information of
a record of a grid and save it in database so I want to give an alarm or an event
that raise from SQL Server (for example in a trigger) , what can I do?
Thanks.

View 3 Replies View Related

How To Find How A Record Was Deleted

Jul 23, 2005

Is there a way to tell who or what process deleted a record in a SQL table.I know you can setup triggers or Profiler - but is there a way to see how arecord was deleted if you DO NOT have a trigger or Profiler already running?This is SQL 2000 Standard, sp3a with default options and configuration.Thanks

View 2 Replies View Related

Find A RowSize On A Record

Dec 11, 2007

I have a select statment that gives me an error, cannot go more than rowsize 8094.
I am trying to find the particualr record/acctno, where the rowsize is excedding the max limi of 8094 chars. Once I have the acctno, I will correct it.
But how do I find which acct has this issue? Bascailly how can I find which record size is more than 8094 chars?

View 1 Replies View Related

Find Most Recent Record For Each Item?

Oct 29, 2014

We have a work order notes table in our ERP system, and I want to see the most recent note record for each work order. Sometimes there will one be one, so I want to see all those, but sometimes there will be several notes for each work order and in this case I want to see only the most recently added note for that work order.

The query below shows some results as an example. In this case I want to see all the records except for work order number DN-000023 where I only want to see the note dated/timed 07-12-2011 16:52 (the most recent one).

select id, worknumber, date, notes from worksordernotes

id worknumber date
----------- ------------ ----------------------- --------------------
1 DN-000056 2011-12-07 13:22:00 13.20 PM JAMES- SPOK
2 DN-000079 2011-12-07 14:24:00 JCB HAVE TOLD ME THE
4 DN-000065 2011-12-07 15:48:00 ANDY FROM SITE RANG
5 DN-000023 2011-12-07 15:54:00 CHASED THIS 4 TIMES
6 DN-000023 2011-12-07 16:52:00 HOLTS ATTENDED THIS
7 DN-000092 2011-12-08 09:50:00 RETURNING WITH PARTS

View 3 Replies View Related

Find Record Where 1 Field Value Has Changed

Apr 2, 2015

I need to find all items in an inventory table where a field has been unticked in the last 24 hours (there is no audit trail), as well as the contract number of the contract it has been added to (it will not have existed in that table before).

These are the two table querys in their basic form:

select item (nvarchar(20)), inactive (bit) from inventory

select item (nvarchar(20) , contract (nvarchar(20)) , original_start_rent (datetime) from deltickitem

I would like to see just the item number and the contract number it has been added to.

View 11 Replies View Related

SQL Statement To Find The The Maximum Record

Oct 12, 2007

I have a table with member information. Each member has multiple effective dates. I would like to query to find the record for each member that has the maximum effective date. A sample table is below:

MEMBERID EFECTIVEDATE
------------ ------------------
699361401 20070101
699361401 20070501
732612701 20070101
732612701 20070501
575424301 20070101
575424301 20070501
192939801 20070101
192939801 20070501
458645001 20070101
458645001 20070501

View 4 Replies View Related

T-SQL (SS2K8) :: CTE Query To Find Specific Record?

Sep 18, 2015

I need to build a CTE query to find for the same Cabstamp (document) where i have different Origin.

I know that i can build this with a correlated subquery, but i´am curious about using CTE.

I post sample create Script:

create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)

insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc)
select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110'
union
select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110'
union
select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110'
union

[code]....

View 5 Replies View Related

Find The Last Record By Date In A Sub Group Of Records.

Oct 1, 2007

Looking to see if thier is a better way to find the last record entered in a group of records.

What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.

Table example using simplied data.






PolId

CoveragId

EffDate

Status

Limit1


2

1

9/7/2007

a

10000


2

2

9/7/2007

a

150000


2

2

10/1/2007

a

200000


3

1

9/7/2007

a

10000

The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.

The current query returns a single value. from a single table.

Current code used in the select protion on a larger query. bpi = basicpolicyInformation.

( Select c1.limit1
From AFW_Coverage as c1
Where c1.PolId=bpi.PolId
and c1.CoverageId = (select max(CoverageId) as CoverageId
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageCode = 'Dwelling'
and status <> 'D'
)
and c1.effDate = (select max(Effdate) as Effdate
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageID = c1.CoverageId
)


Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.

View 16 Replies View Related

SELECT Statement With Multiple Criteria That Returns The Criterion Matched

May 18, 2006

Hello:
I need assistance writing a SELECT statement.  I need data from a table that matches one (or more) of multiple criteria, and I need to know which of those criteria it matched.  For instance, looking at the Orders table in the Northwind database, I might want all the rows with an OrderDate after Jan 1, 1997 and all the rows with a ShippedDate after June 1, 1997.  Depending on which of those criteria the row matches, it should include a field stating whether it is in the result set because of its OrderDate, or its ShippedDate.  One way of doing this that I've already tried is:
SELECT 'OrderDate' AS [ChosenReason], Orders.*FROM OrdersWHERE OrderDate > '1-1-1997'UNIONSELECT 'ShippedDate' AS [ChosenReason], Orders.*FROM OrdersWHERE ShippedDate > '6-1-1997'
In my application, scanning a table with thousands of records for five sets of criteria takes a few seconds to run, which is not acceptable to my boss.  Is there a better way of doing this than with the UNION operator?
Thank you

View 2 Replies View Related

Find Nearest Date Record Without Cross Apply

Oct 13, 2012

Table :

ChangeID ChangeDate EquipmentID ModuleID EquipStatus
1 12/9/08 230 1789 Normal
2 13/9/08 450 1245 Normal
3 17/9/08 230 1789 Open
4 21/9/08 230 1899 Open
5 21/9/08 450 1674 Normal
6 22/9/08 450 2364 Normal

Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.

SELECT outerT.*
FROM your_table AS outerT
CROSS APPLY
(
SELECT TOP 1
equipment_id
, change_date
FROM your_table AS innerT
WHERE innerT.change_date <= @point_in_time
AND innerT.equipment_id = outerT.equipment_id
ORDER BY change_date DESC
) AS applicable_records
WHERE applicable_records.change_date = outerT.change_date

The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.

View 1 Replies View Related

Find Most Recent Record In Table According To Date Field

Sep 19, 2015

The "Last" function in the query below (line 4 & 5) is not exactly what I'm after. The last function finds the last record in that table, but i need to find the most recent record in the table according to a date field.

Code:
SELECT
tblinmate.statusid,
tblinmate.activedate,
Last(tblclassificationhistory.classificationid) AS LastOfclassificationID,
Last(tblsquadhistory.squadid) AS LastOfsquadID,
tblperson.firstname,
tblperson.middlename,
tblperson.lastname,

[Code] ....

The query below finds the most recent record in a table according to a date field, my problem is i dont know how to integrate this Query into the above to replace the "Last" function

Code:
SELECT a.inmateID,
a.classificationID,
b.max_date
FROM (
SELECT tblClassificationHistory.inmateID,
tblClassificationHistory.classificationID,

[Code] .....

View 1 Replies View Related

SQL Server 2012 :: Find Record(s) That Exceed Budget Value

Mar 3, 2015

I have the following tables:

--acts as a transaction table
CREATE TABLE #TestData (
id int not null identity(1,1) primary key,
account varchar(10) not null,
deposit int not null

[Code] ....

--desired results
-- within each account group, when a individual record causes the groups running total to exceed the group's budget, show the difference that causes the groups running total to exceed the budget as unbudgeted. The amount of that transaction upto the budget amount show as renewal. For any succeeding individual records in the group, the amount of that transaction is Unbudgeted.

Here's the SQL I have thus far...

SELECT a.id
,a.account
,a.deposit
,a.total
,a.budget
,case when a.total <= a.budget

[Code] ....

View 4 Replies View Related

DB Engine :: Can Find A Record Of Stored Procedure Being Called?

Jul 15, 2015

I seem to be able to see where a procedure is being recompiled, but not the actual statement that was executing the procedure.

Note, with 2008 there is a DMV called dm_exec_procedure_stats , which is not present in 2005

USE YourDb;

SELECT qt.[text] AS [SP Name],
qs.last_execution_time,
qs.execution_count AS [Execution Count]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = DB_ID()
AND objectid = OBJECT_ID('YourProc')

The above shows results that include the CREATE PROCEDURE statements for the procedure in question, but this only indicates that the procedure was being recompiled, not necessarily that it was being executed?

View 3 Replies View Related

How Do I Handle REPLACE A Particuler Matched String Within An NTEXT Column In SQL Server?

Mar 27, 2008

Hi!
I recently was confronted with a problem where a piece of text that was included in many NTEXT column values in a table needed to be replaced with another piece of text. You can't issue normal REPLACE statements against NTEXT columns, so this seemed to be a bit of a challenge €” issuing a REPLACE() against a TEXT or NTEXT column in SQL Server yields error

I tried following

UPDATE CaseTypeDefs SET definition = replace(LTRIM(RTRIM(definition)), '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]>', '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]>')


But this is producing following error

Server: Msg 8116, Level 16, State 1, Line 1Argument data type ntext is invalid for argument 1 of replace function.

For Example: I want to replace string <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]> with <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]> in NTEXT column values in a table.

Need help, how to do it?.


Thanks In Advance

Devloper
Anil Kumar Dwivedi

View 4 Replies View Related

In Stored Proc - How Do I Find The Next Key Value (integer) And Use It To Populate A Field In The New Record.

Nov 27, 2006

Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert    @Company_ID int,    @Name varchar(200),    AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID)  INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name) 

View 1 Replies View Related

SQL Server 2012 :: Find Most Recent Record Of Consecutive Dates

Feb 23, 2015

I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.

I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:

FromDate ToDate Customer Number Contract Number
02/01/2015 07/31/2015 2555 456
01/15/2015 01/31/2015 2555 456
04/01/2013 09/30/2015 2555 123
03/13/2013 03/31/2013 2555 123

From this table, I would like a query that would give me this result:

01/15/2015 07/31/2015 2555 456
03/13/2013 09/30/2015 2555 123

There will likely be more than just 2 consecutive records per contract number.

View 4 Replies View Related

SQL Server 2008 :: How To Find Time Difference Between Two Rows Of Record

Nov 6, 2015

I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .

LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR

View 6 Replies View Related

Transact SQL :: Getting Last Record In Aggregation

Jul 17, 2015

I have daily data that I wish to summarise into monthly. The field [Close] will return the last day of the month that has a value.The query below summarises the data as I want it but using the max of [Close] in the month instead rather than the Last value for the month. In Access I used to use Last function that worked perfectly. From googling, Last doesn't seem to exist in T-SQL but there must be a way to do it.

    D.YearMonth_Number,max(AT.[Close])
FROM
      tbl_Date
ASD
INNERJOINtbl_ASXStats
ASAT
OND.YearMonthDay_Number
=AT.YearMonthDay_Number

[code]...

View 5 Replies View Related

Transact SQL :: Get One Record By ID Which Has Max Date

May 4, 2015

I have records like this

id      address1          date
1      xyz                 01/01/2013
1       abd                01/01/2014
2       dfg                 01/03/2015

Now what I want is just 1 record by id which has max date,if there is 2 records for same id, if there is 1 record then it should come.

So here result should be

1       abd                01/01/2014
2       dfg                 01/03/2015

View 12 Replies View Related







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