group by month

[bilal_fazlani]

this is my query for displaying monthly business

select convert(varchar,dateadd(month,datediff(month,0,inv_date),0),101) as 'months with date format', sum(inv_amount) as 'business' from invoices inner join customers on customers.cust_id =invoices.cust_id where fname+' '+lname='lala fazlani' group by customers.cust_id,dateadd(month,datediff(month,0,inv_date),0) order by customers.cust_id,dateadd(month,datediff(month,0,inv_date),0)

So when I execute this command, It gives me this result
http://img821.imageshack.us/img821/6492/sqle.jpg
http://img341.imageshack.us/img341/538/chartv.jpg

as you may notice, the months where there was no transaction are missing so the chart looks incomplete.

can anyone help me with this ?

[bilal_fazlani]

OR

select convert(varchar,dateadd(month,datediff(month,0,inv_date),0),101) as 'months with date format', sum(inv_amount) as 'business' from invoices invoices where cust_id=13 group by cust_id,dateadd(month,datediff(month,0,inv_date),0) order by dateadd(month,datediff(month,0,inv_date),0)

to make it simple..

please help

[adam_k]

If you were grouping by month(inv_date) then I'd suggest outer joining your query to a query resulting in numbers from 0 to 11.
Since you are grouping by 1st of each month, without a certain period defined, I'm guessing that you can datediff number of months and outer join to a query resulting in numbers from 0 to number of months - 1. Then instead of selecting

dateadd(month,datediff(month,0,inv_date),0),101)

add that number to your first month/year and you'll be set.

I don't have a PC available right now, so I can't provide the query. If you run into problems let me know and I'll provide it.

[bilal_fazlani]

yes, please provide query..

this is a bit complicated for me..

people are telling me to join it to a calender table
http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

That seems over complicated..

Thank You

[adam_k]

I think this will do it:

create table invoices (inv_date datetime, inv_amount float, cust_id int) 
insert into invoices values ('2011-11-05',100,13)
insert into invoices values ('2011-11-25',200,13)
insert into invoices values ('2011-12-01',1000,13)
insert into invoices values ('2011-12-02',100,13)
insert into invoices values ('2012-02-01',5,13)
insert into invoices values ('2012-02-02',10,13)
insert into invoices values ('2012-02-04',105,13)

declare @start_date datetime
declare @number_of_months int

select @start_date = dateadd(month,datediff(month,0,min(inv_date)),0) , @number_of_months = datediff(month, @start_date, getdate())
FROM invoices invoices WHERE cust_id=13


SELECT convert(varchar,dates.date,101) AS 'months with date format', isnull(sum(inv_amount),0) AS 'business' 
FROM invoices invoices right join (
	-- This select produces a sequence of dates starting from @start
	select DATEADD(month, seq.id, @start_date) as date 
	from 
	(
		-- This select produces a sequence from 0 to 399 using Cartesian product.
		select (a0.id + a1.id ) as id 
		from 
	        (select 0 id union select 1 union select 2 union select 3 union select 4 union select 5 union
			    select 6 union select 7 union select 8 union select 9) a0,  
		
			(select 0 id union select 10 union select 20 union select 30 union select 40 union select 50 union select 
		              60 union select 70 union select 80 union  select 90) a1
	where a0.id + a1.id <= @number_of_months
	) seq
) dates
on year(invoices.inv_date) = year(dates.date)
and month(invoices.inv_date) = month(dates.date) 
WHERE cust_id=13 
GROUP BY cust_id,dates.date 
ORDER BY dates.date

PS: The dates derived table is a script I've seen in an article in sqlteam a long time ago. I prefer it over calendar tables as it doesn't require any maintenance and can be escalated to hold a pretty huge number of dates (by adding derived tables like a0 & a1).

PS2: I've limited the number of months this query will display to 99.

PS3: If you want to execute the whole thing with 1 select you've got to replace @start_date and @number_of_months with subqueries. It is a waste of resources if you ask me, but it's possible.

[bilal_fazlani]

Hi adam..,
Thank you very much for your efforts..
using derived table looks like very smart idea..but unfortunately..its still not showing January 2012 :(

please have a look..
http://img19.imageshack.us/img19/2057/sqlscreenshot.png

I am trying to figure out where we need to make correction..

[adam_k]

It seems that I forgot to take into account the cust_id. When I tested this I forgot to add the cust_id field to the invoices table, so I commented all it's appearences and the query worked great. I corrected the create table and the insert statements, but didn't execute it again.

If you change the last where from WHERE cust_id=13 to WHERE cust_id=13 or cust_id is null then you'll get all months.
The reason for this is that since the invoices table doesn't have a record for that month, it won't have a record for that customer either.

It should look like this:

create table invoices (inv_date datetime, inv_amount float, cust_id int) 
insert into invoices values ('2011-11-05',100,13)
insert into invoices values ('2011-11-25',200,13)
insert into invoices values ('2011-12-01',1000,13)
insert into invoices values ('2011-12-02',100,13)
insert into invoices values ('2012-02-01',5,13)
insert into invoices values ('2012-02-02',10,13)
insert into invoices values ('2012-02-04',105,13)

declare @start_date datetime
declare @number_of_months int

select @start_date = dateadd(month,datediff(month,0,min(inv_date)),0) , @number_of_months = datediff(month, @start_date, getdate())
FROM invoices invoices WHERE cust_id=13


SELECT convert(varchar,dates.date,101) AS 'months with date format', isnull(sum(inv_amount),0) AS 'business' 
FROM invoices invoices right join (
	-- This select produces a sequence of dates starting from @start
	select DATEADD(month, seq.id, @start_date) as date 
	from 
	(
		-- This select produces a sequence from 0 to 399 using Cartesian product.
		select (a0.id + a1.id ) as id 
		from 
	        (select 0 id union select 1 union select 2 union select 3 union select 4 union select 5 union
			    select 6 union select 7 union select 8 union select 9) a0,  
		
			(select 0 id union select 10 union select 20 union select 30 union select 40 union select 50 union select 
		              60 union select 70 union select 80 union  select 90) a1
	where a0.id + a1.id <= @number_of_months
	) seq
) dates
on year(invoices.inv_date) = year(dates.date)
and month(invoices.inv_date) = month(dates.date) 
WHERE cust_id=13 or cust_id is null
GROUP BY cust_id,dates.date 
ORDER BY dates.date

[bilal_fazlani]

ohhhh its working..!!

I'll have to study this code..will get back to you with doubts if any..

thank you so much..!!