SELECT        TOP (100) PERCENT dbo.TransactionHistory.WarehouseName, dbo.TransactionHistory.SourceAreaReference AS CustomerID,
                         dbo.TransactionHistory.SourceAreaName AS CustomerName, dbo.TransactionHistory.Reference, dbo.TransactionHistory.SecondReference,
                         dbo.StockItem.Code AS StockCode, dbo.StockItem.Name AS [Artist/Title], dbo.TransactionType.TransactionTypeName AS TransactionType,
                         (CASE WHEN TransactionType.TransactionGroupID = 1 THEN transactionhistory.Quantity * - 1 ELSE transactionhistory.Quantity END) AS Quantity,
                         MAX(CASE WHEN dbo.TransactionType.TransactionTypeID = 15 THEN dbo.SOPInvoiceCredit.DocumentDate WHEN dbo.TransactionType.TransactionTypeID = 16 THEN
                          dbo.SOPInvoiceCredit.DocumentDate ELSE dbo.TransactionHistory.TransactionDate END) AS DefinitiveDate, MAX(dbo.TransactionHistory.TransactionDate)
                         AS StockTransactionDate, dbo.SOPInvoiceCredit.DocumentDate AS InvoiceDate
FROM            dbo.TransactionHistory INNER JOIN
                         dbo.StockItem ON dbo.TransactionHistory.ItemID = dbo.StockItem.ItemID INNER JOIN
                         dbo.TransactionType ON dbo.TransactionHistory.TransactionTypeID = dbo.TransactionType.TransactionTypeID LEFT OUTER JOIN
                         dbo.SOPInvoiceCredit ON dbo.TransactionHistory.Reference = dbo.SOPInvoiceCredit.SecondReference
GROUP BY dbo.TransactionHistory.SourceAreaReference, dbo.TransactionHistory.SourceAreaName, dbo.TransactionHistory.Reference,
                         dbo.TransactionHistory.SecondReference, dbo.StockItem.Code, dbo.TransactionHistory.WarehouseName, dbo.TransactionType.TransactionTypeID,
                         dbo.TransactionType.TransactionTypeName, dbo.StockItem.Name, dbo.TransactionType.TransactionGroupID,
                         (CASE WHEN TransactionType.TransactionGroupID = 1 THEN transactionhistory.Quantity * - 1 ELSE transactionhistory.Quantity END),
                         dbo.SOPInvoiceCredit.DocumentNo, dbo.SOPInvoiceCredit.DocumentDate, dbo.SOPInvoiceCredit.DocumentStatusID
HAVING        (dbo.TransactionType.TransactionGroupID <> 0) AND (dbo.TransactionType.TransactionGroupID <> 3) AND (dbo.SOPInvoiceCredit.DocumentStatusID <> 4)