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-09-05 : 03:44:36
|
| Anyone can help edit this query for get the 2014-09-01 to nowthe total GP% ,amount, qty ?--------------------------------------------------------SELECT Convert(varchar, GETDATE(),112) as reportdate, case when a.shopcode is null then ' Total' else 'Shop '+a.shopcode end as shop, 'GP% ', ltrim(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, '$',ltrim(SUM(b.actualsalesamt)) AS salesamt FROM 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 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-05 : 08:57:17
|
| Please post your question following these instructions:http://www.sqlservercentral.com/articles/Best+Practices/61537/ |
 |
|
|
|
|
|
|
|
how to calcuate sales GP%