Last Sale Date As Per The Credit Note Date

[ajithsachin]

Hi All,

I have two tables, ie; Sales and credit note data. I need to make a report that should show credit note details of the item batch with when it sold last time.(Last sold date should be less than credit note date)

Below i am attaching my sample table structure with data.

Sales:-

InvNum	Date	Item	Batch	Qty	Value
1	01-04-2016	item1	batch1	10	100
2	10-04-2016	item1	batch1	10	100
3	13-04-2016	item1	batch1	5	50

Credit Note:-

CreditNoteNum	Date	Item	Batch	Qty	Value
1	03-04-2016	item1	batch1	8	80
2	12-04-2016	item1	batch1	5	50
3	15-04-2016	item1	batch1	5	50

and my expected result table is something like this below.

Item	Batch	CreditNote Date	Last SaleDate
item1	batch1	03-04-2016	01-04-2016
item1	batch1	12-04-2016	10-04-2016
item1	batch1	15-04-2016	13-04-2016

Can anybody help please..

Regards,

Ajith

[swuehl]

You can create a date interval from your CreditNote dates:

Creating a Date Interval from a Single Date

Then use INTERVALMATCH LOAD prefix to link your credit notes to the sales dates, using Item and batch as keys:

IntervalMatch

[sunny_talwar]

Does Qty and Value also take a part in knowing how the two tables need to join together?

[ajithsachin]

It is actually inside the FACT table. Qty we can change to Sales_Qty and Return_Qty and value will be Sales_Value and Return_Value.

So that Fact will look like this

[sunny_talwar]

So currently it looks like this? but you want them to be on the same line? Is that the goal?

[ajithsachin]

i want to show a report like same item same batch on which date credit note happened and in next column when was that batch sold last time.(max of sales date where sale date less than equal to credit note date)

May be something like this below.