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.
-
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
-
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?
-
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