Solved

in sql unable to merge two where conditions

Posted on 2015-01-22
9
57 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

696 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