...
i have a condition in sql where we have as below
the problems is i have to do the above for 7 days repeatedly so im trying to automated this
the ones in orange are DD from day filed and they should be consecutive
... so now how to input these in to the the above sql query i.e in > condition it should if its 12/01/2013 then in < condition the date should be 13/01/2013...
I don't know the structure of your table or the data in it, so let's assume the data looks like this:
And let's say you want to fetch the sums of COLUMN_A for dates starting from 15/01/2013 to 21/01/2013.
Now, if you ran your current query for the date: 15/01/2013, then your filter conditions would pick up the two rows in red color - the ones with IDs 3 and 4. And the sum of COLUMN_A would be (101 + 102 =) 203.
If you ran your current query for the date: 16/01/2013, then your filter conditions would pick up the three rows in green color - the ones with IDs 5, 6 and 7. And the sum of COLUMN_A would be (110 + 220 + 330 =) 660.
Similarly for date: 18/01/2013, the sum would be (111 + 122 =) 233.
For date: 20/01/2013, the sum would be (444 + 555=) 999.
For date: 21/01/2013, the sum would be (123 + 456=) 579.
The following query achieves the same result mentioned above:
Notice that the output is "sparse" i.e. it does not contain every date in the range: 15/01/2013 to 21/01/2013.
The date 17/01/2013 is missing because there are no records in TEMP_T that have a DT value that lies between "17/01/2013 10:00:00 AM" and "18/01/2013 09:59:59" both inclusive.
The date 19/01/2013 is missing because there are no records in TEMP_T that have a DT value that lies between "19/01/2013 10:00:00 AM" and "20/01/2013 09:59:59" both inclusive.
If you still wanted to see those two dates with the sum value of 0 i.e. if you wanted the output to be "dense", then an outer join could be used like so -
These 2 Users Gave Thanks to durden_tyler For This Post:
hii all.
I have to get the date of the 7th day past from the current date.
if i give the current date as sep 3 then i must get the date as 27th of august.
can we get the values from the "cal" command.
cal | awk '{print $2}' will this type of command work.
actually my need is
if today is... (17 Replies)
Hi,
Anybody knows how to get what date was 28 days ago of the current system date through UNIX script.
Ex : - If today is 28th Mar 2010 then I have to delete the files which arrived on 1st Mar 2010, (15 Replies)
Hi i am writing a cron job.
so for it i need the 60 days old date form current date in variable.
Like today date is 27 jan 2011 then output value will be stote in variable in formet Nov 27.
i am using EST date, and tried lot of solution and see lot of post but it did not helpful for me. so... (3 Replies)
I am trying to find out the number of days between the current date and user defined date.
I took reference from here for the date2jd() function.
Modified the function according to my requirement. But its not working properly.
Original code from here is working fine.
#!/bin/sh... (1 Reply)
Hi! I am trying to read a file and every line has a specific date as one of its fields.
I want to take that date and compare it to the date today plus 6 days.
while read line
do
date=substr($line, $datepos, 8) #date is expected to be YYYYMMDD
if ; then
...proceed commands
... (1 Reply)
Hi,
One of my Unix scripts needs to look for files coming in on Fridays. This script runs on Mondays. $date +"%y%m%d" will give me today's date. How can I get previous Friday's date.. can I do "today's date minus 3 days" to get Friday's date? If not, then any other way?? Name of the files is... (4 Replies)
I have to display only those subscribers which are in "unconnected state" and the date is 90 days older than today's date.
Below command is used for this purpose:
cat vfsubscriber_20170817.csv | sed -e 's/^"//' -e '1d' | nawk -F '",' '{if ( (substr($11,2,4) == 2017) && ( substr($11,2,8) -lt... (1 Reply)
current date command runs well
awk -v t="$(date +%Y-%m-%d)" -F "'" '$1 < t' myname.dat
subtract 30 days fails
awk -v t="$(date --date="-30days" +%Y-%m-%d)" -F "'" '$1 < t' myname.dat
awk command in hp unix subtract 30 days automatically from current date without date illegal option error... (20 Replies)