This discussion is archived
2 Replies Latest reply: Aug 29, 2013 5:57 AM by MaheshSinha

# Help Required to get Age of an Item at fix day of every week

Currently Being Moderated

Hi Friends suppose i am having following table structure

 INVSTOCK Entcode ItemCode Entdt Enttype Qty Rate Invitem ItemCode Group Name Item Name

Where invitem.itemcode =invstock.itemcode

Now i want to create a report based on user input of month. (If i have given month as Aug)

It  has to show me Closing Stock as in following format

0-30 Days                                                                 31-60 Days                                                            > 60Days

W1_Qty, W2_Qty, W3_Qty, W4_Qty                    W1_Qty, W2_Qty, W3_Qty, W4_Qty                         W1_Qty, W2_Qty, W3_Qty, W4_Qty

Division

Where

W1_qty : Quantity of Division as on first Monday

W2_qty : Quantity of Division as on Second Monday

Calculation Used to get Weekday : TO_NUMBER(TO_DATE (next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+21)

Mahesh Sinha

• ###### 1. Re: Help Required to get Age of an Item at fix day of every week
Currently Being Moderated

Are you asking how to do this in OLAP or in pure SQL?

• ###### 2. Re: Help Required to get Age of an Item at fix day of every week
Currently Being Moderated

Thanks David

In any of the above mention

In fact i am facing problem while joining in OLAP

 Table Invstock Entcode Entdt Enttype Barcode Qty Table Invitem Barcode Division Section Invstock.barcode = Invitem.barcode Now what I want is as follows Week1 Week2 Week3 Week4 0-30 Days 30-60 Days >60 Days Old 0-30 Days 30-60 Days >60 Days Old 0-30 Days 30-60 Days >60 Days Old 0-30 Days 30-60 Days >60 Days Old Division I am having formula to get age and that I am getting on FIFO Basis Now the problem Is 1. either I have to create four different queries for four weeks 2. It could be that an item which was available in First week is not available in other week so how to join?

#### Legend

• Correct Answers - 10 points