OraFAQ Forum: SQL & PL/SQL » Updates by Week

Home » SQL & PL/SQL » SQL & PL/SQL » Updates by Week

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Updates by Week [message #577683]

Tue, 19 February 2013 11:50

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

Report message to a moderator

Re: Updates by Week [message #577684 is a reply to message #577683]

Tue, 19 February 2013 11:53

Solomon Yakobson
Messages: 1404
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.

Report message to a moderator

Re: Updates by Week [message #577686 is a reply to message #577684]

Tue, 19 February 2013 12:06

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

Report message to a moderator

Re: Updates by Week [message #577688 is a reply to message #577686]

Tue, 19 February 2013 12:17

Solomon Yakobson
Messages: 1404
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

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;

Report message to a moderator

Re: Updates by Week [message #577694 is a reply to message #577692]

Tue, 19 February 2013 12:46

Michel Cadot
Messages: 54236
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

SerenityNetworks
Messages: 8
Registered: February 2013
Location: Richardson, TX

Junior Member

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

Report message to a moderator

Re: Updates by Week [message #577701 is a reply to message #577696]

Tue, 19 February 2013 13:38

BlackSwan
Messages: 20109
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!

Report message to a moderator

Previous Topic:	same query taking different time when executed thru diff dbs
Next Topic:	Append collection variable

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 24 23:57:38 CDT 2013

Total time taken to generate the page: 0.24777 seconds