How to create a query to get invoices between dates (help needed)-CodePudding

I need to create a query in SQL to get some invoices depending on the current date. I'm an intern in the company, so I only need to create the logic, not necessarily the query. Can anyone help me to translate this conditions into SQL (Snowflake)?

If Current Date (today's date) is between day 14 and 27 of the month >>> Get invoices from day 14 of the current month
If Current Date (today's date) is between day 28 and 31 of the month >>> Get invoices from day 28 of the current month
If Current Date (today's date) is between day 1 and 13 of the month >>> Get invoices from day 28 of the last month

Thanks in advance!

CodePudding user response：

I think your simplest, clearest option is a case statement. You can put this into your select and work with it from there, or put it directly into a where clause. whatever's more appropriate for your query.

WHERE invoice_date::DATE = (CASE WHEN DAY(current_date) between 14 and 27 then DATE_FROM_PARTS(YEAR(current_date), MONTH(current_date), 14)
                                 WHEN DAY(current_date) between 28 and 31 then DATE_FROM_PARTS(YEAR(current_date), MONTH(current_date), 28)
                                 WHEN DAY(current_date) between 1 and 13  then DATEADD('month', -1, DATE_FROM_PARTS(YEAR(current_date), MONTH(current_date), 28))
                                 END)

CodePudding user response：

select greatest( 
          trunc( current_date(),'MONTHS' )   interval '-1 month, 13 days', 
          trunc( current_date(),'MONTHS' )   interval '-1 month, 27 days',
          trunc( current_date(),'MONTHS' )   interval '13 days') your_result
from dual where your_result  < current_date()

So invoicing is run on the 14th and 28th day of each month.

So when ever you run the invoicing you want to pick up any invoices for the most recent invoice run.

All we do here is work out the 3 options and do a greatest between them.

I think it reads cleaner and should run faster than the case statement approach (which is perfectly good).