[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Query Output and create as a view

Posted on 2008-06-24
2
Medium Priority
?
270 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
[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
2 Comments
 
LVL 39

Accepted Solution

by:
appari earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

650 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