Sales figures by financial years

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

  • [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 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 🙂

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

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

  • No probs 🙂 Have pretty much given up and gone back to manually creating the report in excel!

    Thanks anyway!

    Stephen 🙂

  • 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?

  • left outer join on the months table should work... This looks familiar 😛

  • 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! 🙁

  • 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 🙂

  • 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 🙁

  • On a side note - if I remove the Where clause, I get all of the months with blank values for clientname and total.

  • If I change the where clause to:

    WHERE

    invoices.invoicedate BETWEEN '01-apr-2007' AND '31-mar-2008'

    or invoices.invoicedate is null

    It works 🙂

  • stevecurrey (2/21/2008)


    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 🙁

    It's still the same issue though. Your WHERE clause (because it doesn't "allow" for the fact that invoicedate might be null) tells the Optimizer to turn that OUTER join into an INNER JOIN.

    If you were to make it

    WHERE invoices.invoicedate BETWEEN '01-apr-2007' AND '31-mar-2008'

    Or invoices.invoicedate is Null

    I think you will get better results, even with months with no invoices.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply