Re: Casting Date datatype to Datetime (Year to Month)

I've tried writing just the insert statement with literals; INSERT INTO agg_part VALUES (0, DATETIME(1999-9) YEAR TO MONTH, "101", "123456", "01313070", 25.30, 2) This seems to work OK, but how do I do the GROUP BY in my SELECT statement ? Kire Prostizenovski wrote in message ... >Guys, > >I am building an aggregate table from a fact table with millions of rows. I >require the aggregation to be for monthly periods. The fact table has a >Date column which I would like to aggregate monthly. > >eg. >Fact table; > sale_id serial > due_date date > customer char... > part char ... > amount money ... > quantity decimal ... > ... > <other columns> > ... > >Aggregate Table; > agg_id serial > agg_period datetime(year to month) > customer char ... > part char ... > amount money ... > quantity decimal ... > >I wanted to write a query which would give me the sum of amount and >quantity, grouping by customer, part, and the monthly period, to insert into >the aggregate table. > >Thanks, > >Kire > >Kire Prostizenovski >Business Analyst >J Blackwood and Son Limited >13 Cooper Street >Smithfield NSW 2164 >Australia >Phone +61 2 9203 0133 Fax +61 2 9203 0160 > >

Kire Prostizenovski wrote: > I've tried writing just the insert statement with literals; > > INSERT INTO agg_part > VALUES (0, > DATETIME(1999-9) YEAR TO MONTH, > "101", > "123456", > "01313070", > 25.30, > 2)> > This seems to work OK, but how do I do the GROUP BY in my > SELECT statement ? GROUP BY customer, part, agg_period; > Kire Prostizenovski wrote in message ... > >I am building an aggregate table from a fact table with millions > >of rows. I require the aggregation to be for monthly periods. > >The fact table has a Date column which I would like to aggregate > >monthly. > > > >eg. > >Fact table; > > sale_id serial > > due_date date > > customer char... > > part char ... > > amount money ... > > quantity decimal ... > > ... > > <other columns> > > ... INSERT INTO Agg_part SELECT 0, EXTEND(MDY(MONTH(due_date), 1, YEAR(due_date)), YEAR TO MONTH), customer, part, SUM(amount), SUM(quantity), ... FROM Fact_Table GROUP BY 1, 2, 3, 4; > >Aggregate Table; > > agg_id serial > > agg_period datetime(year to month) > > customer char ... > > part char ... > > amount money ... > > quantity decimal ... > > > >I wanted to write a query which would give me the sum of amount > >and quantity, grouping by customer, part, and the monthly period, > >to insert into the aggregate table. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>