CODEWITHSUNDEEP
sql
sanjay
sanjay

Reputation: 35

i want to merge two select statements

select KeywordId,currentposition,PsnUpdateDate,PsnUpdateBy from  seo.Tbl_KeywordPosition where psnupdatedate = '2015-01-22' 
select KeywordId,currentposition,PsnUpdateDate,PsnUpdateBy from  seo.Tbl_KeywordPosition where psnupdatedate = '2015-01-23' 

1456    10  2015-01-22 00:00:00.000 Ananth
1467    8   2015-01-22 00:00:00.000 gangabhavani
1468    10  2015-01-22 00:00:00.000 admin

1456    9   2015-01-23 00:00:00.000 Ananth
1467    11  2015-01-23 00:00:00.000 gangabhavani
1468    9   2015-01-23 00:00:00.000 admin

output needed = 

KeywordId,    oldPosition      newposition           PsnUpdateBy 
1456                  10                            9                     Ananth
1467                     8                        11                     gangabhavani

im giving two inputs old date and new date . i want to see the difference of position on date wise and userwise.

Upvotes: 1

Views: 81

Answers (3)

munsifali
munsifali

Reputation: 1733

you can use UNION operator

SELECT KeywordId,currentposition,PsnUpdateDate,PsnUpdateBy
FROM seo.Tbl_KeywordPosition
WHERE psnupdatedate = '2015-01-22'
UNION
SELECT KeywordId,currentposition,PsnUpdateDate,PsnUpdateBy
FROM seo.Tbl_KeywordPosition
WHERE psnupdatedate ='2015-01-23';

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

The simplest way to merge these two statements is:

select old.KeywordId, oldposition, newposition, old.PsnUpdateBy 
from 
  (select KeywordId,currentposition as oldposition, PsnUpdateBy 
   from seo.Tbl_KeywordPosition 
   where psnupdatedate = '2015-01-22' ) as old
inner join
 (select KeywordId, currentposition as newposition, PsnUpdateBy 
  from seo.Tbl_KeywordPosition 
  where psnupdatedate = '2015-01-23' ) as new
on old.KeywordId = new.KeywordId

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93754

You don't need two select statement try using Conditional Aggregate

SELECT KeywordId,
       Max(CASE
             WHEN PsnUpdateDate = '2015-01-22' THEN currentposition
           END) Old_Position,
       Max(CASE
             WHEN PsnUpdateDate = '2015-01-23' THEN currentposition
           END) New_Position,
       PsnUpdateBy
FROM   seo.Tbl_KeywordPosition
WHERE  psnupdatedate IN( '2015-01-22', '2015-01-23' )
GROUP  BY KeywordId,
          PsnUpdateBy

Upvotes: 1

Related Questions