Meeting 2 conditions in 2 different columns

user_10652077 0
Viewed 58

I'm trying to run a query where I need very specific conditions to be met:

  1. Sales code is All
  2. Item has Original Price flag set
  3. Item has a price with no Original Price flag set that is the same as the Price with Original Price flag set
  4. Price without Original price flag set must be created after the price with Original price flag

Currently I am using the following query to get the information I need;

select [item no_], [variant code],[unit price including vat], 
[original price], [Starting Date], [Ending Date] from [Sales Price]
where [Sales Code] = 'all'
and [Ending Date] = '1753-01-01 00:00:00.000'

This is the example result: Click here

1 means Original Price flag is set and 0 means it is not

The result I need from this query would be to only show these two: Click here

sql-server sql
2 Answers

I am assuming you are working with SQL Server as your current query syntax suggests.

If, so you can use lag() :

select sp.*
from (select sp.*,
             lag([original price]) over (partition by [item no_] order by [Starting Date]) as prev_price
      from [Sales Price] sp
      where [Sales Code] = 'all'
     ) sp
where ([original price] = 1 or prev_price = 1);
user_55294450

let me know if you need me to explain; else its pretty straight forward.

select a.* 
from (
select [item no_]
, [variant code]
,[unit price including vat]
, [original price]
, [Starting Date]
, [Ending Date] 
,Column_Test = case when ( [original price] = 1 and [original price] = 0 ) and ([Starting Date]<[Ending Date]) then 1 else 0 end
from [Sales Price]
where [Sales Code] = 'all'
and [Ending Date] = '1753-01-01 00:00:00.000' 
) a
where Column_Test = 1
user_34993910
Related