OraFAQ Forum: SQL & PL/SQL » Week in where clause

Home » SQL & PL/SQL » SQL & PL/SQL » Week in where clause

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

Week in where clause [message #627576]

Wed, 12 November 2014 15:14

Omother
Messages: 11
Registered: November 2014

Junior Member

Hi,

I am looking to create an output which gives me data by week number of the year.

following is my where clause criteria I need help with.

from date table
where report_week = last_week.

I need help creating this filter. I need to automate this so I cannot use week number.

I hope someone can help.

Thanks,
O'mother

Report message to a moderator

Re: Week in where clause [message #627577 is a reply to message #627576]

Wed, 12 November 2014 15:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

  1* select to_char(hiredate,'IW'), to_number(to_char(hiredate,'IW'))-1 last_week from emp
SQL> /

TO  LAST_WEEK
-- ----------
51         50
08          7
08          7
14         13
40         39
18         17
24         23
16         15
47         46
37         36
21         20

TO  LAST_WEEK
-- ----------
49         48
49         48
03          2

14 rows selected.

SQL>

Report message to a moderator

Re: Week in where clause [message #627578 is a reply to message #627577]

Wed, 12 November 2014 15:36

Omother
Messages: 11
Registered: November 2014

Junior Member

My column name which I am using is: report_week not report_date. for example

select report_week
from date_dim
where report_week = (current_week)-1

I need help with this part: report_week = (current_week)-1

Report message to a moderator

Re: Week in where clause [message #627579 is a reply to message #627578]

Wed, 12 November 2014 15:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>where report_week = (current_week)-1

where report_week = to_number(to_char(sysdate,'IW'))-1

Report message to a moderator

Re: Week in where clause [message #627580 is a reply to message #627579]

Wed, 12 November 2014 15:44

Omother
Messages: 11
Registered: November 2014

Junior Member

ORA-12801: error signaled in parallel query server P833, instance db-dw9-6001.iad6.amazon.com:dw9-1 (1)
ORA-01722: invalid number
12801. 00000 - "error signaled in parallel query server %s"
*Cause: A parallel query server reached an exception condition.
*Action: Check the following error message for the cause, and consult
your error manual for the appropriate action.
*Comment: This error can be turned off with event 10397, in which
case the server's actual error is signaled instead.

got this error

Report message to a moderator

Re: Week in where clause [message #627582 is a reply to message #627580]

Wed, 12 November 2014 16:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: Week in where clause [message #627605 is a reply to message #627580]

Thu, 13 November 2014 00:58

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

Senior Member
Account Moderator

Seems report_week is not a number.
But how could we when we have neither your table nor your actual code?

Report message to a moderator

Re: Week in where clause [message #627650 is a reply to message #627605]

Thu, 13 November 2014 12:37

Omother
Messages: 11
Registered: November 2014

Junior Member

I need to get data for last 6 weeks. I have to automate it so I cannot enter date range. This is my query:

select * from Date_dim where report_date_week >= sysdate(Week,-6);

Report_date_week shows week. Such as 2014 Week 14, 2014 Week 15, 2014 week 16.

This query is giving error.

Date_dim table is a date table and has data at calender day level too. I am joining this with agent table which has following data:

Agent_ID call_date

anc12 16-10-2014
lmo 24 17-10-2014

I need to get data where I can get call_date data for last 6 weeks at week level not by a day.

I hope I provided enough info.

Thanks,

Report message to a moderator

Re: Week in where clause [message #627651 is a reply to message #627650]

Thu, 13 November 2014 12:41

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

If I analyzed your post more closely, I may be able to figure out what you are saying, but I don't really have the time for a puzzle right now.
If you provided table descriptions with sample data and actual code run, with the output, it would take the mystery away.

[Updated on: Thu, 13 November 2014 12:43]

Report message to a moderator

Re: Week in where clause [message #627652 is a reply to message #627650]

Thu, 13 November 2014 12:44

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

Senior Member
Account Moderator

Quote:

I hope I provided enough info.

NO!
Read the links that have been provided and provide a test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Week in where clause [message #627654 is a reply to message #627650]

Thu, 13 November 2014 12:49

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Omother wrote on Thu, 13 November 2014 12:37

I need to get data for last 6 weeks. I have to automate it so I cannot enter date range. This is my query:

select * from Date_dim where report_date_week >= sysdate(Week,-6);

Report_date_week shows week. Such as 2014 Week 14, 2014 Week 15, 2014 week 16.

So it appears that report_date_week is a varchar containing a character string. A charcter string that YOU see as specifying some date information, but Oracle sees as no different than the character string "Here's your sign.".

Quote:

This query is giving error.

I don't see any error. If you have one, why not post it .. in context.

Quote:

Date_dim table is a date table and has data at calender day level too. I am joining this with agent table which has following data:

Agent_ID call_date

anc12 16-10-2014
lmo 24 17-10-2014

I need to get data where I can get call_date data for last 6 weeks at week level not by a day.

I hope I provided enough info.

Thanks,

No, you are NOT providing enough. you are providing your interpretation of what you have. People have been asking for actual table definitions. We speak SQL (as in CREATE TABLE .) We assume you do, as well.

But what I can tell is you seem to not understand the difference between DATE and string of characters that humans (but not computers) recognize as representing a date.

see: - But I want to store my date as ...

Report message to a moderator

Re: Week in where clause [message #627655 is a reply to message #627651]

Thu, 13 November 2014 12:57

Omother
Messages: 11
Registered: November 2014

Junior Member

Let's try this:

I have two tables.

Table A

Agent_id call_date Agent_survey_result

lmo12 11-10-2014 6
QZC 10-10-2014 4
FRM45 01-10-2014 10
RLM89 01-09-2014 12

TABLE B

CALENDAR_DATE REPORT_WEEK
11-10-2014 2014 Week 41
10-10-2014 2014 Week 41
01-10-2014 2014 Week 40
01-09-2014 2014 Week 35

select report_week, Agent_survey_result
from table A A,
table B B
where a.call_date = B.calendar_date
and report_week = current_week - 6;

(current_week - 6) - I need to get data for past 6 weeks. I don't want to write query saying activity_date between sysdate -42 and sysdate because I want my week to be set between Sunday and Satuday and I need data ouput for past 6 weeks.

I hope this helps.

Thanks,

Report message to a moderator

Re: Week in where clause [message #627656 is a reply to message #627655]

Thu, 13 November 2014 13:00

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

Senior Member
Account Moderator

What don't you understand in "provide CREATE TABLE and INSERT statements"?
What don't you understand in the links we gave you?
If you don't give what we ask, if you don't make any effort in helping us to help you, why should we make any effort to help you?
Do you deserve to be helped?

Report message to a moderator

Re: Week in where clause [message #627657 is a reply to message #627656]

Thu, 13 November 2014 13:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> I want my week to be set between Sunday and Satuday and I need data ouput for past 6 weeks.
If report is run on 16 November 2014, what are the start date & end date for the report?
If report is run on 17 November 2014, what are the start date & end date for the report?
If report is run on 18 November 2014, what are the start date & end date for the report?
If report is run on 19 November 2014, what are the start date & end date for the report?
If report is run on 20 November 2014, what are the start date & end date for the report?
If report is run on 21 November 2014, what are the start date & end date for the report?
If report is run on 22 November 2014, what are the start date & end date for the report?

If you don't answer our questions, then why should we answer your questions?

Report message to a moderator

Re: Week in where clause [message #627665 is a reply to message #627657]

Thu, 13 November 2014 16:02

Bill B
Messages: 1971
Registered: December 2004

Senior Member

assuming that week starts on sunday

select report_week, Agent_survey_result
from table_A A,
table_B B
where a.call_date = B.calendar_date
and B.calendar_date >= next_day(TRUNC(SYSDATE)-7,'SUN')- 42;

[Updated on: Thu, 13 November 2014 16:03]

Report message to a moderator

Re: Week in where clause [message #627666 is a reply to message #627665]

Thu, 13 November 2014 19:11

Omother
Messages: 11
Registered: November 2014

Junior Member

Thank you Bill B.

This is exactly what I was looking for. I also need to set my query to give me output for last 6 weeks but also for the same time last year. This is for YOY comparison. I hope you can help.

Report message to a moderator

Re: Week in where clause [message #627667 is a reply to message #627666]

Thu, 13 November 2014 20:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> but also for the same time last year.
WHY do you keep changing the requirements?
Explain with specific examples how above meshes with the requirement of Sunday through Saturday week

Report message to a moderator

Re: Week in where clause [message #627699 is a reply to message #627667]

Fri, 14 November 2014 18:14

Omother
Messages: 11
Registered: November 2014

Junior Member

I am not trying to ignore anyone's question but at times I get your responses after I reply to anyone's question.

report_week is a varchar.
call_date is a date.

My report week is between Sunday and Saturday. If I need to extract data then I need data from Sunday to Saturday. That's why it helped. If I run my query between (sysdate -7) and sysdate then it will not adjust my day report date from Sunday to Saturday.

My requirement is to get call_date data data for last 6 weeks for 2014 and also for same 6 weeks for 2013 for comparison. I also need same call date data from YTD 2014 and 2013.

Thanks,

[Updated on: Fri, 14 November 2014 18:25]

Report message to a moderator

Re: Week in where clause [message #627700 is a reply to message #627699]

Fri, 14 November 2014 18:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>My requirement is to get call_date data data for last 6 weeks for 2014 and also for same 6 weeks for 2013 for comparison.
WHY do you keep changing the requirements?

>My report week is between Sunday and Saturday.
Last 6 weeks (Sunday-Saturday) in 2014 contain different dates than last 6 weeks (Sunday-Saturday) in 2013.
It appears you want to compare 42 arbitrary dates against 42 different arbitrary dates.

Report message to a moderator

Re: Week in where clause [message #627701 is a reply to message #627700]

Fri, 14 November 2014 18:41

Omother
Messages: 11
Registered: November 2014

Junior Member

Yes, you are correct. I want to compare numbers between week 36 and 42 of 2014 and week 36 an 42 of 2013. I need same YOY comparison for 2014 and 2013 for example: Jan-October 2014 and Jan -October 2013 comparison.

I am done with rest of my query but date is holding me back.

[Updated on: Fri, 14 November 2014 18:43]

Report message to a moderator

Re: Week in where clause [message #627702 is a reply to message #627701]

Fri, 14 November 2014 18:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I want to compare numbers between week 36 and 42 of 2014 and week 36 an 42 of 2013.
WHY do you keep changing the requirements?

> I need same YOY comparison for 2014 and 2013 for example: Jan-October 2014 and Jan -October 2013 comparison.

This is about the SIXTH different & unique result set that you said was needed.
Each requirement will require its own SELECT to produce the desired results.

Report message to a moderator

Re: Week in where clause [message #627703 is a reply to message #627702]

Fri, 14 November 2014 18:56

Omother
Messages: 11
Registered: November 2014

Junior Member

BlackSwan - Maybe this is what I needed. I was trying to see if I can merge them all in one but will do that. I was avoiding to write multiple queries for same result sent. Thanks for your suggestion.

Report message to a moderator

Re: Week in where clause [message #627704 is a reply to message #627703]

Fri, 14 November 2014 19:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT & FROM clause may be the same. but
WHERE clause depends upon the desired range of data to be returned.

Report message to a moderator

Re: Week in where clause [message #627705 is a reply to message #627704]

Fri, 14 November 2014 19:37

Omother
Messages: 11
Registered: November 2014

Junior Member

Where for date is what is holding me back.

I am trying to think how to extract data for last 6 weeks for 2013.

My date table has following data:

Calendar_date- date - 12-08-2014
calendar_mth - number - 8
calendar_qtr - number - 3
calendar_week number - 32
day_of_month number - 12
day of year Number - 224
report_name of the week - Varchar - e.g - 2014 Week 50, 2013 Week 50
report name of the year - varchar - e.g; 2014,2013

Report message to a moderator

Re: Week in where clause [message #627707 is a reply to message #627705]

Fri, 14 November 2014 20:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I am trying to think how to extract data for last 6 weeks for 2013.
What is start date for this period & why is this the correct date?
START_DATE+42 is END_DATE

Which are last 6 weeks in 2014?
What is start date for this period & why is this the correct date?

does last six weeks always start with 52 - 6 = week 46?

Report message to a moderator

Re: Week in where clause [message #627709 is a reply to message #627707]

Fri, 14 November 2014 20:20

Omother
Messages: 11
Registered: November 2014

Junior Member

last 6 weeks in 2014 will be running weeks. for example: this week last 6 weeks will be between week 36 and 46 of 2014 and for next week it will be between 37 and 47. I wrote this query for it so far.

(calendar_date between (trunc(sysdate - (to_char(sysdate,'IW')+6))) and (trunc(sysdate)))

I need to write similar query to get 2013 data.

Report message to a moderator

Re: Week in where clause [message #627710 is a reply to message #627709]

Fri, 14 November 2014 20:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

LAST is LAST; not rolling window

do you mean previous 6 weeks from today?

Since you have repeated declined to answer directly any question I have asked you, I am choosing to "ignore all messages by this user"
PLONK!

Report message to a moderator

Re: Week in where clause [message #627718 is a reply to message #627709]

Sat, 15 November 2014 00:40

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

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 13 November 2014 20:00

What don't you understand in "provide CREATE TABLE and INSERT statements"?
What don't you understand in the links we gave you?
If you don't give what we ask, if you don't make any effort in helping us to help you, why should we make any effort to help you?
Do you deserve to be helped?

Report message to a moderator

Previous Topic:	Question about tracing Web Applications SQL statements by using oracle AUDIT command
Next Topic:	Sync master and local database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 15:45:46 CDT 2024