Need help comparing values in the same field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help comparing values in the same field

Author

Topic

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2014-10-29 : 19:24:51

Hello, I have this table that I am trying to compare two different values in the same field basically checking if one is greater than the other based on another field that is used as a date. The objective is we are checking to see if test scores have increased from one year to the next. The test values are stored in table called OT and the date field (which isn't a datetime type field btw) is TA. So im thinking case statements nested in cte's??? this is what I've been working on returns no results???. Thanks


WITH    cte1 ( a, PID, LF, PT)
          AS (     SELECT  PID,  LF, TST.PT, TST.OT
               FROM     TST INNER JOIN STU ON TST.PID = STU.ID
               WHERE    TST.TA LIKE '%12' AND TST.ID = 'CELDT' AND STU.SC = 12

             ),
             
        cte2 ( b, PID, LF, PT)
          AS ( SELECT PID, LF, TST.PT, TST.OT AS b
               FROM     TST INNER JOIN STU ON TST.PID = STU.ID
               WHERE    TST.TA LIKE '%13'
               AND STU.LF = '3'
               AND TST.PT = 0 AND TST.ID = 'CELDT' AND STU.SC = 12

             )
    SELECT  c1.PID, a, b, CASE WHEN b > a THEN 'Yes'
                 ELSE 'No'
            END AS outcome
     FROM cte1 c1 LEFT JOIN cte2 c2 ON c1.PID = c2.PID
     WHERE c1.LF = '3'  AND c1.PT = 0

I want the table to look like:
TST.PID | a | b | Outcome
------------------------
1234 | 2 | 3 | Yes

Cartesian Yak

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2014-10-30 : 00:00:30

THIS IS WHAT I DID...LOOK RIGHT??? ANY FEEDBACK APPRECIATED

WITH    cte1 ( PID, LF, PT, OT)
          AS (     SELECT  PID,  LF, TST.PT, TST.OT
               FROM     TST INNER JOIN STU ON TST.PID = STU.ID
               WHERE    TST.TA LIKE '%12' AND TST.ID = 'CELDT' AND stu.sc = 12

             ),
             
        cte2 ( PID, LF, PT, OT)
          AS ( SELECT PID, LF, TST.PT, TST.OT AS b
               FROM     TST INNER JOIN STU ON TST.PID = STU.ID
               WHERE    TST.TA LIKE '%13'
               AND STU.LF = '3'
               AND TST.PT = 0 AND TST.ID = 'CELDT' AND stu.sc = 12

             )
    SELECT  c1.PID, c1.OT, c2.ot, CASE WHEN c1.OT < c2.OT THEN 'Yes'
                 ELSE 'No'
            END AS outcome
     FROM cte1 c1 LEFT JOIN cte2 c2 ON c1.PID = c2.PID
     WHERE c1.LF = '3'  AND c1.PT = 0

Cartesian Yak

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-31 : 15:24:28

[code]
SELECT
TST.PID,
MAX(CASE WHEN TST.TA LIKE '%12' THEN TST.OT END) AS Prior_Year,
MAX(CASE WHEN TST.TA LIKE '%13' THEN TST.OT END) AS Current_Year,
CASE WHEN MAX(CASE WHEN TST.TA LIKE '%13' THEN TST.OT END) >
MAX(CASE WHEN TST.TA LIKE '%12' THEN TST.OT END)
THEN 'Yes' ELSE 'No' END AS Outcome
FROM TST
INNER JOIN STU ON TST.PID = STU.ID
WHERE
TST.TA LIKE '%1[23]'
AND STU.LF = '3'
AND TST.PT = 0
AND TST.ID = 'CELDT'
AND STU.SC = 12
GROUP BY
TST.PID
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources