| View previous topic :: View next topic |
| Author |
Message |
schintala
Beginner
Joined: 18 May 2005
Posts: 108
Topics: 31
Location: USA
|
 Posted: Mon May 02, 2011 1:38 pm Post subject: SQL help for finding the Max TOTAL based on Date |
 |
|
Hi,
This could be a simple. I am bad at writing SQL. Please pardon me.
Below is my SQL. I need to get the max(total) and corresponding date (PRCS_DT) associated to it. When I use group by on date column it picks every thing for all the days count. I need only max toal of particular day with date.
Could anyone help me? Hope I am not confused my wording here.
| Code: |
SELECT MAX(TOTAL) AS MAX_TOAL
FROM(
SELECT
PRCS_DT, COUNT(*) AS TOTAL
FROM PSSDBAP.PSIPAYV1 PAY
,PSSDBAP.PSIBLRV1 BLR
WHERE PAY.BLLR_ID = BLR_BLLR_ID
AND DATA_SRC_CD = 'P' -- POS HOURLY DATA ONLY
AND VD_CD = '2' -- PAYMENT TRANSACTIONS ONLY
AND TXN_SRC_CD NOT IN ('AV','AS') -- EXCLUDE VOIDS
AND PRCS_DT BETWEEN '03/01/2011' AND '03/31/2011'
AND BLR.BLLR_PROD_TYP_CD NOT IN ('RT1','RT2')
AND PAY.BLLR_ID = '003443'
GROUP BY PRCS_DT) AS TEMP1
|
|
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue May 03, 2011 10:26 am Post subject: |
|
|
schintala,
Unless I am missing something , isn't it simple as this
Untested SQL
| Code: |
SELECT PRCS_DT
,COUNT(*) AS TOTAL
FROM PSSDBAP.PSIPAYV1 PAY
,PSSDBAP.PSIBLRV1 BLR
WHERE PAY.BLLR_ID = BLR_BLLR_ID
AND DATA_SRC_CD = 'P' -- POS HOURLY DATA ONLY
AND VD_CD = '2' -- PAYMENT TRANSACTIONS ONLY
AND TXN_SRC_CD NOT IN ('AV','AS') -- EXCLUDE VOIDS
AND PRCS_DT BETWEEN '03/01/2011' AND '03/31/2011'
AND BLR.BLLR_PROD_TYP_CD NOT IN ('RT1','RT2')
AND PAY.BLLR_ID = '003443'
GROUP BY PRCS_DT
ORDER BY 2 DESC
FETCH FIRST 1 ROW ONLY
|
|
|
| Back to top |
|
|
schintala
Beginner
Joined: 18 May 2005
Posts: 108
Topics: 31
Location: USA
|
| Posted: Tue May 03, 2011 10:55 am Post subject: |
|
|
Kolusu,
Thank you so much. I made it so complex. You made so simple I can't believe and it ran quick in secs. Thank you very much sir. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
