Hello
i have the following query but it takes long can you help me please?
what should i do to make it faster?
set language german;
set dateformat ymd
declare @restaurantID uniqueidentifier
set @restaurantID = '5CB89A18-2091-4A42-B56C-6BC18F927208'
declare @startdate datetime
declare @enddate datetime
set @startdate = '2011-02-01'
set @enddate = '2011-02-02'
-- CH & CA Zahler (nur Stützungsanteil)
select DAT_Bon.ID as BonID,
DAT_Bon.CreationDate,
--DAT_CustomerGroup_HISTArchive.CustomerGroup_ID as CustomerGroupID,
DAT_CustomerGroup_HISTArchive.Name as CustomerGroupName,
DAT_CustomerGroup_HISTArchive.CustomerGroupNumber as CustomerGroupNumber,
--{1} AS SubGroupName,
--{2} AS SubGroupID,
DAT_BonLine.VATValue as vat,
SUM(Subsidy * (1 / (1 + VATValue))) netPrice,
-- SUM(Round(Round(Subsidy * (1 / (1 + VATValue)), 2) * (1 + DAT_bonline.VATValue), 2)) grossPrice,
0.0 grossPrice,
-- SUM(Round(Round(Subsidy * (1 / (1 + VATValue)), 2) * DAT_bonline.VATValue, 2)) VATValuePrice,
0.0 VATValuePrice,
sum(DAT_bonline.Quantity) as Quantity
FROM DAT_Bon WITH (NOLOCK)
inner join DAT_Person_HISTArchive WITH (NOLOCK) on dbo.GetPersonArchiveID(DAT_Bon.Person_ID, DAT_Bon.CreationDate) = DAT_Person_HISTArchive.id
inner join DAT_CustomerGroup_HISTArchive WITH (NOLOCK) on dbo.GetCustomerGroupArchiveID(DAT_Person_HISTArchive.CustomerGroup_ID, DAT_Bon.CreationDate) = DAT_CustomerGroup_HISTArchive.ID
inner join INV_InvoiceReceiver_CustomerGroup WITH (NOLOCK) on INV_InvoiceReceiver_CustomerGroup.CustomerGroup_ID = DAT_CustomerGroup_HISTArchive.CustomerGroup_ID
inner join DAT_BonLine WITH (NOLOCK) on DAT_BonLine.Bon_ID = DAT_Bon.ID
inner join DAT_Article_HISTArchive WITH (NOLOCK) on dbo.GetArticleArchiveID(DAT_Bonline.Article_ID, DAT_Bon.CreationDate) = DAT_Article_HISTArchive.ID
inner join LOV_ArticleGroup WITH (NOLOCK) on LOV_ArticleGroup.ID = DAT_Article_HISTArchive.ArticleGroup_ID
-- {3} => inner join DAT_Card_HISTArchive on dbo.GetCardArchiveID(DAT_Bon.card_id, DAT_Bon.CreationDate) = DAT_Card_HISTArchive.ID if card filtering is active, otherwise no join
where DAT_Bon.CreationDate between @startdate and @enddate
AND DAT_Article_HISTArchive.Restaurant_ID = @restaurantID
and Subsidy != 0 -- to include BO-Storno in the month the storno was made, here are also negative subsidy amounts allowed
AND dat_bon.id in
(select bon_id from DAT_Bon_PaymentType WITH (NOLOCK)
inner join LOV_PaymentType on LOV_PaymentType.ID = DAT_Bon_PaymentType.PaymentType_ID
where Bon_ID = DAT_Bon.ID and
LOV_PaymentType.ShortName in ('CH', 'CA','ID')) -- only CH and CA
and DAT_BonLine.Cancel_ID IS NULL and DAT_Bon.IsCancel <> 1
-- only decrement card transactions
and DAT_Bon.ID not in
(SELECT Bon_ID FROM DAT_CardTransaction WITH (NOLOCK) INNER JOIN
LOV_TransactionType WITH (NOLOCK) ON LOV_TransactionType.ID = DAT_CardTransaction.TransactionType_ID
WHERE DAT_bon.ID = DAT_CardTransaction.bon_id AND LOV_TransactionType.TransactionType <> 1)
-- not already in a valid (not cancelled) invoice
and DAT_Bon.ID not in
(SELECT Bon_ID FROM INV_Invoice_Bon WITH (NOLOCK) INNER JOIN
INV_Invoice WITH (NOLOCK) ON INV_Invoice_Bon.invoice_ID = INV_Invoice.ID
WHERE DAT_bon.ID = INV_Invoice_Bon.bon_id and INV_Invoice.Cancel_ID IS NULL)
Group by DAT_Bon.ID, DAT_CustomerGroup_HISTArchive.CustomerGroup_ID, DAT_CustomerGroup_HISTArchive.CustomerGroupNumber, DAT_CustomerGroup_HISTArchive.Name, DAT_Bonline.VATValue, DAT_Bon.CreationDate