Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Can this be faster
Message
De
28/10/2015 12:34:03
 
 
À
Tous
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Titre:
Can this be faster
Versions des environnements
SQL Server:
SQL Server 2014
Application:
Desktop
Divers
Thread ID:
01626554
Message ID:
01626554
Vues:
82
Goal: To get all accounts that have purchased a specific product more then one month ago but have *not* purchased within the last month.

I've tried the following but it's very slow where a lot of accounts have purchased the product in the last month:
WITH Temp_CTE (AccountCode,InvoiceDate)
AS
(
SELECT SI.AccountCode,MAX(SI.InvoiceDate) FROM SalesInvoiceItem IV 
INNER JOIN SalesInvoice SI ON IV.InvoiceNumber = SI.InvoiceNumber
WHERE ProductCode='56.GTR003.001.01' AND SI.InvoiceDate >= DATEADD(MONTH, - 1,GETDATE())
GROUP BY SI.AccountCode
)
SELECT SI.AccountCode,MAX(SI.InvoiceDate) AS Latest  FROM SalesInvoiceItem IV 
INNER JOINSalesInvoice SI ON IV.InvoiceNumber = SI.InvoiceNumber
LEFT JOIN Temp_CTE ON SI.AccountCode = Temp_CTE.AccountCode
WHERE ProductCode='56.GTR003.001.01' AND SI.InvoiceDate < DATEADD(MONTH, - 1,GETDATE()) AND Temp_CTE.AccountCode IS NULL
GROUP BY SI.AccountCode
I'm useless at SQL - can anyone suggest a way of speeding this up?
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform