Home » SQL & PL/SQL » SQL & PL/SQL » how to get data between from previous year April to given month and year
how to get data between from previous year April to given month and year [message #330711] Tue, 01 July 2008 00:08 Go to next message
rajendar
Messages: 4
Registered: June 2008
Location: Andhrapradesh
Junior Member

Hi,

I want to retrieve cumulative sum of the each record whenever user gives reporting month and year.I want to retrieve data from previous year's April to current selected month and year.Please send me query for this?
Re: how to get data between from previous year April to given month and year [message #330712 is a reply to message #330711] Tue, 01 July 2008 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Current date: sysdate
Current year: extract(year from sysdate) or to_char(sysdate,'YEAR')
Builting a date: to_date

Regards
Michel
Re: how to get data between from previous year April to given month and year [message #330715 is a reply to message #330712] Tue, 01 July 2008 00:28 Go to previous messageGo to next message
rajendar
Messages: 4
Registered: June 2008
Location: Andhrapradesh
Junior Member

Actually i wrote query like this but it is not working.Iam new to sql please give me help.

SELECT coalesce(SUM(IN_BFRWEEKS),0) FROM NTL_DTLS_TB
WHERE VR_RPTFORMNTH ,IN_YEAR between to_char(TO_DATE(VR_RPTFORMNTH,'mon'),'mm') = rptmnth AND IN_YEAR=year AND to_char(TO_DATE(VR_RPTFORMNTH,'mon'),'mm') >= 4 AND IN_YEAR=year-1




Thanks & Regards,
Rajendar

Re: how to get data between from previous year April to given month and year [message #330717 is a reply to message #330715] Tue, 01 July 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Your query is syntaxically incorrect, you have to first fix that.

By the way, "is not working" is meaningless for us to help you.
You MUST copy and paste your SQL*Plus session... formated of course.

Regards
Michel
Re: how to get data between from previous year April to given month and year [message #330730 is a reply to message #330711] Tue, 01 July 2008 01:06 Go to previous messageGo to next message
rajendar
Messages: 4
Registered: June 2008
Location: Andhrapradesh
Junior Member

Hai,

I am sorry. I eagerly write a new topic to get answer without checking preview message.

here is my code:

SELECT Coalesce(SUM(In_bfrWeeks),0)
FROM ntl_dtls_Tb
WHERE vr_rptFormNth BETWEEN To_char(To_date(vr_rptFormNth,'mon'),'mm') = rptmNth
AND In_Year = YEAR
AND To_char(To_date(vr_rptFormNth,'mon'),'mm') >= 4
AND In_Year = YEAR - 1


Thanks & Regards,

Rajendar
Re: how to get data between from previous year April to given month and year [message #330744 is a reply to message #330730] Tue, 01 July 2008 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still post too fast without reading the guidelines I posted.
Do it now and repost including table description and explaination about what is columns and what is variable. How can you expect we know this reading a query?

Regards
Michel
Re: how to get data between from previous year April to given month and year [message #330781 is a reply to message #330744] Tue, 01 July 2008 03:54 Go to previous messageGo to next message
rajendar
Messages: 4
Registered: June 2008
Location: Andhrapradesh
Junior Member

Hi,

My table ntl_dtls_tb is declared with these fields

in_ntlcd number(20);
in_bfrWeeks number(20);
vr_rptForMnth var char2(20);
in_year number(20);
in_ulbobjid number(10);
ch_delflag char(1);


In this table i inserted 10 records with months starts with 'Jan,Feb,Mar...........like this for 2006,2007,2008 years.
Now i want to retrieve sum(in_bfrWeeks). If user gives me Jul-2008 i want to retrieve data from April - 2007 to Jul-2008 with the help of vr_rptForMnth and in_year.Please give me help for query?


Regards,
Rajendar
Re: how to get data between from previous year April to given month and year [message #330788 is a reply to message #330781] Tue, 01 July 2008 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do it with what I said in my first post.

Regards
Michel

Re: how to get data between from previous year April to given month and year [message #330833 is a reply to message #330730] Tue, 01 July 2008 08:07 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
rajendar wrote on Tue, 01 July 2008 02:06

AND To_char(To_date(vr_rptFormNth,'mon'),'mm') >= 4



Please note that you are comparing a character string to a number here. Implicit conversion will cause problems. Maybe not today, but then tomorrow.
Previous Topic: derive sub-total of one column and divide by another column's distinct value?
Next Topic: sql help
Goto Forum:
  


Current Time: Sun Dec 11 00:05:03 CST 2016

Total time taken to generate the page: 0.08528 seconds