CODEWITHSUNDEEP
sqlsql-serversql-server-2012
lemunk
lemunk

Reputation: 2636

SQL difference between cell

Using MS SQL 2012,

I have table, I need to find the difference against a certain price, this new column will be the price adjustment when i import into a CMS.

SQL Fiddle Schema

DISCLAIMER: not sure if fiddle is working never used sql fiddle before, nut the schema and some data is there.

Pseudo: Each price that is NOT like 'xCarcass' on column st_style, then work out the difference between it and xCarcass based on the same pd_ref

Explanation So basically each PD_Code has several prices depending on what st_style there is. In my cms I want to import these prices, xCarcass is used as my base price and i want all other st_styles to show the price difference rather than the full price. (difference absed on its base price i.e xCarcass price).

So far: I knew i would need to create a cte query for this but im real novice with CTEs.

WITH cte as
(SELECT
ROW_NUMBER() OVER (PARTITION BY [Pricing].[pd_code] ORDER BY [pd_ref]) row,
[pd_code]
  ,[pd_ref]
  ,[pg_groupref]
  ,[pr_price]
  ,[pg_group]
  ,[st_styleref]
  ,[st_style]
FROM [Pricing])

SELECT a.[pd_code]
  ,a.[pd_ref]
  ,a.[pg_groupref]
  ,a.[pg_group]
  ,a.[st_styleref]
  ,a.[st_style],
   a.pr_price - ISNULL(b.pr_price,0)
FROM 
      cte a
      LEFT JOIN cte b
      on a.[pd_code] = b.[pd_code]
      and a.row = b.row+1

As you can see this doesnt work at all, as i have not specified a condition, i think its completly wrong, but I'm pretty sure I need to use a CTE query in this problem.

Can anyone work out the proper way to perform this query to the problem described? If you require any further clarification, please ask.

Expected OutPUT

**pd_code | pd_ref | pg_groupref | pg_group | st_styleref | st_style | Price    | (no col name)**

  DG 100  | 72166  | 1336        | xCarcass | 3484        | xCarcass | 500 | 0 

  DG 100  | 72166  | 1337        | grp 1    | 3480        | stlye 3  | 550 | 50

  DG 100  | 72166  | 1338        | grp 2    | 3488        | style 7  | 700 | 200

NOTE (note sure how to edit this info as a nice table)

Further clarification

To help clairify, each pd_code has about 111 rows of different styles, 1 of which is called 'xCarcass', all styles have a rpice, i need a new column on the end that basically caluclates the difference between its price and that of the specific row of 'xCarcass' for each pd_code

Upvotes: 2

Views: 44

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use the max window function to get the price of xcarcass row for each pd_code and use it for subtraction on all the other non xcarcass row's for that pd_code.

select p.*
,pr_price - coalesce(max(case when st_style like '%xcarcass%' then pr_price end) over(partition by pd_code) ,0) val_diff
from pricing p

Upvotes: 1

Viki888
Viki888

Reputation: 2774

If you are using sql-server-2012, then there is an analytical function available to achieve this which is LEAD(). Below is the query which you can make use of

SELECT 
[pd_code]
  ,[pd_ref]
  ,[pg_groupref]
  ,[pr_price] - ISNULL(LEAD(pr_price) OVER(ORDER BY pd_ref), 0)
  ,[pg_group]
  ,[st_styleref]
  ,[st_style]
FROM [Pricing]

Upvotes: 0

Related Questions