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?
 
ishandoConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.