Is there a more efficient way to write this query?

Transact-SQL
1

Is there a more efficient way for me to write this query. Because right now i'm getting a spool issue.

basically I need to see all the products that a bottler has not purchased in the giving date range. currently the first half of the query before the RIGHT join is the derived table that consist of all the products that WERE purchased by the bottler. And the derived table afterwards is to get all products that was NOT purchased by a bottler

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

-- Query2 - retturns all the products Bottlers has NOT purchased in the past 30-days

SELECT distinct a.BTLR_DLVR_PNT_NO
           ,Bottler_Name
           , b. BrandPack

FROM

(SELECT distinct
inv_corp.Inv_Dt as invoice_date
,snap.BTLR_DLVR_PNT_NO AS BTLR_DLVR_PNT_NO
,btlr_branch_nm as Bottler_Name
,prodet.prd_nm as product
, inv_cat_type_cd as cattype

FROM corp_invoice_views.invoice_corp inv_corp 

JOIN CORP_LINES_views.LINE_CORP line_corp 
   ON inv_corp.Tsr_Inv_Ref_No = line_corp.Tsr_Inv_Ref_No 
   AND inv_corp.Inv_Dt = line_corp.Inv_Dt
   
JOIN CHR_VIEWS.DPT_SNAPSHOT_SELECT snap
   ON inv_corp.BTLR_DLVR_PNT_NO  = snap.BTLR_DLVR_PNT_NO
  and inv_corp.OWNRSHP_ID = snap.OWNRSHP_ID 
  
 Join NSR_VIEWS.PRODET prodet
  ON prodet.cce_material_no = line_corp.cce_material_no 
  
  Join CBS_ASR_VIEWS.CBS_MATL_ENTRPRS_PKG_ROLLUP  entpkg
    ON  prodet.CCE_MATERIAL_NO = entpkg.CCE_MATERIAL_NO

Where 1=1

AND prodet.First_Sale_Dt IS NOT NULL
AND line_corp.Inv_DT >= (current_date - 30) -- Looks back 2weeks
and snap.ownrshp_id in (10,55,75,110,65,85,95) ----Bottler Heartland's ownershipid
and cattype IN ('D', 'B')
) as A

RIGHT JOIN

(Select distinct prodet.prd_nm,
brnd.Brnd_Nm||' '||entpkg.ENTRPRS_PKG_DESC AS BrandPack

From NSR_VIEWS.PRODET prodet

JOIN CBS_ASR_VIEWS.CBS_MATL_ENTRPRS_PKG_ROLLUP AS entpkg
ON prodet.CCE_MATERIAL_NO = entpkg.CCE_MATERIAL_NO

JOIN NSR_VIEWS.Brand AS brnd 
ON prodet.Cce_Brand_Flvr = brnd.Brnd_No

) as B ON a.product <> b.prd_nm

where
a.product is not null
and a.BTLR_DLVR_PNT_NO = '0000500113662' ----Bottler

Order by Bottler_Name
;

2

This is a SQL Server forum. That code is not from SQL Server.

Please at least state the dbms you are using to give people a better idea how to help you.

3

Sorry about that, its from teradata. But everything in this query is pretty much standard SQL

4

hi

these following links may help you !!!!

http://kedar.nitty-witty.com/blog/no-more-spool-space-teradata-query-solution

http://kedar.nitty-witty.com/blog/no-more-spool-space-teradata-query-solution

5

Have you tried using EXCEPT?

DROP TABLE IF EXISTS dbo.DaTable;
DROP TABLE IF EXISTS dbo.DaDrinks;

CREATE TABLE dbo.DaTable(
	id	int	IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	Bottler	NVARCHAR(255)	NOT NULL,
	product	NVARCHAR(255)	NOT NULL
)

CREATE TABLE dbo.DaDrinks(
	id	int	IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	product	NVARCHAR(255)	NOT NULL
)
GO

INSERT INTO dbo.DaTable (Bottler, product) VALUES
('BB', 'beer'),
('BB', 'beer'),
('BB', 'ginder ale'),
('BB', 'ale'),
('BB', 'ale')

INSERT INTO dbo.DaDrinks(product) VALUES
('beer'),
('water'),
('ginder ale'),
('ale'),
('coke')

Let the DBMS find the differences, using EXCEPT

SELECT product 
FROM dbo.DaDrinks
	EXCEPT
SELECT distinct product
FROM dbo.DaTable
WHERE Bottler = 'BB'
1 Like
6

OMG, only now I realise I responded to a 9 month old post.

7

WIM ,

i am going through all the OLD Posts
wherever I feel Like ... i am giving my solutions

when I respond to OLD stuff ...its put back First in line !!

Very Rarely the original poster responds !!!
They need a quick quick FIX and then they disappear !!!

:slight_smile: :slight_smile:

YOU also GOT a like !!!
:+1: