Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Totals for footer sql server query

Posted on 2008-06-19
19
Medium Priority
?
410 Views
Last Modified: 2012-05-05
use [stvfin]
SET NOCOUNT ON;
GO
select 'DRS355008765102873' + convert(varchar(10), getdate(), 112), convert(varchar(10), getdate(), 112) + 'N'  from cashmgmt_payment WHERE record_identity='0000QVSVS7KL';
select right('0000000000' + cast(payment_id as varchar(10)),10) +
       right('0000000000' + left(replace(cast(amount as varchar(10)),'.',''),
       len(replace(cast(amount as varchar(10)),'.','')) -1),10) + '3558765102873' as result
from CASHMGMT_PAYMENT
where bank_account_id='BOA'
and payment_method_id = 'CHECK'
and amount >0.00
and  record_created >5/1/2008
order by record_created DESC;

I need another select at the bottom that will the records for amount so if there was for example 10.50 and 12.00 it would show 0000002250
0
Comment
Question by:MyDanes
[X]
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
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 17

Expert Comment

by:contactkarthi
ID: 21822604
simplest option will be you have to select using as keyword and add those later to get the desired value.

select amount as amt1 from cashmgmt_payment WHERE record_identity='0000QVSVS7KL';

select amount as amt2 from cashmgmt_payment WHERE record_identity='0000QVSVVVVVV';

the you can add thes two amt1 and amt2 to get the value you need
0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21822729
The following should sum up the values of the amount field and format them as 10 digits..

        SELECT right('0000000000' + replace(cast(sum(amount) as varchar(10)),'.','')
        from CASHMGMT_PAYMENT

You can add the required where clause to the end, ie..
        WHERE record_identity='0000QVSVS7KL';

Is this what you are trying to do ?

0
 

Author Comment

by:MyDanes
ID: 21823090
influenz:

SELECT right('0000000000' + replace(cast(sum(amount) as varchar(10)),'.','') from CASHMGMT_PAYMENT;

This is looking like what I am wanting but I get an error with invalid syntax near the word from PLEASE help.  
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21823144
Ah, ye, missed the ",10)" from the end!

Fixed version should look like this..

SELECT right('0000000000' + replace(cast(sum(amount) as varchar(10)),'.',''),10) from CASHMGMT_PAYMENT;
0
 

Author Comment

by:MyDanes
ID: 21823216
now I get :

Msg 8115, Level 16, State 5, Line 3
Arithmetic overflow error converting numeric to data type varchar.

Thanks in advance.
0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21823385
Ok, the sum must be breaking the cast.  Try changing varchar(10) to varchar(20)

Failing that, a slightly different approach would be
    right('0000000000' + cast(sum(amount * 100) as varchar(10)),10)
but may lead to the same problem.

If neither of those work, can you check the datatype of the amount field and let me know, it should be something like numeric(10,2)
0
 

Author Comment

by:MyDanes
ID: 21823841
ok using:
SELECT  right('0000000000' + cast(sum(amount * 100) as varchar(20)),10) from CASHMGMT_PAYMENT;
yielded the below but it needs to read
065038.000
0006503800
0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21824049
ok ! This should handle the .000
right('0000000000' +  left(cast(sum(amount * 100) as varchar(20)),(charindex('.', cast(sum(amount * 100) as varchar(20)),0)-1) ),10)
That should give you 0000065038 ?  (Moving the decimal point has already been handled by the * 100)
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21824508
There is the formatting, but that is not all... if you are trying to return something like a formatted extract with header, details, total, then you do need to union the three parts into a cohesive query :



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 >5/1/2008
order by record_created DESC
 
union all
 
select '3',right('0000000000' + (replace(cast(sum(amount) as varchar(10)),'.','') ),10)
from CASHMGMT_PAYMENT
where bank_account_id='BOA'
  and payment_method_id = 'CHECK'
  and amount >0.00
  and record_created >5/1/2008
) as c
order by line_seq

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21824583
Oh, and if you are getting arithmetic over flow, then it is going to have to be bigger than 10 character number in the total (or anywhere really)...

e.g. lines 20 and 21

select '3',right('000000000000000' + (replace(cast(sum(amount) as varchar(15)),'.','') ),15)
from CASHMGMT_PAYMENT
0
 

Author Comment

by:MyDanes
ID: 21847715
Mark,

Thank you for reply

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 >5/1/2008
order by record_created DESC
 
union all
 
select '3',right('0000000000' + (replace(cast(sum(amount) as varchar(10)),'.','') ),10)
from CASHMGMT_PAYMENT
where bank_account_id='BOA'
  and payment_method_id = 'CHECK'
  and amount >0.00
  and record_created >5/1/2008
) as c
order by line_seq

Msg 104, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if the statement contains a UNION operator.
0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21847774
Hi,

You will need to remove the lines begining with 'order by' to avoid that error.
0
 

Author Comment

by:MyDanes
ID: 21847968
WOO HOOOO almost home :0

Now the dates criterea is NOT being utilized

In other words I said > 5/23/2029
it still returned EVERY RECORD

I think it is because the dates in the field show 5/9/2008 12:00:00 AM (for example)


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 >5/1/2008
 
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 >5/1/2008
) as c

0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 21848166
You could try using datediff for that..
replace..
and record_created >5/1/2008
with..
datediff(day, '05/01/2008', record_created) > 0
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21848251
OK,

The Order by should have worked fine (sql 2005) - and will be needed in your results - so keep the last one at the very least...

What type of columns are your date fields (ie record_created) ?

Going to have to encapsulate your dates in single quotes at least... and maybe use the format '20080501' so it can be natively converted to a datetime construct...
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 21848385
Try this...

also made the amount fields 20 digits - you might want to cut them back again...
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('00000000000000000000' + replace(cast(amount as varchar(20)),'.',''),20) + '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',right('00000000000000000000' + (replace(cast(sum(amount) as varchar(20)),'.','') ),20)
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 Closing Comment

by:MyDanes
ID: 31468798
Thank you so much it is perfect I have  another LAST piece but it wasn't part of this question so I will open a new one.  I AM HOPING that you respond to it :)  Thanks as always for the GREAT help I get from EE
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21849140
if you link the questions, or, post the shortcut here, I will follow it up if I can help that is...

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

636 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