• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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,'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','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 >'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 :)
0
MyDanes
Asked:
MyDanes
1 Solution
 
appariCommented:

>>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 ????
do you mean you want to replace '20080620' with today-1?
then try this


CREATE VIEW yourViewName
AS
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 > convert(varchar(8),getdate()-1,112)
 
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 >convert(varchar(8),getdate()-1,112)
) as c

0
 
MyDanesAuthor Commented:
the above view did not give me the output I need it made ONE column with only ONE line of output.  

I need the output to be to text file somehow.

Thank you in advance.

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now