MyDanes
asked on
Query Output and create as a view
I am finalyzing this project and am ALMOST home - below is what I have thus far and it is functioning EXACTLY as I want thanks to expert (Mark Willis) from EE!!!
Now for the final piece of this puzzle
I need the output to show NO
(98 row(s) affected)
I just want my results ONLY.
Next I need to somehow create a VIEW for this and trigger it to run 'daily'
But now the problem is the 'DATE'
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','1EOF '+right('00000'+convert(va rchar,coun t(distinct payment_id)),5)
+' '
+right('0000000000'+conver t(varchar, sum(case when isnumeric(isnull(payment_i d,'0')) > 0 then convert(int,payment_id) else 0 end)),10)
+right('0000000000' + (replace(cast(sum(amount) as varchar(20)),'.','') ),1)
+substring(right('00000000 00' + (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 >'20080620'
) as c
notice how the date is - how do I force it to each day UPDATE to the current date minus ONE for example or whatever manipulation we may choose ????
Thank you in advance everyone for all the OUTSTANDING - UNBELIEVABLE - PERFECT support :)
Now for the final piece of this puzzle
I need the output to show NO
(98 row(s) affected)
I just want my results ONLY.
Next I need to somehow create a VIEW for this and trigger it to run 'daily'
But now the problem is the 'DATE'
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','1EOF '+right('00000'+convert(va
+' '
+right('0000000000'+conver
+right('0000000000' + (replace(cast(sum(amount) as varchar(20)),'.','') ),1)
+substring(right('00000000
from CASHMGMT_PAYMENT
where bank_account_id='BOA'
and payment_method_id = 'CHECK'
and amount >0.00
and record_created >'20080620'
) as c
notice how the date is - how do I force it to each day UPDATE to the current date minus ONE for example or whatever manipulation we may choose ????
Thank you in advance everyone for all the OUTSTANDING - UNBELIEVABLE - PERFECT support :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I need the output to be to text file somehow.
Thank you in advance.