December 14, 2010 at 4:11 am
SELECT
'IN' AS TRTYPE,
I.INVOICENO AS TRID,
I.INVOICEDATE AS TRDATE,
dbo.GetFinancialYear(I.INVOICEDATE) AS FYEAR,
'' AS REPORTCATEGORY,
I.GROUPNUMBER,
I.REPORTINGYEAR AS REPORTYEAR,
I.REPORTTYPEID AS REPORTTYPEID,
IWI.SUBACCOUNTID AS SUBACCOUNTID,
--'' AS SUBACCOUNTID,
'' AS WEMDepositID,
'' AS BANKFILEID,
RATE AS TRAMOUNT,
TRSTATUS =
CASE
WHEN (I.WRITEOFF = 1) THEN
'W'
ELSE
I.STATUS
END,
I.DESCRIPTION AS NOTES,
I.INVOICENO,
'1' AS ACCOUNTCODE,
I.WRITEOFFDATE AS STATUSDATE,
I.INVOICEBATCHID
FROM
QINVOICE I
-- INVOICE I
INNER JOIN INVOICESUB INVS ON I.INVOICENO=INVS.INVOICENO
INNER JOIN INVOICESUB_WI IWI ON INVS.INVOICESUBID=IWI.INVOICESUBID
LEFT JOIN TIER2_GROUP TG ON I.GROUPNUMBER = TG.GROUPNUMBER
LEFT JOIN TIER2_HEADER T ON TG.PARENTID = T.FCL_ID
UNION ALL
SELECT
'AR' AS TRTYPE,
ACCRECEIVED.ACCRECEIVEDID AS TRID,
ACCRECEIVED.ACCRECEIVEDDATE AS TRDATE,
dbo.GetFinancialYear(ACCRECEIVED.ACCRECEIVEDDATE) AS FYEAR,
dbo.GetDepositCategory(ACCRECEIVED.ACCRECEIVEDDATE) AS REPORTCATEGORY,
ARS.GROUPNUMBER,
ARS.REPORTYEAR,
ASWI.REPORTTYPEID,
ASWI.SUBACCOUNTID,
AWI.WEMDepositID ,
AWI.BANKFILEID,
ARS. AMOUNT * - 1 AS TRAMOUNT,
STATUS AS TRSTATUS,
REFERENCE AS NOTES,
ARS.INVOICENO,
ARS.ACCOUNTCODE,
ACCRECEIVED. STATUSDATE AS STATUSDATE,
NULL AS INVOICEBATCHID
FROM
ACCRECEIVED, ACCRECEIVEDSUB ARS , ACCRECEIVEDSUB_WI ASWI , ACCRECEIVED_WI AWI
WHERE ACCRECEIVED.ACCRECEIVEDID = ARS.ACCRECEIVEDID
AND ARS.ACCRECEIVEDSUBID = ASWI.ACCRECIEVEDSUBID
AND ARS.ACCRECEIVEDID = AWI.ACCRECEIVEDID
AND ARS.ACCOUNTCODE = '1'
UNION ALL
SELECT
'AJ' AS TRTYPE,
ACCADJUSTMENT.ACCADJUSTMENTID AS TRID,
ACCADJUSTMENT.ACCADJUSTMENTDATE AS TRDATE,
dbo.GetFinancialYear(ACCADJUSTMENT.ACCADJUSTMENTDATE) AS FYEAR,
dbo.GetDepositCategory(ACCADJUSTMENT.ACCADJUSTMENTDATE) AS REPORTCATEGORY,
AAS.GROUPNUMBER,
AAS.REPORTYEAR,
ASWI.REPORTTYPEID AS REPORTTYPEID,
ASWI.SUBACCOUNTID AS SUBACCOUNTID,
AWI.WEMDepositID ,
'' AS BANKFILEID,
AAS.AMOUNT AS TRAMOUNT,
STATUS AS TRSTATUS,
DESCRIPTION AS NOTES,
AAS.INVOICENO,
AAS.ACCOUNTCODE,
ACCADJUSTMENT.STATUSDATE AS STATUSDATE,
NULL AS INVOICEBATCHID
FROM
ACCADJUSTMENT, ACCADJUSTMENTSUB AAS , ACCADJUSTMENT_WI AWI ,ACCADJUSTMENTSUB_WI ASWI
WHERE
ACCADJUSTMENT.ACCADJUSTMENTID = AAS.ACCADJUSTMENTID
AND ACCADJUSTMENT.ACCADJUSTMENTID = AWI.ACCADJUSTMENTID
AND AAS.AccAdjustmentSubID = ASWI.AccAdjustmentSubID
AND AAS.ACCOUNTCODE = '1'
UNION ALL
SELECT
'IF' AS TRTYPE,
INVOICEFOLLOWUP.INVOICEFOLLOWUPID AS TRID,
INVOICEFOLLOWUP.INVOICEFOLLOWUPDATE AS TRDATE,
dbo.GetFinancialYear(INVOICEFOLLOWUP.INVOICEFOLLOWUPDATE) AS FYEAR,
'' AS REPORTCATEGORY,
QINVOICE.GROUPNUMBER,
QINVOICE.REPORTINGYEAR AS REPORTYEAR,
QINVOICE.REPORTTYPEID AS REPORTTYPEID,
'2' AS SUBACCOUNTID,
'' AS WEMDepositID ,
'' AS BANKFILEID,
INVOICEFOLLOWUP.FOLLOWUPAMOUNT AS TRAMOUNT,
TRSTATUS =
CASE
WHEN (WRITEOFF = 1) THEN
'W'
ELSE
QINVOICE.STATUS
END,
QINVOICE.WRITEOFFREASON AS NOTES,
INVOICEFOLLOWUP.INVOICENO,
'1' AS ACCOUNTCODE,
INVOICEFOLLOWUP.INVOICEFOLLOWUPDATE AS STATUSDATE,
QINVOICE.INVOICEBATCHID
FROM
INVOICEFOLLOWUP ,QINVOICE
WHERE QINVOICE.INVOICENO = INVOICEFOLLOWUP.INVOICENO
😛
December 14, 2010 at 4:44 am
Formatted query:
SELECT 'IN' AS TRTYPE,
I.INVOICENO AS TRID,
I.INVOICEDATE AS TRDATE,
dbo.GetFinancialYear(I.INVOICEDATE) AS FYEAR,
'' AS REPORTCATEGORY,
I.GROUPNUMBER,
I.REPORTINGYEAR AS REPORTYEAR,
I.REPORTTYPEID AS REPORTTYPEID,
IWI.SUBACCOUNTID AS SUBACCOUNTID,
--'' AS SUBACCOUNTID,
'' AS WEMDepositID,
'' AS BANKFILEID,
RATE AS TRAMOUNT,
TRSTATUS = CASE WHEN ( I.WRITEOFF = 1 ) THEN 'W'
ELSE I.STATUS
END,
I.DESCRIPTION AS NOTES,
I.INVOICENO,
'1' AS ACCOUNTCODE,
I.WRITEOFFDATE AS STATUSDATE,
I.INVOICEBATCHID
FROM QINVOICE I -- INVOICE I
INNER JOIN INVOICESUB INVS ON I.INVOICENO = INVS.INVOICENO
INNER JOIN INVOICESUB_WI IWI ON INVS.INVOICESUBID = IWI.INVOICESUBID
LEFT JOIN TIER2_GROUP TG ON I.GROUPNUMBER = TG.GROUPNUMBER
LEFT JOIN TIER2_HEADER T ON TG.PARENTID = T.FCL_ID
UNION ALL
SELECT 'AR' AS TRTYPE,
ACCRECEIVED.ACCRECEIVEDID AS TRID,
ACCRECEIVED.ACCRECEIVEDDATE AS TRDATE,
dbo.GetFinancialYear(ACCRECEIVED.ACCRECEIVEDDATE) AS FYEAR,
dbo.GetDepositCategory(ACCRECEIVED.ACCRECEIVEDDATE) AS REPORTCATEGORY,
ARS.GROUPNUMBER,
ARS.REPORTYEAR,
ASWI.REPORTTYPEID,
ASWI.SUBACCOUNTID,
AWI.WEMDepositID,
AWI.BANKFILEID,
ARS.AMOUNT * -1 AS TRAMOUNT,
STATUS AS TRSTATUS,
REFERENCE AS NOTES,
ARS.INVOICENO,
ARS.ACCOUNTCODE,
ACCRECEIVED.STATUSDATE AS STATUSDATE,
NULL AS INVOICEBATCHID
FROM ACCRECEIVED,
ACCRECEIVEDSUB ARS,
ACCRECEIVEDSUB_WI ASWI,
ACCRECEIVED_WI AWI
WHERE ACCRECEIVED.ACCRECEIVEDID = ARS.ACCRECEIVEDID
AND ARS.ACCRECEIVEDSUBID = ASWI.ACCRECIEVEDSUBID
AND ARS.ACCRECEIVEDID = AWI.ACCRECEIVEDID
AND ARS.ACCOUNTCODE = '1'
UNION ALL
SELECT 'AJ' AS TRTYPE,
ACCADJUSTMENT.ACCADJUSTMENTID AS TRID,
ACCADJUSTMENT.ACCADJUSTMENTDATE AS TRDATE,
dbo.GetFinancialYear(ACCADJUSTMENT.ACCADJUSTMENTDATE) AS FYEAR,
dbo.GetDepositCategory(ACCADJUSTMENT.ACCADJUSTMENTDATE) AS REPORTCATEGORY,
AAS.GROUPNUMBER,
AAS.REPORTYEAR,
ASWI.REPORTTYPEID AS REPORTTYPEID,
ASWI.SUBACCOUNTID AS SUBACCOUNTID,
AWI.WEMDepositID,
'' AS BANKFILEID,
AAS.AMOUNT AS TRAMOUNT,
STATUS AS TRSTATUS,
DESCRIPTION AS NOTES,
AAS.INVOICENO,
AAS.ACCOUNTCODE,
ACCADJUSTMENT.STATUSDATE AS STATUSDATE,
NULL AS INVOICEBATCHID
FROM ACCADJUSTMENT,
ACCADJUSTMENTSUB AAS,
ACCADJUSTMENT_WI AWI,
ACCADJUSTMENTSUB_WI ASWI
WHERE ACCADJUSTMENT.ACCADJUSTMENTID = AAS.ACCADJUSTMENTID
AND ACCADJUSTMENT.ACCADJUSTMENTID = AWI.ACCADJUSTMENTID
AND AAS.AccAdjustmentSubID = ASWI.AccAdjustmentSubID
AND AAS.ACCOUNTCODE = '1'
UNION ALL
SELECT 'IF' AS TRTYPE,
INVOICEFOLLOWUP.INVOICEFOLLOWUPID AS TRID,
INVOICEFOLLOWUP.INVOICEFOLLOWUPDATE AS TRDATE,
dbo.GetFinancialYear(INVOICEFOLLOWUP.INVOICEFOLLOWUPDATE) AS FYEAR,
'' AS REPORTCATEGORY,
QINVOICE.GROUPNUMBER,
QINVOICE.REPORTINGYEAR AS REPORTYEAR,
QINVOICE.REPORTTYPEID AS REPORTTYPEID,
'2' AS SUBACCOUNTID,
'' AS WEMDepositID,
'' AS BANKFILEID,
INVOICEFOLLOWUP.FOLLOWUPAMOUNT AS TRAMOUNT,
TRSTATUS = CASE WHEN ( WRITEOFF = 1 ) THEN 'W'
ELSE QINVOICE.STATUS
END,
QINVOICE.WRITEOFFREASON AS NOTES,
INVOICEFOLLOWUP.INVOICENO,
'1' AS ACCOUNTCODE,
INVOICEFOLLOWUP.INVOICEFOLLOWUPDATE AS STATUSDATE,
QINVOICE.INVOICEBATCHID
FROM INVOICEFOLLOWUP,
QINVOICE
WHERE QINVOICE.INVOICENO = INVOICEFOLLOWUP.INVOICENO
Use ANSI standard query syntax.
Use proper JOINS - currently you are using INNER JOIN for first part but rest block are using comma separated tables names. SQL server makes cartision product during this type of joins and then using WHERE clause to select necessary records.
Thanks
December 14, 2010 at 5:19 am
You are using different sets of table in each query. there is no scope in further optimization.
Only chance remain is that if you can check the indexes and optimize it.
December 14, 2010 at 5:42 am
Try replacing your two scalar functions
dbo.GetFinancialYear
dbo.GetDepositCategory
with the SQL equivalent.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply