Q:
ms sql query problem with a case statement
[strong]This code works:
SELECT passengers.*, passship.passship_name AS name, passship.passship_speed AS speed, passship.passship_size AS cargo, hyperjump.hops AS hops, ROUND((passengers.pass_engers * passengers.pass_price) / (hyperjump.hops * passship.passship_speed), 0) AS ptprofit
FROM passengers INNER JOIN
hyperjump ON passengers.pass_terms = hyperjump.startsystem AND passengers.pass_system = hyperjump.endsystem CROSS JOIN
passship
WHERE (passship.passship_name = 'Rinkratt')
ORDER BY ptprofit DESC, passengers.pass_terms, passengers.pass_term
The problem is that sometimes [/strong][strong]passengers.pass_engers > passship.passship_size
if it is I need to swap the [/strong][strong]passengers.pass_engers with passship.passship_size[/strong]
[strong]
I am trying this and getting errors on the > symbol
SUM(CASE passengers.pass_engers > passship.passship_size THEN ROUND((passship.passship_size * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0) ELSE ROUND((passengers.pass_engers * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0) AS ptprofit)
Can anyone tell me what is wrong with the sum or if there is a different/better way to do this?[/strong][strong][/strong][strong][/strong]
Software/Hardware used:
using MsSQL on windows server
SELECT passengers.*, passship.passship_name AS name, passship.passship_speed AS speed, passship.passship_size AS cargo, hyperjump.hops AS hops, ROUND((passengers.pass_engers * passengers.pass_price) / (hyperjump.hops * passship.passship_speed), 0) AS ptprofit
FROM passengers INNER JOIN
hyperjump ON passengers.pass_terms = hyperjump.startsystem AND passengers.pass_system = hyperjump.endsystem CROSS JOIN
passship
WHERE (passship.passship_name = 'Rinkratt')
ORDER BY ptprofit DESC, passengers.pass_terms, passengers.pass_term
The problem is that sometimes [/strong][strong]passengers.pass_engers > passship.passship_size
if it is I need to swap the [/strong][strong]passengers.pass_engers with passship.passship_size[/strong]
[strong]
I am trying this and getting errors on the > symbol
SUM(CASE passengers.pass_engers > passship.passship_size THEN ROUND((passship.passship_size * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0) ELSE ROUND((passengers.pass_engers * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0) AS ptprofit)
Can anyone tell me what is wrong with the sum or if there is a different/better way to do this?[/strong][strong][/strong][strong][/strong]
Software/Hardware used:
using MsSQL on windows server
ASKED:
Oct 10 2009 3:02 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
SUM(CASE
WHEN passengers.pass_engers > passship.passship_size
THEN ROUND((passship.passship_size * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0)
ELSE ROUND((passengers.pass_engers * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0)
END) AS ptprofit
I still get errors trying to use the SUM
This is what I ended up using and it worked:
CASE
WHEN passengers.pass_engers >= passship.passship_size
THEN ROUND((passship.passship_size * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0)
WHEN passengers.pass_engers < passship.passship_size
THEN ROUND((passengers.pass_engers * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0)
END AS ptprofit
I also changed the datatype from int to bigint
