I have written three database queries to extract data from yesterday, last week and last month. I need a way to name the output files something like this :
"Daily 2014-05-21", "Weekly 2014-05-19" & "Monthly 2014-05-01" if I run them today ( 2014-05-22 ).
I can specify the output file name from inside the SQL query but then I need to e-mail the files so I thought that naming them from outside the query would be a better idea since I need to use the filename in the scripts to e-mail them.
I can't seem to find a way for the 'date' command to do much except use the current date information but that's not useful.
Am I missing something ?
Does someone have a way to do this ?
Thanks for helping.
Nice - but there's not a specific date I select from a table but a range depending on the report.
Here's an example of the date selection inside the query :
Code:
"select work_group , person , task ,task_status , count(actual_date) "Count"
from actual_date
where upper(work_group) in ('NMCPROV','COCO')
and TRUNC(actual_date) >= TRUNC(sysdate -8) "
I'm not sure how I could make your method work in my script.
Moderator's Comments:
Please use code tags next time for your code and data. Thanks
Thanks for the info. I don't get to script very often and the reference you made to sysdate reminded me of something I did last year to put the date and time into a filename. I found that query and used it to get this :
Code:
COL dt new_val report_date
set termout off
select to_char(sysdate -8, 'YYYY-MM-DD') dt from dual;
set termout on
COL work_group HEA 'Work Group'
COL person HEA 'Person' FORMAT A6
COL task HEA 'Task'
COL task_status HEA 'Status' FORMAT A6
spool Weekly_TecNet_Completion_Report_&report_date..csv
which works like a charm.
Thanks for the info that put me on the right path !
Don
---------- Post updated at 12:25 PM ---------- Previous update was at 12:06 PM ----------
Maybe I spoke too soon !
Naming the output file is one thing but passing that name to the script that e-mails the file is another.
So my question is : How can I pass or replicate that filename created inside the query to a filename created by Solaris ?
I seem to have found a way. A little clunky but it works.
To create the name of the output file I do this inside the query :
Code:
COL dt new_val report_date
set termout off
select to_char(sysdate -8, 'YYYY-MM-DD') dt from dual;
set termout on
...
spool Weekly_TecNet_Completion_Report_&report_date..csv
The first script I run clears out the output directory then runs the query.
The second one e-mails the output file. To do that I need the name of the output file ( which was my original problem ). To get that name I use :
Below are my custom period start and end dates based on a calender, these dates are placed in a file, for each period i need to split into three weeks for each period row, example is given below.
Could you please help out to achieve solution through shell script..
File content:
... (2 Replies)
Hi all,
Need an urgent help on the below scenario.
script:
awk -F","
'BEGIN { #some variable assignment}
{ #some calculation and put values in array}
END {
year=#getting it from array and assume this will be 2014
month=#getting it from array and this will be 05
date=#... (7 Replies)
Dear all,
I would like to know how to move the downloaded files in differenent directories according to the name of the file?
i.e.
P10120111201_122013M.jpg
P10120120101_122013M.jpg
P10120120201_122013M.jpg
The first 4 charactors "P101" is the station name, 4-8 means year "2012",... (5 Replies)
Hi All,
what i want to do in perl is i should give the date at run time .Suppose date given is 23/12/2011(mm/dd/yyyy) the perl script shold find week start date, week end date, previous week start date,end date,next week start date, end date. In this case
week start date will be-:12/19/2011... (2 Replies)
Hi all,
I've used various scripts in the past to work out the date last week from the current date, however I now have a need to work out the date 1 week from a given date.
So for example, if I have a date of the 23rd July 2010, I would like a script that can work out that one week back was... (4 Replies)
Hi,
How to get last week date ?
Normally i use this command :
But somehow this not working in SunOS
And i try use this :
But in SunOS only working only get last 6 days
Cheers, (1 Reply)
05-22-2014
