MyDanes
asked on
Totals for footer sql server query
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='0000QVSVS 7KL';
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
SET NOCOUNT ON;
GO
select 'DRS355008765102873' + convert(varchar(10), getdate(), 112), convert(varchar(10), getdate(), 112) + 'N' from cashmgmt_payment WHERE record_identity='0000QVSVS
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
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='0000QVSVS 7KL';
Is this what you are trying to do ?
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='0000QVSVS
Is this what you are trying to do ?
ASKER
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.
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.
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;
Fixed version should look like this..
SELECT right('0000000000' + replace(cast(sum(amount) as varchar(10)),'.',''),10) from CASHMGMT_PAYMENT;
ASKER
now I get :
Msg 8115, Level 16, State 5, Line 3
Arithmetic overflow error converting numeric to data type varchar.
Thanks in advance.
Msg 8115, Level 16, State 5, Line 3
Arithmetic overflow error converting numeric to data type varchar.
Thanks in advance.
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)
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)
ASKER
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
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
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)
right('0000000000' + left(cast(sum(amount * 100) as varchar(20)),(charindex('.
That should give you 0000065038 ? (Moving the decimal point has already been handled by the * 100)
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
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
e.g. lines 20 and 21
select '3',right('000000000000000
from CASHMGMT_PAYMENT
ASKER
Mark,
Thank you for reply
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 >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.
Thank you for reply
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 >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.
Hi,
You will need to remove the lines begining with 'order by' to avoid that error.
You will need to remove the lines begining with 'order by' to avoid that error.
ASKER
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,'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 >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
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,'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 >5/1/2008
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 >5/1/2008
) as c
You could try using datediff for that..
replace..
and record_created >5/1/2008
with..
datediff(day, '05/01/2008', record_created) > 0
replace..
and record_created >5/1/2008
with..
datediff(day, '05/01/2008', record_created) > 0
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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
if you link the questions, or, post the shortcut here, I will follow it up if I can help that is...
select amount as amt1 from cashmgmt_payment WHERE record_identity='0000QVSVS
select amount as amt2 from cashmgmt_payment WHERE record_identity='0000QVSVV
the you can add thes two amt1 and amt2 to get the value you need