Expecting a query to get a solution [Resolved]

Posted by Thiru under Sql Server on 8/8/2011 | Points: 10 | Views : 831 | Status : [Member] | Replies : 4
Hi

there are two tables tblcutoff and tblrenewal
Data are:
tblfresh	
pdtp login
01/08/2011 ABC
06/08/2011 ABC


tblrenewal	
pdtp login
01/08/2011 ABC
05/08/2011 ABC
07/08/2011 ABC


I want to show records distinct of pdtp from both table

What is the query to get the following result:
pdtp	        login
01/08/2011 ABC
05/08/2011 ABC
06/08/2011 ABC
07/08/2011 ABC


Expecting a nice solutions - please.




Responses

Posted by: Kunalverma31 on: 8/12/2011 [Member] Starter | Points: 50

Up
0
Down

Resolved
Below query will solve your problem, with assumption that column name and type are same in both the tables.

SELECT pdtp,Login FROM tblfresh
UNION
SELECT pdtp,Login FROM tblrenewal

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 8/8/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

I hope the "pdtp" column in both table are "DateTime"
/*Using "FULL OUTER JOIN"*/

SELECT Coalesce(convert(varchar,a.pdtp,103),convert(varchar,b.pdtp,103)) pdtp,Coalesce(a.[Login], b.[Login]) [Login] FROM tblfresh A With(Nolock) full outer Join tblrenewal b With(Nolock)
On (convert(varchar,a.pdtp,103) = convert(varchar,b.pdtp,103))
Order by 1

/*Using "UNION"*/
SELECT convert(varchar,pdtp,103) pdtp, [Login] FROM tblfresh With(Nolock)
Union
SELECT convert(varchar,pdtp,103), [Login] From tblrenewal With(Nolock)
Order by 1


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 8/9/2011 [Member] Bronze | Points: 25

Up
0
Down
SELECT * FROM
(
SELECT CONVERT(VARCHAR,pdtp,103) pdtp, [Login] FROM tblfresh
UNION
SELECT CONVERT(VARCHAR,pdtp,103) pdtp, [Login] FROM tblrenewal
) as a
Order By 1

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 8/12/2011 [Member] Starter | Points: 25

Up
0
Down
Thanks Kunalverma.
Its working fine - this is what i expected to have. - Thanks for your valuable time spend here.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response