Home » SQL & PL/SQL » SQL & PL/SQL » Updates by Week
icon5.gif  Updates by Week [message #577683] Tue, 19 February 2013 11:50 Go to next message
SerenityNetworks
Messages: 8
Registered: February 2013
Location: Richardson, TX
Junior Member
I have a table of records with a date_modified column. I would like to simply report the count of updates made each week. For example, given:
Record          Date_Modified
15529114	1/23/2013
15727720	1/24/2013
15261932	1/25/2013
14226321	1/28/2013
14004342	1/28/2013
15005043	1/28/2013
14831576	1/29/2013
15436428	1/31/2013
14806638	2/1/2013
14613777	2/1/2013
15548478	2/3/2013
14541798	2/3/2013
14185913	2/3/2013
15548478	2/3/2013
14541798	2/3/2013
14322796	2/6/2013
15721749	2/7/2013
15628209	2/8/2013
15827853	2/9/2013
15847512	2/10/2013
15854846	2/10/2013
14185913	2/10/2013
15847512	2/10/2013
15854846	2/10/2013
15864042	2/12/2013
14548369	2/13/2013
14358771	2/21/2013
14444451	2/21/2013
15323291	2/21/2013
15093704	2/21/2013


I would like to return:
Week    Updates
4	3
5	7
6	9
7	7
8	4


Although in place of the week number, providing either the value for the last day of the week or the first day of the week would be just as good.

I have no idea how to go about this task. Any guideance will be appreciated.

Thanks in advance,
Andrew
Re: Updates by Week [message #577684 is a reply to message #577683] Tue, 19 February 2013 11:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
And when does week start for you. Assuming week is Monday through Sunday, take a look at TRUNC date function date format IW.

SY.
Re: Updates by Week [message #577686 is a reply to message #577684] Tue, 19 February 2013 12:06 Go to previous messageGo to next message
SerenityNetworks
Messages: 8
Registered: February 2013
Location: Richardson, TX
Junior Member
Ah, so the following should get me counts by the ISO week number. Is this correct?
SELECT  TO_CHAR(mt.date_modified,'IW'), COUNT(*)
FROM    mytable mt
GROUP BY   TO_CHAR(mt.date_modified,'IW')
ORDER BY   1 ASC;


Thanks,
Andrew
Re: Updates by Week [message #577688 is a reply to message #577686] Tue, 19 February 2013 12:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
SerenityNetworks wrote on Tue, 19 February 2013 13:06
Is this correct?


Close, but no cigar. Think what would happen if you have data for multiple years. Think why you were suggested TRUNC, not TO_CHAR (although you could use TO_CHAR, but with more date format elements).

SY.

[Updated on: Tue, 19 February 2013 12:19]

Report message to a moderator

Re: Updates by Week [message #577692 is a reply to message #577688] Tue, 19 February 2013 12:33 Go to previous messageGo to next message
SerenityNetworks
Messages: 8
Registered: February 2013
Location: Richardson, TX
Junior Member
Yes, I discovered that once I noticed that I had data for 53 weeks and this is only February.

I added the where clause shown below and that got me what I needed.
SELECT  TO_CHAR(mt.date_modified,'IW') AS "Week", COUNT(*) AS "Updates"
FROM    mytable mt
WHERE   TRUNC (mt.date_modified) >= to_date('01-JAN-2013','dd-MON-YYYY')
GROUP BY   TO_CHAR(mt.date_modified,'IW')
ORDER BY   1 ASC;

But examining your note I see that I don't need the TO_CHAR at all and the following would be better. Did this get me the cigar?
SELECT  TRUNC(mt.date_modified,'IW') AS "Week", COUNT(*) AS "Updates"
FROM    mytable mt
GROUP BY   TRUNC(mt.date_modified,'IW')
ORDER BY   1 ASC;

Re: Updates by Week [message #577694 is a reply to message #577692] Tue, 19 February 2013 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The cigar will come when you'll a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Wink

Regards
Michel

[Updated on: Tue, 19 February 2013 12:46]

Report message to a moderator

Re: Updates by Week [message #577696 is a reply to message #577694] Tue, 19 February 2013 12:52 Go to previous messageGo to next message
SerenityNetworks
Messages: 8
Registered: February 2013
Location: Richardson, TX
Junior Member
Smile That luxury will have to come later. At some point I'll need to set up a local DB where I can create ad hoc tables for my own purposes. The only DB's I have access to now, even though I have read-write access, are not ones where I should be creating temporary tables. But I did a few sanity checks on my query by both making and watching for updates. The query returned the changes I expected.

Thanks,
Andrew
Re: Updates by Week [message #577701 is a reply to message #577696] Tue, 19 February 2013 13:38 Go to previous message
BlackSwan
Messages: 22902
Registered: January 2009
Senior Member
you can install VirtualBox on any Windows system; then use pre-configured VM that already contains Oracle DB.
You could an Oracle DB working on your PC in under 30 minutes!
Previous Topic: same query taking different time when executed thru diff dbs
Next Topic: Append collection variable
Goto Forum:
  


Current Time: Wed Oct 22 19:09:20 CDT 2014

Total time taken to generate the page: 0.15322 seconds