Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Same record, multiple occurances

Author  Topic  Robin Starting Member 9 Posts Posted - 2001-10-25 : 17:09:24 Hello Hopefully someone can help me with this. I have a table filled with transactions, I am currently writing a stored procedure to process these reservations for the purpose of invoiceing the client. There ar six billing exceptions based on various different fields with each transaction. The exceptions are stored in an exception table. The problem -> for each and every transaction, it is applying all six exceptions one after another, thereby billing the same transaction six times. Once for each exception. Why? I am using a 'CASE' statement to determine what exception (if any) to use but, it applies each and every part of the case statement one at a time for each and every transaction. I will include the 'Case' statement here. I hope someone can help 'VIPFee' = Case When Not Exists (Select * From Billing_Exceptions Where Property_ID like @PropID And Billing_Exceptions.GDS_Code Like Billing_Table.GDS_Code And Billing_Exceptions.Int_Ref_Code Like Billing_Table.Int_Ref_Code and Billing_Exceptions.Rate_Code Like Billing_Table.Rate_Type) Then Case When Not Exists (Select * From Billing_Exceptions Where Property_ID like @PropID And Billing_Exceptions.GDS_Code Like Billing_Table.GDS_Code And Billing_Exceptions.Int_Ref_Code Like Billing_Table.Int_Ref_Code) Then Case When Not Exists (Select * From Billing_Exceptions Where Property_ID Like @PropID And Billing_Exceptions.GDS_Code Like Billing_Table.GDS_Code) Then Case When Not Exists (Select * From Billing_Exceptions Where Property_ID Like @PropID And Billing_Exceptions.Int_Ref_Code Like Billing_Table.Int_Ref_Code And Billing_Exceptions.Rate_Code Like Billing_Table.Rate_Type) Then Case When Not Exists (Select * From Billing_Exceptions Where Property_ID Like @PropID And Billing_Exceptions.Int_Ref_Code Like Billing_Table.Int_Ref_Code) Then Case When Not Exists (Select * From Billing_Exceptions Where Property_ID Like @PropID And Billing_Exceptions.Rate_Code Like Billing_Table.Rate_Type) Then Case When Basic_Invoice_Data.Flat_Fee = 0 Then Case When Rates_Currency Not Like Bill_Currency Then (([Num_Units]*[Daily_Rate]*[Rental_Period]) * Basic_Invoice_Data.Percentage_Fee) * Exchange_Rate Else ([Num_Units]*[Daily_Rate]*[Rental_Period]) * Basic_Invoice_Data.Percentage_Fee End Else Basic_Invoice_Data.Flat_Fee End Else Case When Billing_Exceptions.Flat_Fee = 0 Then Case When Rates_Currency Not Like Bill_Currency Then (([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee) * Exchange_Rate Else ([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee End Else Billing_Exceptions.Flat_Fee End End Else Case When Billing_Exceptions.Flat_Fee = 0 Then Case When Rates_Currency Not Like Bill_Currency Then (([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee) * Exchange_Rate Else ([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee End Else Billing_Exceptions.Flat_Fee End End Else Case When Billing_Exceptions.Flat_Fee = 0 Then Case When Rates_Currency Not Like Bill_Currency Then (([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee) * Exchange_Rate Else ([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee End Else Billing_Exceptions.Flat_Fee End End Else Case When Billing_Exceptions.Flat_Fee = 0 Then Case When Rates_Currency Not Like Bill_Currency Then (([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee) * Exchange_Rate Else ([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee End Else Billing_Exceptions.Flat_Fee End End Else Case When Billing_Exceptions.Flat_Fee = 0 Then Case When Rates_Currency Not Like Bill_Currency Then (([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee) * Exchange_Rate Else ([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee End Else Billing_Exceptions.Flat_Fee End End Else Case When Billing_Exceptions.Flat_Fee = 0 Then Case When Rates_Currency Not Like Bill_Currency Then (([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee) * Exchange_Rate Else ([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee End Else Billing_Exceptions.Flat_Fee End End Robin Olsen