Ms Sql Server Technology Discussions

[deals2buy]

Table1
ID INT,
Name VARCHAR(50),
State CHAR(2)

Table2
ID INT
Name VARCHAR(50),
Address VARCHAR(200),
State CHAR(2)
County VARCHAR(20),
Active_Date DATETIME,
Inactive_Date DATETIME


[u]Table1 Data[/u]:

ID Name State
101 XYZ TX
102 ABC CA
103 PDF NY


[u]Table2 Data[/u]:

ID Name Address State County Active_Date Inactive_Date
101 XYZ 1st street TX Hill 2008-01-01 2008-12-31
101 XYZ X street CA Bill 2010-02-15 2010-12-31
101 XYZ Fake St CA Fill 2011-01-01 2012-12-31
102 ABC L street CA Mill 2009-01-10 2009-10-31
102 ABC J Street CA Till 2012-01-01 2013-12-31
102 ABC P Street IL Jill 2010-01-01 2011-01-01



When I join these two tables I should be able to pull all the common records in both the tables with the most updated information in Table2 (based on the Max inactive_date in Table2)



Evaraina help cheyyandi vayya

[Darling999]

[quote name='Kaarthikeya' timestamp='1359479736' post='1303195781']
Table1
ID INT,
Name VARCHAR(50),
State CHAR(2)

Table2
ID INT
Name VARCHAR(50),
Address VARCHAR(200),
State CHAR(2)
County VARCHAR(20),
Active_Date DATETIME,
Inactive_Date DATETIME


[u]Table1 Data[/u]:

ID Name State
101 XYZ TX
102 ABC CA
103 PDF NY


[u]Table2 Data[/u]:

ID Name Address State County Active_Date Inactive_Date
101 XYZ 1st street TX Hill 2008-01-01 2008-12-31
101 XYZ X street CA Bill 2010-02-15 2010-12-31
101 XYZ Fake St CA Fill 2011-01-01 2012-12-31
102 ABC L street CA Mill 2009-01-10 2009-10-31
102 ABC J Street CA Till 2012-01-01 2013-12-31
102 ABC P Street IL Jill 2010-01-01 2011-01-01



When I join these two tables I should be able to pull all the common records in both the tables with the most updated information in Table2 (based on the Max inactive_date in Table2)



Evaraina help cheyyandi vayya
[/quote]

where condition lo subquery raaayi, anduloo date max ivvachu i.e. we can use aggegate in where condition.

Try this

Select col1,col2
From tbl1
where a.inactivedate = (select Max(inactivedate) from tbl2)

oka velaa join loney idi raayali antey u can do so by joining and on condition lo chnages cheyyi u should be good.

[deals2buy]

[quote name='DARLING...' timestamp='1359480040' post='1303195832']

where condition lo subquery raaayi, anduloo date max ivvachu i.e. we can use aggegate in where condition.

Try this

Select col1,col2
From tbl1
where a.inactivedate = (select Max(inactivedate) from tbl2)

oka velaa join loney idi raayali antey u can do so by joining and on condition lo chnages cheyyi u should be good.
[/quote]

adi work avvatle...already tried...ee error vastondi

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.


sare ani TOP ichina no use

[Darling999]

Lock Information in SQL Server


[b]Meaning of locks:-[/b]
Relational [url="http://www.dbtalks.com/uploadfile/anjudidi/use-table-hints-and-locks-in-the-sql-server-2005/#"]database systems[/url] like SQL Server use locks to prevent users from stepping on each other's toes. That is, locks prevent users from making conflicting data changes. When one user has a particular piece of data locked, no other user may modify it. In addition, a lock prevents users from viewing uncommitted data changes. Users must wait for the changes to be saved before viewing. Data may be locked using various methods. SQL Server 2005 uses locks to implement pessimistic concurrency control among multiple users performing modifications in a database at the same time.
[b]Types of locks:-[/b]

A database system may lock data items at one of many possible levels within the system hierarchy.
1. Rows:-an entire row from a database table
2. Pages:-a collection of rows (usually a few kilobytes)
3. Extents:-usually a collection of a few pages
4. Table:-an entire database table
5. Database:-the entire database table is locked
[b]Table Hints:-[/b]
There are times when you need to override SQL Server's locking scheme and force a particular range of locks on a table. Transact-SQL provides a set of table-level locking hints that you can use with SELECT, INSERT, UPDATE, and DELETE statements to tell SQL Server how you want it to lock the table by overriding any other system-wide or transactional isolation levels.
[b]Syntax Of Table Hints:-[/b]
USE DatabaseName;
GO
SELECT * FROM TableName WITH(Table_Hints)
GO

The available hints include the following:-
[b]1. HOLDLOCK:-[/b]
Holds the shared locks on the range read, or modified for the duration of the transaction or statement. Overrides the default behavior, which is to release the locks as soon as the data page has been read. HoldLock is equivalent to the serializable transaction isolation level.HoldLock is use the select, Insert, Update and Delete command.
[b]Example:-[/b]
USE CeilInn3;
GO
SELECT *
FROM IntroOfStd
WITH (HOLDLOCK)
WHERE StudentId= '1'
GO

[b]2. NOLOCK:-[/b]
Does not honor shared or exclusive locks. NoLock is equivalent to the read uncommitted transaction isolation level.NoLock use only select command.
[b]Example:-[/b]
USE CeilInn3
GO
SELECT *
FROM Student WITH(NOLOCK)
GO

[b]3. PAGLOCK:-[/b]
Forces the transaction to use page-level locks instead of escalating to table-level locks.PagLock is use the select,Insert,Update and Delete command.
[b]Example:-[/b]
USE CeilInn3;
GO
UPDATE tblEmployee
SET salary = salary * 1.50
FROM tblEmployee WITH (PAGLOCK)
WHERE JoiningDate < '02/02/2009'
SELECT * from tblEmployee
GO

[b]4. READCOMMITTED:-[/b]
Equivalent to the read uncommitted transaction isolation level.This is same as NoLock.
[b]5. READUNCOMMITTED:-[/b]
Equivalent to the read uncommitted transaction isolation level. This is same as NoLock.
[b]6. READPAST:-[/b]
[url="http://www.dbtalks.com/uploadfile/anjudidi/use-table-hints-and-locks-in-the-sql-server-2005/#"]Skip[/url] locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The ReadPast lock hint applies only to transactions operating at Read committed isolation and will read only past row-level locks. Applies only to the SELECT statement.
[b]Example:-[/b]
USE CeilInn3;
GO
DECLARE @EmployeeId INT
BEGIN TRAN TRAN1
SELECT TOP 1 @EmployeeId = EmployeeId
FROM tblEmployee WITH (readpast)
PRINT 'processing @EmployeeId # ' + CAST(@EmployeeId AS VARCHAR)
-- account for delay in processing time
WAITFOR DELAY '00:00:05'
DELETE FROM tblEmployee
WHERE EmployeeId = @EmployeeId
COMMIT
SELECT * FROM tblEmployee
GO

[b]7. REPEATABLEREAD:-[/b]
Equivalent to the Repeatable Read transaction isolation level, which disallows dirty reads, but allows phantoms.
[b]Example:-[/b]
USE CeilInn3;
GO
SELECT EmployeeId
FROM tblEmployee WITH(RepeatableRead)
WHERE EmployeeId BETWEEN 0 AND 2
GO

[b]8. ROWLOCK:-[/b]
Forces the transaction to use row-level locking instead of the page- or table-level locking that would otherwise be used.
[b]Example:-[/b]
USE CeilInn3;
GO
SELECT StudentId
FROM IntroOfStd WITH(ROWLOCK)
WHERE StudentId BETWEEN 0
AND 2
GO

[b]9. SERIALIZABLE:-[/b]
Equivalent to the Serializable transaction isolation level and the HoldLock hint. Holds the shared locks for the duration of the transaction or statement.
[b]Example:-[/b]
USE CeilInn3;
GO
SELECT COUNT(*)
FROM tblEmployee WITH(SERIALIZABLE)
GO

[b]10. TABLOCK:-[/b]
Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds this lock until the end of the statement. However, if you also specify HoldLock, the lock is held until the end of the transaction.
[b]Example:-[/b]
USE CeilInn3;
GO
SELECT EmployeeId AS Employee_Id,
EmpolyeeName AS Employee_Name,
JoiningDate AS Joining_Date
FROM tblEmployee
WITH (TABLOCK)
GO

[b]11. TABLOCKX:-[/b]
Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.
[b]Example:-[/b]
USE CeilInn3;
GO
SELECT P_Id, F_Name, L_Name
FROM Address
with (TabLockx)
WHERE P_Id is not null
ORDER BY P_Id
GO

[b]12. UPDLOCK:-[/b]
Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLock has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
[b]Example:-[/b]
USE CeilInn3;
GO
DECLARE @EmployeeId INT
BEGIN TRAN TRAN1
SELECT TOP 1 @EmployeeId = EmployeeId
FROM tblEmployee WITH (updlock)
PRINT 'processing @EmployeeId # ' + CAST(@EmployeeId AS VARCHAR)
-- account for delay in processing time
WAITFOR DELAY '00:00:05'
DELETE FROM tblEmployee
WHERE EmployeeId = @EmployeeId
COMMIT
SELECT * FROM tblEmployee
GO

[b]13. XLOCK:-[/b]
Use an exclusive lock that will be held until the end of the transaction on all data processed by the statement. Applies only to the SELECT statement. This lock can be specified with either PagLock or TabLock, in which case the exclusive lock applies to the appropriate level of granularity.
[b]Example:-[/b]
USE CeilInn3;
GO
SELECT COUNT(*)
FROM tblEmployee WITH(XLOCK)
GO

[Darling999]

[quote name='Kaarthikeya' timestamp='1359480345' post='1303195869']
adi work avvatle...already tried...ee error vastondi

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.


sare ani TOP ichina no use
[/quote]
wait lunch ki veltunna vachi help cehstaa..

[deals2buy]

[quote name='DARLING...' timestamp='1359480544' post='1303195902']
wait lunch ki veltunna vachi help cehstaa..
[/quote]
ok...ee lopu nenu kusthilu padatha

[loveindia]

[quote name='Kaarthikeya' timestamp='1359479736' post='1303195781']
Table1
ID INT,
Name VARCHAR(50),
State CHAR(2)

Table2
ID INT
Name VARCHAR(50),
Address VARCHAR(200),
State CHAR(2)
County VARCHAR(20),
Active_Date DATETIME,
Inactive_Date DATETIME


[u]Table1 Data[/u]:

ID Name State
101 XYZ TX
102 ABC CA
103 PDF NY


[u]Table2 Data[/u]:

ID Name Address State County Active_Date Inactive_Date
101 XYZ 1st street TX Hill 2008-01-01 2008-12-31
101 XYZ X street CA Bill 2010-02-15 2010-12-31
101 XYZ Fake St CA Fill 2011-01-01 2012-12-31
102 ABC L street CA Mill 2009-01-10 2009-10-31
102 ABC J Street CA Till 2012-01-01 2013-12-31
102 ABC P Street IL Jill 2010-01-01 2011-01-01



When I join these two tables I should be able to pull all the common records in both the tables with the most updated information in Table2 (based on the Max inactive_date in Table2)



Evaraina help cheyyandi vayya
[/quote]

idenaa baa neeku kaavalsina query...

;WITH cte_table
as
(
select *, ROW_NUMBER() over(PARTITION by id order by inactive_date desc) as rnum
from Table2
)
select t1.ID, t1.Name, t1.State, c.id, c.address, c.state, c.county, c.active_date, c.inactive_date
from cte_table c
join Table1 t1 on c.id = t1.ID
where rnum = 1

[loveindia]

<code>

CREATE TABLE Table1
(
ID INT,
Name VARCHAR(50),
State CHAR(2)
)

CREATE TABLE Table2
(
ID INT,
Name VARCHAR(50),
Address VARCHAR(200),
State CHAR(2),
County VARCHAR(20),
Active_Date DATETIME,
Inactive_Date DATETIME
)


Insert into table1(ID, Name, State)
select 101, 'XYZ', 'TX'
union
select 102, 'ABC', 'CA'
union
select 103, 'PDF', 'NY'



Insert into table2(ID, Name, Address, State, County, Active_Date, Inactive_Date)
select 101,'XYZ','1st street','TX','Hill','2008-01-01','2008-12-31'
union
select 101,'XYZ','X street', 'CA','Bill','2010-02-15','2010-12-31'
union
select 101,'XYZ','Fake St','CA','Fill','2011-01-01', '2012-12-31'
union
select 102,'ABC','L street','CA','Mill','2009-01-10','2009-10-31'
union
select 102,'ABC','J Street','CA','Till','2012-01-01','2013-12-31'
union
select 102,'ABC','P Street','IL','Jill','2010-01-01','2011-01-01'

;WITH cte_table
as
(
select *, ROW_NUMBER() over(PARTITION by id order by inactive_date desc) as rnum
from Table2
)
select t1.ID, t1.Name, t1.State, c.id, c.address, c.state, c.county, c.active_date, c.inactive_date
from cte_table c
join Table1 t1 on c.id = t1.ID
where rnum = 1

</code>

[deals2buy]

[quote name='loveindia' timestamp='1359482706' post='1303196174']

idenaa baa neeku kaavalsina query...

;WITH cte_table
as
(
select *, ROW_NUMBER() over(PARTITION by id order by inactive_date desc) as rnum
from Table2
)
select t1.ID, t1.Name, t1.State, c.id, c.address, c.state, c.county, c.active_date, c.inactive_date
from cte_table c
join Table1 t1 on c.id = t1.ID
where rnum = 1
[/quote]


nenu vaadedi Legacy Server...so without CTE ela raayalo cheppu

[lolliman]

[quote name='Kaarthikeya' timestamp='1359483207' post='1303196246']


nenu vaadedi Legacy Server...so without CTE ela raayalo cheppu
[/quote]

legacy server anteee??

[deals2buy]

[quote name='lolliman' timestamp='1359483558' post='1303196289']

legacy server anteee??
[/quote]
SQL Server 2000 mama

[loveindia]

[quote name='Kaarthikeya' timestamp='1359483207' post='1303196246']


nenu vaadedi Legacy Server...so without CTE ela raayalo cheppu
[/quote]


aa mukka nenu code raayaka mundu seppalaa... nenu asalu raayaney lenu ani kada nee feelinguu... osos aagavoooo.. raaasi amputaaa inkoddi sepatlo...

[loveindia]

[quote name='Kaarthikeya' timestamp='1359483826' post='1303196313']
SQL Server 2000 mama
[/quote]

idi vaadu


select t1.*, t3.*
from Table1 t1
join (select ID, max(inactive_date) as inactive_date from Table2 group by ID) t2 on t2.ID =t1.ID
join Table2 t3 on t3.ID = t2.ID and t3.Inactive_Date = t2.inactive_date

[loveindia]

[quote name='loveindia' timestamp='1359484887' post='1303196414']
idi vaadu


select t1.*, t3.*
from Table1 t1
join (select ID, max(inactive_date) as inactive_date from Table2 group by ID) t2 on t2.ID =t1.ID
join Table2 t3 on t3.ID = t2.ID and t3.Inactive_Date = t2.inactive_date
[/quote]
emaindi bedaruuuuuu......???

[deals2buy]

[quote name='loveindia' timestamp='1359484443' post='1303196374']


aa mukka nenu code raayaka mundu seppalaa... nenu asalu raayaney lenu ani kada nee feelinguu... osos aagavoooo.. raaasi amputaaa inkoddi sepatlo...
[/quote]
nuvvu raayalevu anukunte ninnu adige vaadine kaadu
[quote name='loveindia' timestamp='1359484887' post='1303196414']
idi vaadu


select t1.*, t3.*
from Table1 t1
join (select ID, max(inactive_date) as inactive_date from Table2 group by ID) t2 on t2.ID =t1.ID
join Table2 t3 on t3.ID = t2.ID and t3.Inactive_Date = t2.inactive_date
[/quote]

paina req lo cheppinattu only common columns pull cheyyali bedaru...u r pulling everything