CODEWITHSUNDEEP
sqlsql-serversql-server-2008
krishna mohan
krishna mohan

Reputation: 267

Incorrect results in SQL statement while using case statement

I'm comparing two days ranks. If old day rank > new rank then increased, else decreased, but I'm getting decreased in both the cases

 SELECT 
    USERNAME 
    , SUM(IMPROVED) IMPROVED
    , SUM(DECREASED) DECREASED
     , SUM(NoChange) NoChange
    , SUM(TimeLineCOMPLETED) TimeLineCOMPLETED
      , SUM(TLNotCompleted) TLNotCompleted
FROM 
(SELECT (k.keyword)
    ,p.projectname
    ,pa.username
    ,CASE 

        WHEN a.currentposition > b.currentposition
            THEN 1
        ELSE 0
        END IMPROVED
    ,CASE 
        WHEN a.currentposition < b.currentposition
            THEN 1
        ELSE 0
        END  DECREASED
           ,CASE 
        WHEN a.currentposition = b.currentposition
            THEN 1
        ELSE 0
        END NoChange
    ,CASE 
        WHEN pa.KeywordStatus = 'Stopped'
            THEN 1
        ELSE 0
        END TimeLineCOMPLETED
         ,CASE 
        WHEN pa.KeywordStatus = 'InProgress'
            THEN 1
        ELSE 0
        END TLNotCompleted
    ,a.currentposition AS oldposition
    ,b.currentposition AS newposition
    ,pa.KeywordStatus AS TimeLineStatus
    ,k.targetdate
    ,k.positionExp
FROM seo.tbl_keywordposition a
JOIN seo.tbl_keywordposition b ON a.keywordid = b.keywordid
    AND a.psnupdatedate ='10/5/2015' AND b.psnupdatedate ='10/10/2015' LEFT JOIN tbl_keywords k ON k.keywordid = b.keywordid AND a.keywordid = b.keywordid 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 = 'october_project' AND a.psnupdatedate ='10/5/2015' AND b.psnupdatedate ='10/10/2015'  and pa.KeywordStatus!='NULL' )INNERQUERY GROUP by USERNAME

Sample data 

 SELECT * FROM seo.Tbl_KeywordPosition where KeywordId in ('1514','1515')
 PositionId ProjectId   KeywordId   CurrentPosition PsnUpdateDate       
    31592   129           1514        10             2015-10-05           
    31593   129           1514        11             2015-10-10         
    31594   129           1515        10             2015-10-05          
    31595   129           1515         9             2015-10-10

Current output

USERNAME    IMPROVED    DECREASED   NoChange    
Ananth           0          2          0

Desired output

USERNAME    IMPROVED    DECREASED   NoChange    
Ananth         1          1            0

Because keywordid 1514 - old rank 10 and new rank 11 , so it should be decreased.

keywordid 1515 - old rank 10 and new rank 9 , so it should be increased

Upvotes: 0

Views: 110

Answers (3)

BalajiShriram
BalajiShriram

Reputation: 1

Also the ON clause of your query "pa.KeywordStatus!='NULL'" will yield bad results and probably you may have to make it pa.KeywordStatus IS NOT NULL

Upvotes: 0

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

Base on sample and result:

SELECT
    A.KeywordId,
    CASE WHEN B.CurrentPosition - A.CurrentPosition > 0 THEN 1 ELSE 0 END AS IMPROVED,
    CASE WHEN B.CurrentPosition - A.CurrentPosition < 0 THEN 1 ELSE 0 END AS DECREASED,
    CASE WHEN B.CurrentPosition - A.CurrentPosition = 0 THEN 1 ELSE 0 END AS NoChange
FROM
(SELECT * FROM seo.Tbl_KeywordPosition
 WHERE KeywordId IN ('1514','1515') AND PsnUpdateDate = '2015-10-05'
)AS A
    INNER JOIN (SELECT * FROM seo.Tbl_KeywordPosition
                WHERE KeywordId IN ('1514','1515') AND PsnUpdateDate = '2015-10-10'
                ) AS B
        ON A.ProjectId = B.ProjectId AND A.KeywordId = B.KeywordId

Upvotes: 0

Jayvee
Jayvee

Reputation: 10873

The problem is that CurrentPosition is varchar it should be cast(a.currentpostion as int) vs cast(b.currentposition as int)

Upvotes: 1

Related Questions