Solved

in sql unable to merge two where conditions

Posted on 2015-01-22
9
46 Views
Last Modified: 2015-01-26
select  distinct(k.KeyWord),kp.CurrentPosition as previousposition,'Current Position' as CurrentPosition,Pa.username,pa.           KeywordStatus as TimeLineStatus,targetdate,PositionExp from tbl_keywords k
   left join  seo.Tbl_KeywordPosition kp on kp.KeywordId= k.KeywordID  and kp.ProjectId=k.ProjId
   left join tbl_project p  on p.ProjectId=k.ProjId
    left join tbl_projAssignment pa on pa.ProjId=p.ProjectId and pa.KeywordID = k.KeywordID
    where  p.Projectname='Homeocare' and  PsnUpdateDate = '1/22/2015'  and pa.KeywordStatus = 'InProgress'
   
   
    select  distinct(k.KeyWord),kp.CurrentPosition as previousposition,'Current Position' as CurrentPosition,Pa.username,pa.i have two merge these columnsi have two merge these columnsKeywordStatus as TimeLineStatus,targetdate,PositionExp from tbl_keywords k
   left join  seo.Tbl_KeywordPosition kp on kp.KeywordId= k.KeywordID  and kp.ProjectId=k.ProjId
   left join tbl_project p  on p.ProjectId=k.ProjId
    left join tbl_projAssignment pa on pa.ProjId=p.ProjectId and pa.KeywordID = k.KeywordID
    where  p.Projectname='Homeocare' and  PsnUpdateDate = '1/23/2015'  and pa.KeywordStatus = 'InProgress'
0
Comment
Question by:krishna mohan
  • 5
  • 2
  • 2
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40565837
What result do you want?
0
 

Author Comment

by:krishna mohan
ID: 40565840
i want to merge two sql select statement.i want to show 2 column s with different conditions
0
 

Author Comment

by:krishna mohan
ID: 40565842
select  distinct(k.KeyWord),kp.CurrentPosition as previousposition,'Current Position' as CurrentPosition,Pa.username,pa.           KeywordStatus as TimeLineStatus,targetdate,PositionExp from tbl_keywords k
   left join  seo.Tbl_KeywordPosition kp on kp.KeywordId= k.KeywordID  and kp.ProjectId=k.ProjId
   left join tbl_project p  on p.ProjectId=k.ProjId
    left join tbl_projAssignment pa on pa.ProjId=p.ProjectId and pa.KeywordID = k.KeywordID
    where  p.Projectname='Homeocare' and  PsnUpdateDate = '1/22/2015'  and pa.KeywordStatus = 'InProgress'
    union
    select  distinct(k.KeyWord),kp.CurrentPosition,'Current Position' as CurrentPosition,Pa.username,pa.           KeywordStatus as TimeLineStatus,targetdate,PositionExp from tbl_keywords k
   left join  seo.Tbl_KeywordPosition kp on kp.KeywordId= k.KeywordID  and kp.ProjectId=k.ProjId
   left join tbl_project p  on p.ProjectId=k.ProjId
    left join tbl_projAssignment pa on pa.ProjId=p.ProjectId and pa.KeywordID = k.KeywordID
    where  p.Projectname='Homeocare' and  PsnUpdateDate = '1/23/2015'  and pa.KeywordStatus = 'InProgress'
0
 

Author Comment

by:krishna mohan
ID: 40565844
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40565847
I've read what you said. What result do you want?
0
 

Author Comment

by:krishna mohan
ID: 40565870
select * from report
name        month    marks
krishna         dec             8        
krishna         jan             9        

i need

name      decMarks    janMarks
krishna     8                   9



i need query kindly send
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40565906
like this?

select name,sum(case when month='dec' then marks else 0 end) as decmarks
                     ,sum(case when month='jan' then marks else 0 end) as janmarks
from ...
group by name
order by 1

or you could use the PIVOT sql server extension...

however your original sql is confusing since you are left joining but then in effect only using an inner join because of your specific where clause conditions for the optional table data... (e.g. don't you also need

p.Projectname='Homeocare' and  PsnUpdateDate = '1/22/2015'  and pa.KeywordStatus = 'InProgress'
or (p.projectname is null or pa.keywordsratus is null or .... ?)
0
 

Author Comment

by:krishna mohan
ID: 40568054
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  

can u plz help me...thanks in advance..
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 40568122
like this possibly

;with cte as (
select KeywordId,currentposition,PsnUpdateBy,psnupdatedate from  seo.Tbl_KeywordPosition)
select coalesce(n.Keywordid,o.keywordid) as keywordid
      ,o.currentposition as OldPosition
      ,n.position as NewPosition
      ,n.PsnUpdateBy as UpdatedBy
 from cte as N
 full outer join cte as O
   on n.keywordid=o.keywordid
  and n.psnupdatedate>o.psnupdatedate
  
 where (O.psnupdatedate = '2015-01-22' or O.psnupdatedate is null)
   and (N.psnupdatedate='2015-01-23' or n.psnupdate is null) 
order by 1
 

Open in new window


i'm unsure f it will truely give you what your after....

could you state the business problem you are attempting to solve...

(i feel in a real world scenario , you probably want to see the data most relevant to the dates you specify .... not just data for those exact dates....
ie does your system always for rows for each date... (weekends included?)
   what do you want to happen for deleted keywords....
   what do you want to happen for new keywords...
  will the dates cover a range greater than a single day? and if so what shouls be displayed...?)
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Creating Alerts in sql sever 2 25
SqlAdvisor 2016 3 28
Managing Columnstore Indexes 2 17
sql Total query 2 16
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now