CODEWITHSUNDEEP
sqlsql-servert-sql
qotsa42
qotsa42

Reputation: 317

SQL: How to select ONLY orders that occur every three weeks?

I have an orders table where orders are placed every Thursday. Accordingly, each order has fiscal_week_number between 1 and 52 associated with it.

Each order is grouped in batches, where one order can contain multiple transactions, each billed on a separate week. For example, order_number 0001 billed on fiscal_week_numbers 4, 5, 6, 7 and had transaction_numbers 100000001, 100000002, 100000003, 100000004. Assume the transaction_numbers are non-sequential and random.

Some of the orders bill every week, some bill every two weeks, some bill every 3 weeks, etc. How can I query the table to only show orders that bill every X weeks?

For example, I would like to find out how many orders (that is, distinct order_ids) bill every 6 weeks but I can't figure out the SQL.

For example, say I wanted to grab only orders that occur every three weeks from the following data set. I would expect to receive only order 00003. It doesn't really matter if all the order data is there or just the id.

|00003   |319229           |20            |
|00003   |319230           |23            |
|00003   |238678           |26            |
|00003   |319231           |29            |
|00003   |190659           |32            |

+--------+-----------------+--------------+
|order_id|transactionnumber|fiscal_week_no|
+--------+-----------------+--------------+
|00001   |278100           |1             |
|00001   |278101           |2             |
|00001   |278102           |3             |
|00001   |278103           |4             |
|00001   |278104           |5             |
|00002   |319224           |10            |
|00002   |319225           |12            |
|00002   |319226           |14            |
|00002   |319227           |16            |
|00002   |319228           |18            |
|00003   |319229           |20            |
|00003   |319230           |23            |
|00003   |238678           |26            |
|00003   |319231           |29            |
|00003   |190659           |32            |
|00004   |319232           |26            |
|00004   |190660           |30            |
|00004   |190661           |34            |
|00004   |190662           |38            |
|00004   |319233           |42            |
|00005   |190663           |40            |
|00005   |319234           |45            |
|00005   |190664           |50            |
|00005   |190665           |3             |
|00005   |190666           |8             |
|00006   |319235           |10            |
|00006   |190667           |16            |
|00006   |190668           |22            |
|00006   |319236           |28            |
|00006   |190669           |34            |
|00007   |319237           |20            |
|00007   |190670           |28            |
|00007   |190671           |36            |
|00007   |319238           |44            |
|00007   |190672           |52            |
|00007   |190673           |8             |
+--------+-----------------+--------------+

Upvotes: 0

Views: 179

Answers (2)

SteveC
SteveC

Reputation: 6015

I think this code could be useful too. This will return the order_id's for whichever number of transactions is set in the @bill_freq variable. I used made up data.

declare @bill_freq          int=2;

;with rn_cte(order_id, transactionnumber, fiscal_week_no, wk_rn) as (
    select *, row_number() over (partition by order_id order by order_id, fiscal_week_no) wk_rn
    from (values ('00001', 278100, 1),
                 ('00001', 278101, 2),
                 ('00002', 278102, 3),
                 ('00002', 278103, 4),
                 ('00003', 278104, 5),
                 ('00004', 278105, 7),
                 ('00004', 278106, 9)) v(order_id, transactionnumber, fiscal_week_no))
select order_id
from rn_cte
group by order_id
having max(wk_rn)=@bill_freq;

Results

order_id
00001
00002
00004

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270653

Based on your description, orders bill every three weeks if the fiscal_week_no column is always the same, modulo 3. So, one method is:

select order_id
from t
group by order_id
having min(fiscal_week_no % 3) = max(fiscal_week_no % 3);

Note that this does not guarantee that they are billed every three weeks (we could add an additional check for that. But these would be the orders on the same billing cycle.

Upvotes: 1

Related Questions