vsbabu.org:Newbie SQL: Current week range

« Practice of Programming - Style
» Laptop by Porsche

Newbie SQL: Current week range

Newbie queries to get date range.

Many a times you’ll have to find the starting date and end date for a range. Let us say, you’ve an automatic "changes this week" newsletter that goes out on every Friday. You’ll probably have some query that will collect all the information from last Saturday to this Friday. If you use Oracle, here are some sample SQLs.

If you are not an Oracle newbie, skip this! I’m putting this here only because I find myself explaining same thing over and over. This way, I get these kind of things documented, can direct people here when they ask and probably someone will benefit via a web search

Let us say you want to get last Friday’s date and the date of the Friday before last:


SELECT
  TRUNC(SYSDATE, 'DAY')-2  LAST_FRIDAY,
  TRUNC(SYSDATE, 'DAY')-2-7 LAST_LAST_FRIDAY
FROM DUAL

DUAL is a dummy table in Oracle that has one record. You want to break an Oracle application? Easiest way is to delete the record or add additional records to this table. This table is used by lot of programmers.
SYSDATE is a pseudo-column that returns the system date and time.
TRUNC() is a function that truncates the value (works for numeric data too). When you call it with 'DAY', it returns the date for start of the week for SYSDATE. This is typically Sunday - it can be Monday if your LOCALE setting is different.
Subtract 2 and you get last Friday.
Subtract 9 and you get Friday before last. I put it as -2-7 instead of -9 for clarity.

All right. What about the case when newsletter goes out every Friday? So, on Fridays and Saturdays you need to return current Friday and last Friday. Other wise the same logic as before. In short, the date range needs to "break" on Fridays. The code below shows a Saturday to Friday range.


SELECT
 TO_CHAR(TRUNC(SYSDATE, 'DAY')+5, 'MM/DD/YYYY') LAST_FRIDAY,
 TO_CHAR(TRUNC(SYSDATE, 'DAY')-1, 'MM/DD/YYYY') LAST_LAST_SATURDAY
FROM DUAL
WHERE TO_NUMBER(TO_CHAR(SYSDATE,'D')) >= 6
UNION
SELECT
 TO_CHAR(TRUNC(SYSDATE, 'DAY')-2, 'MM/DD/YYYY') LAST_FRIDAY,
 TO_CHAR(TRUNC(SYSDATE, 'DAY')-2-6, 'MM/DD/YYYY') LAST_LAST_SATURDAY
FROM DUAL
WHERE TO_NUMBER(TO_CHAR(SYSDATE,'D')) < 6

UNIONing two queries with mutually exclusive conditions make it very easy to read.
First query returns current week’s Friday and last week’s Saturday. TO_CHAR() with 'D' will return the date’s day number in a week. For Friday, that is six. TO_NUMBER() converts it to a number for comparison. TO_NUMBER() is not necessary since you can compare TO_CHAR() with '6' since it is never going to return a two digit number. Only 7 days in a week, right?
Second query is just like what you saw before.
For UNION, the queries should return columns with same name. That is why the first query still names the column like LAST_FRIDAY and LAST_LAST_SATURDAY, though CURRENT_FRIDAY and LAST_SATURDAY might be more appropriate.

How can I get these ranges for a date other than current date?
Very easy. Replace all occurrences of SYSDATE with your date. If you are using SQL*Plus, you can put this as a variable like &&mydate.

Posted: October 25, 2002 05:36 PM
oracle

hi,
i need your help please, since you post a great example here, you can solve this.

i have the sql below, that is a time between "x" and "y". i need current time minus 10 minutes. it means each time the page refresh i got last 10 minutes report. look at the current sql please. thanks in advance.

between TO_DATE('12/5/2002 9:45:00 AM', 'MM/DD/YYYY HH:MI:SS AM') and TO_DATE('12/5/2002 9:55:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

i need between (now - 10 minutes)

Posted by: john on December 6, 2002 10:11 AM
That'll be:
between sysdate and (sysdate-(10/(24*60)))

explanation:
sysdate = current data and time
sysdate - 1 = yesterday's date and time
so sysdate - 1/24 = last hour
and sysdate - 1/(24*60) = last minute
and sysdate - (10/(24*60)) = 10 minutes before

Posted by: Babu on December 6, 2002 11:16 AM
Hello can you tell me the SQL query to insert BLOBS into a database. Like i have to insert some video files into an oracle table. How is it possible?

Posted by: ruben on March 19, 2003 11:14 PM
There are different ways to insert BLOBs into database, depending upon how you execute your query (ie., ODBC, PL/SQL, ADO, JDBC etc...). The standard way is to insert a record with an empty_blob(); and *then* update the record by loading blob data from the file into the BLOB field. If you use PL/SQL, you can use functions in dbms_lob package to read from a file into the BLOB field.

I believe it is also possible to directly insert a BLOB using prepared statements (dynamic SQL with bindings). However, this may not be a good idea, if the BLOB data is too large. The first method described above will allow you to load BLOB data in chunks - like a buffered file write.

Posted by: Babu on March 20, 2003 05:44 AM
Hi, This question is some what relevant to the question asked by John. I am also in the same situation like i need to get report for the data between two particular instances of time and i am not clear with your answer. Could you please send write it in a sql query. Iwould greatly appreciate ur reply.
Thanks

Posted by: Naveen on April 3, 2003 11:52 AM
Naveen, for getting data within last 10 minutes, the query would look like:

SELECT * FROM mytable WHERE mydatecolumn BETWEEN sysdate AND sysdate - (10/(24*60))

All you need to understand is that date arithmetic is handled in days. So, if you want to get difference in hours, you scale it down by saying days/24. Similarly for minutes, days/(24*60)

Posted by: Babu on April 3, 2003 11:59 AM
I am a newbie. I am using the FrontPage 2002 Database Interface wizared to create my interfaces. (Yes, I know, but I'm not much of a programmer.)

I have 2 text boxes named Date and Date 1. I want to have the database respond to the user input with a between statement with Date being the first or lower date and Date 1 being the latter date. (Using Access as the db.)

The query FrontPage writes for me is as follows:
fp_sQry="SELECT * FROM OnlineWorkshops WHERE (Date = #::Date::# )

Obviously, this only retrieving a single date (Date is the name of the field in Access) from a textbox, but I need a date range.

Anyone willing to help us a greenhorn?
Thanks!

Posted by: Mila Almquist on April 24, 2003 10:33 AM
Hi:

I need to get fridais between two dates. How could I get it.

Thanx a lot

Jaime

Posted by: Jaime on May 16, 2003 08:01 AM
Jaime (#8): you can get records belonging to friday's like SELECT * FROM mytable WHERE mydate BETWEEN start_date AND end_date AND TO_CHAR(mydate,'DY') = 'FRI'

To make this better, use TO_CHAR(mydate, 'D')=6 instead of 'DY'. This one will work even if your locale setting is in some other language where Friday is not 'FRI'.

If you need to only get a list of all Fridays - without having any table with data - between a range you need to create a dummy pivot table. Normally, you wouldn't need calendar like information without any related data.

Posted by: Babu on May 16, 2003 10:35 AM
Hi,

I need to find out the week ending date for any given date.
The week starts from monday and ends on Sunday.
So In this case I have to find out the date of the coming sunday for any given date.

I need to do this in the control file of SQL loader.

Help appreciated.

Regards
PC

Posted by: pc on May 29, 2003 06:21 PM
i'm doing a sytem using mysql and php...how can i do to make the cursor already and always in the textbox..

Posted by: andalusia on June 22, 2003 11:25 PM
How do I compare one time1 with time2 +120 minutes? Variables - Time1 and time2 are in character format, 24Hr clock time.

Posted by: curious on June 30, 2003 04:09 PM
With respect to 6.
SELECT * FROM mytable WHERE mydatecolumn BETWEEN sysdate AND sysdate - (10/(24*60))

Won't a lot of DBMS have return 0 rows from that? We'd need to search between the older date and the newer so it'd be:

SELECT * FROM mytable WHERE mydatecolumn BETWEEN sysdate - (10/(24*60)) AND sysdate

For whatever other name sysdate may use on your system. Paranoid freakery I know if you are dedicated to Oracle, which I assume will handle that with a blink of an eye. I'm using mySQL though, and I know it has problems.
Cheers

Posted by: Pooma on July 5, 2003 02:59 PM
#13, you are right. Oracle also has issues. For BETWEEN to work, it should be from a smaller value to a bigger value.

Thanks for pointing it out.

In case you don't know which value is going to be smaller, you can always right a generic BETWEEN - at least in Oracle - using functions LOWEST() and GREATEST()

Posted by: Babu on July 5, 2003 06:02 PM
Please help, I am trying to report on the number of records entered with the corresponding date they were entered, over various days, weeks, or months.

I need to include count=0 for dates where no records were entered. I am using count(*) and a group by $date and it's fast,.... but for the count=0... I am stuck.

There are no records entered in the database so NVL cannot work... can it? I am hoping for a built in date function/null funtion vs. a script to iterate through zero count dates, namely that speed is an issue and I am restricted to very slow front end scripting.

Any help would be GREATLY appreciated.

SELECT
count(*) the_count,
to_char(date_entered, '$date_frequency_formatting') the_date
FROM
the_table
GROUP BY
to_char(date_entered, '$date_frequency_formatting')

(date_frequency_formatting is just a variable I pass in to give either 'yyyy-mm-dd', 'yyyy-iw', or 'yyyy-mm')Then I simply list the_count and the_date, but skips over dates where nothing was entered.

Posted by: sqpublic on July 9, 2003 07:51 PM
#15: NVL is pretty useless if you don't have records that are selected. What you need to do is to have a pivot table. A table that has one column and 365 records, with values ranging from 1 to 365.

After that, you do an outer join between your table and the pivot table - so for a year, you are bound to get all the days. Where the join has records, it will return counts. Otherwise it will return zero.

eg: select a.mydate, nvl(count(a.*),0) from the_table a, pivot p where p.col = to_char(a.mydate, 'ddd')(+) group by a.mydate

You can make additional joins to make more records from the pivot, where no records exist in your data.

Posted by: Babu on July 9, 2003 11:11 PM
Sir, Help me to solve this problem as early as possible:
I have prob in getting data displayed , retrieved from database (MS Access) table - billdetails- based on date entered by user.dt1 is variable for date in form entered by user.

Waiting for positive reply from your side at your earliest and convenience.
Thanking you,
Chandresh .....

Posted by: Chandresh on July 11, 2003 05:13 AM
Hi,

I am trying to substract dates in a PL/SQL block and print the time elapsed.

DECLARE
startdate CHAR(20); -- reading taken prior to start of block
stopdate CHAR(20); -- reading taken prior to end of block
tmp NUMBER;

BEGIN
select To_Char(SYSDATE,'HH24:MI:SS DD-MM-YYYY') INTO startdate FROM dual;
Dbms_Lock.sleep (3); -- simulating database work time
select To_Char(SYSDATE,'HH24:MI:SS DD-MM-YYYY') INTO stopdate FROM dual;
tmp := To_Date (stopdate,'HH24:MI:SS DD-MM-YYYY') - To_Date (startdate,'HH24:MI:SS DD-MM-YYYY');
Dbms_Output.put_line ('Time elapsed : ' || To_Char(tmp,'HH24:MI:SS DD-MM-YYYY'));

END;

The problem is that I am getting

SQL> /
Time elapsed : ##########

PL/SQL procedure successfully completed.

SQL>

How can I get the elapsed time

Posted by: rama krishna on July 11, 2003 05:56 AM
#18 - naturally. Your tmp is a number variable. You can't convert a plain number into date format like to_char(tmp, 'HH24:MI:SS DD-MM-YYYY')

All you need to do is:

DECLARE
startdate DATE; stopdate DATE;
tmp NUMBER;
BEGIN
startdate := sysdate;
-- simulate delay here
stopdate := sysdate;
tmp := stopdate - startdate;
Dbms_Output.put_line ('Time elapsed (in days): ' || tmp);
END;
/

No need to save start/stopdate variables as characters. If you want to display tmp in hours, minutes etc, you can get it calculated from tmp.

Posted by: Babu on July 11, 2003 07:56 AM
#17 - this is more of a ADO problem. Please try a Microsoft newsgroup to get answers.

Posted by: Babu on July 11, 2003 07:58 AM
I want to track time upto milliseconds. How do I store it in Oracle? I would also be doing some time comparison with millisecond precision.

Posted by: Jet Frost on July 14, 2003 08:18 AM
In a timesheet application I want to store number of hours worked by user on different tasks during the day. Which Oracle datatype is best suited for this? There would be reports based on this data. So it would need time manipulation. How to do that in oracle? Say for example if the user enters time on taskOne = 3:20 hrs and taskTwo = 4:40 hrs then I want to total this time and display Total = 8:00 hrs.
Thanking you in advance.

Posted by: Jet Frost on July 14, 2003 08:23 AM
#21 - Yes, in Oracle 9i you can do comparisons with Millisecond precision. You've a TIMESTAMP data type in 9i. If you use Oracle versions less than 9i, I believe tracking time upto milliseconds is not possible. Older versions used to track time as seconds elapsed since epoch - so the highest precision is only upto second.

Posted by: Babu on July 14, 2003 10:30 AM
#22 - One way to do that would be to store time slices with a start time and end time, as DATE types. Now you can easily calculated the hours elapsed between times.

Unfortunately, this might prove to be very annoying to users, instead of just entering 3:30 hours. Let us say you store the hours in a number column without any colons as 330. Here's how you can get it into base 100 number, ie., 3.5.

trunc(hours/100)+mod(hours,100)/60

Now, you simply sum it up per day using SUM() on the calculation above. You will end up with a result like 8.25 for 8h15minutes. You can use similar logic above to convert base 100 number to hours and minutes.

Posted by: Babu on July 14, 2003 10:50 AM
I have a results webpage getting data from a web MS Access database.

In this webpage I need help getting the following:-
. I need to get totals of certain fields for previous days data .
. I also need to get month to date totals for those same columns.

I am not sure whether this can be achieved by writing SQL. And I dont know how to write SQL. If yes any help will be appreciated.

Posted by: Roger on July 25, 2003 04:06 PM
how can i find out nth maximum or minimum salary value from an table when the salary field has range of values like 3000-6000,7500-9000 etc. I wrote correlated subquey like,
select e.salary from emp e where n-1 = (select count(*) from emp e1 where e.salary > e1.salary)
that for 3rd maximum replace n-1 as 2. This wroks fine as long as salary has type NUMBER. But i don't know how to extract average salary from range of values and apply to the query?

Posted by: venki on August 4, 2003 09:44 AM
#26: To get average salary from a range:
select (to_number(substr(salary,0,instr(salary,'-')-1)) + to_number(substr(salary,instr(salary,'-')+1)))/2 as average_salary from emp

Use this calculation in your comparison.

Posted by: Babu on August 4, 2003 01:21 PM
Hi,
Just wondering how to convert 'DD-MM-YYYY' on sql to DDDD
e.g. '17-08-2003' to 'Sunday'.

I know dateformat works for 2003-08-17 but not 17-08-2003.
Any help would be appreciated.

Thanks in advance

Posted by: nosferatu on August 7, 2003 10:11 AM
Please help me to solve this query:
current sql to generate a report & simpler version of sql. e.g Employee sales by country report.

Posted by: Thandeka on August 26, 2003 10:44 AM
Dear Freinds,
I got stuck in a problem that i want to show all the records of a given week for that i have run the following query
select memberid,dayname(dDateTime) as dayname,dayname(current_date) as today,

sVenue,dDateTime,current_date,count(current_date) as counter from tbl_customer
group by sVenue,dDateTime having dDateTime >=current_date and dDateTime between current_date and DATE_ADD(current_date,INTERVAL 7 DAY) $selEventParty order by sVenue,dDateTime desc";
Now wheen i try to retrieve the record it returns me only the record for there is entry in database whereas i want all the day's name evenif the event for that day be zero.Remember the query get displayed in foreach loop.
Help of any kind will be deeply appreciated
thanking in anticipation
Pratiush Dayal

Posted by: Pratiush Dayal on August 28, 2003 08:05 AM
I need a fairly specialized like statement. I know how to construct for Sybase, but the Oracle syntax eludes me.

Basically, I need any record where the length is at least 8 characters (hence the 7 underscores) and the eighth position must be A-Z. After that I don't care.

Sybase Syntax
... like "_______[A-Z]%'.

Posted by: Patrick Kane on September 22, 2003 11:27 AM
Patrick - In Oracle 10g, you can use regular expressions.
http://otn.oracle.com/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html

Otherwise, it might be much easier to write "where length(col)>=8 and substr(col, 8,1) in ('A','B',..., 'Z')". Or you could use "between 'A' and 'Z'".

Posted by: Babu on September 22, 2003 12:47 PM
HI
I have a calendar for 2004.
It starts on thursday,1st january 2004.

The way I want the week of a year is like
THE WEEK STARTS on a SUNDAY ends on a SATURDAY.

From Jan,1st,2004 thursday
To Jan 3rd Saturday forms a seperate week and the 2nd week starts by 4th january 2004 go up to 10th jan 2004.

Like this I want to have the no of weeks of a year..

Please help,,,
anna

Posted by: Anna on October 22, 2003 06:06 PM
A form allows user to select a range of number from a list box like 100-200. How can I save this value in database through sql queries, prefereably using only one variable. Is there any special data type for that ?
How these values could be stored in mysql or MS access database.?

Posted by: Bill on December 28, 2003 12:06 AM
With Regards No 6.
I am in a great problem to find out a solution.

I need to find out no of rows on every 10 mints within given date range.

Please help on this

Posted by: Nirar on January 5, 2004 02:50 PM
hi,
i need some help in bulding codes using SQL where the user will choose their week ending date and i have to compare the sum of the whole week which ends on the choosen date... (this week) and the whole week before it (last week)

eg. if i choose 14th December 2003..
it will show the sum of sales for (this week) 7-14th and (last week) 1st-7th
..

please help me
thank you

Posted by: nottisue on January 21, 2004 11:53 PM
Hello,
The article I read on 'Newbie SQL: Current week range' was very useful in selecting a date range with reference to SYSDATE. Given below, is an example:

SELECT TRUNC(SYSDATE, 'DAY') +1 LAST_MONDAY,
TRUNC(SYSDATE, 'DAY') +7 NEXT_SUNDAY
FROM DUAL;

However, I have a situation where I would need to populate a table with two DATE fields previous 'MONDAY' and following 'SUNDAY' as above, for 52 weeks in a stretch starting from SYSDATE.

Please let me know if there is way to populate the subsequent weeks (2 to 52) with reference to System Date.

Your suggestions are most welcome.
Regards - karuna

Posted by: karuna lakkineni on March 11, 2004 10:05 PM
#37: That is easy enough. You just need to extend your query to return additional 52 rows. You can easily do that with pivot-table like queries. See the query below.

SELECT
TRUNC(SYSDATE, 'DAY') + 1 + (P.pivot*7) last_monday,
TRUNC(SYSDATE, 'DAY') + 7 + (P.pivot*7) next_sunday
FROM DUAL D, (
SELECT ROWNUM pivot FROM all_objects WHERE
ROWNUM < 53) P;

See my note on pivoting using ALL_OBJECTS at http://vsbabu.org/mt/archives/2003/12/27/pivoting_on_all_objects.html

Posted by: Babu on March 15, 2004 09:47 AM
#36: If you are sure that the variable you pass is a week-ending-date, you can query like

SELECT SUM(sales_amount) FROM sales WHERE
TO_CHAR(sales_date, 'YYYYWW') = TO_CHAR(my_variable_weeke_ending_date, 'YYYYWW') OR
TO_CHAR(sales_date, 'YYYYWW') = TO_CHAR(my_variable_weeke_ending_date-7, 'YYYYWW')

Note that this is not optimized for performance. If you are not sure the variable is indeed a week ending date, then you first need to find out which is the week-ending date and go from there.

The format mask YYYYWW will give you the 4 digit year number and the week number together. Year is required - otherwise, you will get data from all other years too.

Posted by: Babu on March 15, 2004 09:55 AM
I dont have rights to create tables. I need help with a query that counts all business days between dayX and dayY. So I want to count monday - friday between jan1 - feb1.

Thanks in advance.

Posted by: Rod on March 16, 2004 12:20 AM
#40: you don't need to create a table for that. See the query below - this works for Oracle. For other databases, ask their respective newsgroups.

select
trunc(to_date('040101', 'yymmdd')) start_date,
trunc(to_date('040201', 'yymmdd')) end_date,
((((trunc(to_date('040201', 'yymmdd'), 'day')-2) - (trunc(to_date('040101', 'yymmdd'), 'day')+5))/7)*5)
+ (trunc(to_date('040101', 'yymmdd'), 'day')+5 - trunc(to_date('040101', 'yymmdd'))+1)
+ (trunc(to_date('040201', 'yymmdd')) - trunc(to_date('040201', 'yymmdd'), 'day')) work_days,
-- explanation of individual components of the above calculation follows
trunc(to_date('040101', 'yymmdd'), 'day')+5 friday_of_starting_week,
trunc(to_date('040201', 'yymmdd'), 'day')-2 friday_of_ending_week,
(
((trunc(to_date('040201', 'yymmdd'), 'day')-2) - (trunc(to_date('040101', 'yymmdd'), 'day')+5)) --days from friday to friday
/7) -- how many weeks? and each week has 5 working days
*5 work_days_for_weeks_between,
trunc(to_date('040101', 'yymmdd'), 'day')+5 - trunc(to_date('040101', 'yymmdd'))+1 work_days_in_starting_week,
trunc(to_date('040201', 'yymmdd')) - trunc(to_date('040201', 'yymmdd'), 'day') work_days_in_ending_week
from dual

Essentially, you split the range into three. Working days remaining in the starting week + working days in the weeks in between + working days in the last week.

Posted by: Babu on March 16, 2004 09:55 AM
A much more easier way, but not limit proof (ie, you shouldn't give a range that has more days than your total number of rows in all_objects).

select
to_char(to_date('01-jan-04') + p.pivot, 'dd-mon-yy day')
from dual d,
(select rownum pivot from all_objects) p
where
to_date('01-jan-04') + p.pivot <= to_date('02-feb-04')
and
to_number(to_char(to_date('01-jan-04') + p.pivot, 'D')) not in (1,7)

Posted by: Babu on March 16, 2004 10:47 AM
Thanks for the solution suggested with ref. to #37.

Posted by: karuna lakkineni on March 18, 2004 08:42 PM
Hi,
I have a table with records of users and the columns include user_id, user_name, join_date and leave_date. Given a date range (say 01/01/2004 to 31/03/2004) how can I find out the number of users who joined and the number of users who left for each month of the given date range? The result set I need would look somewhat like this:

Month Joined Left
----------------------
Jan 2004 10 5
Feb 2004 0 0
Mar 2004 12 0

I'm not sure it can be done with a simple query. Or can it? Will I need to create a view for monthly data? Or write a stored procedure to get the info?
Thanx & regards,
~Bindu

Posted by: Bindu on March 26, 2004 02:40 AM
Hey, I got the solution. But I'd still appreciate a reply since you might have a better way than what I'm doing now.
Thanx,
~Bindu

Posted by: Bindu on March 26, 2004 04:12 AM
#44 & #45 - see below. Here is a quick solution I could think of. If yours is different, please post it too.

----- assume following criteria ----
-- 01/01/2003 is the begin date
-- 31/03/2004 is the end date
-- create a temp table for our use and fill it up with some data
create table temp_users as
select rownum user_id,
object_name user_name,
(sysdate-rownum*10) join_date,
(sysdate-rownum*5) leave_date
from all_objects where rownum < 40
/

-- adjust data to have some people who have not left yet
update temp_users set leave_date = null where user_id <= 10
/

-- now get number of people joined and left each month for a date range
-- using the examples above
select
add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), p.pivot) month,
sum(decode(trunc(join_date,'mon'), add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), p.pivot),1, 0)) joined,
-- two decodes for leave_date, because we shouldn't count people who haven't left
sum(decode(leave_date, NULL, 0, decode(trunc(leave_date,'mon'), add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), p.pivot),1, 0))) left
from temp_users u, (
-- generate rows for getting all the months between the range.
-- the condition <20 is applied to reduce output from all_objects.
-- so, that number (or in its absence, number of records in all_objects) is the limit
select rownum pivot from all_objects where rownum<20
) p
where
add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), p.pivot) <=
trunc(to_date('31/03/2004', 'dd/mm/yyyy'), 'mon')
group by add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), p.pivot)
/

-- since it is a pain to keep typing in add_months(..), we can move that inside the pivot table
select
p.month,
sum(decode(trunc(join_date,'mon'), p.month, 1, 0)) joined,
sum(decode(leave_date, NULL, 0, decode(trunc(leave_date,'mon'), p.month ,1, 0))) left
from temp_users u, (
select add_months(trunc(to_date('01/01/2003','dd/mm/yyyy'), 'mon'), rownum) month
from all_objects where rownum<20
) p
where
p.month <= trunc(to_date('31/03/2004', 'dd/mm/yyyy'), 'mon')
group by p.month
/

-- get rid of temp table
drop table temp_users
/

Posted by: Babu on March 26, 2004 09:27 AM
Hi,
Thanx for your input. My approach is a bit simpler, but then I dont get records for the months where no users joined or left...also, I am using 2 queries - 1 for the number of users that joined per month and another for the number that left each month...

--To get the number of users joined each month within a given date range
SELECT DISTINCT COUNT(*)
OVER (PARTITION BY TO_CHAR(JOIN_DATE,'Mon YYYY')) Joined,
TO_CHAR(JOIN_DATE,'Mon YYYY') Month
FROM USERS
WHERE JOIN_DATE BETWEEN '01-jan-2004' AND '31-mar-2004'
ORDER BY TO_DATE(Month, 'Mon YYYY')

Output: Feb is skipped since no user joined in Feb.
Joined Month
----------------------
10 Jan 2004
12 Mar 2004

--To get the number of users that left each month within a given date range
SELECT DISTINCT COUNT(*)
OVER (PARTITION BY TO_CHAR(LEAVE_DATE,'Mon YYYY')) Left,
TO_CHAR(LEAVE_DATE,'Mon YYYY') Month
FROM USERS
WHERE LEAVE_DATE BETWEEN '01-jan-2004' AND '31-mar-2004'
ORDER BY TO_DATE(Month, 'Mon YYYY')

Output: No record for Feb & March.
Left Month
----------------------
5 Jan 2004

Well, am checking out what to do about it...if I dont get a way to get all months in the ResultSet, I probably will try to work it out in my application...

Thanx & regards,
~Bindu

Posted by: Bindu on March 26, 2004 09:03 PM
#47, the solution in #46 will give exactly the output (well, I didn't format the data as Mon YYYY, but that you can do) you wanted. It will return all the months within the date range and print the numbers appropriately for each month in the same record.

It can definitely be done in a programming language over a ResultSet. But, I think if you have a database application, you should try to do as much as possible in single SQLs. If that is not enough, then use PL/SQL. Still not possible, then try a Java stored procedure. Still stuck, then try a C external routine. If it is still impossible, may be you want to rethink your logic or requirement. Tom Kyte's "Expert One-on-One Oracle" book is an excellent resource on everything a programmer needs when dealing with Oracle.

Posted by: Babu on March 27, 2004 08:19 AM
Also, whenever you pass a string like '20-mar-2004' to a query, it is always better to do it as to_date('20-mar-2004', 'dd-mon-yyyy'). Or, using whatever is the appropriate format mask. If your session's NLS_DATE_FORMAT is set to the way you give date string, Oracle will correctly identify it and convert it to a date. Otherwise, it will remain a string which is wrong for most where clauses. People may set different NLS_DATE_FORMAT. This is especially true for applications where you embed a query string inside your code. If you ship your code to run on another database, you will end up getting lots of errors if that database is set to have an NLS_DATE_FORMAT of 'dd.mm.yyyy' as in say Germany.

Posted by: Babu on March 27, 2004 08:25 AM
hi. please help me with oracle sql+. i want to create a trigger so that for every new insert, the date field must be the current date.
any help will be much appreciated!
thanking you in advance.

Posted by: lee on April 1, 2004 08:58 AM
#50. Why do you need a trigger? Simply alter the table so that the field (column) has a default value of SYSDATE.

Posted by: Babu on April 1, 2004 10:16 AM
thanks! but i also want to have it as a constraint. each new entry must have the date field as the current date.

Posted by: lee on April 1, 2004 10:29 AM
#53: Easy enough.
create or replace trigger mytable_check_date_trigger after insert on mytable for each row
begin
if :new.mydatecolumn != sysdate then raise program_error; end if;
end;
/

Change mytable and mydatecolumn appropriately. If you only want to check for date and not the time component too, then do the comparison as trunc(:new.mydatecolumn)!=trunc(sysdate).

Posted by: Babu on April 1, 2004 03:48 PM
OK my brain is not working here ... there has to be an easy solution.

I have a query that selects a count of sales calls for a sales rep by the day of the week.

ex:
REP_ID DAY COUNT(*)
12345 TUESDAY 10
12345 MONDAY 12
12345 THURSDAY 5
and so on.

What only need to pull values for REP_IDs who do not have all 5 workdays represented. In other words those who had no sales on a given day of the week that month. Yes its a Big Brother type thing. Any thoughts?

Posted by: Bill on April 16, 2004 12:57 AM
I am assuming your table is like REPS(rep_id, sale_date, sale_info) and the query that gave the output above is like:

select rep_id, to_char(sale_date, 'DAY'), count(*) from reps group by rep_id, to_char(sale_date, 'DAY')

If a rep doesn't make a sale on a day, he doesn't have records. The way to get records when you don't have any is using pivot tables. Please see my note at http://vsbabu.org/mt/archives/2003/12/27/pivoting_on_all_objects.html

First you need to get all the working days (Monday-Friday) on the week that has the given date (in this example, I have used sysdate):

select
trunc(sysdate, 'DAY')+p working_days_in_week
from
(select rownum p from all_objects where rownum < 6) pivot

Now, all you need to do is to make a query that has all the dates and all the reps for a week. Then subtract the records that actually recorded a sale.

select to_char(p.working_day,'DAY'), r.rep_id
from (
select trunc(sysdate, 'DAY')+p working_day
from (select rownum p from all_objects where rownum < 6) pivot) p,
(select distinct rep_id from reps) r
minus
select to_char(sale_date,'DAY'), rep_id from reps
where trunc(sale_date,'DAY')=trunc(sysdate,'DAY')

You can substitute sysdate in the above query with whatever date you want. Also, if you have a master table that has all the rep_ids, you can use that table instead of querying for distinct rep_ids. The last where clause is to make sure we select only the sales data for the week specified.

Posted by: Babu on April 16, 2004 01:09 PM
I would like to be able to determine the week ending date of a given formatted date string:

e.g. what is the end date of ('20/2004','IW/YYYY') in 'mm/dd/yyyy' format.

Posted by: Jay E on May 27, 2004 02:12 AM
#56 - I haven't explained the logic below, but if you read the article above and my article on pivoting on all_objects, you should be able to figure it out.

sql>var p_iwyyyy varchar2(7);
sql>exec :p_iwyyyy := '21/2004';
sql>select to_char(weekbegin, 'iw/yyyy') isoweek, weekbegin, weekend from
(
select
trunc(trunc(to_date(:p_iwyyyy,'ddd/yyyy'), 'yyyy'), 'day') + p.pivot*7 weekbegin,
trunc(trunc(to_date(:p_iwyyyy,'ddd/yyyy'), 'yyyy'), 'day') + p.pivot*7 + 6 weekend
from (select rownum pivot from all_objects where rownum<54) p
) x
where to_char(weekbegin, 'iw/yyyy') = :p_iwyyyy

ISOWEEK WEEKBEGIN WEEKEND
------- --------- ---------
21/2004 23-MAY-04 29-MAY-04

1 row selected.

Posted by: Babu on May 27, 2004 05:26 PM
Hi,

observe the following SQL code and output. What I need is to only display the records for which there is a specialisation (ie., code + desc), but no doctor. I know my outer join is wrong, but I can't work out what condition I need to limit output as I need it.

SQL> select spec_id as Code, specialisation.descrip as Description, doctor.emp_id as Doc
2 from specialisation, doctor
3 where doctor.spec(+)=specialisation.spec_id;

CODE DESCRIPTION DOC
---- ------------------------------ ----
S001 Oncology E221
S002 Cardiology E441
S003 Mixology
S004 Proctology E551
S005 Phrenology

Posted by: Alex on May 29, 2004 10:01 AM
i want to display the last saturdays of every month for the given year (single SQL Query)

Posted by: rajasekaran on June 25, 2004 12:13 AM
Hi,

I need to be able to set the start day of IW to Tuesday - Monday instead of Monday - Sunday. Can you tell me how to do this?

select sum(d.quantity), sum(d.images), c.est_prod_start_dt, TO_CHAR(c.est_prod_start_dt, 'IW') WEEKLY
from print_mail c, print_mail_detail d
where c.est_prod_start_dt between decode(:p_from, '', c.est_prod_start_dt, :p_from) and decode(:p_to, '', c.est_prod_start_dt, :p_to)
group by c.est_prod_start_dt, TO_CHAR(c.est_prod_start_dt, 'IW')

Thanks Melissa

Posted by: Melissa Newport on June 28, 2004 09:56 PM
In Goldmine 5.0, I want a script that will report on all contacts made in the last working week. That's straight forwards, but what i'm not sure on is how to specify in SQL/GM the "current week". I don't want to have to manually change the dates every friday, can I use a formula as shown in your oracle example?

Posted by: Joe on July 13, 2004 05:56 AM
what is the difference between dual and dummy tables.

Posted by: Vijay Kumar on September 1, 2004 11:47 AM
I need to run a report which gets data for the 4th prior month. For example, if I run report in the month of OCT, it should get me the data for the month of JUN. This is the query
select
add_months(trunc(trunc(sysdate,'MONTH')-1,'MONTH'),-3 )
"Start DT of 4th prior month"
,last_day(add_months(trunc(trunc(sysdate,'MONTH')-1,'MONTH'),-3 ))
"End DT of 4th prior month"
, trunc((sysdate)) "Today"
from dual
This doesn't work in the month of JAN 2005. I need to get the report for SEPT 2004 if I run the report in JAN-2005. Please advise. Thanks in advance.

Posted by: Divya on October 6, 2004 09:23 PM

vsbabu.org

Related Entries

Newbie SQL: Current week range