0

I'm sure there's a simple solution here, and I've attempted to use a CASE statement with a concatenation, but it's not quite giving me what I need. Very simply, I am trying to write a query that will select the MIN value that is >= 3, and going forward, only select values that are >= 3 + 12 and so on (3, 15, 27, etc.) Here's my query:

SELECT min(appearance_count) AC
        ,customer
    FROM #final
    WHERE gm IS NOT NULL
    GROUP BY customer

Sample result set:

enter image description here

I have a test row that I can use to validate that my query is working as expected, but as stated, I can't pull it in addition to the results above.

enter image description here

Clarification: The AC column will have numbers that start with 1 and continue to grow with each month (+1 AC for each month that passes).

Sample Data: 

AC       customer
4        BELL TEXTRON INC
...      BELL TEXTRON INC
16       BELL TEXTRON INC

I would need to pull both the 4 and the 16 and none of the values in between, for this example.

jw11432
  • 545
  • 2
  • 20
  • Dont have time for a full answer; A lookup table of Valid AC values you could join on would make life fairly simple and save recalculating the values. If that isnt practical you could use a recursive CTE to generate a list of them to join against https://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers – u07ch Nov 22 '19 at 16:53
  • This is a great idea. However, I think it wouldn't be feasible since my possible numbers could be 4, 5, or 6. I should have further emphasized that 3 is merely the minimum. This would then of course alter my possible numbers from 15 to 16,17, or 18 respectively. – jw11432 Nov 22 '19 at 17:12

1 Answers1

1

Using a modulus check should do it.

But to get the multiples of 12 of the minimum, it could need a sub-query with the window function of MIN. 

SELECT appearance_count AS AC, customer
FROM
(
    SELECT appearance_count, customer,
     MIN(appearance_count) OVER (PARTITION BY customer) AS MinCustAppCnt
    FROM #final
    WHERE gm IS NOT NULL
    GROUP BY appearance_count, customer
) q
WHERE appearance_count%12 = MinCustAppCnt
LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • I like the direction you're going with this. Now, this doesn't fetch my test row with 15, but I figured there had to be some way to calculate this. I should also emphasize that 3 is simply the minimum that the AC can be; it could potentially be 4 or 5 or 6 (max = 6), making the next value 16,17,18 respectively. – jw11432 Nov 22 '19 at 17:08
  • Oh, that's a bit different requirement than expected. Could you please add a bit of fake sample data to your question as text? And the expected results from it? Helps to avoid misunderstandings :) Something with not only minimum 3 – LukStorms Nov 22 '19 at 17:10
  • I hope my addendum is sufficient. – jw11432 Nov 22 '19 at 17:24
  • @jw11432 Haven't tested my new solution on data yet, but it'll probably be something like that. – LukStorms Nov 22 '19 at 17:33
  • I had to add the FROM, but it works. I could hug you. Thank you so much, I'm gonna look over this and see why it works. – jw11432 Nov 22 '19 at 17:35
  • The sub-query first groups, and the MIN OVER works on record level to calculate the minimum per partition. The modulus check is done outside because window functions can not be used in the WHERE or HAVING clauses. – LukStorms Nov 22 '19 at 17:41
  • It's brilliant, thank you so much. Window functions are ones I'm not very versed in, but after a quick Google, I see their potential. Thanks again, this was huge! – jw11432 Nov 22 '19 at 17:46