-- On DeliveryNote
CREATE INDEX idx_DeliveryNote_CustCode_EntryDate ON DeliveryNote(CustomerCode, EntryDate)

-- On DeliveryNoteItem
CREATE INDEX idx_DeliveryNoteItem_DNNo_ItemCode ON DeliveryNoteItem(DNNo, ItemCode)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_PENDINGDELIVERY]
@CustCode NVARCHAR(500),
@FROM DATETIME,
@TO DATETIME
AS
BEGIN

IF OBJECT_ID('tempdb..#DeliveredQty') IS NOT NULL
    DROP TABLE #DeliveredQty;

SELECT 
    ii.dnno, 
    i.customercode,
    ii.itemcode, 
    SUM(CASE 
            WHEN i.sr = 'sales' THEN ii.qty 
            ELSE -ii.qty 
        END) AS DeliveredQty
INTO #DeliveredQty
FROM invoiceitem ii
LEFT JOIN invoice i 
    ON i.invno = ii.invno 
    AND i.sr = ii.sr
WHERE ISNULL(ii.deleted, 'N') = 'N'
  AND ii.dnno IS NOT NULL
GROUP BY ii.dnno, ii.itemcode, i.customercode;

-- Step 2: Main query using the precomputed table
SELECT DISTINCT 
    dn.DNNo,
    dn.CustomerCode,
    dn.CustomerName,
    dn.PONo,
    dn.Attended,
    dn.Telephone AS Phone,
    dn.FAX,
    dn.Address,
    dn.QuotationNo AS QuotNo,
    CONVERT(VARCHAR(25), dn.poDate, 103) AS poDate,
    CONVERT(VARCHAR(25), dn.EntryDate, 103) AS EntryDate,
    LTRIM(RTRIM(dn.Del1)) AS Del1,
    LTRIM(RTRIM(dn.Del2)) AS Del2,
    LTRIM(RTRIM(dn.Del3)) AS Del3,
    LTRIM(RTRIM(dn.Del4)) AS Del4,
    dn.Attention
FROM DeliveryNoteItem dni
LEFT JOIN DeliveryNote dn 
    ON dn.DNNo = dni.DNNo 
    AND ISNULL(dn.deleted, 'N') = 'N'
    AND ISNULL(dni.deleted, 'N') = 'N'
LEFT JOIN #DeliveredQty dq 
    ON dq.dnno = dni.dnno 
    AND dq.itemcode = dni.itemcode 
    AND dq.customercode = dn.customercode
WHERE dn.CustomerCode = @CustCode
  AND dn.EntryDate BETWEEN @FROM AND @TO
GROUP BY 
    dni.Slno, dni.ItemCode, dni.ItemName, dni.Rate, dni.Unit, dni.Qty, dni.Amount,
    dn.DNNo, dn.CustomerCode, dn.CustomerName, dn.PONo, dn.Attended, 
    dn.Telephone, dn.FAX, dn.Address, dn.QuotationNo, dn.poDate, dn.EntryDate,
    dn.Del1, dn.Del2, dn.Del3, dn.Del4, dn.Attention
HAVING SUM(dni.Qty) - ISNULL(SUM(dq.DeliveredQty), 0) > 0
ORDER BY dn.DNNo DESC;
END;