I have the following query that groups every tender id and description and shows its total Sum:
3020 American Express 20 1000 Cash - primary currency 9903.25 3120 House Card 2605.56 4070 Purchase Order 668.25 3000 Visa 26005.19 SELECT B.TENDER_TYPE_ID, A.TENDER_TYPE_DESC, SUM (B.TENDER_AMT) FROM POS_TENDER_TYPE_HEAD A, SA_TRAN_TENDER B, SA_TRAN_HEAD C WHERE A.TENDER_TYPE_ID = B.TENDER_TYPE_ID AND B.TRAN_SEQ_NO = C.TRAN_SEQ_NO GROUP BY B.TENDER_TYPE_ID, A.TENDER_TYPE_DESC
In the table C (SA_TRAN_HEAD), there is a field of TRAN_DATETIME. I want the query to return the results falling between an interval of dates but do not want the date to appear in the query columns.
What could I do to achieve this ?
Advertisement
Answer
Just add the desired date range to the WHERE clause:
SELECT B.TENDER_TYPE_ID,
A.TENDER_TYPE_DESC,
SUM (B.TENDER_AMT)
FROM POS_TENDER_TYPE_HEAD A,
SA_TRAN_TENDER B,
SA_TRAN_HEAD C
WHERE A.TENDER_TYPE_ID = B.TENDER_TYPE_ID AND
B.TRAN_SEQ_NO = C.TRAN_SEQ_NO AND
C.TRAN_DATETIME BETWEEN TO_DATE('01-JAN-2015', 'DD-MON-YYYY')
AND TO_DATE('31-MAR-2015', 'DD-MON-YYYY')
GROUP BY B.TENDER_TYPE_ID,
A.TENDER_TYPE_DESC
I also suggest you get used to using ANSI join syntax, as it makes the joins much clearer and is more transportable (particularly for outer joins). The following should be equivalent:
SELECT B.TENDER_TYPE_ID,
A.TENDER_TYPE_DESC,
SUM (B.TENDER_AMT)
FROM POS_TENDER_TYPE_HEAD A
INNER JOIN SA_TRAN_TENDER B
ON B.TENDER_TYPE_ID = A.TENDER_TYPE_ID
INNER JOIN SA_TRAN_HEAD C
ON C.TRAN_SEQ_NO = B.TRAN_SEQ_NO
WHERE C.TRAN_DATETIME BETWEEN TO_DATE('01-JAN-2015 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
AND TO_DATE('31-MAR-2015 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
GROUP BY B.TENDER_TYPE_ID,
A.TENDER_TYPE_DESC
This makes it clear what the join criteria are, as opposed to the filter criteria.