Solved

in sql unable to merge two where conditions

Posted on 2015-01-22
9
52 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

861 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