March 22, 2017 at 9:23 am
Please find attached Query Plan.
Please help me how to imporve performance. It's take 20 sec to run.
Thanks
Bhavesh
March 22, 2017 at 10:51 am
It would help if you posted the query as well. The SQL Plan does contain some of the query text, but it gets truncated.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 22, 2017 at 11:04 am
Essentally the same subquery appears twice in the part of the query which is attached to the plan. Rather than read these three tables twice, capture the results into a couple of variables and reference those in your query instead.
It probably won't make the query much faster but it will remove 6 table reads. Simpler is always better.
DECLARE @field_cd whatever, @FieldValue whatever
SELECT @field_cd = field_cd, @FieldValue = fv.FieldValue
FROM t_fieldtype ft
INNER JOIN t_fieldvalue fv ON ft.fieldtype_id = fv.fieldtype_id
WHERE ft.fieldtype_nm = 'T_Item.Status_IND' AND fv.fieldvalue = 'On-Hold'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 22, 2017 at 11:59 am
/****** Object: View [dbo].[VW_STG_ITEM] Script Date: 03/22/2017 13:57:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter VIEW [dbo].[VW_STG_ITEM]
AS
SELECT i.item_id AS Item_ID
,i.itemtype_id AS ItemType_ID
,Isnull(c.cluster_id, 0) AS Cluster_ID
,i.item_no AS Item_No
,i.depot_id AS InitialDepot_ID
,CASE
WHEN fsd.shipment_no IS NULL
AND I.site_id IS NULL
AND i.[depot_id] IS NOT NULL THEN i.[depot_id]
WHEN fsd.shipment_no IS NOT NULL
AND fsd.ship_to_depot_id IS NOT NULL THEN i.[depot_id]
ELSE 0
END AS Depot_ID
,i.site_id AS InitialSite_ID
,CASE
WHEN fsd.ship_to_site_id IS NOT NULL THEN i.[site_id]
ELSE 0
END AS Site_ID
,fsd.ship_to_depot_id AS Ship_To_Depot_ID
,fsd.ship_to_site_id AS Ship_To_Site_ID
,fsd.ship_from_depot_id AS Ship_From_Depot_ID
,fsd.ship_from_site_id AS Ship_From_Site_ID
,fsd.ordertype_ind AS OrderType_IND
,fsd.shipment_no AS Shipment_NO
,fsd.ordered_dt AS Ordered_DT
,fsd.received_dt AS Received_DT
,Isnull(i.subject_id, 0) AS Subject_ID
,i.subjectevent_id AS SubjectEvent_ID
,CASE
WHEN fsd.shipment_no IS NULL
AND I.site_id IS NULL THEN 'D' -- Item never shipped from depot
WHEN fsd.shipment_no IS NOT NULL
AND fsd.ship_to_depot_id IS NOT NULL THEN 'D' --last Shipment is to depot
WHEN fsd.shipment_no IS NOT NULL
AND fsd.ship_to_site_id IS NOT NULL THEN 'S' --last Shipment is to site
END AS SiteDepot_IND
,Isnull(i.status_ind, '0') AS originalStatus_IND
,CASE
WHEN ( ( ohl.lot_no IS NOT NULL
AND fsd.ship_to_site_id = ohl.site_id )
OR ( ohld.lot_no IS NOT NULL
AND I.site_id IS NULL )
OR ( fsd.ship_to_depot_id = ohld.depot_id
AND fsd.ordertype_ind <> '06' )--2.01 Update. Returned kits are excluded from OnHold Status
)
AND i.status_ind NOT IN ( '24', '25' ) THEN (SELECT field_cd
FROM t_fieldtype ft
INNER JOIN t_fieldvalue fv
ON ft.fieldtype_id = fv.fieldtype_id
WHERE ft.fieldtype_nm = 'T_Item.Status_IND'
AND fv.fieldvalue = 'On-Hold')
ELSE Isnull(i.status_ind, '0')
END AS Status_IND
, CASE
WHEN (
(ohl.Lot_No IS NOT NULL AND fsd.ship_to_site_id = ohl.site_id) OR (ohld.Lot_No IS NOT NULL AND I.Site_Id IS NULL) OR (fsd.ship_to_depot_id = ohld.depot_id AND fsd.OrderType_IND <> '06') --2.01 Update. Returned kits are not included in OnHold Status
) AND i.Status_Ind NOT IN ('24', '25')
THEN (
SELECT fv.FieldValue
FROM T_FieldType ft
INNER JOIN T_FieldValue fv ON ft.FieldType_ID = fv.FieldType_ID
WHERE ft.FieldType_NM = 'T_Item.Status_IND' AND fv.FieldValue = 'On-Hold'
)
ELSE fv.fieldvalue
END AS Current_Status_Desc
,ItemPrevStatus.previousstatus AS PreviousStatus --2.01 Update
,dt.assigned_dt AS Assigned_DT
,CASE
WHEN i.site_id IS NOT NULL THEN Isnull(i.expiration_dt, '1900-01-01 00:00:00.000') -- 2.01 Update. If kits are ever sent to a site then they retain Expiration date from T_Item table.
ELSE
CASE
WHEN lot.lot_status IS NOT NULL THEN lot.expiration_dt
-- Item is at depot or in transit to a depot and Lot management is set to yes then Lot expiration date
ELSE Isnull(i.expiration_dt, '1900-01-01 00:00:00.000') --Item is at depot or in transit to a depot and Lot management is set to No then Kit expiration date
END
END AS Expiration_DT
,Isnull(i.lot_no, '0') AS Lot_NO
,Isnull(i.control_no, '0') AS Control_No --AIR 1.09
,I.extralot1_no AS Extralot1_no --AIR 1.09
,i.extralot2_no AS Extralot2_no
,lot.lot_status AS Lot_Status_Ind
,lot_status_desc AS Lot_Status
,lot.expiration_dt AS Lot_Expiration_DT
,i.istransferred_flag AS IsTransferred_FLAG
,i.reconciliationstatus AS reconciliationstatus -- 1.09
,i.reconciliationsiteid AS reconciliationsiteid -- 1.09
,i.reconciliationdepotid AS reconciliationdepotid -- 1.09
--,tr.Tracking_NO AS Tracking_NO -- 2.01
,CASE
WHEN i.reconciliationdiscrepancy = '--Select--' THEN 'None'
ELSE Isnull(i.reconciliationdiscrepancy, 'N/A')
END AS ReconciliationDiscrepancy --2.01 Update
,Isnull(CD.dosevalue, 0) AS Dose_Value
,Isnull(CD.dose_txt, 'N/A') AS Dose_Units
,i.modif_id AS Modif_ID
,i.locator_txt
,c.Type_IND AS Cluster_Type_IND
FROM dbo.t_item AS i
LEFT OUTER JOIN dbo.STG_Item_Assigned_DT as dt
ON i.Item_ID = dt.Item_ID
--Getting Cluster info
LEFT JOIN (SELECT cluster_id
,cluster_nm
,type_ind
FROM dbo.t_cluster
-- WHERE type_ind = 'L' Commented out as per 168877
) c
ON c.cluster_id = i.cluster_id -- 2.01 Update
-- Getting Lot Information
LEFT JOIN (SELECT l.lot_id
,li.item_id
,l.status_ind AS Lot_Status
,fv.fieldvalue AS Lot_Status_Desc
,l.expiration_dt
FROM t_lotitem li
INNER JOIN t_lot l
ON l.lot_id = li.lot_id
--Getting decode of lot status
INNER JOIN (SELECT fv.fieldtype_id
,fv.field_cd
,fv.fieldvalue
FROM t_fieldvalue fv
INNER JOIN t_fieldtype ft
ON fv.fieldtype_id = ft.fieldtype_id
WHERE fieldtype_nm = 'T_Lot.Status_IND')fv
ON fv.field_cd = l.status_ind
WHERE l.priority_ind = 'P')lot
ON lot.item_id = i.item_id
--Getting information related to last shipment of the kit
LEFT OUTER JOIN (SELECT od.item_id
,o.todepot_id AS Ship_To_Depot_ID
,o.tosite_id AS Ship_To_Site_ID
,o.fromsite_id AS Ship_From_Site_ID
,o.fromdepot_id AS Ship_From_Depot_ID
,o.shipment_no
,o.ordertype_ind
,o.ordered_dt
,o.received_dt
FROM dbo.t_orderdetail od
INNER JOIN t_order o
ON o.order_id = od.order_id
INNER JOIN (SELECT od.item_id
,Max(o.order_id) AS Order_ID
FROM dbo.t_order o
INNER JOIN t_orderdetail od
ON o.order_id = od.order_id
WHERE o.status_ind NOT IN ( 'C', 'I' )
GROUP BY od.item_id)od1
ON od.item_id = od1.item_id
AND o.order_id = od1.order_id)fsd
ON I.item_id = fsd.item_id
--Lot on hold at site
LEFT JOIN (SELECT lot_no
,site_id
FROM t_onholdlot ohl
WHERE site_id IS NOT NULL)AS ohl
ON ohl.lot_no = i.lot_no
AND ohl.site_id = i.site_id
--Lot on hold at depot
LEFT JOIN (SELECT lot_no
,depot_id
FROM t_onholdlot ohl
WHERE depot_id IS NOT NULL)AS ohld
ON ohld.lot_no = i.lot_no
AND ohld.depot_id = i.depot_id
INNER JOIN (SELECT fv.fieldtype_id
,fv.field_cd
,fv.fieldvalue
FROM t_fieldvalue fv
INNER JOIN t_fieldtype ft
ON fv.fieldtype_id = ft.fieldtype_id
WHERE fieldtype_nm = 'T_Item.Status_IND')fv ON fv.Field_CD = Isnull(i.status_ind, '0')
LEFT OUTER JOIN t_site AS s
ON i.site_id = s.site_id
-- To get Dose information that is used only in Adhoc reports
LEFT JOIN (SELECT ITD.ItemType_ID
,TD.dosevalue
,TD.dose_txt
FROM t_itemtypedrug ITD
INNER JOIN t_treatmentdrug TD
ON TD.studydrug_id = ITD.studydrug_id) AS CD
ON i.ItemType_ID = CD.ItemType_ID
--Finds Previous status of Item before reconciliation
LEFT OUTER JOIN (SELECT ips.ItemId
,ips.originalkitstatus AS PreviousStatus_Ind
,fv.fieldvalue AS PreviousStatus
--,i.Status_IND
--,ips.NewKitStatus
FROM t_itempreviousstatus ips
INNER JOIN (SELECT fv.fieldtype_id
,fv.field_cd
,fv.fieldvalue
FROM dbo.t_fieldvalue fv
INNER JOIN t_fieldtype ft
ON fv.fieldtype_id = ft.fieldtype_id
WHERE ft.fieldtype_nm = 'T_Item.Status_IND') AS fv
ON ips.originalkitstatus = fv.field_cd)ItemPrevStatus
ON i.item_id = ItemPrevStatus.ItemId
WHERE i.status_ind <> '00'
GO
March 23, 2017 at 4:46 am
Thanks Bhavesh.
If I were given this query to tune, including access to a test database, then I'd break it down into manageable chunks and tune each chunk separately. Here's a good example of a chunk worth investigating:
--Getting information related to last shipment of thekit
LEFT JOIN (
SELECT od.item_id
,o.todepot_id AS Ship_To_Depot_ID
,o.tosite_id AS Ship_To_Site_ID
,o.fromsite_id AS Ship_From_Site_ID
,o.fromdepot_id AS Ship_From_Depot_ID
,o.shipment_no
,o.ordertype_ind
,o.ordered_dt
,o.received_dt
FROM dbo.t_orderdetail od
INNER JOIN t_order o
ON o.order_id = od.order_id
INNER JOIN (
SELECT od.item_id
,MAX(o.order_id) AS Order_ID
FROM dbo.t_order o
INNER JOIN t_orderdetail od
ON o.order_id = od.order_id
WHERE o.status_ind NOT IN ( 'C', 'I' )
GROUP BY od.item_id
)od1
ON od.item_id = od1.item_id
AND o.order_id = od1.order_id
)fsd
ON I.item_id = fsd.item_id
[/code]
You're using SQL Server 2008, so make use of ROW_NUMBER instead of the aggregate subquery to locate the most recent row. You will eliminate two tables' worth of reads from the execution plan.
You have this subquery in numerous places:
SELECT fv.fieldtype_id
,fv.field_cd
,fv.fieldvalue
FROM t_fieldvalue fv
INNER JOIN t_fieldtype ft
ON fv.fieldtype_id = ft.fieldtype_id
WHERE fieldtype_nm = 'T_Lot.Status_IND'
[/code]
I'd recommend you change this to either an indexed view, or a #temp table with indexing optimised for use with this query. You will eliminate about 6 tables' worth of reads from the execution plan.
I'd recommend that you use this query within a stored procedure and not saved as a view, it's too complex and you're unlikely to get it working most efficiently without using constructs which are incompatible with views. Also, sooner or later some idiot will join it to a bunch of other tables and your server will weep.
Here's the whole query reformatted for clarity (it WAS, I promise!):
SELECT i.item_id AS Item_ID
,i.itemtype_id AS ItemType_ID
,ISNULL(c.cluster_id, 0) AS Cluster_ID
,i.item_no AS Item_No
,i.depot_id AS InitialDepot_ID
,CASE
WHEN fsd.shipment_no IS NULL
AND I.site_id IS NULL
AND i.[depot_id] IS NOT NULL THEN i.[depot_id]
WHEN fsd.shipment_no IS NOT NULL
AND fsd.ship_to_depot_id IS NOT NULL THEN i.[depot_id]
ELSE 0
END AS Depot_ID
,i.site_id AS InitialSite_ID
,CASE
WHEN fsd.ship_to_site_id IS NOT NULL THEN i.[site_id]
ELSE 0
END AS Site_ID
,fsd.ship_to_depot_id AS Ship_To_Depot_ID
,fsd.ship_to_site_id AS Ship_To_Site_ID
,fsd.ship_from_depot_id AS Ship_From_Depot_ID
,fsd.ship_from_site_id AS Ship_From_Site_ID
,fsd.ordertype_ind AS OrderType_IND
,fsd.shipment_no AS Shipment_NO
,fsd.ordered_dt AS Ordered_DT
,fsd.received_dt AS Received_DT
,ISNULL(i.subject_id, 0) AS Subject_ID
,i.subjectevent_id AS SubjectEvent_ID
,CASE
WHEN fsd.shipment_no IS NULL
AND I.site_id IS NULL THEN 'D' -- Item never shipped fromdepot
WHEN fsd.shipment_no IS NOT NULL
AND fsd.ship_to_depot_id IS NOT NULL THEN 'D' --last Shipment is to depot
WHEN fsd.shipment_no IS NOT NULL
AND fsd.ship_to_site_id IS NOT NULL THEN 'S' --last Shipment is to site
END AS SiteDepot_IND
,ISNULL(i.status_ind, '0') AS originalStatus_IND
,CASE
WHEN ( ( ohl.lot_no IS NOT NULL
AND fsd.ship_to_site_id = ohl.site_id )
OR ( ohld.lot_no IS NOT NULL
AND I.site_id IS NULL )
OR ( fsd.ship_to_depot_id = ohld.depot_id
AND fsd.ordertype_ind <> '06' )--2.01 Update. Returned kits are excluded from OnHoldStatus
)
AND i.status_ind NOT IN ( '24', '25' ) THEN (
SELECT field_cd
FROM t_fieldtype ft
INNER JOIN t_fieldvalue fv
ON ft.fieldtype_id = fv.fieldtype_id
WHERE ft.fieldtype_nm = 'T_Item.Status_IND'
AND fv.fieldvalue = 'On-Hold'
)
ELSE ISNULL(i.status_ind, '0')
END AS Status_IND
, CASE
WHEN (
(ohl.Lot_No IS NOT NULL AND fsd.ship_to_site_id = ohl.site_id) OR (ohld.Lot_No IS NOT NULL AND I.Site_Id IS NULL) OR (fsd.ship_to_depot_id = ohld.depot_id AND fsd.OrderType_IND <> '06') --2.01 Update. Returned kitsare not included in OnHold Status
) AND i.Status_Ind NOT IN ('24', '25')
THEN (
SELECT fv.FieldValue
FROM T_FieldType ft
INNER JOIN T_FieldValue fv
ON ft.FieldType_ID = fv.FieldType_ID
WHERE ft.FieldType_NM = 'T_Item.Status_IND'
AND fv.FieldValue = 'On-Hold'
)
ELSE fv.fieldvalue
END AS Current_Status_Desc
,ItemPrevStatus.previousstatus AS PreviousStatus --2.01 Update
,dt.assigned_dt AS Assigned_DT
,CASE
WHEN i.site_id IS NOT NULL THEN ISNULL(i.expiration_dt, '1900-01-01 00:00:00.000') -- 2.01 Update. If kits are ever sent to a site then theyretain Expiration date from T_Item table.
ELSE
CASE
WHEN lot.lot_status IS NOT NULL THEN lot.expiration_dt
-- Itemis at depot or in transit to a depot and Lot management is set to yes then Lotexpiration date
ELSE ISNULL(i.expiration_dt, '1900-01-01 00:00:00.000') --Item is at depot or intransit to a depot and Lot management is set to No then Kit expiration date
END
END AS Expiration_DT
,ISNULL(i.lot_no, '0') AS Lot_NO
,ISNULL(i.control_no, '0') AS Control_No --AIR 1.09
,I.extralot1_no AS Extralot1_no --AIR1.09
,i.extralot2_no AS Extralot2_no
,lot.lot_status AS Lot_Status_Ind
,lot_status_desc AS Lot_Status
,lot.expiration_dt AS Lot_Expiration_DT
,i.istransferred_flag AS IsTransferred_FLAG
,i.reconciliationstatus AS reconciliationstatus --1.09
,i.reconciliationsiteid AS reconciliationsiteid --1.09
,i.reconciliationdepotid AS reconciliationdepotid --1.09
--,tr.Tracking_NO AS Tracking_NO -- 2.01
,CASE
WHEN i.reconciliationdiscrepancy = '--Select--' THEN 'None'
ELSE ISNULL(i.reconciliationdiscrepancy, 'N/A')
END AS ReconciliationDiscrepancy --2.01Update
,ISNULL(CD.dosevalue, 0) AS Dose_Value
,ISNULL(CD.dose_txt, 'N/A') AS Dose_Units
,i.modif_id AS Modif_ID
,i.locator_txt
,c.Type_IND AS Cluster_Type_IND
FROM dbo.t_item AS i
LEFT JOIN dbo.STG_Item_Assigned_DT AS dt
ON i.Item_ID = dt.Item_ID
--Getting Cluster info
LEFT JOIN (SELECT cluster_id, cluster_nm, type_ind FROM dbo.t_cluster) c
ON c.cluster_id = i.cluster_id -- 2.01 Update
-- Getting Lot Information
LEFT JOIN (
SELECT l.lot_id
,li.item_id
,l.status_ind AS Lot_Status
,fv.fieldvalue AS Lot_Status_Desc
,l.expiration_dt
FROM t_lotitem li
INNER JOIN t_lot l
ON l.lot_id = li.lot_id
--Getting decode of lot status
INNER JOIN (
SELECT fv.fieldtype_id
,fv.field_cd
,fv.fieldvalue
FROM t_fieldvalue fv
INNER JOIN t_fieldtype ft
ON fv.fieldtype_id = ft.fieldtype_id
WHERE fieldtype_nm = 'T_Lot.Status_IND'
) fv
ON fv.field_cd = l.status_ind
WHERE l.priority_ind = 'P'
) lot
ON lot.item_id = i.item_id
--Getting information related to last shipment of thekit
LEFT JOIN (
SELECT od.item_id
,o.todepot_id AS Ship_To_Depot_ID
,o.tosite_id AS Ship_To_Site_ID
,o.fromsite_id AS Ship_From_Site_ID
,o.fromdepot_id AS Ship_From_Depot_ID
,o.shipment_no
,o.ordertype_ind
,o.ordered_dt
,o.received_dt
FROM dbo.t_orderdetail od
INNER JOIN t_order o
ON o.order_id = od.order_id
INNER JOIN (
SELECT od.item_id
,MAX(o.order_id) AS Order_ID
FROM dbo.t_order o
INNER JOIN t_orderdetail od
ON o.order_id = od.order_id
WHERE o.status_ind NOT IN ( 'C', 'I' )
GROUP BY od.item_id
)od1
ON od.item_id = od1.item_id
AND o.order_id = od1.order_id
)fsd
ON I.item_id = fsd.item_id
--Lot on hold at site
LEFT JOIN (
SELECT lot_no
,site_id
FROM t_onholdlot ohl
WHERE site_id IS NOT NULL
) AS ohl
ON ohl.lot_no = i.lot_no
AND ohl.site_id = i.site_id
--Lot on hold at depot
LEFT JOIN (
SELECT lot_no
,depot_id
FROM t_onholdlot ohl
WHERE depot_id IS NOT NULL
)AS ohld
ON ohld.lot_no = i.lot_no
AND ohld.depot_id = i.depot_id
INNER JOIN (
SELECT fv.fieldtype_id
,fv.field_cd
,fv.fieldvalue
FROM t_fieldvalue fv
INNER JOIN t_fieldtype ft
ON fv.fieldtype_id = ft.fieldtype_id
WHERE fieldtype_nm = 'T_Item.Status_IND'
)fv ON fv.Field_CD = ISNULL(i.status_ind, '0')
LEFT JOIN t_site AS s
ON i.site_id = s.site_id
-- To get Dose information that is used only in Adhocreports
LEFT JOIN (
SELECT ITD.ItemType_ID
,TD.dosevalue
,TD.dose_txt
FROM t_itemtypedrug ITD
INNER JOIN t_treatmentdrug TD
ON TD.studydrug_id = ITD.studydrug_id
) AS CD
ON i.ItemType_ID = CD.ItemType_ID
--Finds Previous status of Item before reconciliation
LEFT JOIN (
SELECT ips.ItemId
,ips.originalkitstatus AS PreviousStatus_Ind
,fv.fieldvalue AS PreviousStatus
FROM t_itempreviousstatus ips
INNER JOIN (
SELECT fv.fieldtype_id
,fv.field_cd
,fv.fieldvalue
FROM dbo.t_fieldvalue fv
INNER JOIN t_fieldtype ft
ON fv.fieldtype_id = ft.fieldtype_id
WHERE ft.fieldtype_nm = 'T_Item.Status_IND'
) AS fv
ON ips.originalkitstatus = fv.field_cd
) ItemPrevStatus
ON i.item_id = ItemPrevStatus.ItemId
WHERE i.status_ind <> '00'
[/code]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 23, 2017 at 7:12 am
Thank you.
Thank you.
Please see attached executution plan for below query. It is taking 4 sec to run. What I need to change in this query. How can I re-write this query with Row_Number ?
SELECT od.item_id,
o.todepot_id AS Ship_To_Depot_ID,
o.tosite_id AS Ship_To_Site_ID,
o.fromsite_id AS Ship_From_Site_ID,
o.fromdepot_id AS Ship_From_Depot_ID,
o.shipment_no,
o.ordertype_ind,
o.ordered_dt,
o.received_dt
FROM dbo.t_orderdetail od
INNER JOIN t_order o
ON o.order_id = od.order_id
INNER JOIN (SELECT od.item_id,
Max(o.order_id) AS Order_ID
FROM dbo.t_order o
INNER JOIN t_orderdetail od
ON o.order_id = od.order_id
WHERE o.status_ind NOT IN ( 'C', 'I' )
GROUP BY od.item_id)od1
ON od.item_id = od1.item_id
AND o.order_id = od1.order_id
March 23, 2017 at 7:40 am
You could use ROW_NUMBER or you could use APPLY, like this:
SELECT od.item_id,
o.todepot_id AS Ship_To_Depot_ID,
o.tosite_id AS Ship_To_Site_ID,
o.fromsite_id AS Ship_From_Site_ID,
o.fromdepot_id AS Ship_From_Depot_ID,
o.shipment_no,
o.ordertype_ind,
o.ordered_dt,
o.received_dt
FROM dbo.t_orderdetail od
CROSS APPLY (
SELECT TOP 1
o.todepot_id,o.tosite_id,o.fromsite_id,o.fromdepot_id,
o.shipment_no,o.ordertype_ind,o.ordered_dt,o.received_dt
FROM t_order o
WHERE o.order_id = od.order_id
AND o.status_ind NOT IN ( 'C', 'I' )
ORDER BY o.order_id DESC
) x
[/code]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply