SQL case expression to run report based on Fiscal Year

Question

I'm attempting to create some reports in my organization's new Help Desk system, which uses sql for its database and reporting systems. I've never interacted with sql before, so I'm having to make this up as I go. I've been able to cannibalize most of what I need from other, pre-made reports, but I'm running into a road block getting the report to group by Fiscal Year and not Calendar Year. The report I'm currently working on is meant to calculate the average time to closure on tickets. The original version grouped and ordered everything by the calendar year, and appeared to be working. However, when I try to retrofit the code to group/sort based on a July 1-June 30 Fiscal Year using the suggested method in this answer (as shown in my report code below), I'm getting the error, "The multi-part identifier "htblticket.date could not be bound."

I also tried some variants on this answer, with even less success. Does anyone have some suggestions on what I might be screwing up here, or some ideas on better ways to approach this? I'd appreciate any help/enlightenment you can provide!

Select Top 1000000 Case
    When DatePart(mm, htblticket.date) > 6 Then DatePart(yyyy, htblticket.date)
      + 1
    Else DatePart(yyyy, htlbticket.date)
  End As [Fiscal Year],
  Convert(Decimal(9,2),Avg(Cast(DateDiff(ss, htblticket.date,
  ClosedDate.CloseDate) As decimal) / 86400)) As AverageDays
From htblticket
  Inner Join (Select Top 1000000 htblticket.ticketid,
        Max(htblhistory.date) As CloseDate
      From htblticket
        Inner Join htblticketstates On htblticketstates.ticketstateid =
          htblticket.ticketstateid
        Inner Join htblhistory On htblhistory.ticketid = htblticket.ticketid
        Inner Join htblticketstates htblticketstates1 On
          htblhistory.ticketstateid = htblticketstates1.ticketstateid
        Inner Join htblhistorytypes On htblhistorytypes.typeid =
          htblhistory.typeid
      Where htblticketstates.statename = 'Closed' And
        htblticketstates1.statename = 'Closed' And
        htblhistorytypes.name In ('Status changed',
        'Note added and state changed', 'Internal note added and state changed')
      Group By htblticket.ticketid) As ClosedDate On ClosedDate.ticketid =
    htblticket.ticketid
Group By Case
    When DatePart(mm, htblticket.date) > 6 Then DatePart(yyyy, htblticket.date)
      + 1
    Else DatePart(yyyy, htlbticket.date)
  End
Order By [Fiscal Year] Desc

Maybe try a [Function](https://stackoverflow.com/questions/3897124/grouping-fiscal-year-using-sql-server)? — urdearboy, Aug 19 '19 at 22:39
There are some subtle differences between versions of SQL, such as SQL Server/T-SQL versus MySQL or PostgreSQL. You should tag your question with the variant of SQL you are using so you get more accurate answers. — Jamie F, Aug 20 '19 at 00:47
I think you've got a typo in the code you posted above: the `else` condition of your case (in both the group by and the select) inverts two letters in `htblticket` or `'htlbticket' — Jamie F, Aug 20 '19 at 00:54
@JamieF... You've got to be kidding me. You're exactly right. I won't know for sure until I can get more data into the help desk, but initial tests appear like the report might be working now. I had Lasik last week, and am still having issues seeing my screens... and apparently couldn't tell the difference between "lb" and "bl". Lesson here is don't code while blind. Thank you for helping me find the source of my stupid mistake! I'll try to have a better question next time! — tlonde, Aug 20 '19 at 16:25

score 0 · Answer 1 · answered Aug 20 '19 at 00:34

Your question boils down to how to group by the (Australian) financial year July-June.

One simple approach would be to create a column “financial_year_start” which is an expression that subtracts 6 months from each date and extracts the year of the result, then group by that.

SQL case expression to run report based on Fiscal Year

1 Answers1