Solved

Query Output and create as a view

Posted on 2008-06-24
2
254 Views
Last Modified: 2010-03-19
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
Comment
Question by:MyDanes
2 Comments
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 21863687

>>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
 

Author Comment

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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