Home » SQL & PL/SQL » SQL & PL/SQL » display data based on certain criteria
display data based on certain criteria [message #248715] Sat, 30 June 2007 23:28 Go to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
hi,
my table contains the data as below

table1
(mm/dd/yyyy)
id codename bdate
1 a 10/2/2006
2 a 11/2/2006
3 b 10/2/2006
4 c 12/2/2006
5 c 1/2/2007
6 c 2/2/2007
7 a 4/5/2007
8 b 7/1/2007
9 c 6/30/2007

here I want my data as below.bdate should be checked in the where condition dynamically I mean date ranges should be every 6 months and it should check till the current date.

my bdate to check the ranges is fixed initially.I mean first date starts from 9/30/2006 and from there every 6 months. I need to check, till the current date.

o/p should be as below .count of codename should be displayed based on date ranges.
a codename count is 2 between the dates 9/30/2006 to 3/31/2007.
the range above which I specified falls under 1st term of 2007
and between 4/1/2007 to 9/30/2007, it falls under 2nd term of 2007.the query should work based on the current date.

codename (a) codename (b) codename (c)
2 1 3
1 1 1

the 1st record date range falls between the period 9/30/2006 to 3/31/2007 which is 1st term 2007 and second record date range falls between 4/1/2007 to 9/30/2007 which is second term 2007.the query should be generated according to the current date.
now current date is 1st july 2007 and the records existing till july 1st 2007.If records are added to database after 9/30/2007 to table1.
then those records fall in 1st term of 2008.so when the query is executed one more row should be displayed automatically with count of codename based on the group of codename.
I mean codename should be grouped and should be displayed as shown above.Is it possible in a single query,if so plz. send me or else if stored proc also, no probs.I need it a little urgent.

thanks.

Re: display data based on certain criteria [message #248716 is a reply to message #248715] Sat, 30 June 2007 23:38 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
HUH?
I saw many words, but am totally unclear what you desire.
It appears you did not read or IGNORED the posting guidelines as enumerated in the #1 STICKY post at the top of this forum.

>I need it a little urgent.
Please elaborate why it is URGENT for me to solve this problem for you.

Based upon what you posted I wonder if you can even spell S-Q-L.
Re: display data based on certain criteria [message #248719 is a reply to message #248715] Sat, 30 June 2007 23:53 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
I wrote my query clearly, can you plz. read it once again.
can you tell me where you are unclear.

thanks
Re: display data based on certain criteria [message #248720 is a reply to message #248715] Sun, 01 July 2007 00:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
In the STCKY post in section in BOLD as POSTING GUIDELINES
#5 - #10
maybe I can't see straight, but which of those did your post meet?

What datatype is bdate (string, date, etc.)?

>here I want my data as below.bdate should be checked

Upon what condition? Sunrise, Paris Hilton getting arrested, KC Royal winning the MLB WS, the cow jumping over the moon, other?

>I need it a little urgent.
Please elaborate why it is URGENT for me to solve this problem for you.

[Updated on: Sun, 01 July 2007 00:28] by Moderator

Report message to a moderator

Re: display data based on certain criteria [message #248724 is a reply to message #248715] Sun, 01 July 2007 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I understand, you want the count of each codename group by 6 months period.
Just a question: why 09/30 falls in both periods?

Just a clue and you should be able to write the query:
SQL> select case 
  2         when extract(month from sysdate) between 4 and 9 then 2
  3         else 1
  4         end period
  5  from dual
  6  /
    PERIOD
----------
         2

1 row selected.

Btw, Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

In addition, be polite and patient with people than can help you otherwise You'll be On Your Own (YOYO).

Regards
Michel

[Updated on: Sun, 01 July 2007 01:18]

Report message to a moderator

Re: display data based on certain criteria [message #248725 is a reply to message #248715] Sun, 01 July 2007 01:24 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
hi,
sorry it is 10/1/2006
1st term is from
10/1/2006 to 3/31/2007

2nd term from
4/1/2007 to 9/30/2007

can you plz. send me the query.
thanks
Re: display data based on certain criteria [message #248726 is a reply to message #248715] Sun, 01 July 2007 01:35 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
I am sending it as an attachment.

thanks
  • Attachment: query.doc
    (Size: 22.00KB, Downloaded 188 times)
Re: display data based on certain criteria [message #248727 is a reply to message #248715] Sun, 01 July 2007 01:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>can you plz. send me the query.
The correct query is "May I supersize that for you?"
Re: display data based on certain criteria [message #248728 is a reply to message #248725] Sun, 01 July 2007 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you please:
- answer the question (version)
- don't use IM speak (plz)
- try it yourself (I gave you the core)

Regards
Michel

[Updated on: Sun, 01 July 2007 01:37]

Report message to a moderator

Re: display data based on certain criteria [message #248738 is a reply to message #248726] Sun, 01 July 2007 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use doc, xls, or any other file that can contain virus.
Many of us have firewall/proxy that doesn't allow to download it.
Most of us are reluctant to download something that can endanger their workstation. Are they wrong?

Regards
Michel
Re: display data based on certain criteria [message #248847 is a reply to message #248715] Mon, 02 July 2007 07:40 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
If it is urgent, then hire someone to do it for you. There is no such thing as urgent on this forum
Re: display data based on certain criteria [message #249989 is a reply to message #248715] Fri, 06 July 2007 12:40 Go to previous message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
stevefaulk wrote on Sat, 30 June 2007 23:28
...

codename (a) codename (b) codename (c)
2 1 3
1 1 1
...



SQL> with t as (select 1 id, 'a' codename, to_date('10/2/2006','mm/dd/yyyy') bdate from dual union all
  2           select 2 id, 'a' codename, to_date('11/2/2006','mm/dd/yyyy') bdate from dual union all
  3           select 3 id, 'b' codename, to_date('10/2/2006','mm/dd/yyyy') bdate from dual union all
  4           select 4 id, 'c' codename, to_date('12/2/2006','mm/dd/yyyy') bdate from dual union all
  5           select 5 id, 'c' codename, to_date('1/2/2007','mm/dd/yyyy') bdate from dual union all
  6           select 6 id, 'c' codename, to_date('2/2/2007','mm/dd/yyyy') bdate from dual union all
  7           select 7 id, 'a' codename, to_date('4/5/2007','mm/dd/yyyy') bdate from dual union all
  8           select 8 id, 'b' codename, to_date('7/1/2007','mm/dd/yyyy') bdate from dual union all
  9           select 9 id, 'c' codename, to_date('6/30/2007','mm/dd/yyyy') bdate from dual)
 10           select max(a) a, max(b) b, max(c) c from (
 11           select trunc(decode(mod(to_char(bdate,'q'),2),1,add_months(bdate,-3),bdate),'q') half_year,
 12                  decode(codename,'a',count(1)) a,
 13                  decode(codename,'b',count(1)) b,
 14                  decode(codename,'c',count(1)) c
 15                    from t
 16           where bdate>to_date('9/30/2006','mm/dd/yyyy')
 17           group by codename, trunc(decode(mod(to_char(bdate,'q'),2),1,add_months(bdate,-3),bdate),'q')
 18           )
 19           group by half_year
 20  /

         A          B          C
---------- ---------- ----------
         2          1          3
         1          1          1

SQL> 
Previous Topic: decoce of two columns
Next Topic: sen Mail
Goto Forum:
  


Current Time: Sat Dec 10 20:44:51 CST 2016

Total time taken to generate the page: 0.07430 seconds