-# Customer ImportCSV - TBS SQLs....

--# non VRT
SELECT
 tElement.Description AS Element,
 tServiceID.ServiceID AS ServiceIDDesc,
 tInvoiceDetail.BatchID AS Batch,
 tBatchType.SupplierDisplayedName AS Supplier,
 tLocation.Description AS Location,
 tPerson.Person,
 tCentre.Description AS Centre,
 tActivity.Description AS Activity,
 tElement_1.Description AS Element1,
 tSubledger.Description AS SubLedger,
 tFinalisedTransaction201303.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(tFinalisedTransaction201303.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201303.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201303.AmountIncGST) AS IncGST,
--# tFinalisedTransaction201303.AmountExGST AS ExGST,
--# tFinalisedTransaction201303.GSTAmount AS GST,
--# tFinalisedTransaction201303.AmountIncGST AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
--# tFinalisedTransaction201303.Var01,
 SUM(CASE ISNUMERIC(tFinalisedTransaction201303.Var01)
        WHEN 1 THEN CONVERT(INT, tFinalisedTransaction201303.Var01)
        ELSE 0
        END) AS KBytes,
 tFinalisedTransaction201303.Var02,
 tFinalisedTransaction201303.Var03,
 tFinalisedTransaction201303.Var04,
 tFinalisedTransaction201303.Var05,
 tFinalisedTransaction201303.Var06,
 tFinalisedTransaction201303.Var07,
 tFinalisedTransaction201303.Var08,
 tFinalisedTransaction201303.Var09,
 tFinalisedTransaction201303.Var10,
 tFinalisedTransaction201303.Var11,
 tFinalisedTransaction201303.Var12,
 tFinalisedTransaction201303.Var13,
 tFinalisedTransaction201303.Var14,
 tFinalisedTransaction201303.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201303
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201303.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201303.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER JOIN tInvoice
			ON tInvoiceDetail.InvoiceID = tInvoice.ID
--#			   and tInvoice.ID NOT IN (
--#				SELECT distinct tInvoice.CreditNoteInvID
--#				FROM tInvoice
--#				WHERE tInvoice.CreditNoteInvID is not null
--#			)
	) ON tFinalisedTransaction201303.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201303.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

 LEFT JOIN tElement
	ON tFinalisedTransaction201303.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
        ON tFinalisedTransaction201303.CustomerElementID = tElement_1.ID
 LEFT JOIN tServiceType
        ON tFinalisedTransaction201303.ServiceTypeID = tServiceType.ID

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201303.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201303.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201303.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201303.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201303.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201303.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201303.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = "2013/03" 
        AND tFinalisedTransaction201303.RevenueFlag = 1
        AND tCustomer.ShipTo <> "VRT"
--#        AND tBatchType.Active = 1
          AND tInvoice.InvoiceDate > dateadd(dd,15,"20120331")
          AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,15,"20120331"))
GROUP BY
 tElement.Description, tServiceID.ServiceID, tServiceID.Service,
 tInvoiceDetail.BatchID,
 tBatchType.SupplierDisplayedName, tLocation.Description, 
 tPerson.Person, tCentre.Description, tServiceType.Description,
 tInvoiceDetail.Description, tSubledger.Description, 
 tInvoiceDetail.Period, tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo,
--# tFinalisedTransaction201303.Var01, tFinalisedTransaction201303.Var02, 
 tFinalisedTransaction201303.Var02,
 tFinalisedTransaction201303.Var03, tFinalisedTransaction201303.Var04, 
 tFinalisedTransaction201303.Var05, tFinalisedTransaction201303.Var06,
 tFinalisedTransaction201303.Var07, tFinalisedTransaction201303.Var08,
 tFinalisedTransaction201303.Var09, tFinalisedTransaction201303.Var10,
 tFinalisedTransaction201303.Var11, tFinalisedTransaction201303.Var12,
 tFinalisedTransaction201303.Var13, tFinalisedTransaction201303.Var14,
 tFinalisedTransaction201303.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201303.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description;

--# VRT
SELECT
 tElement.Description AS Element,
 tServiceID.ServiceID AS ServiceIDDesc,
 tFinalisedTransaction201303.BatchID AS Batch,
 tBatchType.SupplierDisplayedName AS Supplier,
 tLocation.Description AS Location,
 tPerson.Person,
 tCentre.Description AS Centre,
 tActivity.Description AS Activity,
 tElement_1.Description AS Element1,
 tSubledger.Description AS SubLedger,
 tFinalisedTransaction201303.PayType,
 tServiceType.Description AS ServiceType,
 "" AS InvoiceDescription,
 tBatch.BillingPeriod AS Period,
 tCustomer.ShipTo,
 "VRT999999" AS Invoice,
 SUM(tFinalisedTransaction201303.AmountExGST) AS ExGST,
 SUM(tFinalisedTransaction201303.GSTAmount) AS GST,
 SUM(tFinalisedTransaction201303.AmountIncGST) AS IncGST,
--# tFinalisedTransaction201303.AmountExGST AS ExGST,
--# tFinalisedTransaction201303.GSTAmount AS GST,
--# tFinalisedTransaction201303.AmountIncGST AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
--# tFinalisedTransaction201303.Var01,
 SUM(CASE ISNUMERIC(tFinalisedTransaction201303.Var01)
        WHEN 1 THEN CONVERT(INT, tFinalisedTransaction201303.Var01)
        ELSE 0
        END) AS KBytes,
 tFinalisedTransaction201303.Var02,
 tFinalisedTransaction201303.Var03,
 tFinalisedTransaction201303.Var04,
 tFinalisedTransaction201303.Var05,
 tFinalisedTransaction201303.Var06,
 tFinalisedTransaction201303.Var07,
 tFinalisedTransaction201303.Var08,
 tFinalisedTransaction201303.Var09,
 tFinalisedTransaction201303.Var10,
 tFinalisedTransaction201303.Var11,
 tFinalisedTransaction201303.Var12,
 tFinalisedTransaction201303.Var13,
 tFinalisedTransaction201303.Var14,
 tFinalisedTransaction201303.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 tFinalisedTransaction201303
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = tFinalisedTransaction201303.BatchID
 LEFT JOIN tServiceID
        ON tFinalisedTransaction201303.ServiceID = tServiceID.ID
 LEFT JOIN tTransactionType
	ON tFinalisedTransaction201303.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

 LEFT JOIN tElement
	ON tFinalisedTransaction201303.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
        ON tFinalisedTransaction201303.CustomerElementID = tElement_1.ID
 LEFT JOIN tServiceType
        ON tFinalisedTransaction201303.ServiceTypeID = tServiceType.ID

 LEFT JOIN tCustomer
        ON tFinalisedTransaction201303.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON tFinalisedTransaction201303.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON tFinalisedTransaction201303.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON tFinalisedTransaction201303.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON tFinalisedTransaction201303.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON tFinalisedTransaction201303.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON tFinalisedTransaction201303.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = "2013/03"
        AND tFinalisedTransaction201303.RevenueFlag = 1
        AND tCustomer.ShipTo = "VRT"
--#        AND tBatchType.Active = 1
GROUP BY
 tElement.Description, tServiceID.ServiceID, tServiceID.Service,
 tFinalisedTransaction201303.BatchID,
 tBatchType.SupplierDisplayedName, tLocation.Description, 
 tPerson.Person, tCentre.Description, tServiceType.Description,
 tSubledger.Description, 
 tBatch.BillingPeriod, tCustomer.ShipTo,
--# tFinalisedTransaction201303.Var01, tFinalisedTransaction201303.Var02,
 tFinalisedTransaction201303.Var02,
 tFinalisedTransaction201303.Var03, tFinalisedTransaction201303.Var04, 
 tFinalisedTransaction201303.Var05, tFinalisedTransaction201303.Var06,
 tFinalisedTransaction201303.Var07, tFinalisedTransaction201303.Var08,
 tFinalisedTransaction201303.Var09, tFinalisedTransaction201303.Var10,
 tFinalisedTransaction201303.Var11, tFinalisedTransaction201303.Var12,
 tFinalisedTransaction201303.Var13, tFinalisedTransaction201303.Var14,
 tFinalisedTransaction201303.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, tFinalisedTransaction201303.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description;