Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
usafelix
Posting Yak Master
165 Posts |
 Posted - 2014-08-12 : 09:12:18
|
| Dear Sir,I want change this query to get this below of 1-6 result.I am try to use of rollup and round command but not completed achieve what I want.This original result is shop sales amount GP A 100.00000 10.08B 200.00000 20.06 300.0000 30.14 Expect result is :-shop saleamount GPA HK$ 1000 10%B HK$ 110 20%------------------------------Total HK$ 1100 avr(GP)1. salesamt = 8510.10 change to $8502. GP = 19.3000 change to 19% 3. sum(acutalsalesamt) , new request added show grandtotal4. Align the salesamount in fix lenght to display.5. Total GP change to average GP5. Insert underline above of total---------------------------------------SELECT a.shopcode as shop, SUM(b.actualsalesamt) AS salesamt, round(SUM(b.actualsalesamt - b.actualsalesqty * (CASE WHEN LEFT(b.sku, 1) IN ('0', '1', '3', '5', '6', 'b') THEN c.bottompx ELSE c.cost END)) / SUM(b.actualsalesamt)*100,0) AS GP, Convert(varchar, GETDATE(),112) as reportdateFROM xsoheader AS a INNER JOIN xsodetail AS b ON a.companycode = b.companycode AND a.shopcode = b.shopcode AND a.stationid = b.stationid AND a.memono = b.memono AND a.txdate = Convert(varchar, GETDATE(),112) INNER JOIN msku AS c ON b.companycode = c.companycode AND b.sku = c.sku INNER JOIN mlocation AS e ON a.companycode = e.companycode AND a.shopcode = e.shopcodeWHERE (a.companycode = 'ac') AND (a.confirmflag = 'y') AND (a.voidflag = 'n') AND (a.txtype IN ('cs', 'ex', 'rf')) AND (a.txdate =Convert(varchar, GETDATE(),112)) AND (e.ref10 = 'retail') and b.naturecode ='01' GROUP BY a.shopcode with rollup |
|
|
|
|
|
|
|
help to write query