Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
kanchanchouhan

New User

Joined: 05 Sep 2007
Posts: 11
Location: kolkata

Posted: Wed Aug 11, 2010 6:51 am    Post subject: need to find sum of quarterly claims?

i have an Employee table with 3 columns:emp, timestap and claim.
 Code: emp  timestamp  claim a    jan-2009    c1 b    feb-2009    c1 c    march-2009  c1 a    feb-2010    c2 b    may-2010    c2 c    jan-2010    c2

if i need to find sum of quarterly claims on an yearly basis how can achieve that? do i need to strip the timestamp first and then on the basis of year i can get it or
 Code: select sum(claim)   from employee  where  month in('jan-2009','feb-2009', march-2009')

is it right?

prem_ibm

New User

Joined: 15 Sep 2007
Posts: 31
Location: Chennai

 Posted: Wed Aug 11, 2010 11:29 am    Post subject: Reply to: need to find sum of quarterly claims? Hi Please find below query.....not tested but hope it works...where as to find the Sum of any three rows....then u need to use IN BETWEEN clause....in that u need to provide the range of columns. SELECT SUM(CLAIM) FROM EMPLOYEE ORDER BY TIEMSTAMP WHERE COUNT(*) = '3'; it gives SUM of first three rows.
dick scherrer

Site Director

Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

 Posted: Thu Aug 12, 2010 12:24 am    Post subject: Hello, This "solution" does not appear to address the requirement. . .
sushanth bobby

Senior Member

Joined: 29 Jul 2008
Posts: 1013
Location: India

Posted: Thu Aug 12, 2010 7:38 am    Post subject:

Kanchan,

Try this,
 Code: SELECT Q,SUM(CLAIM) FROM (                                        SELECT EMPNAME, TS , CLAIM, 'Q1' AS Q FROM  SESSION.EMPTAB               WHERE MONTH(TS) <= 3                                        UNION ALL                                                          SELECT EMPNAME, TS , CLAIM, 'Q2' AS Q FROM  SESSION.EMPTAB               WHERE MONTH(TS) > 3 AND MONTH(TS) <= 6                      UNION ALL                                                          SELECT EMPNAME, TS , CLAIM, 'Q3' AS Q FROM  SESSION.EMPTAB               WHERE MONTH(TS) > 6 AND MONTH(TS) <= 9                      UNION ALL                                                          SELECT EMPNAME, TS , CLAIM, 'Q4' AS Q FROM  SESSION.EMPTAB               WHERE MONTH(TS) > 9 AND MONTH(TS) <= 12                  ) AS TAB                                                          GROUP BY Q

Thanks You,
Sushanth
GuyC

Senior Member

Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

Posted: Thu Aug 12, 2010 12:55 pm    Post subject:

sushanth bobby wrote:
Kanchan,

Try this,
 Code: SELECT Q,SUM(CLAIM) FROM (                                        SELECT EMPNAME, TS , CLAIM, 'Q1' AS Q FROM  SESSION.EMPTAB               WHERE MONTH(TS) <= 3                                        UNION ALL                                                          SELECT EMPNAME, TS , CLAIM, 'Q2' AS Q FROM  SESSION.EMPTAB               WHERE MONTH(TS) > 3 AND MONTH(TS) <= 6                      UNION ALL                                                          SELECT EMPNAME, TS , CLAIM, 'Q3' AS Q FROM  SESSION.EMPTAB               WHERE MONTH(TS) > 6 AND MONTH(TS) <= 9                      UNION ALL                                                          SELECT EMPNAME, TS , CLAIM, 'Q4' AS Q FROM  SESSION.EMPTAB               WHERE MONTH(TS) > 9 AND MONTH(TS) <= 12                  ) AS TAB                                                          GROUP BY Q

Thanks You,
Sushanth

besides disregarding different years (easily solved), You do realize that you probably have 4 tablescans ?

better would be to use year(ts) * 10 + int((month(ts) - 1) / 4 ) + 1 as Q
sushanth bobby

Senior Member

Joined: 29 Jul 2008
Posts: 1013
Location: India

 Posted: Thu Aug 12, 2010 4:12 pm    Post subject: Nice Solution GuyC. Sushanth
GuyC

Senior Member

Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

 Posted: Thu Aug 12, 2010 4:34 pm    Post subject: ty, just noticed it should be /3 instead of /4
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Find & Replace string in CA-SORT mrgnndhmk CA Products 1 Fri Mar 30, 2018 12:58 am Find a string in PS file opened in br... mukkas CLIST & REXX 3 Sat Feb 24, 2018 2:15 pm Command to find TMEMBER name for a TPIPE Siva NKK Kothamasu IMS DB/DC 0 Wed Aug 16, 2017 6:08 pm find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am To find out size allocated to a seque... ashek15 JCL & VSAM 15 Thu Apr 27, 2017 9:42 am

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us