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

JohnJMAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ishandoCommented:
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

0
JohnJMAAuthor Commented:
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
0
ishandoCommented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

JohnJMAAuthor Commented:
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
0
ishandoCommented:
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
to
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
or
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
0
JohnJMAAuthor Commented:
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

0
ishandoCommented:
Ah, I see the problem now, its doing a cartesian join between the two tables within the month.
Try this one, it gets the monthly totals in the with clause, then there is only one record for each month when we do the join:

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnJMAAuthor Commented:
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
0
JohnJMAAuthor Commented:
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
 
0
JohnJMAAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.