Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Sales figures by financial years Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, February 21, 2008 2:12 AM
 Grasshopper Group: General Forum Members Last Login: Friday, October 17, 2008 6:06 AM Points: 11, Visits: 22
 Hi all am having nightmares trying to figure this one out. In the UK our financial year runs from Apr 1st to Mar 31st. I need to create a sales figures report for a financial year for clients from a particular salesperson. The months need to be columns (Apr > Mar) Each row needs to be a client and each cell would be the sum of the invoice totals for that month (0 if none).I have the following tables:monthsmonthid intrealmonthid intmonth(eg 1,4,April)invoicesinvoiceid intinvoicedate datetimeinvoicetotal decimal(19,2)salesperson intclientid intclientsclientid intclientname varchar(150)I envisage getting all 12 months of my financial year from my months table and summing the invoices from particular clients by the month part of the invoice date. Any help would be grreatly appreciated!Thanks in advance,Stephen.
Post #458410
 Posted Thursday, February 21, 2008 3:20 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, February 2, 2011 5:56 AM Points: 51, Visits: 108
 [EDIT] Lol i see you do not need this :) Sorry about that [/Edit]Hope this helps you on your way`create table #Month ( [month] varchar(32))insert into #month ([month])select '1' [Month]union allselect '2' [Month]union allselect '3' [Month]union allselect '4' [Month]union allselect '5' [Month]-- drop table #reportcreate table #report (id int identity(1,1) not null, salesperson int)select 'alter table #report add [' + [month] + '] decimal(19,2) default 0' from #month/*alter table #report add [1] decimal(19,2) default 0alter table #report add [2] decimal(19,2) default 0alter table #report add [3] decimal(19,2) default 0alter table #report add [4] decimal(19,2) default 0alter table #report add [5] decimal(19,2) default 0*/-- drop table #invoicescreate table #invoices (invoiceid int, invoicedate datetime, invoicetotal decimal(19,2), salesperson int, clientid int) insert into #invoices (invoiceid, invoicedate, invoicetotal, salesperson, clientid)select 1, cast('2008-1-15' as datetime), 200, 1, 1union allselect 1, cast('2008-2-15' as datetime), 100, 1, 1union allselect 1, cast('2008-3-15' as datetime), 150, 1, 1-- all salespersons you wantinsert into #report (salesperson)select distinct salespersonfrom #invoiceswhere clientid = 1select 'update r set [' + cast(datepart(month, invoicedate) as varchar(32)) + '] = ''' + cast(sum(invoicetotal) as varchar(32)) + ''' from #report r where salesperson = ' + cast(i.salesperson as varchar(32))from #invoices iwhere i.salesperson in (select salesperson from #invoices)group by i.salesperson, datepart(month, i.invoicedate)/*update r set [1] = '200.00' from #report r where salesperson = 1update r set [2] = '100.00' from #report r where salesperson = 1update r set [3] = '150.00' from #report r where salesperson = 1*/select * from #report`you have to make a function that executes the strings ( i have added the SQL strings so you can execute them by hand for this example) Its not the complete thing you want but it should get you started on how you can do it :)
Post #458435
 Posted Thursday, February 21, 2008 3:42 AM
 Grasshopper Group: General Forum Members Last Login: Friday, October 17, 2008 6:06 AM Points: 11, Visits: 22
 Hi many thanks for that reply - it's gone completely over my head though I'm afraid I've never used functions. When I was developing this app I pretty much knew I'd hit a brick wall when it came to this report I thought that having a months table may help with SSRS creating a matrix.Is there not just a way I can select every month from my months table and for each month a list of clients with invoices in that month being summed?Thanks,Stephen.
Post #458445
 Posted Thursday, February 21, 2008 3:52 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, February 2, 2011 5:56 AM Points: 51, Visits: 108
 In my first example it will come up with the months as columsn`truncate table #invoicesinsert into #invoices (invoiceid, invoicedate, invoicetotal, salesperson, clientid)select 1, cast('2008-1-15' as datetime), 200, 1, 1union allselect 1, cast('2008-2-15' as datetime), 100, 1, 1union allselect 1, cast('2008-3-15' as datetime), 150, 1, 1union allselect 2, cast('2008-1-15' as datetime), 1200, 2, 1union allselect 2, cast('2008-2-15' as datetime), 1100, 2, 1union allselect 2, cast('2008-3-15' as datetime), 1150, 2, 1select salespersons.salesperson, #month.[month], sum(i.invoicetotal) totaalfrom #month, (select distinct salespersonfrom #invoices) salespersons, #invoices iwhere i.salesperson = salespersons.salespersonand #month.[month] = datepart(month, i.invoicedate)group by salespersons.salesperson, #month.[month]`if you use the same tables and execute this query it will come up with month / salesperson / sum(invoices) per salesperson and month... I am not familiar with SSRS so i cannot help you with that.
Post #458447
 Posted Thursday, February 21, 2008 4:12 AM
 Grasshopper Group: General Forum Members Last Login: Friday, October 17, 2008 6:06 AM Points: 11, Visits: 22
 No probs :) Have pretty much given up and gone back to manually creating the report in excel!Thanks anyway!Stephen :)
Post #458454
 Posted Thursday, February 21, 2008 4:53 AM
 Grasshopper Group: General Forum Members Last Login: Friday, October 17, 2008 6:06 AM Points: 11, Visits: 22
 Hi - just had a brainwave - I'm nearly there I *think*I have added a months table with monthid, realmonthid, monthname. April being the first month of the financial year, it would have monthid 1, realmonthid 4. I join this table to the invoices table on the month part of the invoice date. I set the datefrom and dateto to the beginning and end of the financial year in question. Am I completely off the mark here? I have the following query now:SELECT months.monthid, months.month, months.realmonthid, SUM(invoices.cinvoicesubtotal) AS Total, clients.clientname, invoices.invoicedateFROM months INNER JOINinvoices ON months.realmonthid = MONTH(invoices.invoicedate) INNER JOINclients ON invoices.clientid = clients.clientidWHERE invoices.invoicedate BETWEEN '01-apr-2007' AND '31-mar-2008'GROUP BY months.monthid, months.month, months.realmonthid, clients.clientname, invoices.invoicedateORDER BY months.monthidThis works but only brings up the months where invoices have been placed - how would I get all of the other months to display properly?
Post #458471
 Posted Thursday, February 21, 2008 4:56 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, February 2, 2011 5:56 AM Points: 51, Visits: 108
 left outer join on the months table should work... This looks familiar :P
Post #458474
 Posted Thursday, February 21, 2008 5:32 AM
 Grasshopper Group: General Forum Members Last Login: Friday, October 17, 2008 6:06 AM Points: 11, Visits: 22
 lol sorry I'm not too hot on SQL and as soon as I saw temp tables and functions my eyes glazed over...I have:SELECT months.monthid, months.month, months.realmonthid, SUM(invoices.cinvoicesubtotal) AS Total, clients.clientname, invoices.invoicedateFROM months LEFT OUTER JOINinvoices ON months.realmonthid = MONTH(invoices.invoicedate) INNER JOINclients ON invoices.clientid = clients.clientidWHERE invoices.invoicedate BETWEEN '01-apr-2007' AND '31-mar-2008'GROUP BY months.monthid, months.month, months.realmonthid, clients.clientname, invoices.invoicedateORDER BY months.monthidBut still only showing the months with invoices - I definitely need some kind of refresher course!
Post #458490
 Posted Thursday, February 21, 2008 5:41 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, February 2, 2011 5:56 AM Points: 51, Visits: 108
 In a left outer join all values not avalable will become null and you are trying an inner join on the client table with a null value. That is why the rows are missing... add a left outer join on the client table.. Also if you use sum(isnull(invoice.total, 0)) it will be 0 in stead of null in the outer join rows :)
Post #458493
 Posted Thursday, February 21, 2008 6:32 AM
 Grasshopper Group: General Forum Members Last Login: Friday, October 17, 2008 6:06 AM Points: 11, Visits: 22
 Hi Thanks again for the reply - I now have the following:SELECT months.monthid, months.month, months.realmonthid, isnull(SUM(invoices.cinvoicesubtotal),0) AS Total, isnull(clients.clientname,'')FROM months LEFT OUTER JOINinvoices ON months.realmonthid = MONTH(invoices.invoicedate) LEFT OUTER JOINclients ON invoices.clientid = clients.clientidWHERE invoices.invoicedate BETWEEN '01-apr-2007' AND '31-mar-2008'GROUP BY months.monthid, months.month, months.realmonthid, clients.clientname, invoices.invoicedateORDER BY months.monthidUnfortunately I'm still only being presented with the months with invoices in them
Post #458517

 Permissions