Date functionality

[udaya_kumar]

Hi,

I have two tables date_dim and shops_dim.

shops_dim has shop_dim_id, goods_incoming_date and other fields

date_dim has month_date like jan-12,feb-12,mar-12 like this 24 months

Now i need to find the count of shops which had goods_incoming in latest 3 months.

Like if a shop1 gets goods1 at 23-mar-12,

shop2 gets goods2 at 12-feb-12

shop3 gets goods3 at 11-jan-12

shop4 gets goods4 at 12-jan-12

Now if we count the no. of shops which took goods in latest 3 months (jan,feb,mar-12) should be 4.

so i have to find the no. of shops which took goods based on goods_incoming_date.

I should find the no. of goods_incoming_date present in the latest 3months (jan,feb,mar).

How can i find the no. of shops based on date?

one shop will have one date only.

[#2]

Can you attach the example?

[Anonymous]

You can create a full calendar, and link it to the shops by the date field.

But in your case, if you have only one record per shop, it may not be needed.  Just re-format goods_incoming_date in format 'mmm-yy'.  Can be done by cutting off the day part, or by using date(date#()) functions.

[udaya_kumar]

Hi,

Thanks for the reply.

Can you tell me how can i create an id for the dates in a separate field?

for example..

I have dates like

1-jan-2012

5-jan-2012

2-feb-2012

30-mar-2012

29-mar-2012

So i want id's for the dates like

Month             ID

1-Jan-2012       1

5-Jan-2012       1

2-Feb-2012       2

30-Mar-2012     3

29-Mar-2013     3

ID's should be based on Month-Year.

In a date, if month-year repeats then the ID should be same

like 1-Jan-2012 and 5-Jan-2012

i have common month-year

so both should have ID as 1.

How can i get this ID field in script?

[Anonymous]

I guess that this is what you want:

date(date#(goods_incoming_date, 'D-MMM-YYYY'), 'D-MMM-YYYY') as Month     // actually it is date not month

num(month(date#(goods_incoming_date, 'D-MMM-YYYY'))) as ID