Comments by Surandar Raj R (Top 7 by date)

because of ur reference oly i figured it out sinisa hajnal......

As per you said Rownumber is perfectly works,

Here is the code tat perfectly works...
SELECT @row_num := IF(@prev_value= date_format(str_to_date(paydate, '%d-%m-%Y'), '%m%Y'),@row_num+1,1) AS RowNumber
,@Customer:=date_format(str_to_date(paydate, '%d-%m-%Y'), '%m%Y') as Date
,o.PayDate

,@prev_value := date_format(str_to_date(paydate, '%d-%m-%Y'), '%m%Y')
FROM test o,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
order BY RowNumber DESC

Thanks a lot Sinisa Hajnal ....

auto decrement s.no based on month and year in date that to in desc.

Thanks for your reply... It will be useful but i cannot add this ,because i used group by in my stored procedure....

See it ....

CREATE DEFINER = 'root'@'localhost'
PROCEDURE eliteelevator.sp_logistics(
flag varchar(50),
p_memberid int,
p_customerpid int
)
BEGIN


if(flag='logistic') then

SELECT
a.memberid,
a.UserName AS BillingName,
b.ReferredBy AS ReffName,
b.City,
b.Phone,
a.EmailID,
b.Address,
b.Product,
c.totalproductcost AS fact_totalproductcost,
c.fact_Termofschedule_first,
SUM(c.paidamt_first) AS fact_paidamt_first,
c.paiddate_first AS fact_paiddate_first,
c.fact_termofschedule_second,
SUM(c.paidamt_second) AS fact_paidamt_second,
c.paiddate_second AS fact_paiddate_second,
c.fact_termofschedule_third,
SUM(c.paidamt_third) AS fact_paidamt_third,
c.paiddate_third AS fact_paiddate_third,

c.fact_termofschedule_four as fact_term_four,sum(c.paidamt_four) as fact_paidamt_four,c.paiddate_four as fact_paiddate_four,
((c.totalproductcost) - (SUM(c.paidamt_first) + SUM(c.paidamt_second) + SUM(c.paidamt_third) + sum(c.paidamt_four))) AS Fac_Paymentdue,
concat(round(((SUM(c.paidamt_first) + SUM(c.paidamt_second) + SUM(c.paidamt_third) + sum(c.paidamt_four))/c.totalproductcost*100),2),'%') AS Fac_Pay_per,

d.pid,
d.totalamt AS ProductCost,
d.termsofschedule_first,
SUM(d.paidamt) AS cus_paidamt_first,
d.paydate AS cus_paydate_first,
d.fileurl1,
d.termofschedule_second,
SUM(d.paidamt_second) AS cus_paidamt_second,
d.paiddate_second,
d.fileurl2,
d.termofschedule_third,
SUM(d.paidamt_third) AS cus_paidamt_third,
d.paiddate_third,
d.fileurl3,

d.termofschedule_four,sum(d.paidamt_four) as cus_paidamt_four,d.paiddate_four as cus_paydate_four,
((d.totalamt) - (SUM(d.paidamt) + SUM(d.paidamt_second) + SUM(d.paidamt_third) + sum(d.paidamt_four))) AS Cus_paymentdue,
concat(round(((SUM(d.paidamt) + SUM(d.paidamt_second) + SUM(d.paidamt_third) + sum(d.paidamt_four))/d.totalamt*100),2),'%') AS cus_pay_rec_per,

e.installationcost,
e.paiddate AS installation_paidamount,
((e.installationcost) - SUM(e.paidamount)) AS intallation_Paymentdue,

f.ordplacedfactdate,
f.productionstartdate,
f.readlinessdate,
f.pickedupdate,
f.reachindiadate,
f.reachsitedate,

e.installationstartdate,
e.installationcompletedate,
e.handoverdate,

g.startdate,
g.expdate

FROM logintable AS a

LEFT JOIN clientinfo AS b
ON a.memberid = b.MemberID
LEFT JOIN factory_payment AS c
ON a.memberid = c.memberid
LEFT JOIN customerpayment AS d
ON a.memberid = d.memberid
LEFT JOIN installationcost AS e
ON a.memberid = e.memberid
LEFT JOIN logistics AS f
ON a.memberid = f.memberid
LEFT JOIN amc AS g
ON a.memberid = g.memberid
WHERE b.Status='Completed' or b.Status='Installation' or b.Status='Confirmed'
GROUP BY a.memberid,d.pid;


ELSEIF (flag = 'viewone') THEN
if(p_customerpid!=0) then
SELECT
a.memberid,
a.UserName AS BillingName,
b.ReferredBy AS ReffName,
b.City,
b.Phone,
a.EmailID,
b.Address,
b.Product,
c.totalproductcost AS fact_totalproductcost,
c.fact_Termofschedule_first,
SUM(c.paidamt_first) AS fact_paidamt_first,
c.paiddate_first AS fact_paiddate_first,
c.fact_termofschedule_second,
SUM(c.paidamt_second) AS fact_paidamt_second,
c.paiddate_second AS fact_paiddate_second,
c.fact_termofschedule_third,
SUM(c.paidamt_third) AS fact_paidamt_third,
c.paiddate_third AS fact_paiddate_third,

c.fact_termofschedule_four as fact_term_four,sum(c.paidamt_four) as fact_paidamt_four,c.paiddate_four as fact_paiddate_four,
((c.totalproductcost) - (SUM(c.paidamt_first) + SUM(c.paidamt_second) + SUM(c.paidamt_third) + sum(c.paidamt_four))) AS Fac_Paymentdue,
concat(round(((SUM(c.paidamt_first) + SUM(c.paidamt_second) + SUM(c.paidamt_third) + sum(c.paidamt_four))/c.totalproductcost*100),2),'%') AS Fac_Pay_per,
d.pid,
d.totalamt AS ProductCost,
d.termsofschedule_first,
SUM(d.paidamt) AS cus_paidamt