IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Accumulate amount for all months.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Fri Jul 23, 2010 1:07 pm
Reply with quote

Hi,

I need to accumulate some amounts for all the months where date is in format DATE mm/dd/yyyy. How can I group the amount on just the month part.

Please help.

Thanks,
Anshul.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 23, 2010 1:37 pm
Reply with quote

well, first of all, what datatype is the date column?
what are the datatypes of the columns containing the amounts in question.
Back to top
View user's profile Send private message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Fri Jul 23, 2010 2:45 pm
Reply with quote

Hi,

The fields are declared as follows in table -

MTHLY_BUS_AMT - DECIMAL 15
DISC_REV_AMT - DECIMAL 15
SUBM_DT - DATE 4

I need to accumulate MTHLY_BUS_AMT and DISC_REV_AMT for every month and there is entry for all the dates in the table.

Please tell me if you need more info.

Thanks,
Anshul.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 23, 2010 3:06 pm
Reply with quote

what does the 15 after the decimal and 4 after date mean?
Back to top
View user's profile Send private message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Fri Jul 23, 2010 3:12 pm
Reply with quote

Hi,

Its the length of fields -

SUBM_DT - DATE
MTHLY_BUS_AMT - DECIMAL(15, 0)
DISC_REV_AMT - DECIMAL(15, 0)

Thanks,
Anshul.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 23, 2010 3:22 pm
Reply with quote

the length of fields refers to host variables.

simply GROUPing by month would mean Jan 2001 and Jan 2002 would be accumulated together.



now is there a specific year that is to be selected?
Back to top
View user's profile Send private message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Fri Jul 23, 2010 3:35 pm
Reply with quote

Hi,

Thats what my question is,

Can I do the grouping on just the month part of the date, so that i get the total amount for Jan,Feb and so on for all the months.

If the date is in format mm/dd/yyy and there are entries for all the dates of a year. I want total amount for evry month of year 2010.

Please let me know if I am not clear.

Thanks for youfr help.
Anshul.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 23, 2010 3:42 pm
Reply with quote

you can use the below logic for your sql if indeed your date column is of date datatype:

if you only want for 2010 then
Code:

SELECT month(sales_date) as month
      , sum(sales)
FROM DICK.SALES
        where year(sales_date) = 2010
group by month(sales_date)

else
Code:

SELECT  year(sales_date) as year
      , month(sales_date) as month
      , sum(sales)
FROM DICK.SALES
group by   year(sales_date)
      , month(sales_date)


dates are stored internally by db2 in their own format.
the yyddmm or mmddyy or ddmmyy format is based on your select criteria via the scalar function function of your choice
or your sites default date display format
Back to top
View user's profile Send private message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Fri Jul 23, 2010 4:45 pm
Reply with quote

Hi,

Thanks a lot for your help!
Back to top
View user's profile Send private message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Fri Jul 23, 2010 5:59 pm
Reply with quote

Hi,

I executed the above query using batch job and I am getting ouput in file as
Code:
MTHLY-BUS-AMT     MONTH   
8/PS              2/AN     
(1-8)             (9-10)   
1---------------- 2-------
***************************
   36795287909459         
   33957280758912         
   39975360150967         
   40359573616082         
   42103675818360         
   41138337173034         
        279313440         


I am getting blanks for month field. Have defined the layout as
Code:
MTHLY-BUS-AMT        PIC S9(15)V USAGE COMP-3.
MONTH                PIC X(02).               


Can you please tell what can be the problem.

Thanks,
Anshul.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Jul 23, 2010 6:57 pm
Reply with quote

From the SQL Reference manual

"The MONTH function returns the month part of a value.
The result of the function is a large integer."
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Amount of days between two dates PL/I & Assembler 8
No new posts Adding a trailer with record count an... JCL & VSAM 4
No new posts Identify the value if the SUM of the ... SYNCSORT 4
No new posts Formating of Amount field in CLIST be... CLIST & REXX 9
No new posts how to add the amount for duplicate r... COBOL Programming 9
Search our Forums:

Back to Top