Home » SQL & PL/SQL » SQL & PL/SQL » Weeks, Quarters, and Counts (11g)
Weeks, Quarters, and Counts [message #664605] Mon, 24 July 2017 08:24 Go to next message
jmltinc
Messages: 14
Registered: January 2016
Junior Member
Hi Folks,

I have been circling for a week with no solution. Perhaps an expert can help.

I have three tables: One contains a product model, the second has a Primary Key referencing a third table that contains my data. The third table contains a field (ATE_YIELD) that is used in a WHERE condition to indicate a record to count. I have:
SELECT COUNT(ATE_SERIAL) AS Count_ATE_SERIAL
FROM LU_TM_PRODUCTS_MODEL LEFT JOIN TM_TEST_SEQUENCES ON LUMOD_PK = SEQ_MODEL
LEFT JOIN ATE_TESTS ON SEQ_PK = ATE_SEQUENCE_FK
WHERE LUMOD_MODEL = 'EA-555010-012' AND ATE_YIELD = 1
AND TO_CHAR(ATE_END_TIME, 'Q') = TO_CHAR(sysdate-6, 'Q')
GROUP BY ATE_YIELD,
, which counts for the Quarter.

However, I need the count for each week in the quarter with each week ending in a Friday:
SELECT TO_CHAR(dt,'dd-MON') WeekEnd,TO_CHAR(dt-6,'dd-MON') WeekStart 
from (SELECT ( TO_DATE('20170101', 'YYYYMMDD') + Level - 1 ) dt 
FROM DUAL CONNECT BY Level <= TO_DATE('19551231', 'YYYYMMDD') + 1 - TO_DATE('19550101', 'YYYYMMDD') ) 
WHERE TO_CHAR(dt, 'DY') = 'FRI' and TO_CHAR(dt, 'Q') =TO_CHAR(sysdate-6, 'Q')
, which gives me the weeks of the Quarter.

Is there a way to put these together to give me a Yield count for each week in a Quarter?

Thanks,
-J

[Updated on: Mon, 24 July 2017 08:25]

Report message to a moderator

Re: Weeks, Quarters, and Counts [message #664606 is a reply to message #664605] Mon, 24 July 2017 08:26 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
Since we don't have your table or your data, we can't write any solution for you.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Weeks, Quarters, and Counts [message #664610 is a reply to message #664606] Mon, 24 July 2017 10:40 Go to previous messageGo to next message
jmltinc
Messages: 14
Registered: January 2016
Junior Member
I am sorry, but I thought my queries contained sufficient information. Simplified table structure:

LU_TM_PRODUCTS_MODEL
LUMOD_PK (Primary Key) Data = 1,2,3...
LUMOD_MODEL Data=EA-555101-012, blah, blah...

TM_SEQUENCES
SEQ_PK (Primary Key) Data = 1,2,3...
SEQ_Model = Primary Key of LU_TM_PRODUCTS_MODEL

ATE_TESTS
ATE_PK (Primary Key)
ATE_SEQUENCE_FK = Primary Key of TM_SEQUENCES
ATE_YIELD Data = 1 or -1 (This is the field I want to filter upon and count)

These tables are contained in the first query I presented - it counted how many rows had 1 as its value.

However, I need to count by week ending on Friday for each Quarter of the Year. The second query returned the starting date and ending date of every current Quarter.

I need to use the second query to filter out the results of the first so I count only Yields in the current Quarter.

I hope this explanation helps and someone can help me.

Thanks,
-John
Re: Weeks, Quarters, and Counts [message #664611 is a reply to message #664605] Mon, 24 July 2017 10:50 Go to previous messageGo to next message
flyboy
Messages: 1850
Registered: November 2006
Senior Member
Hi,

It seems to me that you want to aggregate (GROUP BY) by the "weeks" which are from Saturday to Friday.

In that case, there is no need for that interval generation (second query). Just assign each date its "week" representation, e.g. the value of WEEK_START would be
trunc(ate_end_time+2, 'IW')-2
Explanation:
Adding 2 days to Saturday makes it Monday,
Adding 2 days to Sunday makes it Tuesday,
...,
Adding 2 days to Friday makes it Sunday.
So after adding 2 days, the days from the same "week" will belong to one ISO week then.
TRUNC returns the start of ISO week (Monday). By subtracting 2 days you will get the starting Saturday.

Simple aggregate by this expression shall suffice.
Re: Weeks, Quarters, and Counts [message #664612 is a reply to message #664611] Mon, 24 July 2017 10:58 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
Problem seems to involve days, weeks & calendar quarters, but none of the posted "tables" contain any DATE datatype column.

We speak SQL.
Do you speak SQL?
If so, post needed CREATE TABLE statements so that we can have the same as you.
Re: Weeks, Quarters, and Counts [message #664613 is a reply to message #664612] Mon, 24 July 2017 11:22 Go to previous message
jmltinc
Messages: 14
Registered: January 2016
Junior Member
Thank you Flyboy for the quick and concise answer and explanation. It works perfectly.

Previous Topic: Mutating error
Next Topic: Ref cursor results to CSV?
Goto Forum:
  


Current Time: Fri Dec 15 23:38:46 CST 2017

Total time taken to generate the page: 0.02004 seconds