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 ?

Recommended Answers

All 7 Replies

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

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.

commented: helpful ! +2

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.

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..

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

ohhhh its working..!!

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

thank you so much..!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.