** SQL Query, Please Help! - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

** SQL Query, Please Help!

Author

Topic

Vertex21
Starting Member

2 Posts

Posted - 2011-09-20 : 23:15:36

Hi and thank you in advance for all the help! I'm just learning SQL querying and have a slight issue I'm hoping someone can help me with.. basically I'm summing data from a column in my database but when it gets to a cell that is a negative value i'd like to have it multiply by -1 to make it a positive value and then continue summing. following is the code I'm writing:


SELECT tblTicketsSummary.fldCreate AS 'Booking Rep', COUNT(tblTicketsSummary.fldTicketID) AS 'Total Tickets Booked', COUNT(DISTINCT tblTicketsSummary.fldClientID) AS 'Total Clients Per Booking Person', SUM(tblTicketsRow.fldRowTotal) AS 'Total $' FROM tblTicketsSummary INNER JOIN tblTicketsRow ON tblTicketsSummary.fldTicketID = tblTicketsRow.fldTicketID 
WHERE (fldDateVoided IS NULL) AND (NOT(fldDateClosed IS NULL)) AND (NOT(fldCreate is NULL)) GetUserDate('AND','fldDateClosed','') AND tblTicketsRow.fldPS = 'S' OR tblTicketsRow.fldPS = 'P' 
GROUP BY tblTicketsSummary.fldCreate ORDER BY tblTicketsSummary.fldCreate

Now, where it says "SUM(tblTicketsRow.fldRowTotal) AS 'Total $'", I'd like to do something like:
IF the returned field LIKE '%-%' THEN multiply by -1
and i want it to continue adding.

Hope that makes sense to someone! thanks again for the help.. :)

Mike

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 23:31:08

Try:
SUM(CASE WHEN tblTicketsRow.fldRowTotal like ('%-%') then tblTicketsRow.fldRowTotal * -1 else tblTicketsRow.fldRowTotal end) as NewColumn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 23:33:47

isnt it just a matter of SUM(ABS( tblTicketsRow.fldRowTotal))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vertex21
Starting Member

2 Posts

Posted - 2011-09-21 : 15:11:26

awesome! thank you both for the help! I did end up going with visakh16's suggestion since it was short and sweet. The report is now pulling perfectly and I can't thank you enough!

Mike

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-21 : 16:41:22

No problem...yes, I have a habit of trying to exactly replicate what the OP posted rather than the best solution. Vis' solution is definitely more compact :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 22:09:58

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources