1

I am brand new to Oracle. I have figured out most of what I need but one field is driving me absolutely crazy. Seems like it should be simple but I think my brain is fried and I just can't get my head around it. I am trying to produce a Sales report. I am doing all kinds of crazy things based on the Invoice Date. The last thing I need to do is to be able to create a Week Number so I can report on weekly sales year vs year. For purposes of this report my fiscal year starts exactly on December 1 (regardless of day of week it falls on) every year. For example, Dec 1-7 will be week 1, etc. I can get the week number using various functions but all of them are based on either calendar year or ISO weeks. How can I easily generate a field that will give me the number of the week since December 1? Thanks so much for your help.

Martin Schapendonk
  • 12,893
  • 3
  • 19
  • 24
Jeff
  • 35
  • 3

1 Answers1

2

Forget about the default week number formats as that won't work for this specific requirement. I'd probably subtract the previous 1 December from invoice date and divide that by 7. Round down, add 1 and you should be fine.

select floor(
  (
  trunc(invoiceDate) - 
  case 
  -- if December is current month, than use 1st of this month
  when to_char(invoiceDate, 'MM') = '12' then trunc(invoiceDate, 'MM')
  -- else, use 1st December of previous year
  else add_months(trunc(invoiceDate, 'YYYY'), -1)
  end
  ) / 7
) + 1
from dual;
Martin Schapendonk
  • 12,893
  • 3
  • 19
  • 24
  • Ok, please pardon my ignorance. I am not only new to oracle but infomaker too. New job, new tools, new challenges - we've all been there. Here is my query - I want the FW field to be the Fiscal Week result. How do I incorporate your suggestion? Scratch that - query too long. Here is the line I am currently using to_char("AR_TRX_HEADER"."SHIPDATETIME",'ww') AS FW, How do I incorporate your suggestion. Thanks for the help!!! – Jeff May 19 '16 at 12:46
  • I'm not familiar with Infomaker, but my guess is to replace "from dual;" with "as fw" and replace invoiceDate with ar_trx_header.shipdatetime – Martin Schapendonk May 19 '16 at 12:54
  • I had already replaced the invoiceDate but since I didn't understand what the from dual was doing, I had left it there. I replaced "from Dual;" with "AS FW," and I removed the leading select since it was already part of a query and it worked PERFECTLY! Thank you so much! – Jeff May 19 '16 at 13:05
  • Dual is a dummy table in Oracle with just one row. You can use it for all kinds of things, for example to try out a query :-) Try it sometime in a SQL client/IDE like SQL Developer. And please be so kind to accept my answer with the checkmark next to it. – Martin Schapendonk May 19 '16 at 13:09
  • 1
    Good info. I'll gladly 'check' your answer. Thanks so much for the help! – Jeff May 19 '16 at 13:20