Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sales figures by financial years


Sales figures by financial years

Author
Message
stevecurrey
stevecurrey
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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:

months
monthid int
realmonthid int
month
(eg 1,4,April)

invoices
invoiceid int
invoicedate datetime
invoicetotal decimal(19,2)
salesperson int
clientid int

clients
clientid int
clientname 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.
Ronald Nijland
Ronald Nijland
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 108
[EDIT] Lol i see you do not need this Smile 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 all
select '2' [Month]
union all
select '3' [Month]
union all
select '4' [Month]
union all
select '5' [Month]


-- drop table #report
create 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 0
alter table #report add [2] decimal(19,2) default 0
alter table #report add [3] decimal(19,2) default 0
alter table #report add [4] decimal(19,2) default 0
alter table #report add [5] decimal(19,2) default 0
*/
-- drop table #invoices
create 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, 1
union all
select 1, cast('2008-2-15' as datetime), 100, 1, 1
union all
select 1, cast('2008-3-15' as datetime), 150, 1, 1

-- all salespersons you want
insert into #report (salesperson)
select distinct salesperson
from #invoices
where clientid = 1

select '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 i
where 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 = 1
update r set [2] = '100.00' from #report r where salesperson = 1
update 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 Smile
stevecurrey
stevecurrey
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 22
Hi many thanks for that reply - it's gone completely over my head though I'm afraid Sad 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 Sad 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.
Ronald Nijland
Ronald Nijland
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 108
In my first example it will come up with the months as columsn


truncate table #invoices

insert into #invoices (invoiceid, invoicedate, invoicetotal, salesperson, clientid)
select 1, cast('2008-1-15' as datetime), 200, 1, 1
union all
select 1, cast('2008-2-15' as datetime), 100, 1, 1
union all
select 1, cast('2008-3-15' as datetime), 150, 1, 1
union all
select 2, cast('2008-1-15' as datetime), 1200, 2, 1
union all
select 2, cast('2008-2-15' as datetime), 1100, 2, 1
union all
select 2, cast('2008-3-15' as datetime), 1150, 2, 1


select salespersons.salesperson, #month.[month], sum(i.invoicetotal) totaal
from #month, (select distinct salesperson
from #invoices) salespersons, #invoices i
where i.salesperson = salespersons.salesperson
and #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.
stevecurrey
stevecurrey
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 22
No probs Smile Have pretty much given up and gone back to manually creating the report in excel!
Thanks anyway!
Stephen Smile
stevecurrey
stevecurrey
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.invoicedate

FROM

months
INNER JOIN
invoices ON months.realmonthid = MONTH(invoices.invoicedate)
INNER JOIN
clients ON invoices.clientid = clients.clientid
WHERE invoices.invoicedate BETWEEN '01-apr-2007' AND '31-mar-2008'

GROUP BY
months.monthid,
months.month,
months.realmonthid,
clients.clientname,
invoices.invoicedate

ORDER BY months.monthid

This works but only brings up the months where invoices have been placed - how would I get all of the other months to display properly?
Ronald Nijland
Ronald Nijland
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 108
left outer join on the months table should work... This looks familiar Tongue
stevecurrey
stevecurrey
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.invoicedate
FROM
months
LEFT OUTER JOIN
invoices ON months.realmonthid = MONTH(invoices.invoicedate)
INNER JOIN
clients ON invoices.clientid = clients.clientid

WHERE invoices.invoicedate BETWEEN '01-apr-2007' AND '31-mar-2008'
GROUP BY
months.monthid,
months.month,
months.realmonthid,
clients.clientname,
invoices.invoicedate
ORDER BY months.monthid

But still only showing the months with invoices - I definitely need some kind of refresher course! Sad
Ronald Nijland
Ronald Nijland
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
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 Smile
stevecurrey
stevecurrey
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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 JOIN
invoices ON months.realmonthid = MONTH(invoices.invoicedate)
LEFT OUTER JOIN
clients ON invoices.clientid = clients.clientid

WHERE invoices.invoicedate BETWEEN '01-apr-2007' AND '31-mar-2008'
GROUP BY
months.monthid,
months.month,
months.realmonthid,
clients.clientname,
invoices.invoicedate
ORDER BY months.monthid

Unfortunately I'm still only being presented with the months with invoices in them Sad
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search