March 12, 2012 at 8:50 am
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.
March 12, 2012 at 8:57 am
March 12, 2012 at 8:59 am
haha no
March 12, 2012 at 8:59 am
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
March 12, 2012 at 9:04 am
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
March 12, 2012 at 9:18 am
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/61537March 12, 2012 at 1:22 pm
Hello,
Thanks for this!
Problem is that I have a inner join on number on another table. Can this solution still be applied?
March 12, 2012 at 1:37 pm
March 12, 2012 at 4:08 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy