Casting Date datatype to Datetime (Year to Month)

Casting Date datatype to Datetime (Year to Month)

Post by Jonathan Leffle » Sun, 31 Dec 1899 09:00:00




> 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;


> >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;

Quote:> >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.

--

Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN
#include <disclaimer.h>
 
 
 

Casting Date datatype to Datetime (Year to Month)

Post by Kire Prostizenovsk » Sun, 31 Dec 1899 09:00:00


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

 
 
 

Casting Date datatype to Datetime (Year to Month)

Post by Kire Prostizenovsk » Sun, 31 Dec 1899 09:00:00


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 ?


>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

 
 
 

1. CAST as datetime swapping Day and Month

On one server, CONVERT and CAST are not behaving correctly for dates; They manage to swap the Day and Month parts of the date.

Consider the following SQL

select getdate()
select CONVERT(VARCHAR(16),getdate(),120)
select cast(CONVERT(VARCHAR(16),getdate(),120) as datetime)

The output is as follows:

---------------------------
2000-12-04 20:12:54.383

(1 row(s) affected)

----------------
2000-12-04 20:12

(1 row(s) affected)

---------------------------
2000-04-12 20:12:00.000

(1 row(s) affected)

Why have the month and day swapped in the output from the final statement????

Slainte mhath
Stephen Lappin

--
Slainte mhath
Stephen

IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humour or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this mail is not authorised (either explicitly or implicitly) and constitutes an irritating social faux pas.
Unless the word absquatulation has been used in its correct context somewhere other than in this warning, it does not have any legal or grammatical use and may be ignored. No animals were harmed in the transmission of this email, although the kelpie next door is living on borrowed time, let me tell you. Those of you with an overwhelming fear of the unknown will be gratified to learn that there is no hidden message revealed by reading this warning backwards, so just ignore that Alert Notice from Microsoft.

2. Full-Text Indexes

3. Date Calculations : Period Between 2 Dates (AGE) in Years, Months and Days

4. Post Snap shot does not run on all subscriptions

5. construct a date string in TSQL

6. MDX: year-to-date/month-to-date problem

7. Sending email on Schema Change

8. Incrementing/decrementing month/year on DateTime fields via script

9. previous month, quarter, year, start and stop date?

10. Converting 2 char for month and year fields to single date field

11. Year/Month (only) dates

12. Need to find start date of the month and enddate of the month given a date