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
Go to Top of Page

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]
Go to Top of Page
   

- Advertisement -