Need guidance with SQL Query to get contacts with specific days in their monthly billing cycle

Transact-SQL
1

I have an order_table in which customer order information is saved.

  • With each month's billing cycle for a customer, a new order_key (row) is added in the order_table
  • billing_cycle_date_time field records the date time of their monthly billing cycle. In this case, I need to calculate date based on their 1st billing_cycle_date_time value only (in order words, billing_cycle_date_time value from the very 1st row only)

Trying to find out the following in 3 separate queries:

  • Get customers who are in 2nd month billing cycle on day 2 (as of
    today)
  • Get customers who are in 3rd month billing cycle on day 2 (as
    of today)
  • Get customers who are in 2nd month billing cycle and 7 days
    before end of second month (as of today)

My Sample Data

+-----------+-------------+-------------------------+------------+
| order_key | customer_id | billing_cycle_date_time | order_type |
+-----------+-------------+-------------------------+------------+
|      1234 |          11 | 2020-07-01 5:36:11      | online     |
|      1289 |          11 | 2020-07-28 9:20:10      | online     |
|      1100 |          14 | 2020-05-26 8:11:08      | online     |
|      1150 |          14 | 2020-06-24 5:05:02      | online     |
|      1267 |          14 | 2020-07-24 7:01:03      | online     |
|      1567 |          15 | 2020-09-01 5:36:11      | online     |
|      1678 |          15 | 2020-10-28 9:20:10      | online     |
|      1690 |          15 | 2020-11-28 5:17:04      | online     |
+-----------+-------------+-------------------------+------------+

Desired output when Aug 3, 2020 is today's date [i.e. Customers who are in 2nd month billing cycle on day 2 (as of today)]

+-------------+-----------------------------+------------------------------------+
| customer_id | MIN_billing_cycle_date_time | Calculated_billing_cycle_date_time |
+-------------+-----------------------------+------------------------------------+
|          11 | 2020-07-01 5:36:11          | 2020-08-03 5:36:11                 |
+-------------+-----------------------------+------------------------------------+

Desired output when July 28, 2020 is today's date [customers who are in 3nd month billing cycle on day 2 (as of today)]

+-------------+-----------------------------+------------------------------------+
| customer_id | MIN_billing_cycle_date_time | Calculated_billing_cycle_date_time |
+-------------+-----------------------------+------------------------------------+
|          14 | 2020-05-26 8:11:08          | 2020-07-28 8:11:08                 |
+-------------+-----------------------------+------------------------------------+

Desired output when Oct 25, 2020 is today's date [customers who are in 2nd month billing cycle and 7 days before end of second month (as of today)]

+-------------+-----------------------------+------------------------------------+
| customer_id | MIN_billing_cycle_date_time | Calculated_billing_cycle_date_time |
+-------------+-----------------------------+------------------------------------+
|          15 | 2020-09-01 5:36:11          | 2020-10-25 5:36:11                 |
+-------------+-----------------------------+------------------------------------+

Existing error: I am able to write 3 sql queries but condition where I am performing calculation with billing_cycle_date_time and comparing with today's date is throwing error

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Here are my 3 queries:

query 1

        -- query to get customers who are in 2nd month billing cycle on day 2 (as of today)

SELECT
customer_id,
billing_cycle_date_time AS MIN_billing_cycle_date_time,
DATEADD(dd,2,DATEADD(month, 1, MIN(billing_cycle_date_time)))  as Calculated_billing_cycle_date_time,
count(order_key) as count
from
order_table
where 
order_type ='online'

--this criteria below is giving ERROR
and CAST(DATEADD(dd,2,DATEADD(month, 1, MIN(billing_cycle_date_time))) AS DATE) = CAST(GETDATE() AS DATE)
group by order_key
--this criteria below is important since it defines number of months in a billing cycle (each row means one month)
Having COUNT(order_key) = 2

query 2

 -- query to get customers who are in 3nd month billing cycle on day 2 (as of today)

SELECT
customer_id,
billing_cycle_date_time AS MIN_billing_cycle_date_time,
DATEADD(dd,2,DATEADD(month, 2, MIN(billing_cycle_date_time)))   as Calculated_billing_cycle_date_time,
count(order_key) as count
from
order_table
where 
order_type ='online'

--this criteria below is giving ERROR
and CAST(DATEADD(dd,2,DATEADD(month, 1, MIN(billing_cycle_date_time))) AS DATE) = CAST(GETDATE() AS DATE)
group by order_key
--this criteria below is important since it defines number of months in a billing cycle (each row means one month)
Having COUNT(order_key) = 3

query 3

-- query to get customers who are in 2nd month billing cycle and 7 days before end of second month (as of today)
    
    SELECT
    customer_id,
    billing_cycle_date_time AS MIN_billing_cycle_date_time,
    DATEADD(dd,-7,DATEADD(month, 2, MIN(billing_cycle_date_time)))   as Calculated_billing_cycle_date_time,
    count(order_key) as count
    from
    order_table
    where 
    order_type ='online'
    
    --this criteria below is giving ERROR
    and CAST(DATEADD(dd,2,DATEADD(month, 1, MIN(billing_cycle_date_time))) AS DATE) = CAST(GETDATE() AS DATE)
    group by order_key
	--this criteria below is important since it defines number of months in a billing cycle (each row means one month)
    Having COUNT(order_key) = 2

Can anyone guide me, please?

2

Lack of consumable test data makes it difficult to tell what you are doing but a quick look suggests that:

CAST(DATEADD(dd,2,DATEADD(month, 1, MIN(billing_cycle_date_time))) AS DATE) = CAST(GETDATE() AS DATE)

should be in the HAVING clause as it contains an aggregate function.

3

My apologies, I have added consumable data above in my question. Would you great to get some guidance on how my final query should look like?

4

Consumable test data!

CREATE TABLE #OT
(
	order_key int NOT NULL
		PRIMARY KEY --??
	,customer_id int NOT NULL
	, billing_cycle_date_time datetime NOT NULL
	,order_type varchar(20) NOT NULL
);
INSERT INTO #OT
VALUES (1234, 11, '20200701 05:36:11', 'online')
	,(1289, 11, '20200728 09:20:10', 'online')
	,(1100, 14, '20200526 08:11:08', 'online')
	,(1150, 14, '20200624 05:05:02', 'online')
	,(1267, 14, '20200724 07:01:03', 'online')
	,(1567, 15, '20200901 05:36:11', 'online')
	,(1678, 15, '20201028 09:20:10', 'online')
	,(1690, 15, '20201128 05:17:04', 'online');

I am still not much the wiser:

  1. What is the PK? It looks like order_key in the test data.
  2. Is MIN(billing_cycle_date_time) by order_key, unlikely if the PK, or customer_id or something else?
    etc

At a guess try something like:

DECLARE @Today datetime = '20200803'
	,@CycleMonth int = 2
	,@CycleDay int = 2;

--DECLARE @Today datetime = '20200728'
--	,@CycleMonth int = 3
--	,@CycleDay int = 2;

--DECLARE @Today datetime = '20201025'
--	,@CycleMonth int = 2
--	,@CycleDay int = -7;

WITH MinOTs
AS
(
	SELECT customer_id
		,MIN(billing_cycle_date_time) AS MIN_billing_cycle_date_time
	FROM #OT
	WHERE order_type ='online'
	GROUP BY customer_id
)
SELECT O.customer_id, O.MIN_billing_cycle_date_time
	,X.Calculated_billing_cycle_date_time
FROM MinOTs O
	CROSS APPLY
	(
		SELECT V.Calculated_billing_cycle_date_time
		FROM
		(
			VALUES (DATEADD(day, @CycleDay, DATEADD(month,
								CASE
									WHEN @CycleDay < 0
									THEN @CycleMonth
									ELSE @CycleMonth - 1
								END
								,O.MIN_billing_cycle_date_time
								)
					)
				) 
		) V (Calculated_billing_cycle_date_time)
	) X
WHERE X.Calculated_billing_cycle_date_time >= @Today
	AND X.Calculated_billing_cycle_date_time < DATEADD(day, 1, @Today);
5

hi

i tried to do this !!! .. looks like the same thing as Ifor .. except for minor changes ..

see if mine or Ifor's helps you .. if not lets discuss ..

please click arrow to the left for drop create data script 
create table sample_data ( order_key int ,customer_id int , billing_cycle_date_time datetime , order_type  varchar(100))
   insert into sample_data select 1234    , 11,'2020-07-01 5:36:11','online'   
     insert into sample_data select 1289    , 11,'2020-07-28 9:20:10','online'   
     insert into sample_data select 1100    , 14,'2020-05-26 8:11:08','online'   
     insert into sample_data select 1150    , 14,'2020-06-24 5:05:02','online'   
     insert into sample_data select 1267    , 14,'2020-07-24 7:01:03','online'   
     insert into sample_data select 1567    , 15,'2020-09-01 5:36:11','online'   
     insert into sample_data select 1678    , 15,'2020-10-28 9:20:10','online'   
     insert into sample_data select 1690    , 15,'2020-11-28 5:17:04','online'

; with cte as 
(
select 
    customer_id 
  , min(billing_cycle_date_time) as min_time 
  , count(order_key) as cnt 
from 
   sample_data 
group by 
   customer_id 
having 
    count(order_key) = 2
)
select 
      *
	, DATEADD(dd,2,DATEADD(month, 1, min_time))  as Calculated_billing_cycle_date_time 
from 
   cte

image

6

Thank you both @Ifor and @harishgg1 for your guidance. I am going to try the query provided by you shortly.

Just need your thoughts / feedback on my original approach of calculating dates like this:

CAST(DATEADD(dd,2,DATEADD(month, 1, MIN(billing_cycle_date_time))) AS DATE) = CAST(GETDATE() AS DATE)

The exact month days that I am trying to get, is this the right approach? will this give accurate results always?

And If I modify my query like this - is it not accurate?

SELECT
customer_id,
billing_cycle_date_time AS MIN_billing_cycle_date_time,
DATEADD(dd,2,DATEADD(month, 1, MIN(billing_cycle_date_time)))  as Calculated_billing_cycle_date_time,
count(order_key) as count
from
order_table
where 
order_type ='online'



group by order_key
--this criteria below is important since it defines number of months in a billing cycle (each row means one month)
Having COUNT(order_key) = 2  
and CAST(DATEADD(dd,2,DATEADD(month, 1, MIN(billing_cycle_date_time))) AS DATE) = CAST(GETDATE() AS DATE)
7

Looks good to me .. I am not an expert though

Lets see the experts FeedBack ..

8

In general you do not want to use functions, including CAST, on columns as it makes them unsargable.
(ie Indexes on these columns will not be used.)

The general pattern you should use for dates is:

ColumnDate >= '20200812'
AND ColumnDate < '20200813'

This will get any datetime on 2020-08-12.

1 Like
9

Ah good to know @Ifor
One quick question...I just found out that my sample data may include a new field... "coupon_id"

This field will only be populated with a value on the first billing cycle row record (considering one customer will have multiple rows as per the sample data). Subsequent billing cycles will have "coupon_id" blank.

Where can I include the criteria? In terms of my original query?
and coupon_id = '123'