Home » SQL & PL/SQL » SQL & PL/SQL » Weeks, Quarters, and Counts (11g)
Weeks, Quarters, and Counts [message #664605] |
Mon, 24 July 2017 08:24 |
|
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 #664610 is a reply to message #664606] |
Mon, 24 July 2017 10:40 |
|
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 |
flyboy
Messages: 1903 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.
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 14 06:58:47 CDT 2024
|