anyone know how to do this sum?

[Newbi1]

I am trying to do a sum on amount (t_amnt) but I need to know whether it is a debit or credit first. it is stored as a binary field, however I was able to get the value of debit = 0x01 and credit = 0x02.

Anyone know how I can write this to process correctly?


sum(
case
when t_dbcr = '0x01' then t_amnt
when t_dbcr = '0x02' then -t_amnt
end)

Much appreciation

Oh, here is the whole query:

SELECT t_leac, sum(
case
when t_dbcr = '0x01' then t_amnt
when t_dbcr = '0x02' then -t_amnt
end),
t_fyer, t_fprd
from [baandb].[dbo].[ttfgld410100]
where t_fyer >= 2005 and t_fprd = 1 and t_dbcr = 0x01
group by t_leac, t_fyer, t_fprd
order by t_fyer, t_fprd, t_leac

 

[vongrunt]

Any special reason why credit card amount goes negative and debit card positive?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[Newbi1]

Well, it is not a credit card it is a credit transaction. This is going into an accounting function so debits - credits = balance. When they designed this DB, they put all the transactions in as positive numbers. The dbcr is the only thing to distinguish if they should be added or subtracted.

 

[vongrunt]

Ah... OK.

What are columns t_fyer, t_fprd, t_leac?

It's hard to write code to process correctly without knowing a little bit more about details. I guess you want to sum debits/credit for each account or something but that's just... another guess.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[Newbi1]

t_fyer = fiscal year, t_fprd= fiscal period, t_leac = gl account number

t_amnt = amount and dbcr is whether ir is a debit or credit.

I want to sum the positive amnt if it is a debit and sum the negative amount if it is a credit.

 

[vongrunt]

Sum for each account number separately? If true, then simply remove t_fyer and t_fprd from both SELECT and GROUP BY lists.

And remove unnecessary stuff from WHERE clause. For example, and t_dbcr = 0x01 excludes everything except debits (and causes results you don't want).



------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[Newbi1]

The user will select the period and year they want this to run for. I need a period and YTD total. This is not affecting the group by though. and it still does not account for sumimg the debit and credits in the same account. It will add when I want it to subtract unless I can do a case statement. I do not want to create 2 separate tables as this will double the length of the query. I am running about 8 million records through the system, so duping the query is not a timely option.

 

[vongrunt]

Have you tried to correct WHERE clause as suggested?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[Newbi1]

I had dropped the and t_dbcr = 0x01 from the where clause as suggested.

It gives me nulls for the sum. So how do I again get the sum to work via the case statement?

If I cannot reverse the signs on the credits, I will not get the rsults I desire.

SELECT t_leac, sum(
case
when t_dbcr = '0x01' then t_amnt
when t_dbcr = '0x02' then -t_amnt
end),
t_fyer, t_fprd
from [baandb].[dbo].[ttfgld410100]
where t_fyer >= 2005 and t_fprd = 1
group by t_leac, t_fyer, t_fprd
order by t_fyer, t_fprd, t_leac

If I need the 1st period of the fiscal year 2005, that would be my query, but the case does not work.

 

[vongrunt]

Let's try a simple test

select count(*)
from [baandb].[dbo].[ttfgld410100]
where t_fyer >= 2005 and t_fprd = 1 
	and t_dbcr in ('0x01', '0x02')

Returns 0, right?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[Newbi1]

yes, it did return 0

 

[vongrunt]

OK then... add and t_dbcr in ('0x01', '0x02') in your last query and this should eliminate all rows with NULL sums.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[Newbi1]

your right, It eliminates the rows with null sums and the rest of the rows with them. 2 million records and no balance. (Wish i could teach this trick during tax time).

Sorry being a smartA$$ but I just needed the original problem solved.

 

[vongrunt]

Somehow I think we are circling a lot...

You said t_dbcr is binary? Then comparison with strings like '0x01' won't work:

select cast (char(1) as binary(1)) 
select case when '0x01' = cast(char(1) as binary(1)) then 'Yep' else 'Nope' end

But conversion to int works:

select case when 1 = cast(char(1) as binary(1)) then 'Yep' else 'Nope' end

So assuming that t_dbcr is binary(1), simply use 1/2 instead of '0x01'/'0x02'.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[Pattycake245]

Do you want to determine the balance on each t_leac?

How about this:

select t_leac, debit-credit as balance, t_fyer, t_fprd
from
(SELECT t_leac, 
 sum(case when t_dbcr = '0x01' then t_amnt end) debit,
 sum(case when t_dbcr = '0x02' then t_amnt end) credit,
 t_fyer, t_fprd
 from [baandb].[dbo].[ttfgld410100]
where t_fyer >= 2005 and t_fprd = 1 and t_dbcr = 0x01
group by t_leac, t_fyer, t_fprd) as results
order by t_fyer, t_fprd, t_leac

Tim

 

[vongrunt]

Idea looks good, with two previously explained things:

- '0x01' won't compare against binary - use int instead (1 or 0x01)
- t_dbcr = 0x01 filters only debits within fiscal period.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba