Data in the form of Weeks YTD query
844883Mar 4 2011 — edited Mar 4 2011Hey Guys,
I need serious help on this query. I am providing the sample of the report:
Week Unit Sales Retail Cost Units On Hand
8/1-8/7 14 1,000,000
8/8-8/14 8 1,250,000
8/15-8/21 249 1,553,000
8/22-8/28 426
8/29-9/4 489
9/5-9/11 640
9/12-9/18 414
9/19-9/25 545
9/26-10/2 539
10/3-10/9 604
10/10-10/16 1112
10/17-10/23 1436
10/24-10/25 436
So basically my data is divided in terms of week. The week begins on Sunday and ends on Saturday. I am capturing unit sales, retail value, cost and units (inventory). I am able to calculate the first three. I am getting the issue with units (inventory). The logic behind unit is that it for a week (for ex 1/1-1/7) it can only be calculated as either the beginning of the week (BOW) or end of week (EOW). Everything for inventory is calculated based on the start_date, end_date and last_activity of the specific unit. There is a specific formula for calculating the units which is :
start_date <= (either BOW or EOW) and end_date>(either BOW or EOW) and last_activity>=(either BOW or EOW).
This is the query I am using for inventory part (and it will make more sense about the formula I mentioned earlier):
select trunc(to_date(START_DATE),'Day') week_begin, trunc(to_date(START_DATE),'Day')+6 weekend
,COUNT(CASE WHEN START_DATE<= trunc(to_date(START_DATE),'Day')+6 AND END_DATE>trunc(to_date(START_DATE),'Day')+6
AND LAST_ACTIVITY>=trunc(to_date(START_DATE),'Day')+6 THEN UNIT_ID END) UNITS
from ACTIVITY
where Start_date>=trunc(sysdate,'YYYY')
I am calculating YTD data.
the issue is the query is not giving me correct results. I have to get the data in terms of week from YTD and I am aggregating the units using the "trunc(to_date(START_DATE),'Day')+6" function in my case statement. Can anybody help me to get the data weekly. Thanks so much.