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
MyDanesAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Ahhh... In my little test database it is a Numeric field !

That makes it a little more challenging...
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(case when isnumeric(isnull(payment_id,'0')) > 0 then convert(int,payment_id) else 0 end)),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

0
 
MyDanesAuthor Commented:
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.
0
 
Mark WillsTopic AdvisorCommented:
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

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Mark WillsTopic AdvisorCommented:
Taht was assuming that payment_id is the same as check numbers or cheques as I know them :)
0
 
MyDanesAuthor Commented:
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.
0
 
MyDanesAuthor Commented:
OUTSTANDING - thanks Mark

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23512309.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.
0
 
MyDanesAuthor Commented:
UNBELIEVABLE  AAAAAA+++++++

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

From novice to tech pro — start learning today.