View previous topic :: View next topic
|
Author |
Message |
anshul_gugnani
New User
Joined: 02 Nov 2009 Posts: 73 Location: Mumbai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009 Posts: 73 Location: Mumbai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
what does the 15 after the decimal and 4 after date mean? |
|
Back to top |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009 Posts: 73 Location: Mumbai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009 Posts: 73 Location: Mumbai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009 Posts: 73 Location: Mumbai
|
|
|
|
Hi,
Thanks a lot for your help! |
|
Back to top |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009 Posts: 73 Location: Mumbai
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
|