Home » SQL & PL/SQL » SQL & PL/SQL » Sql query data to filter in the week & Monthly wise
Sql query data to filter in the week & Monthly wise [message #442505] Mon, 08 February 2010 13:26 Go to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Hello all

I need to generate week wise as well month wise data from the following report.

we are passing this as the input parameters.

Start date -01/01/2010

End date -15/02/2010

based on the input parameter the report displays some data

Worked date Names task status Avg elapsed time
01/01/2010 Win Completed 1

12/01/2010 erwin Completed 2

13/01/2010 gertin Completed 0.5

14/01/2010 gerald Completed 3

15/01/2010 suman Completed 4

17/01/2010 williams elapsed 0

18/02/2010 ian elapsed 4

01/03/2010 thoma elapsed 3

05/03/2010 irvin elapsed 1

09/03/2010 ian elapsed 4

10/03/2010 wei ling elapsed 3

11/03/2010 sam elapsed 6


For the above data
User requires data to be filtered in the week or Month or Year for the given start date and end date and he needs to be formatted in this way.
Worked date Names task status Avg elapsed time
01/01/2010 Win Completed 1

02/01/2010 0 0 0

03/01/2010 0 0 0
04/01/2010 0 0 0
05/01/2010 0 0 0
06/01/2010 0 0 0
07/01/2010 0 0 0

.......

like wise if the user selects Month wise
Month wise data has to be populated based on the start date and end date.

Can some one thru some valuable inputs in this regard.



Re: Sql query data to filter in the week & Monthly wise [message #442506 is a reply to message #442505] Mon, 08 February 2010 13:30 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.


On which Day Of Week, does week start?
Re: Sql query data to filter in the week & Monthly wise [message #442507 is a reply to message #442506] Mon, 08 February 2010 13:39 Go to previous messageGo to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Hi BlackSwan

The week startdate or Day is nothing but the input parameter start date which is 01/01/2010

Please let me know if you required some more details
Re: Sql query data to filter in the week & Monthly wise [message #442509 is a reply to message #442507] Mon, 08 February 2010 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think BlackSwan's post was clear:
Post a working Test case: create table and insert statements along with the result you want with these data and explain this result.
Also 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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
And always post your Oracle version with 4 decimals.

Regards
Michel

Regards
Michel
Re: Sql query data to filter in the week & Monthly wise [message #442612 is a reply to message #442505] Tue, 09 February 2010 07:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to create a row generator to give you the missing rows.

If you add an inline view like this to your query:
SELECT p_start_date +(level-1) dte
FROM   dual
CONNECT BY level <= (p_end_date-p_start_date)

and then outer join your query to this one, joining by date then you should get the answer you're looking for.
Re: Sql query data to filter in the week & Monthly wise [message #442966 is a reply to message #442505] Thu, 11 February 2010 13:23 Go to previous messageGo to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Hello Blackswan

I Have attached the test data for my requirement.

SQL> DESC TEMP;
Name Null? Type
----------------------------------------- -------- ----------------------------
STARTDATE DATE
COMPLETIONDATE DATE
TASKDETAILS VARCHAR2(64)
TASKSTATUS VARCHAR2(64)
ELAPSEDTIME NUMBER

sql>select startdate,completiondate,taskdetails,taskstatus,elapsedtime from temp where startdate > to_date('22/01/2010','dd/mm/yyyy')
and completiondate < to_date('10/01/2010','dd/mm/yyyy')
and taskdetails ='Completed.

STARTDATE COMPLETIO TASKDETAILS TASKSTATUS ELAPSE
--------- --------- ---------------------------------------------------------------- ---------------
06-FEB-10 07-FEB-10 Boarding Pack Completed
06-FEB-10 07-FEB-10 Live Proving DMR completed
25-JAN-10 25-JAN-10 Facts Uploaded Completed
25-JAN-10 25-JAN-10 Facts Uploaded Completed
26-JAN-10 26-JAN-10 Facts Uploaded Completed
26-JAN-10 26-JAN-10 Facts Uploaded Completed
01-FEB-10 01-FEB-10 Enrolment Message Received Completed
01-FEB-10 01-FEB-10 Facts Uploaded Completed
02-FEB-10 02-FEB-10 Facts Uploaded Completed
03-FEB-10 03-FEB-10 Address Request Completed

10 rows selected.

The Requirement is user requires the data to be segregated in the period wise based on the week ,month,year which is based upon the start date and the completion dates.How ever there are no week and month , year columns data stored in the database .

All i need to know is how to separate the data based on periods.

please look into the attached report output.

Thanks
joe
Re: Sql query data to filter in the week & Monthly wise [message #442968 is a reply to message #442966] Thu, 11 February 2010 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 08 February 2010 21:04
I think BlackSwan's post was clear:
Post a working Test case: create table and insert statements along with the result you want with these data and explain this result.
Also 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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
And always post your Oracle version with 4 decimals.

Regards
Michel

Re: Sql query data to filter in the week & Monthly wise [message #442973 is a reply to message #442968] Thu, 11 February 2010 14:02 Go to previous messageGo to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Hello all

I need to generate week wise as well month wise data from the following report.

we are passing this as the input parameters.

Start date -22/01/2010

End date -10/02/2010

based on the input parameter the report displays some data


I Have attached the test data for my requirement.

SQL> DESC TEMP;
Name Null? Type
----------------------------------------- -------- ----------------------------
STARTDATE DATE
COMPLETIONDATE DATE
TASKDETAILS VARCHAR2(64)
TASKSTATUS VARCHAR2(64)
ELAPSEDTIME NUMBER

sql>select startdate,completiondate,taskdetails,taskstatus,elapsedtime from temp where startdate > to_date('22/01/2010','dd/mm/yyyy')
and completiondate < to_date('10/01/2010','dd/mm/yyyy')
and taskdetails ='Completed.

STARTDATE COMPLETIO TASKDETAILS TASKSTATUS ELAPSE
--------- --------- ---------------------------------------------------------------- ---------------
06-FEB-10 07-FEB-10 Boarding Pack Completed
06-FEB-10 07-FEB-10 Live Proving DMR completed
25-JAN-10 25-JAN-10 Facts Uploaded Completed
25-JAN-10 25-JAN-10 Facts Uploaded Completed
26-JAN-10 26-JAN-10 Facts Uploaded Completed
26-JAN-10 26-JAN-10 Facts Uploaded Completed
01-FEB-10 01-FEB-10 Enrolment Message Received Completed
01-FEB-10 01-FEB-10 Facts Uploaded Completed
02-FEB-10 02-FEB-10 Facts Uploaded Completed
03-FEB-10 03-FEB-10 Address Request Completed

10 rows selected.

Requirement
user requires the data to be segregated in the period wise based on the week ,month,year which is based upon the start date and the completion dates.How ever there are no week and month, year columns data stored in the database .

All i need to know is how to separate the data based on periods.

Appreciated someone's valuable inputs in this regard.

please look into the attached report output format.

Thanks
Re: Sql query data to filter in the week & Monthly wise [message #442974 is a reply to message #442973] Thu, 11 February 2010 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which part of the following post don't you understand?

Michel Cadot wrote on Thu, 11 February 2010 20:31
Michel Cadot wrote on Mon, 08 February 2010 21:04
I think BlackSwan's post was clear:
Post a working Test case: create table and insert statements along with the result you want with these data and explain this result.
Also 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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
And always post your Oracle version with 4 decimals.

Regards
Michel


Re: Sql query data to filter in the week & Monthly wise [message #442980 is a reply to message #442974] Thu, 11 February 2010 17:04 Go to previous messageGo to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Mike

i hope i have Fulfilled the requirements of the post as you notified.my apolozies if i have missed anyone of them.

i also hope that the clarification which i need is clear and lucid.please let me know if i need to elaborate further.
Re: Sql query data to filter in the week & Monthly wise [message #442981 is a reply to message #442973] Thu, 11 February 2010 17:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This should give you a pointer in the right direction:
create table test_003 (col_1 date, col_2 varchar(30));

insert into test_003 values(trunc(sysdate),'John');
insert into test_003 values(trunc(sysdate)+3,'Bill');
insert into test_003 values(trunc(sysdate)+6,'Algernon');
insert into test_003 values(trunc(sysdate)+8,'Stuart');

SQL> select x.dte,t.col_2
  2  from   test_003 t
  3        ,(select (level-1)+trunc(sysdate,'MM') dte
  4          from   dual
  5          connect by level <=30) x
  6  where  x.dte = t.col_1(+)
  7  order by x.dte;

DTE       COL_2
--------- ------------------------------
01-FEB-10
02-FEB-10
03-FEB-10
04-FEB-10
05-FEB-10
06-FEB-10
07-FEB-10
08-FEB-10
09-FEB-10
10-FEB-10
11-FEB-10 John
12-FEB-10
13-FEB-10
14-FEB-10 Bill
15-FEB-10
16-FEB-10
17-FEB-10 Algernon
18-FEB-10
19-FEB-10 Stuart
20-FEB-10
21-FEB-10
22-FEB-10
23-FEB-10
24-FEB-10
25-FEB-10
26-FEB-10
27-FEB-10
28-FEB-10
01-MAR-10
02-MAR-10

30 rows selected.
Re: Sql query data to filter in the week & Monthly wise [message #443039 is a reply to message #442980] Fri, 12 February 2010 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
hemalatha_81 wrote on Fri, 12 February 2010 00:04
Mike

i hope i have Fulfilled the requirements of the post as you notified.


Quote:
create table and insert statements along with the result you want with these data

Don't see them in your posts.

Quote:
align the columns in result.
Use the "Preview Message" button to verify.

Do you really see your columns aligned in your posts?

Quote:
And always post your Oracle version with 4 decimals

Don't see it in your posts.

I hope I have answered your question.

Regards
Michel
Re: Sql query data to filter in the week & Monthly wise [message #443045 is a reply to message #442981] Fri, 12 February 2010 04:25 Go to previous messageGo to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Hello Jrow,

A Thanks for providing the reply for my post.However my requirement is quite different.

The requirement is segregating of data on the periodwise based on the input parameters which is the start date and completiondate and Bifurcation of the data is on the weekly, Monthly and year basis
Between the startdate and completiondate.

For Example
from the report output I need is the taskdetails data has to be filtered on the period wise which is week, Month and year based on the startdate and the completiondate.
However the End users has to be prompt the option to display it in a week, Month and year.

If startdate -01/01/2010
Completiondate -12/02/2010

I need the task details data segregating which is 6weeks data between the startdate and Completiondate.
And if the user prompts for the Month the data has to be segregated for Month-Jan and For the Month-Feb'12 2010.

Please look into the attached report output where you will get the clear idea.

please let me know how these requirement can be accomplished

Please let me know if you require some more additional information.
Re: Sql query data to filter in the week & Monthly wise [message #443047 is a reply to message #443045] Fri, 12 February 2010 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please let me know if you require some more additional information.

All those I previously mentioned, 4 times yet.

Regards
Michel

[Updated on: Fri, 12 February 2010 04:26]

Report message to a moderator

Re: Sql query data to filter in the week & Monthly wise [message #443079 is a reply to message #443047] Fri, 12 February 2010 08:04 Go to previous messageGo to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Hi Michel

My Apologizes for the Misinterpretation as well annoying your patience. I Hope i have taken consideration of all your Notification upon the post.Only the Align of Columns is still improper even though i have tried my level best.

Here is the test result.
SQL> Create table temp1
  2  ( startdate  date,
  3     Completiondate  date,
  4     Taskdetails   VARCHAR2(100),
  5     TASKSTATUS    VARCHAR2(100));
Table created

SQL> Insert into temp1 values ( to_date ('10/01/2010','dd/mm/yyyy'),to_date('10/01/2010','dd/mm/yyyy'),'BLAR PACK_LOADED','Completed');

SQL> Insert into temp1 values ( to_date ('12/01/2010','dd/mm/yyyy'),to_date('12/01/2010','dd/mm/yyyy'),'BLAR_PACK UNLOADED', 'Completed' );

sQL> Insert into temp1 values ( to_date ('14/01/2010','dd/mm/yyyy'),to_date('15/01/2010','dd/mm/yyyy'),'ABC_BLAR_PACK', 'Completed');

SQL> Insert into temp1 values ( to_date ('15/01/2010','dd/mm/yyyy'),to_date('16/01/2010','dd/mm/yyyy'),'BSCK_BLAR_PACK', 'Completed');


SQL> Insert into temp1 values ( to_date ('20/01/2010','dd/mm/yyyy'),to_date('21/01/2010','dd/mm/yyyy'),'BLAR_PACK1', 'Completed');


SQL> Insert into temp1 values ( to_date ('22/01/2010','dd/mm/yyyy'),to_date('23/01/2010','dd/mm/yyyy'),'BLAR_PACK1', 'Completed');

SQL> Insert into temp1 values ( to_date ('25/01/2010','dd/mm/yyyy'),to_date('26/01/2010','dd/mm/yyyy'),'BLAR_PACK3', 'Completed');

SQL> Insert into temp1 values ( to_date ('01/02/2010','dd/mm/yyyy'),to_date('02/02/2010','dd/mm/yyyy'),'BLAR_PACK3', 'Completed');

SQL> Insert into temp1 values ( to_date ('03/02/2010','dd/mm/yyyy'),to_date('04/02/2010','dd/mm/yyyy'),'BLAR_PACK1', 'Completed');

SQL> Insert into temp1 values ( to_date ('06/02/2010','dd/mm/yyyy'),to_date('07/02/2010','dd/mm/yyyy'),'BLAR_PACK1', 'Completed');

SQL> SELECT STARTDATE,COMPLETIONDATE,TASKDETAILS,TASKSTATUS FROM TEMP1

  2  WHERE STARTDATE  >= TO_DATE('10/01/2010','DD/MM/YYYY')

  3  AND COMPLETIONDATE < to_date('10/02/2010','DD/MM/YYYY')

  4  AND TASKSTATUS ='Completed';

STARTDATE  COMPLETIO  TASKDETAILS        TASKSTATUS

--------- --------- --------------------------------------------------------------------------------

10-JAN-10  10-JAN-10  BLAR PACK_LOADED     Completed

12-JAN-10  12-JAN-10  BLAR_PACK UNLOADED   Completed

14-JAN-10  15-JAN-10  ABC_BLAR_PACK       Completed

15-JAN-10  16-JAN-10  BSCK_BLAR_PACK      Completed

20-JAN-10  21-JAN-10  BLAR_PACK1          Completed

22-JAN-10  23-JAN-10  BLAR_PACK1          Completed

25-JAN-10  26-JAN-10  BLAR_PACK3          Completed
 
01-FEB-10  02-FEB-10  BLAR_PACK3          Completed

03-FEB-10  04-FEB-10  BLAR_PACK1          Completed

06-FEB-10  07-FEB-10  BLAR_PACK1          Completed

 

10 rows selected.



Requirment:The requirement is grouping of data merely on the periodwise which is weekly,Monthly and Yearly which should taken into the account Between the input parameters startdate and completiondate.

For Example from the report output
The taskdetails data has to be filtered on the period wise which is week, Month and year based on the startdate and the completiondate.
However the End users has to be given a prompt for selecting the option to display it in a week, Month and year.

If startdate -10/01/2010
Completiondate -10/02/2010


I need the task details data to be segregated which is 4 weeks data between the startdate and Completiondate.
And if the user prompts for the Month the data has to be segregated for Month-Jan and For the Month-Feb'10 2010.


Please look into the attached report output where you will get the clear idea.




CM - replaced ALIGN tags with CODE tags - which is what the orafaq forum guide tells you to use, which is why Michel keeps telling you to read it.

[Updated on: Fri, 12 February 2010 12:33] by Moderator

Report message to a moderator

Re: Sql query data to filter in the week & Monthly wise [message #443092 is a reply to message #443079] Fri, 12 February 2010 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

[Updated on: Fri, 12 February 2010 09:34]

Report message to a moderator

Re: Sql query data to filter in the week & Monthly wise [message #443104 is a reply to message #443092] Fri, 12 February 2010 11:17 Go to previous messageGo to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Blackswan

Expected results

From the Result set it has to be grouped on the Weekly,Monthly and Yearly Basis based upon the Input parameters.

Week1
Taskdetails quantity completed
BLAR PACK_LOADED 2
ABC_BLAR_PACK 1

Week2
Taskdetails quantity completed
BSCK_BLAR_PACK 1

Week3
Taskdetails quantity completed
BLAR_PACK1 2
BLAR_PACK3 2
------------------------------------------
if is for Month then

Month-Jan

Taskdetails quantity completed
BLAR PACK_LOADED 2
ABC_BLAR_PACK 1
BSCK_BLAR_PACK 1
BLAR_PACK1 2
BLAR_PACK3 2

These is the result format which i expect.
Kindly provide me the valuable inputs.
Re: Sql query data to filter in the week & Monthly wise [message #443106 is a reply to message #443104] Fri, 12 February 2010 11:46 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Taskdetails quantity completed
>BLAR PACK_LOADED 2
>BLAR_PACK3 2

I do not understand how/where you get the totals above for test data for January.

Why is not "BLAR_PACK UNLOADED" included in January totals
Re: Sql query data to filter in the week & Monthly wise [message #443112 is a reply to message #443106] Fri, 12 February 2010 12:50 Go to previous messageGo to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Blackswan,
i am extremely Sorry for missing data

Result Format should be like This
Week1 (10/01/2010-17/10/2010)
Taskdetails quantity completed
BLAR PACK_LOADED 1
BLAR_PACK UNLOADED 1
ABC_BLAR_PACK 1
BSCK_BLAR_PACK 1

Week2 (18/01/2010-25/10/2010)
Taskdetails quantity completed
BLAR_PACK1 2
BLAR_PACK3 1

Week3(26/10/2010-02/02/2010)
Taskdetails quantity completed
BLAR_PACK3 1

------------------------------------------
if is for Month then
Month-jan
Taskdetails quantity completed
BLAR PACK_LOADED 1
BLAR_PACK UNLOADED 1
ABC_BLAR_PACK 1
BSCK_BLAR_PACK 1
BLAR_PACK1 2
BLAR_PACK3 1

Month-Feb


Taskdetails quantity completed

BLAR_PACK1 2
BLAR_PACK3 1

The Count(taskdetails) will give the quantity completed which is grouped for respective Period of week or Month or year.
Re: Sql query data to filter in the week & Monthly wise [message #443115 is a reply to message #443112] Fri, 12 February 2010 13:30 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
below is a way to obtain results
  1  select to_char(startdate,'MM') MM, taskdetails, count(*)
  2  from temp1
  3  group by to_char(startdate,'MM'), taskdetails
  4* order by 1, 2
SQL> /

MM TASKDETAILS		      COUNT(*)
-- ------------------------ ----------
01 ABC_BLAR_PACK		     1
01 BLAR PACK_LOADED		     1
01 BLAR_PACK UNLOADED		     1
01 BLAR_PACK1			     2
01 BLAR_PACK3			     1
01 BSCK_BLAR_PACK		     1
02 BLAR_PACK1			     2
02 BLAR_PACK3			     1

8 rows selected.

Re: Sql query data to filter in the week & Monthly wise [message #443170 is a reply to message #443115] Sat, 13 February 2010 03:55 Go to previous message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Black swan.

Thanks for Providing Some Inputs.However the result set which i expect was little Different.

The Sql query you have written is giving the sum of the totals
of the all the MonthS based upon the Input paramters.

Whereas My expected result is Sum of the Totals to be Segregated or divided on the weekly basis with the Report Headers as week1 , week2 and Week3 .As well as Month1 and Month2 based upon the input paramters.

Actually we are Providing the user with the Prompt of Week,Month and Year ,If he selects the week or Month or Year.The data has to be grouped on the respective periods and the display of the data in the resultset has to be the sum of totals for the selected periods only with report headers .

Please let me know if you need some more additional Informations.

Kindly please provide me the valuable inputs.

[Updated on: Sat, 13 February 2010 04:01]

Report message to a moderator

Previous Topic: can we do it with one update statement for updating the same column with different conditions
Next Topic: how can increase the performance of existing procedure?
Goto Forum:
  


Current Time: Fri Dec 02 16:32:32 CST 2016

Total time taken to generate the page: 0.37104 seconds