How to not generate report on weekend using sql

SQL> select decode(to_char(sysdate, 'd'),4,5,count(*)) from retrocharge_requests rr, retrocharge_request_items rri where rr.retrocharge_request_id = rri.retrocharge_request_id and rr.marketplace_id =1 and rr.created_date_utc > sysdate - 1 and rri.request_item_reason_type = 'InstantRefund' and to_char(sysdate,'d') not in (4);

DECODE(TO_CHAR(SYSDATE,'D'),4,5,COUNT(*))

-----------------------------------------

                                        5

SQL> select decode(to_char(sysdate, 'd'),1,6,7,6,count(*)) from retrocharge_requests rr, retrocharge_request_items rri where rr.retrocharge_request_id = rri.retrocharge_request_id and rr.marketplace_id =1 and rr.created_date_utc > sysdate - 1 and rri.request_item_reason_type = 'InstantRefund' and to_char(sysdate,'d') not in (1,7);

DECODE(TO_CHAR(SYSDATE,'D'),1,6,7,6,COUNT(*))

---------------------------------------------

                                          660

SQL> select to_char(sysdate, 'day') from dual

  2  ;

TO_CHAR(SYSDATE,'DAY')

---------------------------

wednesday

SQL> select to_char(sysdate, 'day') from dual where to_char(sysdate, 'day')<>'wednesday';

no rows selected

SQL> select length(to_char(sysdate, 'day')) from dual where to_char(sysdate, 'day')<>'wednesday';

no rows selected

SQL> select length(to_char(sysdate, 'day')) from dual;

LENGTH(TO_CHAR(SYSDATE,'DAY'))

------------------------------

                             9

SQL> select length(to_char(sysdate-1, 'day')) from dual;

LENGTH(TO_CHAR(SYSDATE-1,'DAY'))

--------------------------------

                               9

SQL> select length(to_char(sysdate, 'day')) from dual where to_char(sysdate-1, 'day')<>'tuesday';

LENGTH(TO_CHAR(SYSDATE,'DAY'))

------------------------------

                             9

SQL> select length(to_char(sysdate, 'day')) from dual where to_char(sysdate-1, 'day')<>'tuesday  ';

Unix-Day2Day

Find if host is ssh'able or not:

#!/bin/bash
i=`traceroute $1 | grep -v "traceroute to" | grep -c $1`
##echo $i
if [ $i -eq 1 ];
then
echo " $1 Host is SSH'able";
else
echo " $1 Host is not SSH'able";
fi



Find if host is pingable or not.

Solution1:

for host in host1 host2 ; do ping -c2 $host >/dev/null && echo $host is up || echo $host is down; done

Solution2:
ping -c 1 "$1" > /dev/null

if [ "$?" -eq 0 ] ; then
    echo "$1 Host is up"
else
    echo "$1 Host down"
fi

Solution3:

#!/bin/bash
HOSTS=" $1 $2 $3 $4"

COUNT=4

for myHost in $HOSTS
do
        count=$(ping -c $COUNT $myHost | grep 'received' | awk -F',' '{print $2}' | awk '{print $1}')
        if [ $count -eq 4 ]
                then echo  "$myHost  is up!"
                else echo  "$myHost  is having ping issues!"
        fi
done

 Sed command usage:

% _now="$(sed -e 's/\//_/g;s/:/_/g' <<<$(date +'%D_%T'))"
% echo $_now
02_08_12_15_49_26
% _now=$(sed 's/[\/:]/_/g' <<<$(date +'%D_%T'))
% echo "$_now"

Move error files generated by find command to /dev/null:

find / -name cat.txt -print 2>/dev/null

Negation of a particular file type:
$ find ./temp/* ! -name cat.txt -print 2>/dev/null


Remove files from a directory:
find ./temp/* ! -name cat.txt -exec rm -f {} \;


Move files from one directory to another:
find ./Test1/* -exec mv {} ./temp/ \;

tr command usage:Most useful command

who | tr -s ' ' | cut -d ' ' -f3 | tr '-' '/' |tr -d '/'

tr command to count frequency of words in a document:

 tr ' ' '\n' < user4 | sort | grep -v ^$ | uniq -c

To print only the frequency of characters and number in the file
tr ' ' '\n' < user3.txt | tr -cd "a-z A-Z 0-9 \n" | sort | grep -v ^$ | uniq -c

Print multi-column to single column and Single column to multi-column:
tr ' ' '\n' < user3.txt | tr -cd "a-z A-Z 0-9 \n" | sort -nr | grep -v ^$ | uniq -c |pr -t -5

^$ is a special regex that represents white row.

How do you create a file in UNIX

using touch command

using cat command

If you want to see top 3 lines from each file in your directory:
head -3 *

Show 1st line of ps command without header:

 ps | tail -3 | head -1

Sort file by date and count number of occurrences by each date:
cat user2.txt | sort -k 3 | tr -s ' ' | cut -d ' ' -f3 | uniq -c

Search specific line in the file:
$ cat -n user3.txt | head -4 | tail -1 | grep anandy

Hint:quoting is essential if the search string consists of more then 1 word or usage any of the shell characters like *,$ etc.

Hint: Grep command return fail ($?=1)when it is not able to locate file but sed/awk are not considered to fail.

Hint: Grep -l option display only file names those having search pattern.

Retrieve various elements from date.
date '+%a'