I am start using SQL queries for BI projects and I need to optimize the attached code....It tooks more that 30min to have the result and unfortunately I don't know yet the best practiques to optimize the query... Please if you are an SQL expert, let me know how to write it in a better manner. Thanks
/*******UPDATE booked amount***********/
update t
set booked_amount = isnull(s.booked_amount,0)
from #sales_accumulated_fact t
inner join
(
select [TripId] [trip_flwr_id]
, EditDate [booked_date]
, convert(decimal, NewValue) [booked_amount]
from [dbo].[Trip__History__c_flwr_t] amount_history_trancsactions
where amount_history_trancsactions.[Field] = 'fwb__Total_Amount__c'
and amount_history_trancsactions.EditDate =
(
select MAX(EditDate)
from [dbo].[Trip__History__c_flwr_t] latest_date
where latest_date.[Field] = 'fwb__Total_Amount__c'
and latest_date.TripId =amount_history_trancsactions.TripId
and latest_date.EditDate <=
(
select booking_dates.booking_date
from #sales_accumulated_fact booking_dates
where booking_dates.trip_flwr_id = latest_date.TripId COLLATE database_default
)
)
) s
Somebody please check this SQL .. no data to test with
Reworded SQL
UPDATE t
SET booked_amount = Isnull(s.booked_amount,0)
FROM #sales_accumulated_fact t
INNER JOIN
(
SELECT a.tripid ,
Max(b.editdate) AS editdate
FROM [dbo].[Trip__History__c_flwr_t] a
WHERE a.[Field] = 'fwb__Total_Amount__c'
GROUP BY a.tripid ) s
and t.trip_flwr_id = s.tripid
AND s.editdate <= t.booking_date
UPDATE t
SET booked_amount = Isnull(s.booked_amount,0)
FROM #sales_accumulated_fact t
INNER JOIN
(
SELECT a.tripid ,
Max(b.editdate) AS editdate
FROM [dbo].[Trip__History__c_flwr_t] a
WHERE a.[Field] = 'fwb__Total_Amount__c'
GROUP BY a.tripid
) s
ON t.trip_flwr_id = s.tripid
AND s.editdate <= t.booking_date
Unfortunately, it doesn't work I have tried again to modify the code based on your suggestion, but I am not getting the correct result...I got 0 value for some cases and NULL of the rest. However, my first code gives what I need after 30min I still have a lot to learn to optimise my code...
Thanks for your reply. So, in the begining, I got this error message:
Msg 468, Level 16, State 9, Line 187
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
with an invalidcol name 'booked_amount' in the second line just after the update t. The issue perphaps with this amount type...
we can change the collation of both to some common collation and compare
OR
change the collation of left column to right column
OR
change the collation of right column to left column
doing google search will
show plenty of articles on how to do this !!
UPDATE t
SET booked_amount = Isnull(s.booked_amount,0)
FROM #sales_accumulated_fact t
INNER JOIN
(
SELECT a.tripid ,
Max(b.editdate) AS editdate
FROM [dbo].[Trip__History__c_flwr_t] a
WHERE a.[Field] = 'fwb__Total_Amount__c'
GROUP BY a.tripid
) s
ON t.trip_flwr_id = s.tripid collate SQL_Latin1_General_CP1_CS_AS
AND s.editdate <= t.booking_date
you are updating t booked_amount
t is this #sales_accumulated_fact t
you are getting booked_amount from s
s is this
(
SELECT a.tripid ,
Max(b.editdate) AS editdate
FROM [dbo].[Trip__History__c_flwr_t] a
WHERE a.[Field] = 'fwb__Total_Amount__c'
GROUP BY a.tripid
) s
the error could mean #sales_accumulated_fact t does not have column booked_Amount
or
as you thought s should include booked_amount in the select
s is this
(
SELECT a.tripid ,
Max(b.editdate) AS editdate
FROM [dbo].[Trip__History__c_flwr_t] a
WHERE a.[Field] = 'fwb__Total_Amount__c'
GROUP BY a.tripid
) s
@harishgg1 in fact, I have declared booked_amount in the ( #sales_accumulated_fact t) and when I have replaced (s.booked_amount) by (t.booked_amount) I don't have an error, but the result is incorrect.
When I run this modified code with a select * from #sales_accumulated_fact
where booking_date ='2018-01-01' , I got this result:
trip_number booked_amount
228117 0
228433 NULL
228390 0
However, using the original code took a long time (35min) to be executed and I got this result
trip_number booked_amount
228117 78500
228433 25000
228390 15000
I have tried again to modify the code based on your suggestion, but I am not getting the correct result...I got 0 value for some cases and NULL of the rest. However, my first code gives what I need after 30min