January 12, 2003 at 3:47 pm
Hi
Remove this from your SELECT:
'[Amount] = ' - you don't need to name columns in a SELECT which is part of an INSERT.
Regards
Simon
January 12, 2003 at 3:57 pm
Thanks, that solved it
January 12, 2003 at 4:22 pm
The first piece worked, now I am trying to add the second layer of logic which is prorating based on start dates: This is what I have and it keeps giving me an error near the ELSE statement:
CREATE PROCEDURE sp_HVAC_Escalation (@center_id int,@start_date datetime,@end_date datetime,@recovery_factor real, @unit_service_charge real) AS
INSERT INTO bill_item (tenant_id, utility_group_id,charge_code_id,item_type_id,bill_item_date,bill_item_amount,bill_item_notes)
SELECT tenant_id,1,9,1,Getdate(),
CASE
WHEN t.tenant_ID in
(SELECT tenant_ID FROM tenant_hvac_lease_cap WHERE start_date >= @start_date AND end_date <= @end_date )
THEN
(Select max_dollar FROM tenant_hvac_lease_cap a
WHERE start_date >= @start_date AND end_date <= @end_date
AND a.tenantid = tenant_id)
ELSE
CASE
WHEN rtrim(t.tenant_type) = 'CODE 2' THEN
CASE
WHEN hvac_start_date > @start_date THEN
t.hvac_load_factor * t.hvac_area * @recovery_factor /**
(DateDiff(day,@start_date,@end_date) - DateDiff(day,t.hvac_start_date,@end_date) /
DateDiff(day,@start_date,@end_date)*/
ELSE
t.hvac_load_factor * t.hvac_area * @recovery_factor
END
ELSE
CASE
WHEN hvac_start_date > @start_date THEN
t.hvac_load_factor * t.hvac_area * @unit_service_charge
--(DateDiff(day,@start_date,@end_date) - DateDiff(day,hvac_start_date,@end_date) /
--DateDiff(day,@start_date,@end_date)
ELSE
t.hvac_load_factor * t.hvac_area * @unit_service_charge
END
END
END
,'Adjustment of HVAC Service Charge per Lease'
FROM tenant t
WHERE t.center_id = @center_ID AND rtrim(t.tenant_type) IN ('CODE 2','CODE 5')
AND tenant_ID IN
(
Select t.tenant_ID FROM bill_item WHERE charge_code_id = 1 and item_type_id = 1
HAVING MAX(bill_item_date) BETWEEN DateAdd(day,1,DateAdd(month,-1,@end_date)) AND @end_date
)
GO
you can see where I commented out things to see what worked. I just now need to see how to do calculations with the DateDiff or any other function applied
Thank you again for whatever light you can shed on this.
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply