asked on

Comparing year/month to next year/month totals

I am trying to compare one year/month data with the next year/month data. I have one table with a InvcDate and InvAmt and totals and compares the year/month with the next year/month until I change years. Here is a sample of the output I am getting:

Year Month Invc Amt Invc Amt Next
2008. 9 525.69 643.76
2008 10 643.75 689.23
2008 11 689.23 712.68
2008 12 712.68 NULL (Null value should be value in 2009 1 - 698.42)
2009 1 698.42 703.93
2009 2 703.93 714.67
2009 3 714.67 NULL (which is correct because no more months are reported)

I don't know how to get the value in Invc Amt for 2009 1 in the Invc Amt Next column for the row 2008 12.

I have attached a copy of the code I wrote.

select datepart(yyyy,i.invoicedtm) as invcyr, 
   datepart(mm, i.invoicedtm) as invcmnth, sum(i.ttlbill) as totalbill, 
    prevbill = (select sum(i2.ttlbill) as totalbill 
		from invoiceheader i2
		where (i2.invoicedtm between '1/1/2008' 
                  and '12/31/2009') and 
                  (datepart(yyyy,i2.invoicedtm) = datepart
                   (yyyy,i.invoicedtm) and 
		 (datepart(mm,i2.invoicedtm)-1) = datepart
                    (mm,i.invoicedtm))
		group by datepart(yyyy,i2.invoicedtm), 
                    datepart(mm,i2.invoicedtm))
from invoiceheader i
where i.invoicedtm between '1/1/2008' and '12/31/2009' 
group by datepart(yyyy,i.invoicedtm), 
    datepart(mm,i.invoicedtm)
order by invcyr, invcmnth

Open in new window

ishando

You could try tomething like this:

select datepart(yyyy, i.invoicedtm) as invcyr, datepart(mm, i.invoicedtm) as invcmnth, 
       sum(i.ttlbill) as totalbill, sum(i2.ttlbill) as prevbill
from invoiceheader i
  left outer join invoiceheader i2
    on i2.invoicedtm between '1/1/2008' and '12/31/2009'
    and datediff(m, convert(date, year(i2.invoicedtm)* 10000 + month(i2.invoicedtm)*100 + 1),112),
           convert(date, year(i.invoicedtm)* 10000 + month(i.invoicedtm)*100 + 1),112)) = 1
where i.invoicedtm between '1/1/2008' and '12/31/2009' 
group by datepart(yyyy,i.invoicedtm), datepart(mm,i.invoicedtm)
order by i.invcyr, i.invcmnth

Open in new window

JohnJMA

ASKER

Ishando, thanks for the help. Though I am getting an "Artihmetic overflow error converting expressiion to data type datetime. This is happening on the datediff function in connection with hte conver funcition. SQL doesn't like convert(date, , ) so i substituted datetime for date. Any ideas?
Thanks. John

ishando

you might need to put a cast(x to varchar) around the year(i.invoicedtm)* 10000 + month(i.invoicedtm)*100 + 1)
I think it is expecting a varchar input
Sorry, I don't have a sql server handy to test at the moment

JohnJMA

ASKER

Ishando, I just had an opportunity to work on your suggestion and it works but with the incorrect results. I only get the null value in the prevbill on the first record which it should be but the prevbill for all of the following records I can not figure out where the numbers are coming from.
Thanks,John

ishando

It could be the datediff - the dates might be the wrong way round, so try either swapping them around or changing the compared result to -1
change

and datediff(m, convert(date, year(i2.invoicedtm)* 10000 + month(i2.invoicedtm)*100 + 1),112),

convert(date, year(i.invoicedtm)* 10000 + month(i.invoicedtm)*100 + 1),112)) = 1

and datediff(m, convert(date, year(i.invoicedtm)* 10000 + month(i.invoicedtm)*100 + 1),112),

convert(date, year(i2.invoicedtm)* 10000 + month(i2.invoicedtm)*100 + 1),112)) = 1

and datediff(m, convert(date, year(i2.invoicedtm)* 10000 + month(i2.invoicedtm)*100 + 1),112),

convert(date, year(i.invoicedtm)* 10000 + month(i.invoicedtm)*100 + 1),112)) = -1

JohnJMA

ASKER

Ishando,
Still not getting it to work. I might not haveexplained it correctly. I have given another example of the data that I am starting with and the results I am looking for from the data. The reuslting sql procedure may be different and easier than what I have been working with. What I am looking to do is add up all of the Invoice Amounts by Year and Month and showing the previous Months Invoice Amount Total. So for the date range of 1/12008 thru 4/30/2008 the results would be the 2008-1 has a value for Currnet (or Monthly) Invc Total and Prev Month would have Null. For the 2008-2 has a vaule of Current Invc Total and the Prev Month value would be equal to the Current (or Monthly) Value for 2008-1.
Does that make it easier to understand?
Thanks, John

Original data:
Invc Date  Invc Amnt
1/10/2008  500.00
1/10/2008  250.00
1/21/2008  100.00
1/22/2008  75.00
2/09/2008  100.00
2/10/2008  25.0.00
2/15/2008  25.00
2/15/2008  750.00
3/10/2008  50.00
3/10/2008  225.00
3/21/2008  125.00
3/22/2008  75.00
4/10/2008  200.00
4/10/2008  350.00
4/21/2008  425.00
4/22/2008  175.00

Results with next month
Year    Month     Current Invc Ttl	Prev Month     $ or % Change
2008	1	925.00		Null		<$/% +/->
2008	2	900.00		925.00		<$/% +/->
2008	3	475.00		900.00		<$/% +/->
2008	4	1150.00		475.00		<$/% +/->

Open in new window

ASKER CERTIFIED SOLUTION

ishando

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JohnJMA

ASKER

ishando, getting an error message "type date is not a defined system type." and if I change the convert(date... to convert(datetime... then I get the error "Arithmetic overflow error converting expression to data type datetime. " Once I can ressolve this problem I think it will be good to go.
John

JohnJMA

ASKER

ishando, I did get it to work. If I use convert(varchar(25)... instead of convert(date.... or convert(datetime... I get the correct values associated with the correct yaer/month combination.
Thanks, John

JohnJMA

ASKER

For the convert statements where it says "convert(date,...)" change that to "convert(varchar(25),...)" and it is a solid solution and delivers the required results. Thanks.