Solved

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

Posted on 2009-07-07
3
180 Views
Last Modified: 2012-05-07
Hello,
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
0
Comment
Question by:Lee Redhead
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 24794917
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'
            END
0
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 500 total points
ID: 24794998
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'
            END,
      ROUND(ROUND(@Days/@CycleDays, 0) / 2,0) AS GreenCount,
      ROUND(@Days/@CycleDays, 0) - ROUND(ROUND(@Days/@CycleDays, 0) / 2, 0) as RedCount

0
 

Author Closing Comment

by:Lee Redhead
ID: 31600622
Prefect, thank you very much.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question