January 12, 2003 at 3:38 pm
I have a stored procedure that checks the contents of another table to see if a value exists, and if so then put it into the table (this is a set level insert statement).
If the value doesn't exist, it rolls down to then check to see if the results of one field, doing one calculation in one case and another calculation in the other.
I am getting an error message when I check the syntax. Can someone tell me what I am doing wrong? Thanks you much in advance.
Here is the stored procedure:
CREATE PROCEDURE 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(),
[Amount] = 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 9
--SELECT max_dollar
--FROM tenant_hvac_lease_cap a
--WHERE start_date >= @start_date AND end_date <= @end_date AND t.tenant_id = a.tenant_id
ELSE
CASE
WHEN rtrim(t.tenant_type) = 'CODE 2' THEN
t.hvac_load_factor * t.hvac_area * @recovery_factor
ELSE
t.hvac_load_factor * t.hvac_area * @unit_service_charge
END
END
,'Adjustment of HVAC Service Charge per Lease'
FROM vw_hvac 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("d",-30,@end_date) AND @end_date
)
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply