Post reply

Fiscal years

  • April 15, 2015 at 3:47 am

    #302948

    Hey,

    Our fiscal year is May to April. I have this query:

    selectmax(month(oi.ITEMDATE)) [Month], datename(month, oi.ITEMDATE) [Month Name],

    cast(sum(case when year(oi.ITEMDATE) = year(getdate()) then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) as decimal(11, 2)) [Total Fees 1],

    cast(sum(case when year(oi.ITEMDATE) = year(dateadd(year, -1, getdate())) then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) as decimal(11, 2)) [Total Fees 2],

    cast(sum(case when year(oi.ITEMDATE) = year(getdate()) then (case when wh.WIPCODE in ('AGENTS', 'CHGINV', 'OFFEES', 'TRANSL', 'MISC', 'SEARCH', 'TRAVLR') then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) end) as decimal(11, 2)) [Disbursements 1],

    cast(sum(case when year(oi.ITEMDATE) = year(dateadd(year, -1, getdate())) then (case when wh.WIPCODE in ('AGENTS', 'CHGINV', 'OFFEES', 'TRANSL', 'MISC', 'SEARCH', 'TRAVLR') then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) end) as decimal(11, 2)) [Disbursements 2],

    cast(sum(case when year(oi.ITEMDATE) = year(getdate()) then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) as decimal(11, 2)) -

    cast(sum(case when year(oi.ITEMDATE) = year(getdate()) then (case when wh.WIPCODE in ('AGENTS', 'CHGINV', 'OFFEES', 'TRANSL', 'MISC', 'SEARCH', 'TRAVLR') then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) end) as decimal(11, 2)) [Profit 1],

    cast(sum(case when year(oi.ITEMDATE) = year(dateadd(year, -1, getdate())) then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) as decimal(11, 2)) -

    cast(sum(case when year(oi.ITEMDATE) = year(dateadd(year, -1, getdate())) then (case when wh.WIPCODE in ('AGENTS', 'CHGINV', 'OFFEES', 'TRANSL', 'MISC', 'SEARCH', 'TRAVLR') then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) end) as decimal(11, 2)) [Profit 2]

    fromWORKHISTORY wh, NAME n, OPENITEM oi, DEBTORHISTORY dh

    wherewh.STATUS <> 0 and (wh.REFENTITYNO = -5167 or (wh.REFENTITYNO is null and wh.ENTITYNO = -5167)) and wh.MOVEMENTCLASS = 2 and

    n.NAMENO = wh.EMPLOYEENO and dh.REFENTITYNO = wh.REFENTITYNO and dh.REFTRANSNO = wh.REFTRANSNO and dh.MOVEMENTCLASS in (1, 2) and

    dh.ITEMENTITYNO = oi.ITEMENTITYNO and dh.ITEMTRANSNO = oi.ITEMTRANSNO and dh.ACCTENTITYNO = oi.ACCTENTITYNO and

    dh.ACCTDEBTORNO = oi.ACCTDEBTORNO and oi.ITEMTYPE not in (513, 514)

    group bymonth(oi.ITEMDATE), datename(month, oi.ITEMDATE)

    order bymax(oi.ITEMDATE)

    That results in this:

    The problem I have is that for May to Dec, as I am generating the results in Apr, they should be last years' year (so 2014 - current year minus 1) with Jan to Apr the current year (2015). In a years' time, May to Dec should be 2015 and Jan to Apr the current year (2016).

    If I was generating the results between May to Dec, May to Dec should be the current year (2015) and Jan to Apr should be the next year (current year plus 1). The '1' columns are this fiscal year, and the '2' columns are previous fiscal year.

    I can't seem to work this one out!

    Thanks

  • April 15, 2015 at 3:49 am

    #1791028

    PS: I know this uses the 'no join predicate', but the query is based on the 3rd party apps workings - I ran a profile trace to capture what one of the front-end reports was doing to adapt to our needs (the front-end only allows reports for single items, in this case months).

Viewing 2 posts - 1 through 1 (of 1 total)

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