Loading

How to find Record Compare to Month Product?

How to find Record Compare to Month Product?

Hardik Bhavsar

Hardik Bhavsar

12y2.4kSQL

How to create a query when i give the month name same as product record display in amount otherwise not match product are not display in record?

EXAMPLE :-

Product Name Month Amount

ABCD 5 20000

XYZ 5 10000

Kapchi 5 4500

RETI 5 65000

Jab Tak Hai Jaan 5 30000

Jab Tak Hai Jaan 6 30000

ABCD 6 20000

XYZ 6 10000

Kapchi 7 1000

RETI 7 45000

ABCD 8 20000

XYZ 8 10000

RETI 8 45000

Jab Tak Hai Jaan 8 30000

Kapchi 9 1000

GIVE OUTPUT :-

Product Name Month Amount

Kapchi 5 4500

RETI 5 65000

Kapchi 7 1000

RETI 7 45000

RETI 8 45000

Kapchi 9 1000

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Hardik Bhavsar12y ago
This is My StoreProcedure. after this query execution i get some output but i want to give same productid for other months.


alter procedure uspGetProductWiseAmountDetailChartRpt1
(
@CompanyId int,
@ChannelId int,
@Month int
)
as

begin


select bb.ProductID,bb.ProductName,bb.Month,bb.Amount

from(select * from (select
poid.ProductID,
MONTH(POM.PODate) as 'Month',
p.ProductName,
SUM(POID.Amount) as 'Amount',
SUM(poid.TaxAmount) as 'TaxAmount',
RANK() OVER (partition by MONTH(POM.PODate) order by SUM(POID.Amount) desc) as 'Rnk'

from PurchaseOrderItemDetail POID
inner join PurchaseOrderMaster POM on POID.POID=pom.ID
inner join Products P on poid.ProductID=p.id
inner join Companies C on p.CompanyID=C.ID
inner join Channels CH on p.ChannelID=CH.ID
where p.CompanyID=isnull(@CompanyId,C.ID)
and p.ChannelID=isnull(@ChannelId,CH.ID)
and MONTH(POM.PODate)=@Month
group by p.ProductName,MONTH(POM.PODate),poid.ProductID

)aa where
Rnk <=5)bb


end

Now Output is Above SP-->
ProductId ProductName    Month Amount
16        Kapchi                738520750.00
15        RETI                        738001125.00
1        Jab Tak Hai Jaan        725029605.60
11        ABCD                        79715303.89
12        XYZ                        73264279.80

same top 5 highest amount wist productid find the record for different months.
but i want to Give the output is-->

ProductId ProductName    Month Amount
16        Kapchi                512345.00
15        RETI                        54544845.00
1        Jab Tak Hai Jaan        634399.60
11        ABCD                        6348445.89
12        XYZ                        6222435.80
16        Kapchi                738520750.00
15        RETI                        738001125.00
1        Jab Tak Hai Jaan        725029605.60
11        ABCD                        79715303.89
12        XYZ                        73264279.80
11        ABCD                        89868683.89
12        XYZ                        83279.80

Thanking You,
Hardik Bhavsar
Jaganathan Bantheswaran12y ago
Hi,

It is answered here
Jignesh Trivedi12y ago
hi,
From the out put we can not conclude any thing can you please business scenarios?
also above query is also not help us to problem which you are facing...
Hardik Bhavsar12y ago
select ProductID from purchaseorderitemdetail where productid in (select top 5 ProductID,SUM(Amount) from PurchaseOrderItemDetail group by ProductID order by SUM(amount) desc)
Jaganathan Bantheswaran12y ago
At what what condition you want this result? what is your input?
Hardik Bhavsar12y ago
Please Show the OUTPUT Thank You HB
Jaganathan Bantheswaran12y ago
Question is not clear. Please give somemore details.