Solved

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

Posted on 2009-07-07
3
174 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
  • 2
3 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
Comment Utility
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
Comment Utility
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
Comment Utility
Prefect, thank you very much.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now