Skip to Content
0

Special prices with gross profit query

Nov 24, 2016 at 04:14 PM

50

avatar image

Hi guys is there anyway that I could calculate gross profit in the below query without having to dump it into excel? So that we can run the report and it shows the gross profit on each line of the customers special pricing.

SELECT T1.[CardCode] AS 'BP Code', T1.[ItemCode] AS 'Item No.', T1.[Price] AS 'Special Price', T0.[LastPurPrc] AS 'Last Purchase Price' FROM [dbo].[OITM] T0 INNER JOIN [dbo].[OSPP] T1 ON T1.[ItemCode] = T0.[ItemCode]

WHERE T1.CardCode = [%0]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Johan Hakkesteegt Nov 25, 2016 at 09:27 AM
0

Hi Roy,

Please give this a try:

SELECT T1.[CardCode] AS 'BP Code'
     , T1.[ItemCode] AS 'Item No.'
     , T1.[Price] AS 'Special Price'
     , T0.[LastPurPrc] AS 'Last Purchase Price' 
     , (T1.[Price] - T0.[LastPurPrc]) AS 'Gross Profit'
FROM [dbo].[OITM] T0 
     INNER JOIN [dbo].[OSPP] T1 ON T1.[ItemCode] = T0.[ItemCode]
WHERE T1.CardCode = '[%0]'

Regards,

Johan

Share
10 |10000 characters needed characters left characters exceeded
Roy Bright Nov 25, 2016 at 10:10 AM
1

Amazing - thank you Johan and this also prompted me to realise a small change to allow for the profit to be shown as a % - thank you very much for this.

SELECT T1.[CardCode] AS 'BP Code'
, T1.[ItemCode] AS 'Item No.'
, T1.[Price] AS 'Special Price'
, T0.[LastPurPrc] AS 'Last Purchase Price'
, (T1.[Price] - T0.[LastPurPrc]) / (T1.[Price]) * 100 AS 'Gross Profit %'
FROM [dbo].[OITM] T0
INNER JOIN [dbo].[OSPP] T1 ON T1.[ItemCode] = T0.[ItemCode]
WHERE T1.CardCode = '[%0]'

Share
10 |10000 characters needed characters left characters exceeded