problems with where

  • Hello,

    I have problems figuring this out. What I want is a conditional where. If a column has a value greater than zero I want to select all the rows belonging to the same number. If the column has no value greater than zero I dont want these rows selected.

    I have a table like this:

    ID NUMBER VALUE

    ------------------------

    1 1 0

    2 1 1

    3 1 0

    4 2 0

    5 2 0

    In the example above the three rows with number of 1 would be selected because one of the rows has a value that is greater than zero.

  • Is it homework?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • haha no

  • declare @t table (ID int, VALUE int)

    insert @t select 1, 0

    insert @t select 1, 0

    insert @t select 1, 1

    insert @t select 2, 0

    insert @t select 2, 0

    select t1.*

    from @t t1

    join @t t2 on t2.ID = t1.ID

    where t2.VALUE > 0

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • something like this

    create table #temp (id int, number int, value int)

    insert into #temp (id, number, value) values (1,1,0),(2,1,1),(3,1,0),(4,2,0),(5,2,0)

    with cte1 as

    (

    select

    number

    from

    #temp

    where

    value > 0

    group by

    number

    )

    select

    #temp.*

    from

    #temp

    join

    cte1

    on

    #temp.number = cte1.number

  • Another way

    DECLARE @t TABLE (ID INT, NUMBER INT, VALUE INT)

    INSERT INTO @t(ID,NUMBER,VALUE)

    SELECT 1,1,0 UNION ALL

    SELECT 2,1,1 UNION ALL

    SELECT 3,1,0 UNION ALL

    SELECT 4,2,0 UNION ALL

    SELECT 5,2,0;

    WITH CTE AS (

    SELECT ID,NUMBER,VALUE,

    MAX(VALUE) OVER(PARTITION BY NUMBER) AS maxVal

    FROM @t)

    SELECT ID,NUMBER,VALUE

    FROM CTE

    WHERE maxVal>0;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Hello,

    Thanks for this!

    Problem is that I have a inner join on number on another table. Can this solution still be applied?

  • Yes, it can.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Ok great, it worked with the inner join. Here is the full query.

    Thanks again!

    USE [navtables]

    GO

    /****** Object: StoredProcedure [dbo].[RS_OrderStock] Script Date: 03/12/2012 12:36:32 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[RS_OrderStock]

    AS

    SELECT

    SH.[No_] AS 'Ordernr.',

    SIL.[Document_Type] as 'saleslinetyp',

    SH.[Sell-to_Customer_No_] AS 'Förs.kundnr.',

    SH.[Bill-to_Name] AS 'Kundnamn',

    SH.[Order_Date] AS 'Orderdat.',

    SH.[Shipment_Date] AS 'Lastdatum',

    SH.[Requested_Delivery_Date] AS 'Beg. Lev.dat.',

    SIL.[Promised_Delivery_Date] AS 'Lov. lev dat.',

    SIL.[No_] AS 'Art.nr.',

    SH.[Document_Type] AS 'DokTyp',

    SIL.[Description] AS 'Beskr.',

    SIL.[Location_Code] AS 'Lagerst.',

    SIL.[Quantity] AS 'Antal',

    SIL.[Unit_of_Measure] AS 'Enhet',

    SIL.[Amount] AS 'Belopp',

    SIL.[Unit_Price] AS 'A-pris',

    SIL.[Line_Amount] AS 'Radbelopp',

    SIL.[Qty__to_Ship] AS 'Ant. att utlev.',

    SIL.[Quantity_Shipped] AS 'Utlev. ant.',

    SIL.[Qty__to_Invoice] AS 'Ant. att fakt.',

    SIL.[Shortcut_Dimension_1_Code] AS 'VG',

    SH.[Salesperson_Code] AS 'SKod',

    SIL.[Line_Discount_Amount] AS 'Rabatt SEK',

    (CASE WHEN SIL.Qty__to_Invoice > 0 THEN (SIL.[Qty__to_Invoice] * SIL.[Unit_Price]) - ((SIL.[Line_Discount_Amount] / SIL.[Quantity])) * SIL.[Qty__to_Invoice] ELSE NULL END) as 'spec'

    FROM Sales_Header SH

    INNER JOIN Sales_Line SIL

    ON SH.No_ = SIL.Document_No_

    join Sales_Line SIL2 on SIL.Document_No_ = SIL2.Document_No_

    WHERE SH.[Document_Type] = 'Order' AND SIL.[Type] = 'Item' AND SIL.[Document_Type] = 'Order' AND SIL2.Qty__to_Invoice > 0 --AND SH.[No_] IN ('28146', '28187')

    GROUP BY SH.[No_] ,

    SIL.[Document_Type] ,

    SH.[Sell-to_Customer_No_],

    SH.[Bill-to_Name],

    SH.[Order_Date],

    SH.[Shipment_Date],

    SH.[Requested_Delivery_Date],

    SIL.[Promised_Delivery_Date] ,

    SIL.[No_],

    SH.[Document_Type],

    SIL.[Description],

    SIL.[Location_Code],

    SIL.[Quantity],

    SIL.[Unit_of_Measure],

    SIL.[Amount],

    SIL.[Unit_Price],

    SIL.[Line_Amount],

    SIL.[Qty__to_Ship],

    SIL.[Quantity_Shipped],

    SIL.[Qty__to_Invoice],

    SIL.[Shortcut_Dimension_1_Code],

    SH.[Salesperson_Code],

    SIL.[Line_Discount_Amount],

    SIL.[Line_No_]

    ORDER BY SH.[No_], SIL.[Line_No_], SH.[Order_Date]

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply