Avatar of mavmanau
mavmanau
Flag for Australia asked on

ODBC SQL issue - SUM or SUMIF

ODBC SQL Query question

Hi,

We have recently upgraded some of our systems, and have changed our connectivity that some of our pages use to reach into our DB.

I have had to rewrite some of our SQL query's to suit.  We are now using an ODBC connection that talks to an SQL server in php 5.6.
the below query works:

Select dbo.client.cl_number, dbo.debtor.de_type, dbo.debtor.de_listed_date, dbo.de_transaction.tr_posted_date, sum(CASE when dbo.de_transaction.tr_account='16' then (dbo.de_transaction.tr_to_agency+dbo.de_transaction.tr_to_client)*-1 ELSE 0) FROM dbo.de_transaction LEFT JOIN dbo.debtor on dbo.de_transaction.tr_rowid_debtor = dbo.debtor.de_rowid JOIN dbo.client on dbo.client.cl_rowid = dbo.debtor.de_rowid_client where dbo.client.cl_number > '110' and dbo.client.cl_number < '119' group By dbo.debtor.de_listed_date,dbo.client.cl_number,dbo.debtor.de_type,dbo.de_transaction.tr_posted_date

apart from this part:

 sum(CASE when dbo.de_transaction.tr_account='16' then (dbo.de_transaction.tr_to_agency+dbo.de_transaction.tr_to_client)*-1 ELSE 0)

could you please help me?
The basics of it are this:  if de_transaction.tr_account = 16 then I want it to add up de_transaction.tr_to_agency and de_transaction.tr_to_client and then multiply it by -1 otherwise it equals 0.
Microsoft SQL ServerPHPSQL

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
mavmanau

ASKER
Thank you. Can’t believe I missed that!
Mark Wills

A pleasure, and easy enough to do, we all have - at some point in time - even multiple times :)

Many eyes makes light work - and that is what we are here for :)

Cheers,
Mark Wills
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes