Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Query help Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 10:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:16 PM
Points: 57, Visits: 545
Eugene Elutin (7/3/2013)

like this:

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 converted
FROM


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 = 1
6 - 4 = 2
6 - 3 = 3
6 - 2 = 4
6 - 1 = 5

Now, 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.



Thanks Eugene !! I appreciate your help !!
Post #1470158
Posted Wednesday, July 3, 2013 10:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:16 PM
Points: 57, Visits: 545
elee1969 (7/3/2013)
Eugene Elutin (7/3/2013)

like this:

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 converted
FROM


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 = 1
6 - 4 = 2
6 - 3 = 3
6 - 2 = 4
6 - 1 = 5

Now, 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.



Thanks Eugene !! I appreciate your help !!


Right now 6 - 0 returns 6. What if i want to keep it as 0?
Post #1470164
Posted Wednesday, July 3, 2013 1:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:16 PM
Points: 57, Visits: 545
any help?
Post #1470211
Posted Wednesday, July 3, 2013 2:20 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:57 AM
Points: 4,055, Visits: 9,229
You can use a CASE statement.

SELECT q.Course_Code, CASE WHEN res.[4a] = 0 THEN 0 ELSE 6-res.[4a] END as [4a],
...




Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1470226
Posted Wednesday, July 3, 2013 2:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:16 PM
Points: 57, Visits: 545
Luis Cazares (7/3/2013)
You can use a CASE statement.

SELECT q.Course_Code, CASE WHEN res.[4a] = 0 THEN 0 ELSE 6-res.[4a] END as [4a],
...



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 !!
Post #1470236
Posted Thursday, July 4, 2013 3:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
elee1969 (7/3/2013)
Luis Cazares (7/3/2013)
You can use a CASE statement.

SELECT q.Course_Code, CASE WHEN res.[4a] = 0 THEN 0 ELSE 6-res.[4a] END as [4a],
...



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 !!


or:

SELECT q.Course_Code, 6 - ISNULL(NULLIF(res.[4a],0),6) as [4a],



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1470356
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse