Partition Orders table by arbitrary start and end day-of-month

user_802482 0
Viewed 78

Given the following fairly standard Orders table:

CREATE TABLE [dbo].[orders](
    [bill_product_id] [int] IDENTITY(1,1) NOT NULL,
    [Bill_date] [datetime] NULL,
    [TenantId] [int] NULL,
    [Product_type] [int] NULL,
    [Quantity] [int] NULL,
    [Customer_cost] [numeric](18, 10)
)

The following query gives me the start_date and end_date of each Month for which there are records in the table orders

SELECT distinct DATEADD(DAY,1,EOMONTH(bill_date,-1)) as start_date,
       EOMONTH(bill_date) as end_date
    FROM orders

Example resultset:

start_date end_date

2020-07-01 2020-07-31

2020-08-01 2020-08-31

Now we need to change the 'Billing Cycle' so instead of starting on the 1st of the month, and ending on the last day of the month, we need the start of the billing cycle to be the 26th of the month, with the end being the following 25th day of the month (don't ask).

I'm sure I can determine the end_date given the start date, but I'm unsure the best way to partition the data into 26th-to-25th of the month segments. Please suggest an effecient way to do this.

sql-server sql
4 Answers

Maybe you can use DATEFROMPARTS

DECLARE @bill_date DATE = '2020-08-12'

SELECT DATEFROMPARTS(YEAR(@bill_date), MONTH(@bill_date), 26)
      ,DATEFROMPARTS(YEAR(@bill_date), MONTH(@bill_date) + 1, 25)

But your way is valid, too and can be enhanced.

As you pointed, it should be:

SELECT DATEFROMPARTS(YEAR(@bill_date), MONTH(@bill_date) - (CASE WHEN DAY(@bill_date) >= 26 then 0 else 1 end) , 26);

in order to ensure dates smaller than 26 remain in the current interval, not the next one like in my original solution.

user_10803540

Thanks to @gotqn, I can use DATEFROMPARTS as follows

 SELECT DATEFROMPARTS(YEAR(@bill_date),  
         MONTH(@bill_date) - (CASE WHEN DAY(@bill_date) >= 26 then 0 else 1 end), 
         26) as start_date
        ,DATEFROMPARTS(YEAR(@bill_date),  
         MONTH(@bill_date) + (CASE WHEN DAY(@bill_date) >= 26 then 1 else 0 end),
         25) as end_date
user_8024820

You can subtract 26 days and use datefromparts():

SELECT DISTINCT DATEFROMPARTS(YEAR(yyyymm_start), MONTH(yyyymm_start), 26) as start_date,
       DATEFROMPARTS(YEAR(yyyymm_end), MONTH(yyymm_end), 25) as end_date
FROM orders o CROSS APPLY
     (VALUES (DATEADD(DAY, -26, BILL_DATE),
              DATEADD(MONTH, 1, DATEADD(DAY, -26, BILL_DATE))
             )
     ) as v(yyyymm_start, yyyymm_end)
user_11440350

Just use dateadd to "move" you billing cycle.

I assume that then you query

  declare @billDate date = '2020-07-16'
 SELECT distinct DATEADD(DAY,1,EOMONTH(@billDate)) as start_date,
       EOMONTH(@billDate) as end_date, 'Original' as query
    --FROM orders


 SELECT distinct DATEADD(DAY,26,EOMONTH(@billDate,-1)) as start_date,
       dateadd(day,25,EOMONTH(@billDate) )as end_date, 'Modified' as query
    --FROM orders

will result in

start_date end_date   query
---------- ---------- -------- 
2020-07-01 2020-07-31 Original
         
start_date end_date   query
---------- ---------- -------- 
2020-07-26 2020-08-25 Modified
user_10127950
Related