Joining Master Calender to two or more tables on Date field

[#1]

Hi Everyone,

Below is my requirement

Table	Order No	Item No	Order Date	GRN Date
Purchase Order (PO)	P	P	P
Purchase Reciept (PR)	P	P	P	P
Master Calender			P	P

I have to connect Master Calendar on Date field to Purchase Order Order Date field & Purchase Receipt GRN Date Field.

Order Date id Present in PO & PR.

So that,when user select Any perticular Month-Year,he will get all the Orders generated in that selected month as well as Orders whose GRN generated in that month.

How can I do this?

Thanks

[kamalqlik]

Hi,

Kiran may be this can help you

Calendar:
LET vDateMin = Num(MakeDate(2010,1,1)); 
LET vDateMax = Num(MakeDate(2011,12,31));  
LET vDateToday = Num(Today()); 

TempCalendar: 
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber, 
Date($(vDateMin) + RowNo() - 1) AS TempDate 
AUTOGENERATE 1 
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

Calendar: 
LOAD
Date(TempDate) AS CalendarDate, 
Month(TempDate) AS CalendarMonthName, 
Year(TempDate) AS CalendarYear
RESIDENT TempCalendar ORDER BY TempDate ASC;

Link:
LOAD

Order,
OrderNo,etc
Order DATE as CalendarDate,
'OrderDate' as DateTypeFlag
RESIDENT Purcahse Order;
concatenate (Link)
LOAD

GRC No,

etc

Purchase Date as CalendarDate,
'PurcaseDate' as DateTypePurcase
RESIDENT [Purcse Reciept];

DROP TABLE TempCalendar;

Hope it helps you.

Regards

Kamal

[hic]

A link table or a bridge table can connect one single master calendar to several dates. But why do you want to do that? If you do that you cannot make selections like "Find transactions that have order dates in Oct and GRN dates in November". It's much better to load several calendar tables.

See more on the blog about multiple calendar tables.

HIC