SQL Query to sum by month

1
votes

i have the two tables, Payment Table and Person Table, a person por month can have more than one payment, so i want so sum all "amount" fields from a parson per month and per year, if there is no payment the result should be 0 and ID of the person should appear in the month.

i am almost there, but in my curreny query the data displayed is all payments per person and not the sum. how can o het this?

current results are like this (see october) i need to sum below 3 payments and olny show one line of october 2013:

My Table

MonthNr---MonthAbr---Amount---PersonID---YearAmount

1---JAN---0---2---2013

2---FEB---0---2---2013

3---MAR---0---2---2013

4---APR---0---2---2013

5---MAY---0---2---2013

6---JUN---0---2---2013

7---JUL---0---2---2013

8---AUG---0---2---2013

9---SEP---0---2---2013

10---OCT---64,74---2---2013

10---OCT---73,66---2---2013

10---OCT---24,3---2---2013

11---NOV---24,3---2---2013

12----DEC----0---2----2013

My query:

SELECT 
months.monthno as MonthNr,
CAST(CASE   WHEN CAST(months.monthno AS int) =1  THEN 'JAN'
            WHEN CAST(months.monthno AS int) =2  THEN 'FEB'
            WHEN CAST(months.monthno AS int) =3  THEN 'MAR'
            WHEN CAST(months.monthno AS int) =4  THEN 'APR'
            WHEN CAST(months.monthno AS int) =5  THEN 'MAY'
            WHEN CAST(months.monthno AS int) =6  THEN 'JUN'
            WHEN CAST(months.monthno AS int) =7  THEN 'JUL'
            WHEN CAST(months.monthno AS int) =8  THEN 'AUG'
            WHEN CAST(months.monthno AS int) =9  THEN 'SEP'
            WHEN CAST(months.monthno AS int) =10  THEN 'OCT'
            WHEN CAST(months.monthno AS int) =11  THEN 'NOV'
            WHEN CAST(months.monthno AS int) =12  THEN 'DEC'
     ELSE 
     '' 
     END AS nvarchar) as MonthAbr,

    Amount = isnull(sum(o.Amount),0),
    c.IDPerson  as PersonID,
    isnull(year(o.Date ),2013) as YearAmount
    FROM
    Person c 
     cross join     
    (select number monthNo from master..spt_values where type='p' and number between 1 and 12) months
     full join Payments o
    ON o.IDPerson   = c.IDPerson
    AND month(o.Date ) = months.monthNo
    where c.IDPerson = 2
    GROUP BY
    months.monthno, c.IDPerson ,o.Date
    ORDER BY
    months.monthno, c.IDPerson

can anyone help me? thanks in advance.

3
sql
why are you grouping on o.date? looks like it should be YearAmount, MonthNr,IDPerson - Daniel E.
what happens when month(o.Date) is null in your join clause? Why are you not taking that into account as you are here isnull(year(o.Date ),2013) - Daniel E.

3 Answers

1
votes

Since you are using the isnull function on o.date I assume this means there are nulls in this column. If so, you need to account for this within your group by clause, e.g. "group by months.monthno, c.idperson, isnull(year(o.date),2013)".

0
votes

You shouldn't group by o.Date, but only by the month of the date, which you already have included as months.monthno.

-1
votes

Why dont you simplify it:

 select
 months.monthno as MonthNr
 ,case when monthno='1' then 'JAN'
       when monthno='2' then 'FEB'
       end as MonthAbr
 isnull(year(o.Date ),2013) as YearAmount
,sum(Amount)
from PERSONS c
left join Payments o ON o.IDPerson = c.IDPerson
left join    
(select number monthNo from master..spt_values where type='p' and number between 1 and    12) months on months.number= select month(o.Date)
group by months.monthno, o.date