Sequential Column Where Conditional - Sql / Access

Suppose we have some data that looks like so R_Id Nm Base Dest Proj Cust_id 201203 201202 201201 MRBR Bob LONDON UK Project1 1 0

Solution 1:

Well I can think of a somewhat ugly solution to this question, but it involves the use of a custom VBA function.

Concatenate and Test String

Your SQL statement should be something like:

SELECT*FROM tblName 
WHERE IsSeqHigh([201203] & ";" & [201202] & ";" & ..., 1000);

And then, in a VBA module we define:

PublicFunction IsSeqHigh(seq AsString, thres AsDouble) AsBoolean
    IsSeqHigh = FalseDim valStrs() AsString
    valStrs = Split(seq, ";")

    For n = 1To UBound(valStrs) - 1If (valStrs(n) >= thres) And (valStrs(n + 1) >= thres) Then
            IsSeqHigh = TrueExitForEndIfNext n
EndFunction

Another approach

Alternatively, if your schema is fixed and unlikely to change - and you have a primary key value, you can write a VBA function which takes the primary key value and scans the columns for the specific condition you are looking for.

In short, there is no good SQL-only solution that I can think of.

Solution 2:

You said "orders over 1000", which should exclude Pjt_Id = 1 from the output. If you actually want orders at least 1000, change > to >= in this query.

SELECT
    p.Pjt_Id,
    p.OrderPriceQ1,
    p.OrderPriceQ2,
    p.OrderPriceQ3,
    p.CustomerFROM TblPureferret AS p
WHERE
       (p.OrderPriceQ1 > 1000 AND p.OrderPriceQ2 > 1000)
    OR (p.OrderPriceQ2 > 1000 AND p.OrderPriceQ3 > 1000);

Solution 3:

Try this

select 
    Pjt_Id  ,OrderPriceQ1 ,OrderPriceQ2 ,OrderPriceQ3 ,Customer  
fromtablewhere 
(
    (OrderPriceQ1>=1000and OrderPriceQ2 >=1000) or
    (OrderPriceQ1>=1000and OrderPriceQ3 >=1000) or
    (OrderPriceQ2>=1000and OrderPriceQ3 >=1000) 
)