Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Totals for footer sql server query

Posted on 2008-06-23
7
235 Views
Last Modified: 2010-04-21
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
0
Comment
Question by:MyDanes
  • 4
  • 3
7 Comments
 

Author Comment

by:MyDanes
ID: 21849240
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21849445
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21849462
Taht was assuming that payment_id is the same as check numbers or cheques as I know them :)
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:MyDanes
ID: 21849957
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 21851637
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
 

Author Comment

by:MyDanes
ID: 21859551
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
 

Author Closing Comment

by:MyDanes
ID: 31469894
UNBELIEVABLE  AAAAAA+++++++

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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question