Expecting a query to get a solution [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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.

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	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

0	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

Latest Posts