Link to home
Start Free TrialLog in
Avatar of MyDanes
MyDanes

asked on

Totals for footer sql server query

below is my working query thus far.

I am missing a final piece which is a select with special concantonation in front of the current final select to ADD all the CHECK NUMBERS together in otherwords check number 1003, 1002, 1000, and 1001 would show in the result as 0000004006
Also - right now with the current code (working) my bottom line of output is  
000000624168510

so now with the example I gave it would read like this:

0000004006000000624168510
Then if there were 4 checks (as in the example it will appear:

                                       
1EOF 00004                    00000040060624168510

finally - THE TRAILING ZERO ON 00000040060624168510 NEEDS TO MOVE AT THE END OF THE CHECK NUMBERS ADDED TOGETHER THUS

1EOF 00004                    00000040060062416851

I wish I could assign DOUBLE TRIPLE etc points for this but I can't  Thank you so much IN ADVANCE :)
use [stvfin]
select lines
from(
select top 1 '1' as line_seq,'DRS355008765102873' + convert(varchar(10), getdate(), 112) + convert(varchar(10), getdate(), 112) + 'N'  as Lines
from cashmgmt_payment
WHERE record_identity='0000QVSVS7KL'
 
union all
 
select top 100 percent '2',right('0000000000' + cast(payment_id as varchar(10)),10) +
       right('0000000000' + replace(cast(amount as varchar(10)),'.',''),10) + '3558765102873' as result
from CASHMGMT_PAYMENT
where bank_account_id='BOA'
and payment_method_id = 'CHECK'
and amount >0.00
and  record_created > '20080620'
 
union all
 
select '3',right('000000000000000' + (replace(cast(sum(amount) as varchar(15)),'.','') ),15)
from CASHMGMT_PAYMENT
where bank_account_id='BOA'
  and payment_method_id = 'CHECK'
  and amount >0.00
  and record_created > '20080620'
) as c
Avatar of MyDanes
MyDanes

ASKER

by the way This is for a export to Bank of America - the expert (Mark Willis) who helped me with another problem on this project pointed out that it does make a difference for what I am trying to do ;)

Thanks in advance.
Avatar of Mark Wills
Think this should go close... It is really just the footer / last bit.
select lines
from(
select top 1 '1' as line_seq,'DRS355008765102873' + convert(varchar(10), getdate(), 112) + convert(varchar(10), getdate(), 112) + 'N'  as Lines 
from cashmgmt_payment 
WHERE record_identity='0000QVSVS7KL'
 
union all
 
select top 100 percent '2',right('0000000000' + cast(payment_id as varchar(10)),10) + 
       right('0000000000' + replace(cast(amount as varchar(20)),'.',''),10) + '3558765102873' as result
from CASHMGMT_PAYMENT
where bank_account_id='BOA'
and payment_method_id = 'CHECK'
and amount >0.00
and  record_created >'20080105'
order by record_created DESC
 
union all
 
select '3','1EOF '+right('00000'+convert(varchar,count(distinct payment_id)),5)
+'                   '
+right('0000000000'+convert(varchar,sum(payment_id)),10)
+right('0000000000' + (replace(cast(sum(amount) as varchar(20)),'.','') ),1)
+substring(right('0000000000' + (replace(cast(sum(amount) as varchar(20)),'.','') ),10),1,9)
from CASHMGMT_PAYMENT
where bank_account_id='BOA'
  and payment_method_id = 'CHECK'
  and amount >0.00
  and record_created >'20080105'
) as c
order by line_seq

Open in new window

Taht was assuming that payment_id is the same as check numbers or cheques as I know them :)
Avatar of MyDanes

ASKER

You are right payment_id is the check (cheque) number but I am getting this error:

Msg 409, Level 16, State 2, Line 2
The sum or average aggregate operation cannot take a varchar data type as an argument.
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MyDanes

ASKER

OUTSTANDING - thanks Mark

https://www.experts-exchange.com/questions/23512309/Query-Output-and-create-as-a-view.html

that is THE LAST new question for this project which was not encompassed in this issue (which you have resolved) :)

I would LOVE if you could help me with the last piece of the problem

Have a GREAT day.
Avatar of MyDanes

ASKER

UNBELIEVABLE  AAAAAA+++++++

would assign 10k points if I could to this - it was TOUGH :)