September 14, 2004 at 2:48 am
I have issues view which has columns issno,issdate,item,trxqty and i have receipts view
which has columns receiptno,rptdate,item,rcdqty.I want to retreive noofissues,totalissqty,
totalrcdqty between a range of items and between a range of dates.How can i do it.
This the query but the answer is incorrect.
select COUNT(DOCNUMBR)AS NOOFISSUES,ABS(SUM(TRXQTY))AS TOTISSQTY,
MAX(ITMISSUES.DOCDATE)AS LASTISSDATE,SUM(QTY)AS RCDQTY
from ITMISSUES
INNER JOIN USR_VIEW_RPTS ON ITMISSUES.ITEMNMBR=USR_VIEW_RPTS.ITEMNMBR
WHERE ITMISSUES.ITEMNMBR BETWEEN 'ALX0100505' AND 'ALX0100505'
AND ITMISSUES.DOCDATE BETWEEN '2004-04-01' AND '2004-04-30'
September 14, 2004 at 3:00 am
You can join both the views on the column on Item (if u can) and then just write the range of items in where clause and dates. Don't forget to handle dates using CAST or CONVERT.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply