How to find Record Compare to Month Product?

Question

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

Hardik Bhavsar · Answer

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 end Now Output is Above SP--> ProductId ProductName Month Amount 16 Kapchi 7 38520750.00 15 RETI 7 38001125.00 1 Jab Tak Hai Jaan 7 25029605.60 11 ABCD 7 9715303.89 12 XYZ 7 3264279.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 5 12345.00 15 RETI 5 4544845.00 1 Jab Tak Hai Jaan 6 34399.60 11 ABCD 6 348445.89 12 XYZ 6 222435.80 16 Kapchi 7 38520750.00 15 RETI 7 38001125.00 1 Jab Tak Hai Jaan 7 25029605.60 11 ABCD 7 9715303.89 12 XYZ 7 3264279.80 11 ABCD 8 9868683.89 12 XYZ 8 3279.80 Thanking You, Hardik Bhavsar

Jaganathan Bantheswaran · Answer

Hi, It is answered here

Jignesh Trivedi · Answer

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 Bhavsar · Answer

select ProductID from purchaseorderitemdetail where productid in (select top 5 ProductID,SUM(Amount) from PurchaseOrderItemDetail group by ProductID order by SUM(amount) desc)

Jaganathan Bantheswaran · Answer

At what what condition you want this result? what is your input?

Hardik Bhavsar · Answer

Please Show the OUTPUT Thank You HB

Jaganathan Bantheswaran · Answer

Question is not clear. Please give somemore details.