Solved

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

Posted on 2009-07-07
3
179 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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

737 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