Home » SQL & PL/SQL » SQL & PL/SQL » urgent help needed in the query
urgent help needed in the query [message #207581] Wed, 06 December 2006 02:27 Go to next message
grk28
Messages: 38
Registered: June 2006
Member
Hi

Here is my requirement.

I am having written a query which gives a cross tab report.

Item Jan Feb Mar Apr May Jun July Aug Sep Oct Nov Dec
Search Count 1 1 1 1 1
Search Res Count 1 1 1 1 1

I need to futher modify the query so that i get only last 6 months data with sysdate as bench mark..

something like this as this month of current date is december it should display last 6 months.


Item Jun July Aug Sep Oct Nov Dec
Search Count 1 1 1 1 1 1 1
Search Res Count 1 1 1 1 1 1 1


Can this be done thru query.

Immediate help would be appreciated.

Regards,
RK
Re: urgent help needed in the query [message #207592 is a reply to message #207581] Wed, 06 December 2006 02:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
select 'Item Jun July Aug Sep Oct Nov Dec
Search Count 1 1 1 1 1 1 1
Search Res Count 1 1 1 1 1 1 1 '
from dual

will give you exactly the right output.

Please read the sticky. It shows you what to post in order to get a decent answer.

[Updated on: Wed, 06 December 2006 02:43]

Report message to a moderator

Re: urgent help needed in the query [message #207686 is a reply to message #207592] Wed, 06 December 2006 10:49 Go to previous messageGo to next message
grk28
Messages: 38
Registered: June 2006
Member
Hi Frank,

I am very sorry for that.

Well here is my requirement

Here is my table structure with sample data.

SearchId Search Date
1 2006-01-02
2 2006-02-01
3 2006-03-02
4 2006-04-01
5 2006-05-02
6 2006-06-01
7 2006-07-02
8 2006-08-01
9 2006-09-02
10 2006-10-01
11 2006-11-02
12 2006-07-01
13 2006-07-01
14 2006-01-01
15 2006-01-10



Here goes my requirement

I need the count of searches happened in each month,provided it should display only last six months based on sysdate.I dont want other months to be displayed .

For example as we are in december

My output should be something like this.

Item Jul06 Aug06 Sep06 Oct06 Nov06 Dec06
Count 3 1 1 1 1 1

If i change my sysdate to Jun 2006

It should be

Item Jan06 Feb06 Mar06 Apr06 May06 Jun06
count 3 1 1 1 1 1

If i change my sysdate to Mar 2006

It should be

Item Oct05 Nov05 Dec05 Jan06 Feb06 Mar06
count 0 0 0 3 1 1

So everything should be dynamic.Can this be done through a query ?

Frank,i hope my requirement is clear this time.

So please help me out ..

I am extremely sorry for my earlier post.

Regards,
RK
Re: urgent help needed in the query [message #207717 is a reply to message #207686] Wed, 06 December 2006 14:12 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
For next time, even more convenient is to provide your data like this:

create table your_table (SearchId number, Search_Date date); 
insert into your_table values (1, to_date('2006-01-02','yyyy-mm-dd')); 
insert into your_table values (2, to_date('2006-02-01','yyyy-mm-dd'));  
insert into your_table values (3, to_date('2006-03-02','yyyy-mm-dd'));  
insert into your_table values (4, to_date('2006-04-01','yyyy-mm-dd'));  
insert into your_table values (5, to_date('2006-05-02','yyyy-mm-dd'));  
insert into your_table values (6, to_date('2006-06-01','yyyy-mm-dd'));  
insert into your_table values (7, to_date('2006-07-02','yyyy-mm-dd'));  
insert into your_table values (8, to_date('2006-08-01','yyyy-mm-dd'));  
insert into your_table values (9, to_date('2006-09-02','yyyy-mm-dd'));  
insert into your_table values (10, to_date('2006-10-01','yyyy-mm-dd'));  
insert into your_table values (11, to_date('2006-11-02','yyyy-mm-dd')); 
insert into your_table values (12, to_date('2006-07-01','yyyy-mm-dd'));  
insert into your_table values (13, to_date('2006-07-01','yyyy-mm-dd')); 
insert into your_table values (14, to_date('2006-01-01','yyyy-mm-dd'));  
insert into your_table values (15, to_date('2006-01-10','yyyy-mm-dd'));  


As for your request, try something like this:

SQL> SELECT COUNT(*)
  2        ,to_char(t.search_date
  3                ,'mm-yyyy')
  4    FROM your_table t
  5   WHERE months_between(SYSDATE
  6                       ,t.search_date) < 6
  7   GROUP BY to_char(t.search_date
  8                   ,'mm-yyyy')
  9  /

  COUNT(*) TO_CHAR(T.SEARCH_DATE,'MM-YYYY
---------- ------------------------------
         3 07-2006
         1 08-2006
         1 11-2006
         1 09-2006
         1 10-2006


What you can do now, is search for "pivot" on this forum to "swap" the columns and rows in order to get the output you need.
Previous Topic: sql/plsql
Next Topic: How to minus rows where the site id is blank from the total records between a from and to date.
Goto Forum:
  


Current Time: Sun Dec 04 16:53:38 CST 2016

Total time taken to generate the page: 0.11619 seconds