/* Existing GetSQL() dctransactions */ SELECT DC3.* FROM (SELECT DC2.*, tCriticalType.Description AS Reason FROM (SELECT DC1.*, (DC1.RENT + DC1.CALL + DC1.OTHER) AS Total, tServiceType.AdminFee FROM ((SELECT tDataCollection.ServiceID, tDataCollection.StatusID, Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tDataCollection.AmountExGST ELSE 0 END) AS Rent, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tDataCollection.AmountExGST ELSE 0 END) AS Call, Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tDataCollection.AmountExGST ELSE 0 END) AS Other, Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tDataCollection.FromDate END) AS RentFrom, Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tDataCollection.ToDate END) AS RentTill, Max(CASE tTransactionGroup.Description WHEN 'CALL' THEN tDataCollection.ToDate END) AS CallTill FROM (tDataCollection LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tDataCollection.BatchID = 50004 GROUP BY tDataCollection.ServiceID, tDataCollection.StatusID) AS DC1 LEFT JOIN tServiceID ON DC1.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC2 LEFT JOIN tCriticalType ON DC2.StatusID = tCriticalType.ID) AS DC3 ORDER BY DC3.ServiceID /* MODIFIED GetSQL() dctransactions */ /////////////////////////////////////////////////////////////////////// SELECT DC3.* FROM (SELECT DC2.*, tCriticalType.Description AS Reason FROM (SELECT DC1.*, (DC1.RENT + DC1.CALL + DC1.OTHER) AS Total, tServiceType.AdminFee FROM ((SELECT DC0.ServiceID, DC0.StatusID, DC0.ServiceTypeID, DC0.Code, Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN DC0.AmountExGST ELSE 0 END) AS Rent, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN DC0.AmountExGST ELSE 0 END) AS Call, Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN DC0.AmountExGST ELSE 0 END) AS Other, Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN DC0.FromDate END) AS RentFrom, Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN DC0.ToDate END) AS RentTill, Max(CASE tTransactionGroup.Description WHEN 'CALL' THEN DC0.ToDate END) AS CallTill FROM ((SELECT DC.*, tServiceType.ID AS ServiceTypeID FROM (SELECT tDataCollection.*, 'Code' = CASE WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE RTRIM(SUBSTRING(tDataCollection.ServiceType,1,5)) END FROM (tDataCollection LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID WHERE tDataCollection.BatchID = 50004) AS DC LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC0 LEFT JOIN tTransactionType ON DC0.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID GROUP BY DC0.ServiceID, DC0.StatusID, DC0.ServiceTypeID, DC0.Code) AS DC1 LEFT JOIN tServiceID ON DC1.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON DC1.ServiceTypeID = tServiceType.ID) AS DC2 LEFT JOIN tCriticalType ON DC2.StatusID = tCriticalType.ID) AS DC3 ORDER BY DC3.ServiceID ****************************************************************************************************** /* Existing qDCdetail (dblclick) */ SELECT DCDA.* FROM (SELECT DCD.BatchID, DCD.ServiceID, DCD.Code, DCD.AmountExGST, DCD.CallCode, DCD.DialledNumber, DCD.RateDescription, DCD.TransactionType, DCD.Var01, DCD.Var02, DCD.Var03, DCD.Var04, DCD.Var05, DCD.Var06, DCD.Var07, DCD.Var08, DCD.Var09, DCD.Var10, DCD.Var11, DCD.Var12, DCD.Var13, DCD.Var14, DCD.Var15 FROM (SELECT tDataCollection.BatchID, tDataCollection.ServiceID, tDataCollection.AmountExGST, tDataCollection.CallCode, tDataCollection.RateDescription, tDataCollection.DialledNumber, tDataCollection.Var01, tDataCollection.Var02, tDataCollection.Var03, tDataCollection.Var04, tDataCollection.Var05, tDataCollection.Var06, tDataCollection.Var07, tDataCollection.Var08, tDataCollection.Var09, tDataCollection.Var10, tDataCollection.Var11, tDataCollection.Var12, tDataCollection.Var13, tDataCollection.Var14, tDataCollection.Var15, tServiceType.Code, tTransactionType.Description AS TransactionType FROM ((tDataCollection LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID WHERE tDataCollection.BatchID = 50004 AND tDataCollection.ServiceID LIKE '0404-813968') AS DCD) AS DCDA ORDER BY DCDA.ServiceID /* MODIFIED qDCdetail (dblclick) */ SELECT DCDA.* FROM (SELECT DCD.BatchID, DCD.ServiceID, DCD.Code, DCD.AmountExGST, DCD.CallCode, DCD.DialledNumber, DCD.RateDescription, DCD.TransactionType, DCD.Var01, DCD.Var02, DCD.Var03, DCD.Var04, DCD.Var05, DCD.Var06, DCD.Var07, DCD.Var08, DCD.Var09, DCD.Var10, DCD.Var11, DCD.Var12, DCD.Var13, DCD.Var14, DCD.Var15 FROM (SELECT tDataCollection.BatchID, tDataCollection.ServiceID, tDataCollection.AmountExGST, tDataCollection.CallCode, tDataCollection.RateDescription, tDataCollection.DialledNumber, tDataCollection.Var01, tDataCollection.Var02, tDataCollection.Var03, tDataCollection.Var04, tDataCollection.Var05, tDataCollection.Var06, tDataCollection.Var07, tDataCollection.Var08, tDataCollection.Var09, tDataCollection.Var10, tDataCollection.Var11, tDataCollection.Var12, tDataCollection.Var13, tDataCollection.Var14, tDataCollection.Var15, tTransactionType.Description AS TransactionType, 'Code' = CASE WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE SUBSTRING(tDataCollection.ServiceType,1,5) END FROM ((tDataCollection LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID WHERE tDataCollection.BatchID = 50004 AND tDataCollection.ServiceID LIKE '0404-813968') AS DCD) AS DCDA ORDER BY DCDA.ServiceID ****************************************************************************************************** /* Existing OpenSQL() (adminsummary) */ SELECT TN1.* FROM (SELECT TN.BatchID, tServiceID.ServiceID, tServiceType.Code, TN.DateFrom, TN.DateTo, TN.Rent, TN.Calls, TN.Other, TN.Admin, tDiscount.DiscountRate, tDiscount.NonStdRevAdminFee, tDiscount.NonStdExpAdminFee FROM (SELECT tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID, Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.FromDate END) AS DateFrom, Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.ToDate END) AS DateTo, Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountExGST ELSE 0 END) AS Rent, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS Calls, Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountExGST ELSE 0 END) AS Other, Sum(CASE tTransactionGroup.Description WHEN 'ADMIN' THEN tTransaction.AmountExGST ELSE 0 END) AS Admin, tTransaction.CustomerID FROM ((tTransaction LEFT JOIN tBatch ON tTransaction.Batchid = tBatch.ID) LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tBatch.ID = 50004 AND tBatch.BatchStatusID > 4 AND tBatch.BatchStatusID < 8 AND tTransaction.StatusID IS NULL AND tTransaction.RevenueFlag <> 0 GROUP BY tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID, tTransaction.CustomerID) AS TN LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID LEFT JOIN tDiscount ON ((TN.CustomerID = tDiscount.CustomerID) AND (tServiceType.ID = tDiscount.ServiceTypeID))) AS TN1 ORDER BY TN1.ServiceID /* MODIFIED OpenSQL() (adminsummary) */ SELECT TN1.* FROM (SELECT TN.BatchID, tServiceID.ServiceID, tServiceType.Code, TN.DateFrom, TN.DateTo, TN.Rent, TN.Calls, TN.Other, TN.Admin, tDiscount.DiscountRate, tDiscount.NonStdRevAdminFee, tDiscount.NonStdExpAdminFee FROM (SELECT tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID, tTransaction.ServiceTypeID, Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.FromDate END) AS DateFrom, Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.ToDate END) AS DateTo, Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountExGST ELSE 0 END) AS Rent, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS Calls, Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountExGST ELSE 0 END) AS Other, Sum(CASE tTransactionGroup.Description WHEN 'ADMIN' THEN tTransaction.AmountExGST ELSE 0 END) AS Admin, tTransaction.CustomerID FROM ((tTransaction LEFT JOIN tBatch ON tTransaction.Batchid = tBatch.ID) LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tBatch.ID = 50004 AND tBatch.BatchStatusID > 4 AND tBatch.BatchStatusID < 8 AND tTransaction.StatusID IS NULL AND tTransaction.RevenueFlag = 1 GROUP BY tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID ) AS TN LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID LEFT JOIN tServiceType ON TN.ServiceTypeID = tServiceType.ID LEFT JOIN tDiscount ON ((TN.CustomerID = tDiscount.CustomerID) AND (TN.ServiceTypeID = tDiscount.ServiceTypeID))) AS TN1 ORDER BY TN1.ServiceID ****************************************************************************************************** /* Existing DubiousTest_1() */ SELECT TN2.* FROM (SELECT tTransaction.ServiceID, tTransaction.CustomerID, Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountIncGST ELSE 0 END) AS RENT FROM (tTransaction LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tTransaction.BatchID = 50004 AND tTransaction.RevenueFlag <> 0 GROUP BY tTransaction.ServiceID, tTransaction.CustomerID) AS TN2 LEFT JOIN tServiceID ON TN2.ServiceID = tServiceID.ID LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID WHERE tServiceType.ExpectedRent IS NOT NULL AND TN2.Rent <> tServiceType.ExpectedRent /* MODIFIED DubiousTest_1() */ /////////////////////////////////////////////////////////////////////// SELECT TN2.* FROM ((SELECT tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID, Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountIncGST ELSE 0 END) AS RENT FROM (tTransaction LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tTransaction.BatchID = 50004 AND tTransaction.RevenueFlag = 1 GROUP BY tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID) AS TN2 LEFT JOIN tServiceID ON TN2.ServiceID = tServiceID.ID) LEFT JOIN tServiceType ON TN2.ServiceTypeID = tServiceType.ID WHERE tServiceType.ExpectedRent IS NOT NULL AND TN2.Rent <> tServiceType.ExpectedRent ****************************************************************************************************** /* Existing DubiousTest_2() */ SELECT TN2.* FROM (SELECT tTransaction.ServiceID, tTransaction.CustomerID, Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountIncGST ELSE 0 END) AS RENT, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountIncGST ELSE 0 END) AS CALL FROM (tTransaction LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tTransaction.BatchID = 50004 AND tTransaction.RevenueFlag = 1 GROUP BY tTransaction.ServiceID, tTransaction.CustomerID) AS TN2 LEFT JOIN tServiceID ON TN2.ServiceID = tServiceID.ID LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID WHERE TN2.Call <> 0 AND TN2.Call < TN2.Rent AND tServiceType.NoCalls = 0 /* MODIFIED DubiousTest_2() */ /////////////////////////////////////////////////////////////////////// SELECT TN2.* FROM ((SELECT tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID, Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountIncGST ELSE 0 END) AS RENT, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountIncGST ELSE 0 END) AS CALL FROM (tTransaction LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tTransaction.BatchID = 50004 AND tTransaction.RevenueFlag = 1 GROUP BY tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID) AS TN2 LEFT JOIN tServiceID ON TN2.ServiceID = tServiceID.ID) LEFT JOIN tServiceType ON TN2.ServiceTypeID = tServiceType.ID WHERE TN2.Call <> 0 AND TN2.Call < TN2.Rent AND tServiceType.NoCalls = 0 ****************************************************************************************************** /* Existing CodeFile() */ SELECT TN1.RentAmt, TN1.CallAmt, TN1.OtherAmt, (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt) AS TotalAll, tStatus.Status, tCustomer.ShipTo, tPerson.Person, tLocation.Description AS Location, tCentre.Description AS Centre, tActivity.Description AS Activity, tElement.ElementCode AS Element, tSubledger.Description AS Subledger, tDepartment.Description AS Department, tServiceID.ServiceID AS ServiceName, tServiceID.Description AS ServiceDesc, tServiceID.InstallationDate, tServiceType.Code FROM ((((((((((SELECT tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.StatusID, tTransaction.PersonID, tTransaction.LocationID, tTransaction.CustomerCentreID, tTransaction.CustomerActivityID, tTransaction.CustomerElementID, tTransaction.CustomerSubledgerID, Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountExGST ELSE 0 END) AS RentAmt, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS CallAmt, Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountExGST ELSE 0 END) AS OtherAmt FROM (tTransaction LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tTransaction.BatchID = 50004 GROUP BY tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.StatusID, tTransaction.PersonID, tTransaction.LocationID, tTransaction.CustomerCentreID, tTransaction.CustomerActivityID, tTransaction.CustomerElementID, tTransaction.CustomerSubledgerID) AS TN1 LEFT JOIN tStatus ON TN1.StatusID = tStatus.ID) LEFT JOIN tCustomer ON TN1.CustomerID = tCustomer.ID) LEFT JOIN tPerson ON TN1.PersonID = tPerson.ID) LEFT JOIN tLocation ON TN1.LocationID = tLocation.ID) LEFT JOIN (tCentre LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID) ON TN1.CustomerCentreID = tCentre.ID) LEFT JOIN tActivity ON TN1.CustomerActivityID = tActivity.ID) LEFT JOIN tElement ON TN1.CustomerElementID = tElement.ID) LEFT JOIN tSubledger ON TN1.CustomerSubledgerID = tSubledger.ID) LEFT JOIN tServiceID ON TN1.ServiceID = tServiceID.ID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID WHERE tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' ORDER BY tServiceID.ServiceID /* MODIFIED CodeFile() */ /////////////////////////////////////////////////////////////////////// SELECT TN1.RentAmt, TN1.CallAmt, TN1.OtherAmt, (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt) AS TotalAll, tStatus.Status, tCustomer.ShipTo, tPerson.Person, tLocation.Description AS Location, tCentre.Description AS Centre, tActivity.Description AS Activity, tElement.ElementCode AS Element, tSubledger.Description AS Subledger, tDepartment.Description AS Department, tServiceID.ServiceID AS ServiceName, tServiceID.Description AS ServiceDesc, tServiceID.InstallationDate, tServiceType.Code FROM ((((((((((SELECT tTransaction.ServiceID, tTransaction.ServiceTypeID, tTransaction.CustomerID, tTransaction.StatusID, tTransaction.PersonID, tTransaction.LocationID, tTransaction.CustomerCentreID, tTransaction.CustomerActivityID, tTransaction.CustomerElementID, tTransaction.CustomerSubledgerID, Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountExGST ELSE 0 END) AS RentAmt, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS CallAmt, Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountExGST ELSE 0 END) AS OtherAmt FROM (tTransaction LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tTransaction.BatchID = 50004 GROUP BY tTransaction.ServiceID, tTransaction.ServiceTypeID, tTransaction.CustomerID, tTransaction.StatusID, tTransaction.PersonID, tTransaction.LocationID, tTransaction.CustomerCentreID, tTransaction.CustomerActivityID, tTransaction.CustomerElementID, tTransaction.CustomerSubledgerID) AS TN1 LEFT JOIN tStatus ON TN1.StatusID = tStatus.ID) LEFT JOIN tCustomer ON TN1.CustomerID = tCustomer.ID) LEFT JOIN tPerson ON TN1.PersonID = tPerson.ID) LEFT JOIN tLocation ON TN1.LocationID = tLocation.ID) LEFT JOIN (tCentre LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID) ON TN1.CustomerCentreID = tCentre.ID) LEFT JOIN tActivity ON TN1.CustomerActivityID = tActivity.ID) LEFT JOIN tElement ON TN1.CustomerElementID = tElement.ID) LEFT JOIN tSubledger ON TN1.CustomerSubledgerID = tSubledger.ID) LEFT JOIN tServiceID ON TN1.ServiceID = tServiceID.ID) LEFT JOIN tServiceType ON TN1.ServiceTypeID = tServiceType.ID WHERE tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' ORDER BY tServiceID.ServiceID ****************************************************************************************************** /* Existing CriticalError_4() Test */ SELECT DC2.* FROM (SELECT DC1.*, tServiceType.NoCalls FROM ((SELECT tDataCollection.ServiceID, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tDataCollection.AmountIncGST ELSE 0 END) AS CALL FROM (tDataCollection LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tDataCollection.BatchID = 50004 GROUP BY tDataCollection.ServiceID) AS DC1 LEFT JOIN tServiceID ON DC1.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC2 WHERE DC2.Call <> 0 AND DC2.NoCalls <> 0 /* MODIFIED CriticalError_4() Test */ /////////////////////////////////////////////////////////////////////// SELECT DC2.* FROM (SELECT DC1.*, tServiceType.NoCalls FROM (SELECT DC.ServiceID, DC.Call, 'Code' = CASE WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tDataCollection.AmountIncGST ELSE 0 END) AS CALL FROM (tDataCollection LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tDataCollection.BatchID = 50004 GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code) AS DC2 WHERE DC2.Call <> 0 AND DC2.NoCalls = 1 ****************************************************************************************************** /* Existing CriticalError_4() Update */ UPDATE tDataCollection SET tDataCollection.StatusID = 4 FROM (SELECT DC2.ServiceID FROM (SELECT DC1.*, tServiceType.NoCalls FROM (SELECT tDataCollection.ServiceID, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tDataCollection.AmountIncGST ELSE 0 END) AS CALL FROM (tDataCollection LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tDataCollection.BatchID = 50004 AND tDataCollection.StatusID IS NULL GROUP BY tDataCollection.ServiceID) AS DC1 LEFT JOIN tServiceID ON DC1.ServiceID = tServiceID.ServiceID LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC2 WHERE DC2.Call <> 0 AND DC2.NoCalls <> 0) AS DC3 WHERE tDataCollection.BatchID = 50004 AND tDataCollection.ServiceID = DC3.ServiceID /* MODIFIED CriticalError_4() Update */ /////////////////////////////////////////////////////////////////////// UPDATE tDataCollection SET tDataCollection.StatusID = 4 FROM (SELECT DC2.ServiceID FROM (SELECT DC1.*, tServiceType.NoCalls FROM (SELECT DC.ServiceID, DC.Call, 'Code' = CASE WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType, Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tDataCollection.AmountIncGST ELSE 0 END) AS CALL FROM (tDataCollection LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tDataCollection.BatchID = 50004 GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code) AS DC2 WHERE DC2.Call <> 0 AND DC2.NoCalls = 1) AS DC3 WHERE tDataCollection.BatchID = 50004 AND tDataCollection.ServiceID = DC3.ServiceID ****************************************************************************************************** /* Existing CriticalError_5() Test */ SELECT DC.ServiceID FROM (((SELECT tDataCollection.ServiceID FROM tDataCollection WHERE tDataCollection.BatchID = 50004 GROUP BY tDataCollection.ServiceID) AS DC INNER JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID) INNER JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) LEFT JOIN tServiceSplit ON tServiceID.ID = tServiceSplit.ServiceID WHERE tServiceType.SplitService = 1 AND tServiceSplit.ServiceID IS NULL /* MODIFIED CriticalError_5() Test */ /////////////////////////////////////////////////////////////////////// SELECT SID, DC2.ServiceID FROM (SELECT DC1.*, tServiceType.ID, tServiceType.SplitService FROM (SELECT tServiceID.ID AS SID, DC.ServiceID, 'Code' = CASE WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType FROM tDataCollection WHERE tDataCollection.BatchID = 50004 GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code WHERE tServiceType.SplitService = 1) AS DC2 LEFT JOIN tServiceSplit ON DC2.SID = tServiceSplit.ServiceID WHERE tServiceSplit.ServiceID IS NULL ****************************************************************************************************** /* Existing CriticalError_5() Update */ UPDATE tDataCollection SET tDataCollection.StatusID = 5 FROM (SELECT tDataCollection.ServiceID FROM ((tDataCollection LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) LEFT JOIN tServiceSplit ON tServiceID.ID = tServiceSplit.ServiceID WHERE tDataCollection.BatchID = 50004 AND tDataCollection.StatusID IS NULL AND tServiceType.SplitService = 1 AND tServiceSplit.ServiceID IS NULL) AS DC1 WHERE tDataCollection.BatchID = 50004 AND tDataCollection.ServiceID = DC1.ServiceID /* MODIFIED CriticalError_5() Update */ /////////////////////////////////////////////////////////////////////// UPDATE tDataCollection SET tDataCollection.StatusID = 5 FROM (SELECT DC2.ServiceID FROM (SELECT DC1.*, tServiceType.ID, tServiceType.SplitService FROM (SELECT tServiceID.ID AS SID, DC.ServiceID, 'Code' = CASE WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType FROM tDataCollection WHERE tDataCollection.BatchID = 50004 AND tDataCollection.StatusID IS NULL GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code WHERE tServiceType.SplitService = 1) AS DC2 LEFT JOIN tServiceSplit ON DC2.SID = tServiceSplit.ServiceID WHERE tServiceSplit.ServiceID IS NULL) AS DC2 WHERE tDataCollection.BatchID = 50004 AND tDataCollection.ServiceID = DC2.ServiceID ****************************************************************************************************** /* Existing Save2Transactions() Expenses */ INSERT INTO tTransaction (DataRefID, SequenceNo, Duration, CallCode, BatchID, TxnDate, AmountExGST, GSTAmount, AmountIncGST, FromDate, ToDate, TransactionTypeID, StatusID, GSTFlag, ActivityID, CentreID, ServiceID, SubledgerID, CustomerCentreID, CustomerActivityID, CustomerElementID, CustomerSubledgerID, LocationID, UpEnd, UpMDF, DownEnd, DownMDF, Length, PersonID, ServiceTypeID, RevenueFlag, ElementID, CustomerID, DialledNumberID, OriginID, DestinationID, RateDescriptionID, Peak, Var01, Var02, Var03, Var04, Var05, Var06, Var07, Var08, Var09, Var10, Var11, Var12, Var13, Var14, Var15) SELECT tDataCollection.ID AS DataRefID, tDataCollection.SequenceNo, tDataCollection.Duration, tDataCollection.CallCode, tDataCollection.BatchID, (CASE tDataCollection.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.TxnDate,7,4) + SUBSTRING(tDataCollection.TxnDate,4,2) + SUBSTRING(tDataCollection.TxnDate,1,2) + (CASE tDataCollection.TxnTime WHEN NULL THEN '' ELSE ' ' + tDataCollection.TxnTime END) END) AS TxnDate, tDataCollection.AmountExGST, tDataCollection.GSTAmount, tDataCollection.AmountIncGST, (CASE tDataCollection.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.FromDate,7,4) + SUBSTRING(tDataCollection.FromDate,4,2) + SUBSTRING(tDataCollection.FromDate,1,2) END) AS FromDate, (CASE tDataCollection.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.ToDate,7,4) + SUBSTRING(tDataCollection.ToDate,4,2) + SUBSTRING(tDataCollection.ToDate,1,2) END) AS ToDate, tDataCollection.TransactionTypeID, tDataCollection.StatusID, (CASE tDataCollection.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS GSTFlag, tServiceID.ActivityID, tServiceID.CentreID, tServiceID.ID AS ServiceID, tServiceID.SubledgerID, tServiceID.CustomerCentreID, tServiceID.CustomerActivityID, tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, tServiceID.LocationID, tServiceID.UpEnd, tServiceID.UpMDF, tServiceID.DownEnd, tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, tServiceType.ID AS ServiceTypeID, (0) AS RevenueFlag, tServiceType.ExpenseElementID AS ElementID, tCustomer.ID AS CustomerID, tDialledNumber.ID as DialledNumberID, tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID, tDataCollection.Peak, tDataCollection.Var01, tDataCollection.Var02, tDataCollection.Var03, tDataCollection.Var04, tDataCollection.Var05, tDataCollection.Var06, tDataCollection.Var07, tDataCollection.Var08, tDataCollection.Var09, tDataCollection.Var10, tDataCollection.Var11, tDataCollection.Var12, tDataCollection.Var13, tDataCollection.Var14, tDataCollection.Var15 FROM ((((tDataCollection LEFT JOIN ((tServiceID LEFT JOIN (tCentre LEFT JOIN (tDepartment LEFT JOIN (tGroup LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID) ON tDepartment.GroupID = tGroup.ID) ON tCentre.DepartmentID = tDepartment.ID) ON tServiceID.CustomerCentreID = tCentre.ID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tDialledNumber ON tDataCollection.DialledNumber = tDialledNumber.Description) LEFT JOIN tOrigin ON tDataCollection.Origin = tOrigin.Description) LEFT JOIN tDestination ON tDataCollection.Destination = tDestination.Description) LEFT JOIN tRateDescription ON tDataCollection.RateDescription = tRateDescription.Description WHERE tServiceID.ServiceID IS NOT NULL AND tDataCollection.BatchID = 50004 AND tServiceType.SplitService <> 1 AND tServiceType.ExpenseElementID IS NOT NULL /* MODIFIED Save2Transactions() Expenses */ /////////////////////////////////////////////////////////////////////// INSERT INTO tTransaction (DataRefID, SequenceNo, Duration, CallCode, BatchID, TxnDate, AmountExGST, GSTAmount, AmountIncGST, FromDate, ToDate, TransactionTypeID, StatusID, GSTFlag, ActivityID, CentreID, ServiceID, SubledgerID, CustomerCentreID, CustomerActivityID, CustomerElementID, CustomerSubledgerID, LocationID, UpEnd, UpMDF, DownEnd, DownMDF, Length, PersonID, ServiceTypeID, RevenueFlag, ElementID, CustomerID, DialledNumberID, OriginID, DestinationID, RateDescriptionID, Peak, Var01, Var02, Var03, Var04, Var05, Var06, Var07, Var08, Var09, Var10, Var11, Var12, Var13, Var14, Var15) SELECT DC.ID AS DataRefID, DC.SequenceNo, DC.Duration, DC.CallCode, DC.BatchID, (CASE DC.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.TxnDate,7,4) + SUBSTRING(DC.TxnDate,4,2) + SUBSTRING(DC.TxnDate,1,2) + (CASE DC.TxnTime WHEN NULL THEN '' ELSE ' ' + DC.TxnTime END) END) AS TxnDate, DC.AmountExGST, DC.GSTAmount, DC.AmountIncGST, (CASE DC.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.FromDate,7,4) + SUBSTRING(DC.FromDate,4,2) + SUBSTRING(DC.FromDate,1,2) END) AS FromDate, (CASE DC.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.ToDate,7,4) + SUBSTRING(DC.ToDate,4,2) + SUBSTRING(DC.ToDate,1,2) END) AS ToDate, DC.TransactionTypeID, DC.StatusID, (CASE DC.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS GSTFlag, tServiceID.ActivityID, tServiceID.CentreID, tServiceID.ID AS ServiceID, tServiceID.SubledgerID, tServiceID.CustomerCentreID, tServiceID.CustomerActivityID, tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, tServiceID.LocationID, tServiceID.UpEnd, tServiceID.UpMDF, tServiceID.DownEnd, tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, tServiceType.ID AS ServiceTypeID, (0) AS RevenueFlag, tServiceType.ExpenseElementID AS ElementID, tGroup.CustomerID, tDialledNumber.ID as DialledNumberID, tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID, DC.Peak, DC.Var01, DC.Var02, DC.Var03, DC.Var04, DC.Var05, DC.Var06, DC.Var07, DC.Var08, DC.Var09, DC.Var10, DC.Var11, DC.Var12, DC.Var13, DC.Var14, DC.Var15 FROM ((((((SELECT tDataCollection.*, 'Code' = CASE WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END FROM (tDataCollection LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID WHERE tDataCollection.BatchID = 50004 AND tServiceID.ServiceID IS NOT NULL) AS DC LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) LEFT JOIN (tServiceID LEFT JOIN (tCentre LEFT JOIN (tDepartment LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID) ON tCentre.DepartmentID = tDepartment.ID) ON tServiceID.CustomerCentreID = tCentre.ID) ON DC.ServiceID = tServiceID.ServiceID) LEFT JOIN tDialledNumber ON DC.DialledNumber = tDialledNumber.Description) LEFT JOIN tOrigin ON DC.Origin = tOrigin.Description) LEFT JOIN tDestination ON DC.Destination = tDestination.Description) LEFT JOIN tRateDescription ON DC.RateDescription = tRateDescription.Description WHERE tServiceID.ServiceID IS NOT NULL AND tServiceType.SplitService <> 1 AND tServiceType.ExpenseElementID IS NOT NULL ****************************************************************************************************** /* Existing Save2Transactions() SplitTest */ SELECT COUNT(*) AS TotalCount FROM ((tDataCollection LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) CROSS JOIN tServiceSplit WHERE tDataCollection.BatchID = 50003 AND tServiceType.SplitService = 1 AND tServiceID.ID = tServiceSplit.ServiceID /* MODIFIED Save2Transactions() SplitTest */ /////////////////////////////////////////////////////////////////////// SELECT COUNT(*) AS TotalCount FROM ((SELECT tDataCollection.*, tServiceID.ID AS SID, 'Code' = CASE WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END FROM (tDataCollection LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID WHERE tDataCollection.BatchID = 50003 AND tServiceID.ServiceID IS NOT NULL) AS DC LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) CROSS JOIN tServiceSplit WHERE tServiceType.SplitService = 1 AND DC.SID = tServiceSplit.ServiceID ****************************************************************************************************** /* Existing Save2Transactions() SplitFix */ SELECT tDataCollection.ID AS DataRefID, tDataCollection.SequenceNo, tDataCollection.Duration, tDataCollection.CallCode, tDataCollection.ServiceID, tDataCollection.BatchID, (CASE tDataCollection.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.TxnDate,7,4) + SUBSTRING(tDataCollection.TxnDate,4,2) + SUBSTRING(tDataCollection.TxnDate,1,2) + (CASE tDataCollection.TxnTime WHEN NULL THEN '' ELSE ' ' + tDataCollection.TxnTime END) END) AS TxnDate, tDataCollection.AmountExGST, tDataCollection.GSTAmount, tDataCollection.AmountIncGST, (CASE tDataCollection.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.FromDate,7,4) + SUBSTRING(tDataCollection.FromDate,4,2) + SUBSTRING(tDataCollection.FromDate,1,2) END) AS FromDate, (CASE tDataCollection.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.ToDate,7,4) + SUBSTRING(tDataCollection.ToDate,4,2) + SUBSTRING(tDataCollection.ToDate,1,2) END) AS ToDate, tDataCollection.TransactionTypeID, tDataCollection.StatusID, (CASE tDataCollection.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS GSTFlag, tServiceID.ActivityID, tServiceID.CentreID, tServiceID.ID AS SID, tServiceID.SubledgerID, tServiceID.CustomerCentreID, tServiceID.CustomerActivityID, tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, tServiceID.LocationID, tServiceID.UpEnd, tServiceID.UpMDF, tServiceID.DownEnd, tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, tServiceType.ID AS ServiceTypeID, tServiceType.Code, tTransactionGroup.Description AS TransactionGroup, tServiceType.RevenueElementID, tServiceType.ExpenseElementID, tCustomer.ID AS CustomerID, tDialledNumber.ID as DialledNumberID, tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID, tDataCollection.Var01, tDataCollection.Var02, tDataCollection.Var03, tDataCollection.Var04, tDataCollection.Var05, tDataCollection.Var06, tDataCollection.Var07, tDataCollection.Var08, tDataCollection.Var09, tDataCollection.Var10, tDataCollection.Var11, tDataCollection.Var12, tDataCollection.Var13, tDataCollection.Var14, tDataCollection.Var15 FROM ((((((tDataCollection LEFT JOIN ((tServiceID LEFT JOIN (tCentre LEFT JOIN (tDepartment LEFT JOIN (tGroup LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID) ON tDepartment.GroupID = tGroup.ID) ON tCentre.DepartmentID = tDepartment.ID) ON tServiceID.CustomerCentreID = tCentre.ID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tDialledNumber ON tDataCollection.DialledNumber = tDialledNumber.Description) LEFT JOIN tOrigin ON tDataCollection.Origin = tOrigin.Description) LEFT JOIN tDestination ON tDataCollection.Destination = tDestination.Description) LEFT JOIN tRateDescription ON tDataCollection.RateDescription = tRateDescription.Description) LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tServiceID.ServiceID IS NOT NULL AND tDataCollection.BatchID = 50003 AND tServiceType.SplitService = 1 ORDER BY tDataCollection.ServiceID /* MODIFIED Save2Transactions() SplitFix */ /////////////////////////////////////////////////////////////////////// SELECT DC.ID AS DataRefID, DC.SequenceNo, DC.Duration, DC.CallCode, DC.ServiceID, DC.BatchID, (CASE DC.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.TxnDate,7,4) + SUBSTRING(DC.TxnDate,4,2) + SUBSTRING(DC.TxnDate,1,2) + (CASE DC.TxnTime WHEN NULL THEN '' ELSE ' ' + DC.TxnTime END) END) AS TxnDate, DC.AmountExGST, DC.GSTAmount, DC.AmountIncGST, (CASE DC.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.FromDate,7,4) + SUBSTRING(DC.FromDate,4,2) + SUBSTRING(DC.FromDate,1,2) END) AS FromDate, (CASE DC.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.ToDate,7,4) + SUBSTRING(DC.ToDate,4,2) + SUBSTRING(DC.ToDate,1,2) END) AS ToDate, DC.TransactionTypeID, DC.StatusID, (CASE DC.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS GSTFlag, tServiceID.ActivityID, tServiceID.CentreID, tServiceID.ID AS SID, tServiceID.SubledgerID, tServiceID.CustomerCentreID, tServiceID.CustomerActivityID, tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, tServiceID.LocationID, tServiceID.UpEnd, tServiceID.UpMDF, tServiceID.DownEnd, tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, tServiceType.ID AS ServiceTypeID, tServiceType.Code, tTransactionGroup.Description AS TransactionGroup, tServiceType.RevenueElementID, tServiceType.ExpenseElementID, tGroup.CustomerID, tDialledNumber.ID as DialledNumberID, tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID, DC.Peak, DC.Var01, DC.Var02, DC.Var03, DC.Var04, DC.Var05, DC.Var06, DC.Var07, DC.Var08, DC.Var09, DC.Var10, DC.Var11, DC.Var12, DC.Var13, DC.Var14, DC.Var15 FROM ((((((((SELECT tDataCollection.*, 'Code' = CASE WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END FROM (tDataCollection LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID WHERE tDataCollection.BatchID = 50004 AND tServiceID.ServiceID IS NOT NULL) AS DC LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) LEFT JOIN (tServiceID LEFT JOIN (tCentre LEFT JOIN (tDepartment LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID) ON tCentre.DepartmentID = tDepartment.ID) ON tServiceID.CustomerCentreID = tCentre.ID) ON DC.ServiceID = tServiceID.ServiceID) LEFT JOIN tDialledNumber ON DC.DialledNumber = tDialledNumber.Description) LEFT JOIN tOrigin ON DC.Origin = tOrigin.Description) LEFT JOIN tDestination ON DC.Destination = tDestination.Description) LEFT JOIN tRateDescription ON DC.RateDescription = tRateDescription.Description) LEFT JOIN tTransactionType ON DC.TransactionTypeID = tTransactionType.ID) LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID WHERE tServiceID.ServiceID IS NOT NULL AND tServiceType.SplitService = 1 ****************************************************************************************************** ****************************************************************************************************** SELECT DC.* FROM ((SELECT tDataCollection.*, tServiceID.ID AS SID, 'Code' = CASE WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END FROM (tDataCollection LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID WHERE tDataCollection.BatchID = 50004 AND tServiceID.ServiceID IS NOT NULL) AS DC LEFT JOIN tServiceType ON DC.ServiceType = tServiceType.Code) SELECT DC.* FROM (SELECT tDataCollection.*, 'Code' = CASE WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END FROM (tDataCollection LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID) LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID WHERE tDataCollection.BatchID = 50004 AND tServiceID.ServiceID IS NOT NULL) AS DC LEFT JOIN tServiceType ON DC.ServiceType = tServiceType.Code