please help with the script

Question

i have two table

table A

product_master

productid         quantity                   date_filled

1                     20                          2005-04-12

2                        40                      2005-03-23

3                   40                          2005-02-23

---------------------

here date_filled i have for every month and it can be same date

now in another table

product 

productid                name

1                           ortho

2                           zerox

3                          tghjy

what i want is in every year ,every month ,what is max product used?

for exampel, in 2005 janu zerox (max used)

                         2005 feb  ortho(max used)

please help me

Answer 1

Are you looking for the largest single order for each month OR the product with the largest combined total (SUM) for each month?

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

largest single for each month

Answer 3

The problem is not clear, so I am guessing that what you want is:

;with R as (
select
    P.productid,
    P.name,
    convert(char(6), T.date_filled, 112) as year_month,
    sum(T.quantity),
    row_number() over(partition by convert(char(6), T.date_filled, 112) order by sum(T.quantity) DESC, P.productid) as rn
from
    product_master as T
    inner join
    product as P
    on T.productid = P.productid
group by
    P.productid,
    P.name, 
    convert(char(6), T.date_filled, 112)
)
select *
from R
where rn = 1;

If you want to select ties, then you need to use function RANK instead ROW_NUMBER and drop P.productid from the ordering.

---

AMB

Some guidelines for posting questions...

Answer 4

well this is not working 

Msg 8120, Level 16, State 1, Line 3
Column 'p.productid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 5

You are right, I forgot to include P.productid and P.name as part of the grouping.

I fixed my reply.

---

AMB

Some guidelines for posting questions...

Answer 6

still its not getting me desire result

i need to know each year ,each month ,max 10 product used

Answer 7

Please, do not waste our time and post table schema, sample data, and expected result.

To answer your new request just change the filter to "where rn <= 10".

---

AMB

Some guidelines for posting questions...

Answer 8

ok. your solution is worked  but i want year and month in separate field how to do it

Answer 9

Try:

;with R as (
select
    P.productid,
    P.name,
    convert(char(6), T.date_filled, 112) as year_month,
    sum(T.quantity) as sum_qty,
    row_number() over(partition by convert(char(6), T.date_filled, 112) order by sum(T.quantity) DESC, P.productid) as rn
from
    product_master as T
    inner join
    product as P
    on T.productid = P.productid
group by
    P.productid,
    P.name,
    convert(char(6), T.date_filled, 112)
)
select left(year_month, 4) as [year], right(year_month, 2) as [month], name as product_name, sum_qty as qty
from R
where rn <= 10;

You can aslo use functions YEAR() and MONTH() in the CTE.

---

AMB

Some guidelines for posting questions...

Answer 10

If you're looking for 10 largest single orders for each month, then

;with cte as (select PM.ProductID, P.[Name] as [Product Name], PM.quantity, 
datename(month, PM.date_filled) as [Month], 
datename(year, PM.date_filled) as [Year],

row_number() over (partition by datepart(year, PM.date_filled), datepart(month, PM.date_filled) order by PM.quantity DESC) as Rn

from Product_Master PM INNER JOIN Product P on PM.ProductID = P.ProductID)

select * from cte where Rn <=10 -- if we want to find 10 top ordered products per month

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog