Solved

in sql unable to merge two where conditions

Posted on 2015-01-22
9
48 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 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