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,'DRS3550087651028 73' + convert(varchar(10), getdate(), 112) + convert(varchar(10), getdate(), 112) + 'N' as Lines
from cashmgmt_payment
WHERE record_identity='0000QVSVS 7KL'
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
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,'DRS3550087651028
from cashmgmt_payment
WHERE record_identity='0000QVSVS
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
from CASHMGMT_PAYMENT
where bank_account_id='BOA'
and payment_method_id = 'CHECK'
and amount >0.00
and record_created > '20080620'
) as c
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
Taht was assuming that payment_id is the same as check numbers or cheques as I know them :)
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
UNBELIEVABLE AAAAAA+++++++
would assign 10k points if I could to this - it was TOUGH :)
would assign 10k points if I could to this - it was TOUGH :)
ASKER
Thanks in advance.