OraFAQ Forum: SQL & PL/SQL » Need to get records equal to the number of month days.

Home » SQL & PL/SQL » SQL & PL/SQL » Need to get records equal to the number of month days. (Oracle Database 10g Rel.2)

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

Need to get records equal to the number of month days. [message #608571]

Fri, 21 February 2014 02:41

mazam
Messages: 47
Registered: October 2009
Location: Multan

Member

Dear All
My Table and its data is as given:

Table Name: Transaction_Master

Trn_Num Trn_Dte
1 02-Jan-2014
2 03-Jan-2014
3 15-Feb-2014
4 29-Dec-2013
5 30-Dec-2013
and so on

Now I wish that my query should return records equal to the number of days for each month of Trn_Dte. Be careful, I don't need Trn_Num column in my result. Its mentioned here only for explanation.
For example there should be 31 records for January, 31 for December and 28 for February.
I've tried following query but it goes into loop and my db session gets hang.

With T As
(
Select Distinct To_Char(Trn_Dte, 'Month') Trn_Mon, To_Number(To_Char(Last_Day(Trn_Dte), 'DD')) Tot_Day
From Transaction_Master
Where Trn_Dte Between Parameter_1 And Parameter_2
)
Select Distinct Trn_Mon, Level
From T
Connect By Level <= Tot_Day
Order By Trn_Mon, Level

Could you give me a working solution for this case?

Report message to a moderator

Re: Need to get records equal to the number of month days. [message #608577 is a reply to message #608571]

Fri, 21 February 2014 02:56

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

If you provide a working test case (ie, the CREATE TABLE and INSERT statements for your Transaction_Master table) then perhaps someone will provide a working solution. Without the test case, I can't even see what your code produces.
And please use [code] tags to format you code properly, as described here How to use [code] tags and make your code easier to read

[Updated on: Fri, 21 February 2014 02:57]

Report message to a moderator

Re: Need to get records equal to the number of month days. [message #608586 is a reply to message #608571]

Fri, 21 February 2014 03:25

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

To get the number of days in a month just use the LAST_DAY function:

SQL> with data as (select add_months(trunc(sysdate,'year'),level-1) mnth from dual connect by level <= 12)
  2  select to_char(mnth,'Month') mnth, to_char(last_day(mnth),'DD') nbday 
  3  from data
  4  /
MNTH      NB
--------- --
January   31
February  28
March     31
April     30
May       31
June      30
July      31
August    31
September 30
October   31
November  30
December  31

Report message to a moderator

Re: Need to get records equal to the number of month days. [message #608587 is a reply to message #608577]

Fri, 21 February 2014 03:41

mazam
Messages: 47
Registered: October 2009
Location: Multan

Member

Sorry John...Here is yours requirement:

Create Table Transaction_Master
(
Trn_Num Number,
Trn_Dte Dte
)

Insert into Transaction_Master Values(1, '01-Dec-2013');
Insert into Transaction_Master Values(1, '02-Dec-2013');
Insert into Transaction_Master Values(1, '31-Dec-2013');
Insert into Transaction_Master Values(1, '01-Jan-2014');
Insert into Transaction_Master Values(1, '02-Jan-2014');
Insert into Transaction_Master Values(1, '03-Jan-2014');
Insert into Transaction_Master Values(1, '31-Jan-2014');
Insert into Transaction_Master Values(1, '01-Feb-2014');
Insert into Transaction_Master Values(1, '02-Feb-2014');
Insert into Transaction_Master Values(1, '28-Feb-2014');

Report message to a moderator

Re: Need to get records equal to the number of month days. [message #608588 is a reply to message #608586]

Fri, 21 February 2014 03:44

mazam
Messages: 47
Registered: October 2009
Location: Multan

Member

Dear Michel
I don't need total days of a month but I need records equal to number of total days of respective month which is appearing in my Transaction table...Thanks.

Report message to a moderator

Re: Need to get records equal to the number of month days. [message #608590 is a reply to message #608587]

Fri, 21 February 2014 04:01

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

orclz> Create Table Transaction_Master
  2  (
  3  Trn_Num Number,
  4  Trn_Dte Dte
  5  );
Trn_Dte Dte
        *
ERROR at line 4:
ORA-00902: invalid datatype


orclz>

Furthermore, you have not used [code] tags, and your inserts will fail unless the user has his NLS_DATE_FORMAT set the same way that you do.

[Updated on: Fri, 21 February 2014 04:02]

Report message to a moderator

Re: Need to get records equal to the number of month days. [message #608591 is a reply to message #608588]

Fri, 21 February 2014 04:02

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

So, based on your CREATE TABLE and INSERT INTO statements you posted in your previous message, what would be the output?

Report message to a moderator

Re: Need to get records equal to the number of month days. [message #608667 is a reply to message #608588]

Fri, 21 February 2014 16:43

ravi470
Messages: 1
Registered: February 2014
Location: bangalore

Junior Member

Hi,

Once check below one for your requirement.

SELECT TRN_MON,  COUNT(DISTINCT TRN_DT) TRN_DAYS FROM (
SELECT  DISTINCT TO_CHAR(TRN_DTE, 'Month') TRN_MON,TO_NUMBER(TO_CHAR(TRN_DTE, 'DD')) TRN_DT FROM TRANSACTION_MASTER  )
GROUP BY TRN_MON

Thanks

Report message to a moderator

Re: Need to get records equal to the number of month days. [message #608674 is a reply to message #608667]

Sat, 22 February 2014 00:18

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If you want to count the number of days in the table there is no need of a subquery:

select to_char(Trn_Dte,'Month') mnth, count(distinct trunc(Trn_Dte)) cnt
from Transaction_Master
group by Trn_Dte
/

Report message to a moderator

Previous Topic:	Performance impact by using Number/Char column as primary key
Next Topic:	Split a varchar variable

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 18:10:27 CDT 2024