0

I have an Orders table (text customer_id, int total_orders) which contains overview stats of total orders for each customer.

enter image description here

What I am trying to do is generate a query output that will represent a customer as a row for each third order they've made similar to this.

enter image description here

The combined_key is used as the primary/unique key which is simply the customer_key and count combined as I cannot use an auto-number.

I know how I can identify which records I need to be in the query but I don't know how I can split the records as desired.

select *
from stats
where order_count / 3

I've included a fiddle, please note it's in MySQL because T-SQL is not available.

  • What if the total_orders is not evenly divisible by 3? What version of SQL Server are you using? – Scott Hodgin Jan 18 at 14:03
  • The query will be run inside a SaaS application so they don't expose exactly what SQL Server they're running... I do know its variant of T-SQL (sorry I know this probably isnt much help). To keep things simple, let's say if the total_orders doesn't evenly divide by 3 we don't need them. If we could still get the previous rows (i.e. total count is 11 but we have rows for 3, 6 9) that would be great, but I feel this might over complicate it all. – Imran Jan 18 at 14:20
1

You can use db<>fiddle for sql-server:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c87b972491dda6462cc4f8b80ff2bd95

This is just a sketch. You need to generate something out of nothing (1 row -> 3 rows). For this you can use a recursive CTE:

with gen (account_id, n, order_count) as (
    select account_id, 1, order_count from stats
    union all
    select account_id, n+1, order_count
    from gen where n < order_count
)
select n-2 as x, n as y, account_id, n
from gen
where n % 3 = 0
order by account_id, n;

I used mod(%) to pick each 3:rd row.

x   y   account_id  n
1   3   1   3
4   6   1   6
7   9   1   9
1   3   2   3
  • Thanks Lennant, it looks like with command isn't supported. Would there be be an alternative to this? – Imran Jan 18 at 14:42
  • @Imran, what version of SQL-server are you running? CTE (with) where introduced in SQL Server 2005. You somehow must generate a table that you can use as a seed. I don't at all know SQL-server, and especially very old ones so I wont be of much help I'm afraid. – Lennart Jan 18 at 15:18
  • So I have done some digging and it appears that the backend uses SQL Server 2005 however some commands are not permitted, including with command. – Imran Jan 18 at 15:23
  • 2
    @Imran - can your SaaS application consume the results of a SQL Server stored procedure call? Perhaps you could encapsulate Lennart's solution into the stored procedure and invoke the stored procedure from your application. The stored procedure should be able to handle the with. – Scott Hodgin Jan 18 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.