• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

How can I count the times something has occured from odd and even number in SQL?

I have a very strange problem that I am trying to solve and am stuck.
I currently need to work out how many times an event would have occured depending on the total times it would have been possible. SOund confusing so I will explain.

There is a set interval for an event to happen, so for example every 10 days the colour of an item needs to alternate, so Red,Green,Red,Green,etc.

Now the total time to check this over is say 50 days, so the cycle has happened 5 times.

This means that Red has been shown 3 times and Green has been shown 2 times.

How can I work this out programtically within SQL? So for example the system querys the data and works out that the time of 74 days it would then work out how many times Green has been shown and how many times Red has been shown?

Lee Redhead
Lee Redhead
  • 2
1 Solution
Divide the days by 10 and round to get the number of cycles.  use module to determine if the number of cycles is odd (red) or even (green)

SELECT CASE ROUND(74/10, 0) % 2 WHEN 1 THEN 'Red'
                        ELSE 'Green'
Here's with variables and the counts

DECLARE @Days int
SET @Days = 50
DECLARE @CycleDays int
SET @CycleDays = 10
SELECT CASE ROUND(@Days/@CycleDays, 0) % 2 WHEN 1 THEN 'Red'
                        ELSE 'Green'
      ROUND(ROUND(@Days/@CycleDays, 0) / 2,0) AS GreenCount,
      ROUND(@Days/@CycleDays, 0) - ROUND(ROUND(@Days/@CycleDays, 0) / 2, 0) as RedCount

Lee RedheadManaging DirectorAuthor Commented:
Prefect, thank you very much.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now