Insert new row and average GP query

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Insert new row and average GP query

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-13 : 00:37:54

Please edit my query to calcuate the average GP and show it in last column. I also want insert new blank row for sepreate total and like picture 1.

picture 1
date shop GP% amount
20140813 KC001 GP% 4.000000 102.00
20140813 LM001 GP% 55.000000 1897.50
20140813 MC001 GP% 55.000000 684.00
20140813 MK002 GP% 50.000000 1500.30
20140813 NP002 GP% 70.000000 395.30
20140813 TM002 GP% 47.000000 2230.60
20140813 TM003 GP% 64.000000 823.90
20140813 TS001 GP% 40.000000 676.40
20140813 TW003 GP% 26.000000 21.80
20140813 WC002 GP% 53.000000 1962.80
20140813 YL002 GP% 52.000000 1914.90

20140813 Total GP% (calcuate avg GP) 12209.50
-----------------------------------------------------
SELECT

Convert(varchar, GETDATE(),112) as reportdate,
--- a.shopcode as shop,

case when a.shopcode is null

then 'Total'

else
a.shopcode end as shop,

'GP%', 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,
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.shopcode
WHERE (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-08-13 : 09:44:48

Please post your tables in a consumable format:

1. CREATE TABLE statements
2. INSERT INTO statements

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources