SQLServerCentral.com / T-SQL (SS2K8) / SQL Server 2008 / Query help / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 08:33:42 GMT20RE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspx[quote][b]elee1969 (7/3/2013)[/b][hr][quote][b]Luis Cazares (7/3/2013)[/b][hr]You can use a CASE statement.[code="sql"]SELECT q.Course_Code, CASE WHEN res.[4a] = 0 THEN 0 ELSE 6-res.[4a] END as [4a],...[/code][/quote]ah thanks luis. :-) i knew it would be the CASE statement but didn't know how i would work it into the Select section. Thanks again !![/quote]or:[code="sql"]SELECT q.Course_Code, 6 - ISNULL(NULLIF(res.[4a],0),6) as [4a],[/code]Thu, 04 Jul 2013 03:22:05 GMTEugene ElutinRE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspx[quote][b]Luis Cazares (7/3/2013)[/b][hr]You can use a CASE statement.[code="sql"]SELECT q.Course_Code, CASE WHEN res.[4a] = 0 THEN 0 ELSE 6-res.[4a] END as [4a],...[/code][/quote]ah thanks luis. :-) i knew it would be the CASE statement but didn't know how i would work it into the Select section. Thanks again !!Wed, 03 Jul 2013 14:44:18 GMTelee1969RE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspxYou can use a CASE statement.[code="sql"]SELECT q.Course_Code, CASE WHEN res.[4a] = 0 THEN 0 ELSE 6-res.[4a] END as [4a],...[/code]Wed, 03 Jul 2013 14:20:34 GMTLuis CazaresRE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspxany help?Wed, 03 Jul 2013 13:45:41 GMTelee1969RE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspx[quote][b]elee1969 (7/3/2013)[/b][hr][quote][b]Eugene Elutin (7/3/2013)[/b][hr][quote]like this:[quote]SELECT q.Course_Code, 6-res.[4a] as [4a],6-res.[4b] as [4b], 6-res.[4c] as [4c], 6-res.[5a] as ,res.[5b],res.[5c],res.[6a],res.[6b],res.[6c],res.[7a],res.[7b],res.[7c],res.[7d],res.[7e] as convertedFROM[/quote][/quote]The above willnot compile as has quite few errors. You should be more accurate.Basically, in order to convert existing returned values as you requested, these values hould be subtrected out of 6, as: 6 - 5 = 16 - 4 = 26 - 3 = 36 - 2 = 46 - 1 = 5Now, if you use the expression when retunring columns, they will need to be given aliases, therefore: 6-res.[4a] as [4a],6-res.[4b] as [4b],...etc. [/quote]Thanks Eugene !! I appreciate your help !![/quote]Right now 6 - 0 returns 6. What if i want to keep it as 0?Wed, 03 Jul 2013 10:53:51 GMTelee1969RE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspx[quote][b]Eugene Elutin (7/3/2013)[/b][hr][quote]like this:[quote]SELECT q.Course_Code, 6-res.[4a] as [4a],6-res.[4b] as [4b], 6-res.[4c] as [4c], 6-res.[5a] as ,res.[5b],res.[5c],res.[6a],res.[6b],res.[6c],res.[7a],res.[7b],res.[7c],res.[7d],res.[7e] as convertedFROM[/quote][/quote]The above willnot compile as has quite few errors. You should be more accurate.Basically, in order to convert existing returned values as you requested, these values hould be subtrected out of 6, as: 6 - 5 = 16 - 4 = 26 - 3 = 36 - 2 = 46 - 1 = 5Now, if you use the expression when retunring columns, they will need to be given aliases, therefore: 6-res.[4a] as [4a],6-res.[4b] as [4b],...etc. [/quote]Thanks Eugene !! I appreciate your help !!Wed, 03 Jul 2013 10:33:38 GMTelee1969RE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspx[quote]like this:[quote]SELECT q.Course_Code, 6-res.[4a] as [4a],6-res.[4b] as [4b], 6-res.[4c] as [4c], 6-res.[5a] as ,res.[5b],res.[5c],res.[6a],res.[6b],res.[6c],res.[7a],res.[7b],res.[7c],res.[7d],res.[7e] as convertedFROM[/quote][/quote]The above willnot compile as has quite few errors. You should be more accurate.Basically, in order to convert existing returned values as you requested, these values hould be subtrected out of 6, as: 6 - 5 = 16 - 4 = 26 - 3 = 36 - 2 = 46 - 1 = 5Now, if you use the expression when retunring columns, they will need to be given aliases, therefore: 6-res.[4a] as [4a],6-res.[4b] as [4b],...etc. Wed, 03 Jul 2013 10:09:49 GMTEugene ElutinRE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspx[quote][b]Eugene Elutin (7/3/2013)[/b][hr]just replace first line of your SELECT query to:SELECT q.Course_Code, 6-res.[4a] as [4a], 6-res.[4b] as [4b], 6-res.[4c] as [4c], 6-res.[5a] as [5a] ... etc.[/quote]like this:[quote]SELECT q.Course_Code, 6-res.[4a] as [4a],6-res.[4b] as [4b], 6-res.[4c] as [4c], 6-res.[5a] as ,res.[5b],res.[5c],res.[6a],res.[6b],res.[6c],res.[7a],res.[7b],res.[7c],res.[7d],res.[7e] as convertedFROM[/quote]Wed, 03 Jul 2013 09:57:00 GMTelee1969RE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspxjust replace first line of your SELECT query to:SELECT q.Course_Code, 6-res.[4a] as [4a], 6-res.[4b] as [4b], 6-res.[4c] as [4c], 6-res.[5a] as [5a] ... etc.Wed, 03 Jul 2013 09:34:00 GMTEugene ElutinRE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspx[quote][b]Eugene Elutin (7/3/2013)[/b][hr]6 - X formula will work for you:[code="sql"]SELECT v, 6-v as converted_vFROM (VALUES (1),(2),(3),(4),(5)) v(v) [/code][/quote]Eugene - not sure how i would add this to the query. Can you elaborate?Wed, 03 Jul 2013 09:28:44 GMTelee1969RE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspx6 - X formula will work for you:[code="sql"]SELECT v, 6-v as converted_vFROM (VALUES (1),(2),(3),(4),(5)) v(v) [/code]Wed, 03 Jul 2013 09:26:00 GMTEugene ElutinRE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspx[quote][b]Eugene Elutin (7/3/2013)[/b][hr][quote][b]elee1969 (7/3/2013)[/b][hr]Hello - I was handed a script to modify. I am an accidental DBA. I am confused on how to modify a query to return certain results. For example when the script returns a 5 i want it to display a 1, when it returns a 4 i want to display a 2 etc.. Any help would be great.Thanks!![/quote]In order to help you we need a bit more details from you. What dio you mean by "script return"? Is it just a query script or a stored procedure?Does it return recordset and it's a value in one of its columns, or it is atored proc return value or output parameter?[/quote]It is a query that pulls survey results. When the script pulls a survey result we want the 5's to display as 1's in the result. We don't want to change it in the tables. We just want the results to display as a different number.[code="sql"]SELECT q.Course_Code, res.[4a],res.[4b],res.[4c],res.[5a],res.[5b],res.[5c],res.[6a],res.[6b],res.[6c],res.[7a],res.[7b],res.[7c],res.[7d],res.[7e]FROMSELECTSUBSTRING(section_master.crs_cde,1,2)+' '+rtrim(SUBSTRING(section_master.crs_cde,6,5))+' '+RTRIM(substring(section_master.crs_cde,11,3))+' - '+SECTION_MASTER.CRS_TITLE as course_title,(rtrim(cast(SECTION_MASTER.REQUEST_NUM as CHAR))+'-'+rtrim(CAST((ROW_NUMBER() OVER (PARTITION BY section_master.REQUEST_NUM ORDER BY faculty_load_table.LEAD_INSTRCTR_FLG desc, faculty_load_table.INSTRCTR_ID_NUM asc)) as CHAR))) as Course_Code,faculty_load_table.INSTRCTR_ID_NUM as fac_idFROM NAME_MASTER, SECTION_MASTER,FACULTY_LOAD_TABLEWHERE FACULTY_LOAD_TABLE.INSTRCTR_ID_NUM = NAME_MASTER.ID_NUM AND SECTION_MASTER.YR_CDE = FACULTY_LOAD_TABLE.YR_CDE AND SECTION_MASTER.TRM_CDE = FACULTY_LOAD_TABLE.TRM_CDE AND SECTION_MASTER.CRS_CDE = FACULTY_LOAD_TABLE.CRS_CDE AND FACULTY_LOAD_TABLE.INSTRCTR_ID_NUM IN (SELECT ID_NUM FROM EMPL_MAST)AND SECTION_MASTER.YR_CDE = 2007 AND SECTION_MASTER.TRM_CDE = 'Q1'AND SECTION_MASTER.INSTITUT_DIV_CDE in ('AE')) q, mse_ccsd_survey_results resWHERE res.id_num = q.fac_idAND res.REQUEST_NUM = SUBSTRING(q.Course_Code, 1,LEN(q.Course_Code)-2)[/code]this returns something like this:Course_Code 4a 4b 4c 5a 5b 5c 6a 6b 6c 7a 7b 7c 7d 7e00001 5 4 4 4 3 4 5 5 5 5 4 4 3 4so the basically all the 5's we want them to display as 1's in the results, 4 displays as 2, 3 stays the same, 2 display as 4 and 1 display as 5Wed, 03 Jul 2013 09:22:30 GMTelee1969RE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspx[quote][b]Steve Jones - SSC Editor (7/3/2013)[/b][hr]In addition to Eugene's notes, you can use a CASE statement to change things around.[code="sql"]caseWhen x = 5 then 1when x = 4 then 2end[/code][/quote]If OP only interested in converting of the above two values, he can use just this:6 - X ;-)Wed, 03 Jul 2013 09:18:46 GMTEugene ElutinRE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspxIn addition to Eugene's notes, you can use a CASE statement to change things around.[code="sql"]caseWhen x = 5 then 1when x = 4 then 2end[/code]Wed, 03 Jul 2013 09:16:08 GMTSteve Jones - SSC EditorRE: Query helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspx[quote][b]elee1969 (7/3/2013)[/b][hr]Hello - I was handed a script to modify. I am an accidental DBA. I am confused on how to modify a query to return certain results. For example when the script returns a 5 i want it to display a 1, when it returns a 4 i want to display a 2 etc.. Any help would be great.Thanks!![/quote]In order to help you we need a bit more details from you. What dio you mean by "script return"? Is it just a query script or a stored procedure?Does it return recordset and it's a value in one of its columns, or it is atored proc return value or output parameter?Wed, 03 Jul 2013 09:14:24 GMTEugene ElutinQuery helphttp://www.sqlservercentral.com/Forums/Topic1470106-392-1.aspxHello - I was handed a script to modify. I am an accidental DBA. I am confused on how to modify a query to return certain results. For example when the script returns a 5 i want it to display a 1, when it returns a 4 i want to display a 2 etc.. Any help would be great.Thanks!!Wed, 03 Jul 2013 09:10:42 GMTelee1969