how to extract e specific data from a table

• oonica43

  SSC Journeyman

  Points: 87

  More actions

  December 7, 2018 at 12:44 am

  #369355

  hello,

  I am a beginner in SQL Server  and i really need some help in extracting some data from a table.
  I have this table:

  create table Table
  (
  [Porduct Line] [nvarchar]2 NULL,
  [Business Unit] [nvarchar]20 NULL,
  )

  insert into Table([Porduct Line],[Business Unit] )
  values('1Y',''),
  ('1Y', 'IT Product'),
  ('1Y','Blades'),
  ('FE','IT Product'),
  ('FE',''),
  ('H7','Storage'),
  ('H7',''),
  ('H7','Blades'),
  ('H7','Hybrid')

  

  I need to extract only the Product Lines where i have blanks and IT Products in the Business Unit. So i need to partition it by Porduct Line. In my case the result would be FE. Even if 1Y has blanks and IT Product, it also have Blades, so i am not interested in it.
  This is just a sample of my data, i dont know what other exception besides blades i could have. Maybe i will have a Product line with blanks, IT Product and Hybrid etc.
  So i need to extract only the Product Lines that have Blanks as well as IT Products. If the same product line has besides those two, other units, then i dont need it.
  It saw a similar topic on another forum but it didnt work for me.
  Ii would really appreciate any help.

• pietlinden

  SSC Guru

  Points: 63573

  More actions

  December 7, 2018 at 1:04 am

  #2015724

  Not bad for a beginner!  I should ask you what you tried first. (It helps if you post that too, because people are more likely to help you if you can show that you at least tried to answer the question.) At any rate, since you're new, I'll give you my solution (or attempt at it).
  use tempdb;
GO

  create table ProductLines
(
    ProductLine char(2) NOT NULL,
    BusinessUnit varchar(20) NULL,
);
GO

  insert into ProductLines(ProductLine, BusinessUnit)
values('1Y',NULL),
('1Y', 'IT Product'),
('1Y','Blades'),
('FE','IT Product'),
('FE',NULL),
('H7','Storage'),
('H7',NULL),
('H7','Blades'),
('H7','Hybrid');

  /* I need to extract only the Product Lines where i have blanks and IT Products in the Business Unit. */
  SELECT ProductLine
FROM ProductLines
WHERE BusinessUnit = 'IT Product'
INTERSECT
SELECT ProductLine
FROM ProductLines
WHERE BusinessUnit IS NULL

  The answer is {TY, FE}, right?

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  December 7, 2018 at 1:06 am

  #2015725

  Here is an example that should get you passed this hurdle.
  

  
USE TEEST;
GO
SET NOCOUNT ON;

  declare @table table
(
[Porduct Line] nvarchar(2) NULL,
[Business Unit] nvarchar(20) NULL
)

  insert into @table([Porduct Line],[Business Unit] )
values('1Y',''),
('1Y', 'IT Product'),
('1Y','Blades'),
('FE','IT Product'),
('FE',''),
('H7','Storage'),
('H7',''),
('H7','Blades'),
('H7','Hybrid');

  ;WITH BASE_DATA AS
(
  SELECT
   T.[Porduct Line]
   ,SUM(CASE 
    WHEN T.[Business Unit] = '' THEN 0
    WHEN T.[Business Unit] = 'IT Product' THEN 0
    ELSE 1
   END) AS FLG
  FROM  @table T
  GROUP BY T.[Porduct Line]
)
SELECT
  BD.[Porduct Line]
FROM BASE_DATA BD
WHERE BD.FLG = 0;


• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  December 7, 2018 at 6:24 am

  #2015743

  drop table #Table;
go
create table #Table
(
    ProductLine [nvarchar](2) NULL,
    BusinessUnit [nvarchar](20) NULL,
)

  insert into #Table(ProductLine,BusinessUnit )
values('1Y',''),
('1Y', 'IT Product'),
('1Y','Blades'),
('FE','IT Product'),
('FE',''),
('H7','Storage'),
('H7',''),
('H7','Blades'),
('H7','Hybrid')

  select * from #Table

  SELECT t.ProductLine
  FROM #Table t
 WHERE t.BusinessUnit = 'IT Product'
   AND NOT EXISTS(SELECT *
                    FROM #Table t2
                   WHERE t2.ProductLine = t.ProductLine
                     AND t2.BusinessUnit NOT IN('','IT Product'))
   AND EXISTS(SELECT *
                FROM #Table t2
               WHERE t2.ProductLine = t.ProductLine
                 AND t2.BusinessUnit IN(''))

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  December 7, 2018 at 10:57 am

  #2015792

  
SELECT [Product Line]
FROM dbo.[Table]
GROUP BY [Product Line]
HAVING MAX(CASE WHEN [Business Unit] = '' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN [Business Unit] = 'IT Product' THEN 1 ELSE 0 END) = 1
ORDER BY [Product Line]

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  December 8, 2018 at 3:29 am

  #2015814

  Jonathan AC Roberts - Friday, December 7, 2018 6:24 AM

  drop table #Table;
  go
  create table #Table
  (
      ProductLine [nvarchar](2) NULL,
      BusinessUnit [nvarchar](20) NULL,
  )

  insert into #Table(ProductLine,BusinessUnit )
  values('1Y',''),
  ('1Y', 'IT Product'),
  ('1Y','Blades'),
  ('FE','IT Product'),
  ('FE',''),
  ('H7','Storage'),
  ('H7',''),
  ('H7','Blades'),
  ('H7','Hybrid')

  select * from #Table

  SELECT t.ProductLine
  FROM #Table t
 WHERE t.BusinessUnit = 'IT Product'
   AND NOT EXISTS(SELECT *
                    FROM #Table t2
                   WHERE t2.ProductLine = t.ProductLine
                     AND t2.BusinessUnit NOT IN('','IT Product'))
   AND EXISTS(SELECT *
                FROM #Table t2
               WHERE t2.ProductLine = t.ProductLine
                 AND t2.BusinessUnit IN(''))

  I would be careful here, this code scans the set three times, can be a potential time bomb if the set is growing.
  
  Even with a covering index added, it will still do one table scan and two index scans.

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  December 8, 2018 at 4:12 am

  #2015817

  ScottPletcher - Friday, December 7, 2018 10:57 AM

  
  SELECT [Product Line]
  FROM dbo.[Table]
  GROUP BY [Product Line]
  HAVING MAX(CASE WHEN [Business Unit] = '' THEN 1 ELSE 0 END) = 1 AND
      MAX(CASE WHEN [Business Unit] = 'IT Product' THEN 1 ELSE 0 END) = 1
  ORDER BY [Product Line]

  This query will return the unwanted "1Y" element, which has an additional "Blades" entry.
  

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  December 8, 2018 at 5:12 am

  #2015820

  Eirikur Eiriksson - Saturday, December 8, 2018 3:29 AM

  Jonathan AC Roberts - Friday, December 7, 2018 6:24 AM

  SELECT t.ProductLine
  FROM #Table t
 WHERE t.BusinessUnit = 'IT Product'
   AND NOT EXISTS(SELECT *
                    FROM #Table t2
                   WHERE t2.ProductLine = t.ProductLine
                     AND t2.BusinessUnit NOT IN('','IT Product'))
   AND EXISTS(SELECT *
                FROM #Table t2
               WHERE t2.ProductLine = t.ProductLine
                 AND t2.BusinessUnit IN(''))

  I would be careful here, this code scans the set three times, can be a potential time bomb if the set is growing.
  
  Even with a covering index added, it will still do one table scan and two index scans.

  Not really a "time bomb". A quick test on 10 million rows and it's faster than your code:
  drop table #Table;
go
create table #Table
(
  ProductLine [nvarchar](2) NULL,
  BusinessUnit [nvarchar](20) NULL,
)

  ;WITH A(A) AS (SELECT '' FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) T(C)),
B AS (SELECT TOP(10000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM A A,A B,A C,A D,A E,A F,A G,A H) -- 16^8
insert into #Table(ProductLine,BusinessUnit)
SELECT LEFT(CONVERT(nvarchar(50), NEWID()),2), CASE WHEN t.N%41=0 THEN 'IT Product' WHEN T.N%43=0 THEN '' ELSE LEFT(CONVERT(nvarchar(50), NEWID()),3) END
FROM B t

  --select * from #Table
set statistics io,time on
SELECT t.ProductLine
FROM #Table t
WHERE t.BusinessUnit = 'IT Product'
 AND NOT EXISTS(SELECT *
       FROM #Table t2
       WHERE t2.ProductLine = t.ProductLine
       AND t2.BusinessUnit NOT IN('','IT Product'))
 AND EXISTS(SELECT *
      FROM #Table t2
     WHERE t2.ProductLine = t.ProductLine
      AND t2.BusinessUnit IN(''))

  ;WITH BASE_DATA AS
(
SELECT
  T.ProductLine
 ,SUM(CASE
  WHEN T.BusinessUnit = '' THEN 0
  WHEN T.BusinessUnit = 'IT Product' THEN 0
  ELSE 1
  END) AS FLG
FROM #Table T
GROUP BY T.ProductLine
)
SELECT
BD.ProductLine
FROM BASE_DATA BD
WHERE BD.FLG = 0;

  (0 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Table______________________________________________________________________________________________________________00000000000B'. Scan count 26, logical reads 62643, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
 CPU time = 3718 ms, elapsed time = 503 ms.

  (0 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Table______________________________________________________________________________________________________________00000000000B'. Scan count 9, logical reads 31140, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
 CPU time = 6298 ms, elapsed time = 831 ms.


• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  December 8, 2018 at 6:07 am

  #2015821

  Eirikur Eiriksson - Friday, December 7, 2018 1:06 AM

  Here is an example that should get you passed this hurdle.
  

  
USE TEEST;
GO
SET NOCOUNT ON;

  declare @table table
(
[Porduct Line] nvarchar(2) NULL,
[Business Unit] nvarchar(20) NULL
)

  insert into @table([Porduct Line],[Business Unit] )
values('1Y',''),
('1Y', 'IT Product'),
('1Y','Blades'),
('FE','IT Product'),
('FE',''),
('H7','Storage'),
('H7',''),
('H7','Blades'),
('H7','Hybrid');

  ;WITH BASE_DATA AS
(
  SELECT
   T.[Porduct Line]
   ,SUM(CASE 
    WHEN T.[Business Unit] = '' THEN 0
    WHEN T.[Business Unit] = 'IT Product' THEN 0
    ELSE 1
   END) AS FLG
  FROM  @table T
  GROUP BY T.[Porduct Line]
)
SELECT
  BD.[Porduct Line]
FROM BASE_DATA BD
WHERE BD.FLG = 0;


  This code will return a row if it only has one row for that Product Line and that row has Business Unit with a value 'IT Product' 
  The OP stated "I need to extract only the Product Lines where i have blanks and IT Products in the Business Unit. 
  So i need to extract only the Product Lines that have Blanks as well as IT Products"
  I read that as needing both values for a product line.

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  December 8, 2018 at 7:18 am

  #2015822

  Jonathan AC Roberts - Saturday, December 8, 2018 6:07 AM

  Eirikur Eiriksson - Friday, December 7, 2018 1:06 AM

  Here is an example that should get you passed this hurdle.
  

  
USE TEEST;
GO
SET NOCOUNT ON;

  declare @table table
(
[Porduct Line] nvarchar(2) NULL,
[Business Unit] nvarchar(20) NULL
)

  insert into @table([Porduct Line],[Business Unit] )
values('1Y',''),
('1Y', 'IT Product'),
('1Y','Blades'),
('FE','IT Product'),
('FE',''),
('H7','Storage'),
('H7',''),
('H7','Blades'),
('H7','Hybrid');

  ;WITH BASE_DATA AS
(
  SELECT
   T.[Porduct Line]
   ,SUM(CASE 
    WHEN T.[Business Unit] = '' THEN 0
    WHEN T.[Business Unit] = 'IT Product' THEN 0
    ELSE 1
   END) AS FLG
  FROM  @table T
  GROUP BY T.[Porduct Line]
)
SELECT
  BD.[Porduct Line]
FROM BASE_DATA BD
WHERE BD.FLG = 0;


  This code will return a row if it only has one row for that Product Line and that row has Business Unit with a value 'IT Product' 
  The OP stated "I need to extract only the Product Lines where i have blanks and IT Products in the Business Unit. 
  So i need to extract only the Product Lines that have Blanks as well as IT Products"
  I read that as needing both values for a product line.

  I stand corrected, your solution is, according to the OP's ask, better than mine.
  

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  December 10, 2018 at 10:04 am

  #2015909

  Eirikur Eiriksson - Saturday, December 8, 2018 4:12 AM

  ScottPletcher - Friday, December 7, 2018 10:57 AM

  
  SELECT [Product Line]
  FROM dbo.[Table]
  GROUP BY [Product Line]
  HAVING MAX(CASE WHEN [Business Unit] = '' THEN 1 ELSE 0 END) = 1 AND
      MAX(CASE WHEN [Business Unit] = 'IT Product' THEN 1 ELSE 0 END) = 1
  ORDER BY [Product Line]

  This query will return the unwanted "1Y" element, which has an additional "Blades" entry.
  

  Sorry, D'OH, thought I had my last HAVING condition in there. I even ran a test on the data first, I obviously incompletely copied it.

  
SELECT [Product Line]
FROM dbo.[Table]
GROUP BY [Product Line]
HAVING MAX(CASE WHEN [Business Unit] = '' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN [Business Unit] = 'IT Product' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN [Business Unit] IN ('', 'IT Product') THEN 0 ELSE 1 END) = 0
ORDER BY [Product Line]

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".