TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Abdul Amin Khan
1.1k
603
21.3k
How to Merge highlighted Rows in one Row
Sep 28 2018 8:39 AM
My Query is----
SELECT Date,Bill_No,Restaurant_name,Discounts,[GST 0%],[0],[GST 5%],[5],[GST 12%],[12],[GST 18%],[18],[GST 28%],[28],ServiceCharge_Percent,ServiceCharge_Amount,VATOnServiceCharge_Amount,Othercharges,ChangediscVal,NetSaleBeforeDiscount,NetSaleAfterDiscount,RoundOff,BillTotalCard_Amount,BillCash_Amount,NetTotal,Restaurant_Code,Terminal_Code,Bill_no1
FROM
(select ih.Date,
ih.Bill_No,ih.Bill_no1,
rest.Restaurant_name,
Round(ih.SubDiscount_Amount,2) as Discounts,
Round(ss.Service_charge,2) as ServiceCharge_Percent,
Round(ih.ServiceCharge_Amount,2) as ServiceCharge_Amount,
Round(ih.VATOnServiceCharge_Amount,2) as VATOnServiceCharge_Amount,
Round((isnull(ih.othercharge1,0)+isnull(ih.othercharge2,0)+isnull(ih.othercharge3,0)),2)as Othercharges,
Round(ih.ChangediscVal,2) as ChangediscVal,
Round(ih.SubTotal,2) as NetSaleBeforeDiscount,
Round((ih.SubTotal-ih.SubDiscount_Amount),2) as NetSaleAfterDiscount,
Round(isnull(ih.RoundOff,0),2) as RoundOff,
Round(ih.BillTotalCard_Amount,2) as BillTotalCard_Amount,
Round(ih.BillCash_Amount,2) as BillCash_Amount,
Round(ih.NetTotal,2) as NetTotal,
ih.Restaurant_Code,
ih.Terminal_Code,
Sum(il.Item_Price) as Price,
il.Vat_percent AS Tax_Percent,
tr.Tax_name AS Tax_Percent1,
Sum(il.Vat_amount) as Tax_amount
from INVHEAD ih
inner join InvLine il on il.Bill_no=ih.Bill_no
inner join TaxRates tr on tr.Tax_Rate=il.VAT_Percent
inner join Restaurants rest on ih.Restaurant_Code=rest.Restaurant_Code
inner join SystemSetup ss on rest.Restaurant_Code=ss.Restaurant_Code
where ih.Date BETWEEN '01 Aug 2018' AND '31 Aug 2018'
and ih.Restaurant_Code='1'
Group By il.Vat_percent,ih.Date,ih.Bill_No,rest.Restaurant_name,ih.SubDiscount_Amount,ss.Service_charge,ih.ServiceCharge_Amount,ih.VATOnServiceCharge_Amount,ih.othercharge1,ih.othercharge2,ih.othercharge3,ih.ChangediscVal,ih.SubTotal,ih.RoundOff,ih.BillTotalCard_Amount,ih.BillCash_Amount,ih.NetTotal,ih.Restaurant_Code,ih.Terminal_Code,
il.Vat_percent,ih.Bill_No1,tr.Tax_name)Tab1
PIVOT
(Sum(Tax_amount) FOR [Tax_Percent] IN ([0],[5],[12],[18],[28])) AS Tab2
PIVOT
(Sum(Price) FOR [Tax_Percent1] IN ([GST 0%],[GST 5%],[GST 12%],[GST 18%],[GST 28%])) AS
Tab3
ORDER BY Date,Bill_No1
Reply
Answers (
2
)
C Sahrp Date Time Functions
i have error there is no row at position 0