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

user_17490985 0
Viewed 140

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!

snowflake-cloud-data-platform sql
2 Answers

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)
user_21178720 
select  
   current_date()+1 your_date
   ,dateadd(day,27-(sign(FLOOR(day(your_date)/14,0))*14) 
     ,dateadd(month,sign(FLOOR(day(your_date)/14,0))-1 
     ,date_trunc(month,your_date))) your_result

Revised solution.

Removing inequalities and reducing the problem down to multiples of 14 with month adjustments instead of multiple case statements (expensive to run at scale).

enter image description here

user_13524900
Related