HI.
select
sum (ISNULL(TransI_decNoOfItems,0)) AS Quantity
,sum(CAST(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decNoOfItems,0) AS MONEY)) AS GrossValue
,ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode) as workstationcode
,BH.BookingH_intNextBookingNo
,sum( CAST(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decNoOfItems,0) -
((ISNULL(TransI_curSTaxEach,0)+
ISNULL(TransI_curSTaxEach2,0)+
ISNULL(TransI_curSTaxEach3,0)+
ISNULL(TransI_curSTaxEach4,0))*ISNULL(TransI_decNoOfItems,0)) AS MONEY)) AS NetValue
from tblTrans_Inventory I
INNER JOIN VISTA.dbo.tblItem IT
ON IT.Item_strItemId = I.Item_strItemId
INNER JOIN VISTA.dbo.tblSalesTax
ON tblSalesTax.STax_strCode = IT.STax_strCode
LEFT JOIN ZZ_tblTrans_Inventory_EXT BI
ON I.TransI_lgnNumber = BI.TransI_lgnNumber
AND I.TransI_intSequence = BI.TransI_intSequence
LEFT JOIN tblBooking_Header BH ON BH.TransC_lgnNumber = I.TransI_lgnNumber
LEFT JOIN tblItem_Class IC
ON IC.Class_strCode = IT.Class_strCode
LEFT JOIN tblWorkstation W
ON W.Workstation_strCode = ISNULL(BI.TransI_strPickupWorkstn,I.Workstation_strCode)
--where I.TransI_strType = 'S' --if like so we do not get refunded items
where IT.Item_strBookingFee <> 'Y' --Exclude Booking Fees
AND ISNULL(BI.TransI_strPickupWorkstn,'') NOT LIKE '%UNRED%'
and
CASE WHEN ISNULL(I.TransI_decNoOfItems,0) < 0 THEN I.TransI_dtmRealTransTime --Refund
ELSE CASE WHEN BH.BookingH_intNextBookingNo IS NULL THEN I.TransI_dtmRealTransTime ELSE I.TransI_dtmDateCollected END
END between '20170902 06:00:00' and '20171011 05:59:59'
and CASE
WHEN ISNULL(TransI_decNoOfItems,0) < 0 THEN 'C' --Refund
ELSE CASE TransI_strStatus WHEN 'R' THEN 'R' ELSE 'V' END
END = 'V' --Transaction Status C:Refund, V:Valid, R:Refunded
-- remove inline recipre
and TransI_curValueEach > 0
group by BH.BookingH_strPickupWorkstn,ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode),BH.BookingH_intNextBookingNoHi.
First a quick question.
Am I correct to group by ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode) as per the select statement?
When I group by only by I.Workstation_strCode I get the same results but I do not group on a broad range right now.
OK Next. I get A table scan on tbltrans_inventory and a missing query.
As I cannot temper with the database, I cannot insert any indexes, so I was hoping to somehow make the cast better.
An problem may be the like '%unread%' but unfortunately I cannot remove the beginning % . However when I remove the like , I get the same execution table scan.
I also removed the casts on the select clause and the execution plan breaks down to index scans on tbltrans_inventory.
But from what I can see the problem seems to originate from the 2 case on the and statement.
and
CASE WHEN ISNULL(I.TransI_decNoOfItems,0) < 0 THEN I.TransI_dtmRealTransTime --Refund
ELSE CASE WHEN BH.BookingH_intNextBookingNo IS NULL THEN I.TransI_dtmRealTransTime ELSE I.TransI_dtmDateCollected END
END between '20170902 06:00:00' and '20171011 05:59:59'
and CASE
WHEN ISNULL(TransI_decNoOfItems,0) < 0 THEN 'C' --Refund
ELSE CASE TransI_strStatus WHEN 'R' THEN 'R' ELSE 'V' END
END = 'V' --Transaction Status C:Refund, V:Valid, R:Refunded Lastly, let me say that the query does not take a long time to execute but I guess this may have to do with the new servers we have installed a month ago. So this is not actually a very huge problem but I would appreciate some thoughts, as we will be running this also on older servers from time to time.
Thanks.
Images:
