Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Sep 2010
    Posts
    11

    Unanswered: Displaying previous monthly totals

    Hi

    I'm trying to create a query to display separate totals from previous monthly invoice runs.

    The table i'm querying is pretty simplistic and has a ref no, and invoice date and a total. All I want to do is display the values for the current month and be able to see the previous months total within the same query i.e:

    Data Table Example [Invoice_Data]

    Reference Inv__Date Total
    BEF345453 01AUG2010 20.00
    BEF345453 05JUL2010 30.00
    BEF345453 01JUN2010 20.00
    BEF345400 05JUL2010 60.00
    BEF345400 01AUG2010 20.00
    BEF345400 05JUN2010 10.00
    BEF367402 05JUL2010 40.00
    BEF367402 01AUG2010 10.00
    BEF367402 05JUN2010 30.00

    I want the query to be displayed in the format below if I were to use an August Parameter

    Reference CurMnth LastMnth
    BEF345453 20.00 30.00
    BEF345400 20.00 60.00
    BEF367402 10.00 40.00

    Can this be done?

    It seems simple enough but i'm really struggling to get the syntax right.

    Any help would be much appreciated.

    Thanks in advance

    Ben

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    First problem
    Inv_Date That look like String Value what you need to is put it to date value

    then you can do a date-1 month

    Lastmnth = dateadd('m',-1,date())

    so here a start
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    SELECT Reference, 
        SUM(CASE WHEN Inv__Date like '%AUG%' 
                THEN Total ELSE 0 END) as CurMnth,
        SUM(CASE WHEN Inv__Date like '%JUL%' 
                THEN Total ELSE 0 END) as LastMnth
    FROM Invoice_Data
    WHERE Inv__Date like '%AUG%' OR Inv__Date like '%JUL%'
    GROUP BY Reference
    The Inv__Date like '%xxx%' parts are ugly and inefficient to work with. Do as myle suggested and convert them to a DATE type.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Sep 2010
    Posts
    11
    Hi

    Thanks for the replies.

    Sorry I misled you a bit here, the date is a date field I just typed in Aug to make it clear what month I wanted......sorry..!!

    It should have read:

    Reference Inv____Date Total
    BEF345453 2010-08-01 20.00
    BEF345453 2010-07-05 30.00

    I have tried this already:

    SELECT Reference,
    SUM(CASE WHEN Inv__Date between '2010-08-01' and '2010-08-31'
    THEN Total ELSE 0 END) as CurMnth,
    SUM(CASE WHEN Inv__Date between '2010-07-01' and '2010-07-31'
    THEN Total ELSE 0 END) as LastMnth
    FROM Invoice_Data

    However this does not work as it seems to be bringing back all accounts but replacing anything outside of the daterange with a '0'.

    How can I just return the values within the daterange specified?

    Thanks again

    Ben

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    DECLARE @Month    INT
    SET @Month = 8
    
    SELECT Reference, 
        SUM(CASE WHEN MONTH(Inv__Date) = @Month 
                THEN Total ELSE 0 END) as CurMnth,
        SUM(CASE WHEN MONTH(Inv__Date) = @Month - 1 
                THEN Total ELSE 0 END) as LastMnth
    FROM Invoice_Data AS I
    WHERE MONTH(Inv__Date) BETWEEN @Month - 1 AND @Month
    GROUP BY Reference
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Sep 2010
    Posts
    11
    Thanks Wim

    That's pretty much returned what I want, however there are some accounts that have a couple of invoices in the month where one has gone out incorrectly and another revised one has bee issued a few days later.

    Would I be able to select the Latest (maximum) invoice date for accounts that have more than one invoice within the month?

    How would I incorporate this in the code?

    Thanks

    Ben

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Give it a try and show us what you came up with. We'll gladly help you further.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Sep 2010
    Posts
    11
    Hi Wim

    I thought I'd just post where i'm upto with this, however i've seemed to hit a bit of a wall getting the last months data total out.

    The code that I have at the moment is

    SELECT
    ID.Account_Num,
    ID.invoice_date,
    SUM(CASE WHEN ID.invoice_date between '2010-07-01' and '2010-07-31' THEN ID.Invoice_Total ELSE 0 END) as [Total_Last_Month],
    SUM(CASE WHEN ID.invoice_date between '2010-08-01' and '2010-08-31' THEN ID.Invoice_Total ELSE 0 END) as [Total_This_Month]
    FROM Invoice_Data ID,
    (SELECT Account_Num, MAX(Invoice_Date) AS MAX_INV_Date
    FROM Invoice_Data WHERE Invoice_Date BETWEEN '2010-07-01' AND '2010-08-31' GROUP BY Account_Num) ID1
    WHERE ID.Account_Num=ID1.Account_Num AND ID.Invoice_Date=ID1.MAX_INV_Date
    AND Invoice_Date BETWEEN '2010-07-01' AND '2010-08-31' --and ID.Account_Num='BES109032'
    GROUP BY
    ID.Account_Num,ID.invoice_date,ID.invoice_total
    ORDER BY 1 ASC


    This returns the MAX combined values for both this month and last month.

    Please see results below.

    AccNum InvoiceDat Last This
    BEG0029 2010-08-06 0.00 28.46
    BEG0024 2010-07-07 6.10 0.00
    BEG0026 2010-08-06 0.00 279.60
    BEG0027 2010-08-06 0.00 468.44

    As you can see If there is a date in August it takes that date as the MAX date and does not see a July date.

    What I want is the Max date for July as well as the MAX date for August.

    I'm getting pretty frustrated now as i'm sure it can be done using something similar to what I have already.

    Any more help would be much appreciated.

    Thanks

    Ben

  9. #9
    Join Date
    Sep 2010
    Posts
    15
    Hi bendertez,

    Look like the requirement in your first and last message are different .... am I wrong?

    The value of InvoiceDat column can be other than AUG and JUL and 2010?
    Could it be: 1/12/2010, 8/8/2009, 1/7/2011 ....

    Not sure, but just curious, why you do not use subquery? It is much easier, I think.

  10. #10
    Join Date
    Sep 2010
    Posts
    11
    Hi Namman

    The date field is a true date format and not a string. That was my fault I just wanted to be clear about the months as being in the UK our date format is dd/mm/yyyy.

    You mention a subquery but how can I achieve this I've tried a few different ways but cannot get the desired results.

    The closest i'm getting is the query I posted below, but this is only returning the latest Invoice and not the previous months.

    Can you think of any other way round this?

    Thanks

    Ben

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    SELECT
        ID.Account_Num,
        SUM(CASE WHEN ID.invoice_date between '2010-07-01' and '2010-07-31' THEN ID.Invoice_Total ELSE 0 END) as [Total_Last_Month],
        IDPrev.MAX_INV_Date as MAX_INV_Date_Last_Month,
        SUM(CASE WHEN ID.invoice_date between '2010-08-01' and '2010-08-31' THEN ID.Invoice_Total ELSE 0 END) as [Total_This_Month],
        IDCur.MAX_INV_Date as MAX_INV_Date_This_Month
    FROM Invoice_Data ID
        LEFT OUTER JOIN (SELECT Account_Num, MAX(Invoice_Date) AS MAX_INV_Date
                    FROM Invoice_Data 
                    WHERE Invoice_Date BETWEEN '2010-08-01' AND '2010-08-31' 
                    GROUP BY Account_Num) AS IDCur ON
            ID.Account_Num = IDCur.Account_Num
        LEFT OUTER JOIN (SELECT Account_Num, MAX(Invoice_Date) AS MAX_INV_Date
                        FROM Invoice_Data 
                        WHERE Invoice_Date BETWEEN '2010-07-01' AND '2010-07-31' 
                        GROUP BY Account_Num) AS IDPrev ON
            ID.Account_Num = IDPrev.Account_Num
    WHERE ID.Invoice_Date BETWEEN '2010-07-01' AND '2010-08-31' --and ID.Account_Num='BES109032'
    GROUP BY ID.Account_Num
    ORDER BY ID.Account_Num ASC
    I used LEFT OUTER JOINs to join with the max_date "tables", because you can never be sure all accountNumbers will have invoices in both this month and in last month. Though they will have at least one invoice in both months combined.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Sep 2010
    Posts
    11
    Thanks again Wim

    This will certainly work for me now, I can see why you have used Left Outer Joins returning the account even if it has a bill in only one of the months.

    There is one more adaptation that I'm sure I will need to implement. Would it be possible to adapt this so it only returned the latest invoice value within the month (if there are more than one) and not a combined total?

    It's just that sometimes accounts can be re-issued within the month because the first invoice was incorrect.

    I've tried removing the SUM clauses within the query but now seem to have the accounts duplicating.

    Thanks Wim for your support on this it has been very helpful.

    Ben

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by bendertez View Post
    There is one more adaptation that I'm sure I will need to implement. Would it be possible to adapt this so it only returned the latest invoice value within the month (if there are more than one) and not a combined total?

    It's just that sometimes accounts can be re-issued within the month because the first invoice was incorrect.
    I don't understand what you want. Can you work out an example? It often makes things much clearer for an outsider.
    What code have you come up with so far to get the desired result?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  14. #14
    Join Date
    Sep 2010
    Posts
    11
    Quote Originally Posted by Wim View Post
    I don't understand what you want. Can you work out an example? It often makes things much clearer for an outsider.
    What code have you come up with so far to get the desired result?
    Hi Wim

    Sorry for not explainingn things more clearly.

    There are occasions where there are more than one invoice for an account in the one month. This could be where an account has been invoiced for example on the 2010-08-04 for $300.00 but then has a revised invoice issued on the 2010-08-09 for $200.00 as we have incorrectly invoiced the account.

    At the moment the Invoice total in your query will return $500.00 against the account as it combines the two invoices together with the SUM command.

    What I want returning is only the latest invoice (in the example above the one for $200.00).

    The below code returns what I want for the current month but I thought I could just simply do the same sort of PRVMNTH derived table for the previous month similar to your query, but it falls over.

    SELECT
    ID.Invoice_Num, Account_Num,ThisMonth
    FROM GMS_Invoice_Data ID
    RIGHT OUTER JOIN
    (Select Invoice_Num As Invoice_Num, Invoice_Total as ThisMonth FROM GMS_Invoice_Data where invoice_num in
    (select max(convert(float,invoice_num)) as Invoice_Num
    From GMS_Invoice_Data where invoice_date between '2010-08-01' AND '2010-08-31'
    GROUP BY Account_Num)) as CURMNTH

    on ID.Invoice_Num=CURMNTH.invoice_Num
    GROUP BY ID.Invoice_Num,Account_num, ThisMonth
    order by 2 asc


    The additional code I was planning on adding was:

    SELECT
    ID.Invoice_Num, Account_Num,CURMNTH.ThisMonth,PRVMNTH.LastMonth
    FROM GMS_Invoice_Data ID
    RIGHT OUTER JOIN
    (Select Invoice_Num As Invoice_Num, Invoice_Total as ThisMonth FROM GMS_Invoice_Data where invoice_num in
    (select max(convert(float,invoice_num)) as Invoice_Num
    From GMS_Invoice_Data where invoice_date between '2010-08-01' AND '2010-08-31'
    GROUP BY Account_Num)) as CURMNTH
    on ID.Invoice_Num=CURMNTH.invoice_Num

    RIGHT OUTER JOIN
    (Select Invoice_Num As Invoice_Num, Invoice_Total as LastMonth FROM GMS_Invoice_Data where invoice_num in
    (select max(convert(float,invoice_num)) as Invoice_Num
    From GMS_Invoice_Data where invoice_date between '2010-07-01' AND '2010-07-31'
    GROUP BY Account_Num)) as PRVMNTH
    on ID.Invoice_Num=PRVMNTH.invoice_Num


    The result of the combined code is that now I only get the previous months totals and NULL values for all the other fields.

    Can you spot what is wrong with the code or sufggest another way around the problem.

    Thanks again

    Ben

  15. #15
    Join Date
    Sep 2010
    Posts
    15
    Hi bendertez,

    I thought you had a solution ....

    I am still not sure I understand your problem correctly ... Just try this to see if it works for you.


    declare @t table(a varchar(20), b datetime, c int)
    insert into @t values('BEF345453', '01AUG2010', 20.00)
    insert into @t values('BEF345453', '05JUL2010', 30.00)
    insert into @t values('BEF345453', '01JUN2010', 20.00)
    insert into @t values('BEF345400', '05JUL2010', 60.00)
    insert into @t values('BEF345400', '01AUG2010', 20.00)
    insert into @t values('BEF345400', '05JUN2010', 10.00)
    insert into @t values('BEF367402', '05JUL2010', 40.00)
    insert into @t values('BEF367402', '01AUG2010', 10.00)
    insert into @t values('BEF367402', '05JUN2010', 30.00)
    select * from @t order by a, b desc

    ;with temp as(select *, rn = row_number() over(partition by a order by b desc) from @t)
    select a, convert(varchar(10),b,101) as b, c, (select top 1 c from temp where a = t.a and b < t.b order by b desc) as pre from temp t
    where t.rn = 1 order by a, b desc


    -- result
    a b c pre
    BEF345400 08/01/2010 20 60
    BEF345453 08/01/2010 20 30
    BEF367402 08/01/2010 10 40

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •