• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

in sql unable to merge two where conditions

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
krishna mohan
Asked:
krishna mohan
  • 5
  • 2
  • 2
1 Solution
 
Phillip BurtonCommented:
What result do you want?
0
 
krishna mohanAuthor Commented:
i want to merge two sql select statement.i want to show 2 column s with different conditions
0
 
krishna mohanAuthor Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
krishna mohanAuthor Commented:
0
 
Phillip BurtonCommented:
I've read what you said. What result do you want?
0
 
krishna mohanAuthor Commented:
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
 
LowfatspreadCommented:
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
 
krishna mohanAuthor Commented:
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
 
LowfatspreadCommented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now