Ms Sql Server Technology Discussions

[Darling999]

[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]

good one rey Loveindia, appreciate it. I ran this query in Local, hope he he looking for the Same.

[Darling999]

[quote name='Kaarthikeya' timestamp='1359486151' post='1303196530']
nuvvu raayalevu anukunte ninnu adige vaadine kaadu


paina req lo cheppinattu only common columns pull cheyyali bedaru...u r pulling everything
[/quote]

neeku akkarled=ni columns teeseyyi vayya dantlo nundi, china pillagani laaga anni raayamantaav endi

[deals2buy]

[quote name='DARLING...' timestamp='1359486510' post='1303196568']

neeku akkarled=ni columns teeseyyi vayya dantlo nundi, china pillagani laaga anni raayamantaav endi
[/quote]
ala raasthe count diff vastundi mayya anduke kada malla malla adigedi

[Darling999]

[quote name='Kaarthikeya' timestamp='1359486676' post='1303196578']
ala raasthe count diff vastundi mayya anduke kada malla malla adigedi
[/quote]
Count Different Raavadam endi, ee query raastey lastest records based on inactive date vastaayi.

For eg if U run the above query u get the result like this

[img]http://img109.imageshack.us/img109/8104/scrdr.jpg[/img]

[Darling999]

nuvvu chepina req prakaram ayitey idi correctey mari, inka eminaa dig cheyyala cheppu req lo?

[deals2buy]

Also, oka table last eppudu update ayindo teluskodaaniki em query no cheppandi vayya

Google lo dorikindi work avvale naaku..

PS: nenu vaadedi Legacy server

[deals2buy]

[quote name='DARLING...' timestamp='1359486948' post='1303196598']
Count Different Raavadam endi, ee query raastey lastest records based on inactive date vastaayi.

For eg if U run the above query u get the result like this

[img]http://img109.imageshack.us/img109/8104/scrdr.jpg[/img]
[/quote]
[quote name='DARLING...' timestamp='1359486992' post='1303196601']
nuvvu chepina req prakaram ayitey idi correctey mari, inka eminaa dig cheyyala cheppu req lo?
[/quote]

paina cheppanu kada bhayya... I cannot use CTEs ani

[Darling999]

[quote name='Kaarthikeya' timestamp='1359487007' post='1303196603']
Also, oka table last eppudu update ayindo teluskodaaniki em query no cheppandi vayya

Google lo dorikindi work avvale naaku..

PS: nenu vaadedi Legacy server
[/quote]
eskoo naa raaja, dorkindi choodu


SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'tempdb')
AND OBJECT_ID=OBJECT_ID('test')

I Tested it in Local

but will check for 2000 wait.

[Darling999]

[quote name='Kaarthikeya' timestamp='1359487081' post='1303196608']
paina cheppanu kada bhayya... I cannot use CTEs ani
[/quote]
idi use cheyyi vastundi


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

t1.*, t3.* dagga nee field names icheyyi saripoddi I verified now in Local its working

[Darling999]

[quote name='Kaarthikeya' timestamp='1359487081' post='1303196608']
paina cheppanu kada bhayya... I cannot use CTEs ani
[/quote]
[img]http://img801.imageshack.us/img801/9716/scrwa.jpg[/img]

[Guest]

[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]



select T2.ID,T2.Name,T2.State from Table1 T1
JOIN(
select ID,Name,State, Max(InactiveDate)
From Table2
Group by ID,Name,State
) T2

On T1.ID = T2.ID AND T1.Name = T2.Name AND T1.State = T2.State

[deals2buy]

[quote name='DARLING...' timestamp='1359487432' post='1303196642']
idi use cheyyi vastundi


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

t1.*, t3.* dagga nee field names icheyyi saripoddi I verified now in Local its working
[/quote]

second join daggara date meeda kuda cheyyaalaa?
ala chesina cheyyakapoina diff em kanipinchaledu naaku

[deals2buy]

[quote name='Guest' timestamp='1359487613' post='1303196666']



select T2.ID,T2.Name,T2.State from Table1 T1
JOIN(
select ID,Name,State, Max(InactiveDate)
From Table2
Group by ID,Name,State
) T2

On T1.ID = T2.ID AND T1.Name = T2.Name AND T1.State = T2.State
[/quote]

same question neeku kuda....ID meeda okkate join cheste saripodaa?

[loveindia]

[quote name='DARLING...' timestamp='1359487432' post='1303196642']
idi use cheyyi vastundi


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

t1.*, t3.* dagga nee field names icheyyi saripoddi I verified now in Local its working
[/quote]

aaa tooch tooch... idi nenu raasinaa query... .. endi va darling office loney anukuntey ikkada kuda naa credit kottestunnav... ... just kidding.. Thanks baa..

Mr.Karthikeya, nuvvu cheppina requirement ki adey query... neeku result set ela kaavalo adi chupi aitey... raayadaniki try chesta...

[deals2buy]

[quote name='loveindia' timestamp='1359489194' post='1303196832']

aaa tooch tooch... idi nenu raasinaa query... .. endi va darling office loney anukuntey ikkada kuda naa credit kottestunnav... ... just kidding.. Thanks baa..

Mr.Karthikeya, nuvvu cheppina requirement ki adey query... neeku result set ela kaavalo adi chupi aitey... raayadaniki try chesta...
[/quote]
meer iddaru same office aa? [img]http://lh3.ggpht.com/_KVkPY2XIbRQ/TWAgXprYLuI/AAAAAAAABCo/VzL0ae41lc4/brahmi%20laugh.gif[/img]

naa doubt entante date meeda kuda join cheyyalna ani?