Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Accumulate amount for all months.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Accumulate amount for all months.
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm
No new posts How can amount of the Storage Usage b... esduman61 CICS 6 Wed Mar 09, 2016 1:19 pm
No new posts Get rows based on amount entered from... Rohit Umarjikar DB2 10 Thu Feb 25, 2016 9:57 pm
No new posts Need to add new column with the amount pradeepkit DFSORT/ICETOOL 7 Fri Nov 21, 2014 12:38 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us