| suggest [message #152304] |
Thu, 22 December 2005 07:31 |
sethybibeka
Messages: 10
Registered: August 2005
Location: bhubanes
|
Junior Member |
|
|
This query taking 40 mins plz suggest how to reduce time
SELECT (AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING) as DATEOFUPLOADING,
sum(AINVVOUCHERHISTORY.INVOICEAMOUNT) as amount,
count(distinct(AINVVOUCHERDETAILS.AINVVOUCHERDETAILID)) AS INVOICES
FROM
INVOICE.MDF_AINVTHERAAREA,
INVOICE.MDF_AINVDEPARTMENTS,
INVOICE.MDF_AINVTHERAAREADEPARTMENTS,
INVOICE.AINVVOUCHERDETAILS,
INVOICE.AINVVOUCHERHISTORY,
INVOICE.AINVVOUCHERDATEHISTORY,
ainvapproverhistorydetails
WHERE
INVOICE.AINVVOUCHERDATEHISTORY.AINVVOUCHERDETAILID=INVOICE.AINVVOUCHERHISTORY.ANVVOUCHERDETAILID and
( INVOICE.AINVVOUCHERDETAILS.AINVVOUCHERDETAILID= INVOICE.AINVVOUCHERDATEHISTORY.AINVVOUCHERDETAILID )
and AINVVoucherHistory.ANVVoucherDetailID=ainvapproverhistorydetails.AINVVOUCHERDETAILID
AND ( INVOICE.MDF_AINVDEPARTMENTS.AINVDEPARTMENTID=INVOICE.MDF_AINVTHERAAREADEPARTMENTS.AINVDEPARTMENTID )
AND ( INVOICE.MDF_AINVTHERAAREADEPARTMENTS.AINVTHERAAREAID=INVOICE.MDF_AINVTHERAAREA.AINVTHERAAREAID )
AND ( INVOICE.AINVVOUCHERHISTORY.DATEOFUPLOADING >= INVOICE.AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING AND INVOICE.AINVVOUCHERHISTORY.ENTEREDDATE <= INVOICE.AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING )
AND ( INVOICE.AINVVOUCHERHISTORY.DEPARTMENT=INVOICE.MDF_AINVDEPARTMENTS.DEPARTMENT )
and ((ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING-INVOICE.AINVVOUCHERDETAILS.DUEDATE)>=90)
and to_char(ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING,'dd')='10'
and AINVVOUCHERHISTORY.INVOICEAMOUNT>=0
and ainvapproverhistorydetails.PROJECTAPPROVER NOT IN('**NOT ACTIVE IN WF') and
ainvapproverhistorydetails.EXITDATE is Null
GROUP BY (ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING)
For 30-59
SELECT (AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING) as DATEOFUPLOADING,
sum(AINVVOUCHERHISTORY.INVOICEAMOUNT) as amount,
count(distinct(AINVVOUCHERDETAILS.AINVVOUCHERDETAILID)) AS INVOICES
FROM
INVOICE.MDF_AINVTHERAAREA,
INVOICE.MDF_AINVDEPARTMENTS,
INVOICE.MDF_AINVTHERAAREADEPARTMENTS,
INVOICE.AINVVOUCHERDETAILS,
INVOICE.AINVVOUCHERHISTORY,
INVOICE.AINVVOUCHERDATEHISTORY,
ainvapproverhistorydetails
WHERE
INVOICE.AINVVOUCHERDATEHISTORY.AINVVOUCHERDETAILID=INVOICE.AINVVOUCHERHISTORY.ANVVOUCHERDETAILID and
( INVOICE.AINVVOUCHERDETAILS.AINVVOUCHERDETAILID= INVOICE.AINVVOUCHERDATEHISTORY.AINVVOUCHERDETAILID )
and AINVVoucherHistory.ANVVoucherDetailID=ainvapproverhistorydetails.AINVVOUCHERDETAILID
AND ( INVOICE.MDF_AINVDEPARTMENTS.AINVDEPARTMENTID=INVOICE.MDF_AINVTHERAAREADEPARTMENTS.AINVDEPARTMENTID )
AND ( INVOICE.MDF_AINVTHERAAREADEPARTMENTS.AINVTHERAAREAID=INVOICE.MDF_AINVTHERAAREA.AINVTHERAAREAID )
AND ( INVOICE.AINVVOUCHERHISTORY.DATEOFUPLOADING >= INVOICE.AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING AND INVOICE.AINVVOUCHERHISTORY.ENTEREDDATE <= INVOICE.AINVVOUCHERDATEHISTORY.DATEOFVOUCHERUPLOADING )
AND ( INVOICE.AINVVOUCHERHISTORY.DEPARTMENT=INVOICE.MDF_AINVDEPARTMENTS.DEPARTMENT )
and ((ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING-INVOICE.AINVVOUCHERDETAILS.DUEDATE)>=30) and
((ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING-INVOICE.AINVVOUCHERDETAILS.DUEDATE) < 60)
and to_char(ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING,'dd')='10'
and AINVVOUCHERHISTORY.INVOICEAMOUNT>=0
and ainvapproverhistorydetails.PROJECTAPPROVER NOT IN('**NOT ACTIVE IN WF') and
ainvapproverhistorydetails.EXITDATE is Null
GROUP BY (ainvvoucherdatehistory.DATEOFVOUCHERUPLOADING)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
