SELECT SI.ServiceID, 'Network Access' AS "RateDescription",
 (CASE ST.Description WHEN '%VICTRACK%' THEN 'N' ELSE 'Y' END) AS "GSTFlag",
 (CASE SI.Cost WHEN NULL THEN NULL ELSE (CAST SI.Cost AS varchar) END) AS "Var01",
 (CASE SI.Cost100 WHEN NULL THEN NULL ELSE (CAST SI.Cost100 AS varchar) END) AS "Var02", SI.Invoiced AS "Var03",
 (CASE SI.Length WHEN NULL THEN NULL ELSE (CAST SI.Length AS varchar) END) AS "Var01",
 SI.UpMDF AS "Var05", SI.DownMDF AS "Var06",
 SI.Description AS "Var07", SI.StatusID AS "Var08", tStatus.Status AS "Var09", SI.SACCnumber AS "Var10",
 (CASE SI.Invoiced WHEN NULL THEN 'No Data' ELSE (CAST SI.Invoiced AS varchar) END) AS "Var11"
FROM tServiceID AS SI LEFT JOIN tServiceType AS ST ON SI.ServiceTypeID = ST.id
LEFT JOIN tStatus ON SI.StatusID = tStatus.ID




SELECT SI.ServiceID, 'Network Access' AS "RateDescription",
 (CASE ST.Description WHEN '%VICTRACK%' THEN 'N' ELSE 'Y' END) AS "GSTFlag",
 tRate.Cost AS "Var01",
 tRate.Cost100 AS "Var02", SI.Invoiced AS "Var03",
 SI.Length AS "Var04",
 SI.UpMDF AS "Var05", SI.DownMDF AS "Var06",
 SI.Description AS "Var07", SI.StatusID AS "Var08", tStatus.Status AS "Var09", SI.SACCnumber AS "Var10",
 SI.Invoiced AS "Var11"
FROM tServiceID AS SI LEFT JOIN tServiceType AS ST ON SI.ServiceTypeID = ST.id
LEFT JOIN tStatus ON SI.StatusID = tStatus.ID
LEFT JOIN tRate ON ((SI.ServiceTypeID = tRate.ServiceTypeID) AND (SI.Length <= tRate.Length))
WHERE SI.StatusID IN (2,4)

































SELECT SI.ServiceID, 'Network Access' AS "RateDescription",
 (CASE ST.Description WHEN '%VICTRACK%' THEN 'N' ELSE 'Y' END) AS "GSTFlag",
 tRate.Cost AS "Var01",
 tRate.Cost100 AS "Var02", SI.Invoiced AS "Var03",
 SI.Length AS "Var04",
 SI.UpMDF AS "Var05", SI.DownMDF AS "Var06",
 SI.Description AS "Var07", SI.StatusID AS "Var08", tStatus.Status AS "Var09", SI.SACCnumber AS "Var10",
 SI.Invoiced AS "Var11"
FROM tServiceID AS SI LEFT JOIN tServiceType AS ST ON SI.ServiceTypeID = ST.id
LEFT JOIN tStatus ON SI.StatusID = tStatus.ID
LEFT JOIN tRate ON ((SI.ServiceTypeID = tRate.ServiceTypeID) AND (SI.Length <= tRate.Length))
WHERE SI.StatusID IN (2,4)



//////////// OLD: START  ////////////////////////////////////

SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, RateDescription,
 AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, ToDate, TransactionTypeID,
 Var01, Var02, Var03,
 Var04, Var05, Var06, Var07, Var08, Var09, Var10
INTO tDC_Temp
FROM (

SELECT SID2.*, CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST"
FROM (
SELECT SID1.*,
 CAST((CASE WHEN SID1.GSTFlag LIKE 'Y' THEN (SID1.AmountExGST * 0.1) ELSE 0 END) AS money) AS "GSTAmount"

FROM (
SELECT SID.*, SI.ServiceID, 'Network Access' AS "RateDescription", CAST('55555' AS int) AS "BatchID",
 ('01/04/2006') AS "FromDate", ('30/04/2006') AS "ToDate",
 CAST('12345' AS int) AS "TransactionTypeID",
 CAST((CASE WHEN SID.Invoiced IS NULL THEN SID.Cost ELSE SID.Invoiced END) AS money) AS AmountExGST,
 (CASE WHEN tGroup.CustomerID = 499 THEN 'N' ELSE 'Y' END) AS "GSTFlag",
 CAST(SID.Cost AS varchar) AS "Var01",
 CAST(SID.Cost100 AS varchar) AS "Var02", CAST(SID.Invoiced AS varchar) AS "Var03",
 CAST(SID.ServiceLen AS varchar) AS "Var04",
 SI.UpMDF AS "Var05", SI.DownMDF AS "Var06",
 RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var07", CAST(SI.StatusID AS varchar) AS "Var08", tStatus.Status AS "Var09", SI.SACCnumber AS "Var10"
FROM (

SELECT SI0.ID, Max(tRate.Cost) AS "Cost", Max(tRate.Cost100) AS "Cost100",
 SI0.Length AS "ServiceLen", SI0.Invoiced
FROM tServiceID AS SI0 
LEFT JOIN tRate ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) AND (SI0.Length >= tRate.Length))
WHERE SI0.StatusID IN (2,4)
AND SI0.Active = 1
GROUP BY SI0.ID, SI0.Length, SI0.Invoiced

) AS SID
INNER JOIN tServiceID AS SI ON SID.ID = SI.ID
LEFT JOIN tStatus ON SI.StatusID = tStatus.ID
LEFT JOIN tCentre ON SI.CustomerCentreID = tCentre.ID
LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID
LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID

) AS SID1
WHERE SID1.AmountExGST IS NOT NULL
) AS SID2

) AS SIF

//////////////// OLD : END ///////////////////////////////////////

drop table tDC_Temp

//////////////// Import to tDataCollection ///////////////////////

INSERT INTO tDataCollection
(SequenceNo, BatchID, ServiceID, RateDescription,
 AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, ToDate, TransactionTypeID,
 Var01, Var02, Var03,
 Var04, Var05, Var06, Var07, Var08, Var09, Var10)
SELECT CAST(SequNo AS varchar) AS "SequenceNo", BatchID, ServiceID, RateDescription,
 AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, ToDate, TransactionTypeID,
 Var01, Var02, Var03,
 Var04, Var05, Var06, Var07, Var08, Var09, Var10
FROM tDC_Temp

/////////// End: Import to tDataCollection ///////////////////////


/////// Update the Cost and Cost100 fields in tSeviceID ///////////////////

UPDATE tServiceID
SET Cost = CostAmt, Cost100 = Cost100Amt
FROM (SELECT SI0.ID, Max(tRate.Cost) AS "CostAmt", Max(tRate.Cost100) AS "Cost100Amt"
FROM tServiceID AS SI0
LEFT JOIN tServiceType ON SI0.ServiceTypeID = tServiceType.ID
LEFT JOIN tRate ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) AND (SI0.Length >= tRate.Length))
WHERE tServiceType.SystemID = 53
AND SI0.Active = 1
GROUP BY SI0.ID) AS CostRate
WHERE tServiceID.ID = CostRate.ID

/// End: Update the Cost and Cost100 fields in tSeviceID ///////////////////














//////////// WAS NEW: START  ////////////////////////////////////

SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, RateDescription,
 AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, ToDate, TransactionTypeID,
 Var01, Var02, Var03,
 Var04, Var05, Var06, Var07, Var08, Var09, Var10
INTO tDC_Temprjc2006
FROM (

SELECT SID2.*, CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST"
FROM (SELECT SID1.*,
 CAST((CASE WHEN SID1.GSTFlag LIKE 'Y' THEN (SID1.AmountExGST * 0.1) ELSE 0 END) AS money) AS "GSTAmount"
FROM (SELECT SID.*, SI.ServiceID, 'Network Access' AS "RateDescription", CAST(22423 AS int) AS "BatchID",
 ('01/05/2006') AS "FromDate", ('31/05/2006') AS "ToDate",
 CAST(93 AS int) AS "TransactionTypeID",
 CAST(((CASE WHEN SID.Invoiced IS NULL THEN SID.Cost ELSE SID.Invoiced END)/12*tServiceType.Frequency) AS money) AS "AmountExGST",
 (CASE WHEN tGroup.CustomerID = 499 THEN 'N' ELSE 'Y' END) AS "GSTFlag",
 CAST(SID.Cost AS varchar) AS "Var01",
 CAST(SID.Cost100 AS varchar) AS "Var02", CAST(SID.Invoiced AS varchar) AS "Var03",
 CAST(SID.ServiceLen AS varchar) AS "Var04",
 SI.UpMDF AS "Var05", SI.DownMDF AS "Var06",
 RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var07", CAST(SI.StatusID AS varchar) AS "Var08", tStatus.Status AS "Var09", SI.SACCnumber AS "Var10"
FROM (SELECT SI0.ID, Max(tRate.Cost) AS "Cost", Max(tRate.Cost100) AS "Cost100",
 SI0.Length AS "ServiceLen", SI0.Invoiced
FROM tServiceID AS SI0 INNER JOIN (SELECT SC.ID
FROM (SELECT SC1.*
FROM (SELECT tServiceID.ID, (CASE
 WHEN tServiceType.Frequency IS NULL THEN 1
 WHEN tServiceType.Frequency = 0 THEN 1
 WHEN (CAST(Month(tServiceID.InstallationDate)-5 AS Real) / tServiceType.Frequency) =
  ROUND((Month(tServiceID.InstallationDate)-5) / tServiceType.Frequency,0,0) THEN 0
 ELSE 1
END) AS "Period"
FROM tServiceID
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tServiceType.SystemID = 53
AND tServiceID.StatusID IN (2,4)
AND tServiceID.Active = 1) AS SC1
WHERE SC1.Period = 0) AS SC) AS SI1 ON SI0.ID = SI1.ID
LEFT JOIN tRate ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) AND (SI0.Length >= tRate.Length))
GROUP BY SI0.ID, SI0.Length, SI0.Invoiced) AS SID
INNER JOIN tServiceID AS SI ON SID.ID = SI.ID
LEFT JOIN tServiceType ON SI.ServiceTypeID = tServiceType.ID
LEFT JOIN tStatus ON SI.StatusID = tStatus.ID
LEFT JOIN tCentre ON SI.CustomerCentreID = tCentre.ID
LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID
LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID
LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID
WHERE tCustomer.InvoiceServices = 1) AS SID1
WHERE SID1.AmountExGST IS NOT NULL) AS SID2
) AS SIF

//////////////// WAS NEW: END ///////////////////////////////////////












//////////// NEW: START  ////////////////////////////////////

SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, RateDescription,
 AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, ToDate, TransactionTypeID,
 Var01, Var02, Var03,
 Var04, Var05, Var06, Var07, Var08, Var09, Var10
INTO tDC_Temprjc2006
FROM (

SELECT SID2.*, CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST"
FROM (SELECT SID1.*,
 CAST((CASE WHEN SID1.GSTFlag LIKE 'Y' THEN (SID1.AmountExGST * 0.1) ELSE 0 END) AS money) AS "GSTAmount"
FROM (SELECT SID.*, SI.ServiceID, 'Network Access' AS "RateDescription", CAST(22423 AS int) AS "BatchID",
 ('01/05/2006') AS "FromDate", ('31/05/2006') AS "ToDate",
 CAST(93 AS int) AS "TransactionTypeID",

 CAST(((CASE WHEN SID.Invoiced IS NULL THEN SID.Cost ELSE SID.Invoiced END)*SID.Rate/12*tServiceType.Frequency) AS money) AS "AmountExGST",

 (CASE WHEN SI.CustomerCentreID = 499 THEN 'N' ELSE 'Y' END) AS "GSTFlag",
 CAST(SID.Cost AS varchar) AS "Var01",
 CAST(SID.Cost100 AS varchar) AS "Var02", CAST(SID.Invoiced AS varchar) AS "Var03",
 CAST(SID.ServiceLen AS varchar) AS "Var04",
 SI.UpMDF AS "Var05", SI.DownMDF AS "Var06",
 RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var07", CAST(SI.StatusID AS varchar) AS "Var08", tStatus.Status AS "Var09", SI.SACCnumber AS "Var10"
FROM (

SELECT SI0.ID, SI1.Rate, Max(tRate.Cost) AS "Cost", Max(tRate.Cost100) AS "Cost100",
 SI0.Length AS "ServiceLen", SI0.Invoiced
FROM (SELECT SC.ID, SC.Rate
 FROM (SELECT SC1.*, (CASE tDiscountedRate.Rate WHEN NULL THEN 100.0 ELSE tDiscountedRate.Rate END) AS "Rate"
 FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID,
 tCustomer.ID AS "CustomerID", (CASE
  WHEN ST.Frequency IS NULL THEN 1
  WHEN ST.Frequency = 0 THEN 1
  WHEN (CAST(Month(tServiceID.InstallationDate)-5 AS Real) / ST.Frequency) =
   ROUND((Month(tServiceID.InstallationDate)-5) / ST.Frequency,0,0) THEN 0
  ELSE 1
 END) AS "Period"
 FROM tServiceID
 LEFT JOIN tServiceType AS ST ON tServiceID.ServiceTypeID = ST.ID
 LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID
 LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID
 LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID
 LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID
 WHERE ST.SystemID = 53
 AND tServiceID.StatusID IN (2,4)
 AND tServiceID.Active = 1
 AND tCustomer.InvoiceServices = 1) AS SC1
LEFT JOIN tDiscountedRate ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND
 (SC1.CustomerID = tDiscountedRate.CustomerID) AND (tDiscountedRate.Active = 1))
WHERE SC1.Period = 0) AS SC) AS SI1
INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID
LEFT JOIN tRate ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) AND (SI0.Length >= tRate.Length))
GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate

) AS SID
INNER JOIN tServiceID AS SI ON SID.ID = SI.ID
LEFT JOIN tServiceType ON SI.ServiceTypeID = tServiceType.ID
LEFT JOIN tStatus ON SI.StatusID = tStatus.ID

) AS SID1
WHERE SID1.AmountExGST IS NOT NULL) AS SID2

) AS SIF

//////////////// NEW: END ///////////////////////////////////////