Question on CASE statement

Question

Question on CASE statement

melinda.savoy

Mr or Mrs. 500

Points: 506
More actions
July 18, 2017 at 6:03 am

#331874

In the pic above of the SQL results above I am needing to alter the DisciplinaryActionReasonCode value for any seq value that is greater than 1 to the matching DisciplinaryIncidentNumber record's DisciplinaryActionReasonCode. Meaning for example that the DisciplinaryActionReasonCode for the record with a seq=2 value needs to be altered to 21 from 41. I am having problems trying to come up with the case statement logic to make that happen since the records are in the same result set.
What is tried in my SQL code is:
SELECT SchoolYear, Submission, DistrictID, seq, PERMNUM, DisciplinaryIncidentNumber, CASE WHEN seq = 2 THEN CASE WHEN seq = 1 THEN DisciplinaryActionReasonCode ELSE DisciplinaryActionReasonCode END END as DisciplinaryReasonCode FROM CTE1 ORDER BY PERMNUM asc, DisciplinaryIncidentNumber asc, DisciplinaryActionReasonCode asc;
Here are the results I get then:
Any help/direction would be appreciated.
Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

melinda.savoy - Tuesday, July 18, 2017 6:03 AM
In the pic above of the SQL results above I am needing to alter the DisciplinaryActionReasonCode value for any seq value that is greater than 1 to the matching DisciplinaryIncidentNumber record's DisciplinaryActionReasonCode. Meaning for example that the DisciplinaryActionReasonCode for the record with a seq=2 value needs to be altered to 21 from 41. I am having problems trying to come up with the case statement logic to make that happen since the records are in the same result set.
What is tried in my SQL code is:
SELECT SchoolYear, Submission, DistrictID, seq, PERMNUM, DisciplinaryIncidentNumber, CASE WHEN seq = 2 THEN CASE WHEN seq = 1 THEN DisciplinaryActionReasonCode ELSE DisciplinaryActionReasonCode END END as DisciplinaryReasonCode FROM CTE1 ORDER BY PERMNUM asc, DisciplinaryIncidentNumber asc, DisciplinaryActionReasonCode asc;
Here are the results I get then:
Any help/direction would be appreciated.
Thank you.

A CASE expression won't help as you would need the values to be on the same row. A better option would be to use FIRST_VALUE()
WITH CTE1(SchoolYear, Submission, DistrictID, seq, PERMNUM, DisciplinaryIncidentNumber, DisciplinaryActionReasonCode, DateofDisciplinaryAction) AS( SELECT 2016, 3, 220905, 1, 1042003, '005510', 21, '20160928' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '013910', 21, '20161025' UNION ALL SELECT 2016, 3, 220905, 2, 1042003, '019460', 41, '20161109' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '019460', 21, '20161109' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '023166', 21, '20161206' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '154028', 21, '20170202' )

SELECT SchoolYear, Submission, DistrictID, seq, PERMNUM, DisciplinaryIncidentNumber, FIRST_VALUE(DisciplinaryActionReasonCode) OVER(PARTITION BY SchoolYear, Submission, DistrictID, PERMNUM ORDER BY seq) FROM CTE1 ORDER BY PERMNUM asc, DisciplinaryIncidentNumber asc, DisciplinaryActionReasonCode asc;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

melinda.savoy Mr or Mrs. 500 Points: 506 More actions · Answer 2

Luis Cazares - Tuesday, July 18, 2017 6:21 AM
melinda.savoy - Tuesday, July 18, 2017 6:03 AM
In the pic above of the SQL results above I am needing to alter the DisciplinaryActionReasonCode value for any seq value that is greater than 1 to the matching DisciplinaryIncidentNumber record's DisciplinaryActionReasonCode. Meaning for example that the DisciplinaryActionReasonCode for the record with a seq=2 value needs to be altered to 21 from 41. I am having problems trying to come up with the case statement logic to make that happen since the records are in the same result set.
What is tried in my SQL code is:
SELECT SchoolYear, Submission, DistrictID, seq, PERMNUM, DisciplinaryIncidentNumber, CASE WHEN seq = 2 THEN CASE WHEN seq = 1 THEN DisciplinaryActionReasonCode ELSE DisciplinaryActionReasonCode END END as DisciplinaryReasonCode FROM CTE1 ORDER BY PERMNUM asc, DisciplinaryIncidentNumber asc, DisciplinaryActionReasonCode asc;
Here are the results I get then:
Any help/direction would be appreciated.
Thank you.
A CASE expression won't help as you would need the values to be on the same row. A better option would be to use FIRST_VALUE()
WITH CTE1(SchoolYear, Submission, DistrictID, seq, PERMNUM, DisciplinaryIncidentNumber, DisciplinaryActionReasonCode, DateofDisciplinaryAction) AS( SELECT 2016, 3, 220905, 1, 1042003, '005510', 21, '20160928' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '013910', 21, '20161025' UNION ALL SELECT 2016, 3, 220905, 2, 1042003, '019460', 41, '20161109' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '019460', 21, '20161109' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '023166', 21, '20161206' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '154028', 21, '20170202' )
SELECT SchoolYear, Submission, DistrictID, seq, PERMNUM, DisciplinaryIncidentNumber, FIRST_VALUE(DisciplinaryActionReasonCode) OVER(PARTITION BY SchoolYear, Submission, DistrictID, PERMNUM ORDER BY seq) FROM CTE1 ORDER BY PERMNUM asc, DisciplinaryIncidentNumber asc, DisciplinaryActionReasonCode asc;

Thank you Luis. I will try it. I appreciate your time in replying.

melinda.savoy Mr or Mrs. 500 Points: 506 More actions · Answer 3

melinda.savoy - Tuesday, July 18, 2017 6:25 AM
Luis Cazares - Tuesday, July 18, 2017 6:21 AM
melinda.savoy - Tuesday, July 18, 2017 6:03 AM
In the pic above of the SQL results above I am needing to alter the DisciplinaryActionReasonCode value for any seq value that is greater than 1 to the matching DisciplinaryIncidentNumber record's DisciplinaryActionReasonCode. Meaning for example that the DisciplinaryActionReasonCode for the record with a seq=2 value needs to be altered to 21 from 41. I am having problems trying to come up with the case statement logic to make that happen since the records are in the same result set.
What is tried in my SQL code is:
SELECT SchoolYear, Submission, DistrictID, seq, PERMNUM, DisciplinaryIncidentNumber, CASE WHEN seq = 2 THEN CASE WHEN seq = 1 THEN DisciplinaryActionReasonCode ELSE DisciplinaryActionReasonCode END END as DisciplinaryReasonCode FROM CTE1 ORDER BY PERMNUM asc, DisciplinaryIncidentNumber asc, DisciplinaryActionReasonCode asc;
Here are the results I get then:
Any help/direction would be appreciated.
Thank you.
A CASE expression won't help as you would need the values to be on the same row. A better option would be to use FIRST_VALUE()
WITH CTE1(SchoolYear, Submission, DistrictID, seq, PERMNUM, DisciplinaryIncidentNumber, DisciplinaryActionReasonCode, DateofDisciplinaryAction) AS( SELECT 2016, 3, 220905, 1, 1042003, '005510', 21, '20160928' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '013910', 21, '20161025' UNION ALL SELECT 2016, 3, 220905, 2, 1042003, '019460', 41, '20161109' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '019460', 21, '20161109' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '023166', 21, '20161206' UNION ALL SELECT 2016, 3, 220905, 1, 1042003, '154028', 21, '20170202' )
SELECT SchoolYear, Submission, DistrictID, seq, PERMNUM, DisciplinaryIncidentNumber, FIRST_VALUE(DisciplinaryActionReasonCode) OVER(PARTITION BY SchoolYear, Submission, DistrictID, PERMNUM ORDER BY seq) FROM CTE1 ORDER BY PERMNUM asc, DisciplinaryIncidentNumber asc, DisciplinaryActionReasonCode asc;
Thank you Luis. I will try it. I appreciate your time in replying.

Thank you, Luis!! That got it. Again I appreciate your time and help.

Thom A SSC Guru Points: 99126 More actions · Answer 4

In your example data, seq 2 already has a value of 41. Seq 1 has a value of 21. I've guessed that, perhaps, you always want the highest ReasonCode. Also, please supply DDL and DLM when posting, so we don't have to create sample data, or create SQL without testing.

A simple MAX value should work for you:
CREATE TABLE #Discipline (SchoolYear int, Submission int, DistrictID int, seq int, permnum int, IncidentNumber varchar(6), ReasonCode int, ActionDate date); GO

INSERT INTO #Discipline VALUES (2016,3,220905,1,1042003,'005510', 21,'20160928'), (2016,3,220905,1,1042003,'013910', 21,'20161025'), (2016,3,220905,2,1042003,'019460', 41,'20161109'), (2016,3,220905,1,1042003,'019460', 21,'20161109'), (2016,3,220905,1,1042003,'023166', 21,'20161206'), (2016,3,220905,1,1042003,'154028', 21,'20170202'); GO SELECT D.SchoolYear, D.Submission, D.DistrictID, D.seq, D.IncidentNumber, MAX(ReasonCode) OVER (PARTITION BY D.SchoolYear,D.Submission,D.DistrictID, D.IncidentNumber) AS ReasonCode FROM #Discipline D;

GO DROP TABLE #Discipline; GO

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

melinda.savoy - Tuesday, July 18, 2017 6:30 AM
Thank you, Luis!! That got it. Again I appreciate your time and help.

You're welcome. Be sure to understand it, as you'll be the one in charge of supporting it. If you understand it correctly, you might be able to place it in the best location at the query (which is not just what you posted).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

melinda.savoy Mr or Mrs. 500 Points: 506 More actions · Answer 6

Luis Cazares - Tuesday, July 18, 2017 6:40 AM
melinda.savoy - Tuesday, July 18, 2017 6:30 AM
Thank you, Luis!! That got it. Again I appreciate your time and help.
You're welcome. Be sure to understand it, as you'll be the one in charge of supporting it. If you understand it correctly, you might be able to place it in the best location at the query (which is not just what you posted).

Understood. I'm trying to go through what it is doing now. I've not used the FIRST_VALUE function before and I don't know much about the PARTITION function either. So it does appear a little confusing to me. This code will be part of a larger query statement so I'll definitely need to understand it to put it where it will go correctly. Thanks.