Total Quantity of Items Sold per Month

Database
N/A

Total Quantity of Items Sold per Month

The below query is giving me a result of how many of each product has been sold over time but I need it to tell me how many were sold each month for a chosen year. What am I doing wrong?

Select qwe.sku_id, qwe.mnfctr_cd, qwe.itm_desc, qwe.itm_type_cd,
sum (qwe. totalSold) as totalQtySold, qwe.MonthNum, qwe.Year_
from
(
Select item.sku_id, item.mnfctr_cd, item.itm_desc,
item.itm_type_cd, sale.sold_qty as totalSold,
extract (month from item.eff_bgn_dt_tm) as MonthNum,
extract (year from item.eff_bgn_dt_tm) as Year_
from edwviews.sku_poss_trans_ln_itm sale
inner join edwviews.itm item
on sale.sku_id = item.sku_id
) qwe
where qwe.itm_type_cd like any ('A', 'P')
group by 6,1,2,3,4,7
9 REPLIES

Re: Total Quantity of Items Sold per Month

Looks like you have the correct query.

Enthusiast

Re: Total Quantity of Items Sold per Month

Based on your query you get sales of all the years data for each month and for each product.

It is sounding you are expecting only choosen year sales. does it mean  you want to filter the data for a specific year only  by using year in "where condition" ?

or probably you want to aggreate the sales of all the products monthly wise. Then need to eliminate product level details in select clause and group by clause.

Hope i unders stood your requirement correctly

Enthusiast

Re: Total Quantity of Items Sold per Month

Hi am getting following error while inserting data into table.please help me on this.

This is my tables syntax

create table k.test (eventdate timestamp);

insert into k.test (eventdate) values(to_timestamp('07-FEB-14 07.37.38.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));

am getiing following error

name require 30 bytes in latin iternal form.

Am new to this blog where to post i dn't knw srry for anything wrong

Please any bdy help me on this




Enthusiast

Re: Total Quantity of Items Sold per Month

Hi,

Check if this works out....

Select qwe.Year_, qwe.MonthNum, qwe.mnfctr_cd, sum (qwe. totalSold) as totalQtySold

from ....

....

....

group by 1, 2, 3

Correct me if something is wrong :)

Enthusiast

Re: Total Quantity of Items Sold per Month

Hi Vinod can u help on above post..

Enthusiast

Re: Total Quantity of Items Sold per Month

Try with this

insert into k.test (eventdate) values('2014-02-24 19.37.38.000000');

Enthusiast

Re: Total Quantity of Items Sold per Month

How to move oracle data into teradata..if do you have any documentation please share with me ..please am strugle on this teradata..

Enthusiast

Re: Total Quantity of Items Sold per Month

Hi Kartheek,

this looks odd.  This generally happends when the object name (tablename or column name) contains more than 30 character during DDL operation

Enthusiast

Re: Total Quantity of Items Sold per Month

@Kartheek, Teradata does not support RR format. Also the max timestamp allowed is timestamp(6). If you need to convert RR notation you need to cast that column in character and then add the century and then insert.

@Denise, what your query is taking is the item's effective begin dt time  which is always a single date and you will get only the month and year of the day when the item was launched. what you need to take into consideration is the sale date from sku_poss_trans_ln_item table. then you will get the sum(sales).