Solved

Counting Number of Occurrences in Date Range

Posted on 2007-03-27
6
529 Views
Last Modified: 2012-06-27
Sql Server 2005
Is there a straightforward way to calculate the number of occurrences in a given date range? Ex.
I have a Vehicle table V joined with VehicleRepairOrder table RO on V.VehicleId  = RO.VehicleId.

It's a one-to-many relationship and I need to find the number of vehicles with 3 or more repair orders in any given year (RO.DateOfRO is the field I'm trying to make the date calculations from)

Any input or suggestions would be appreciated.
0
Comment
Question by:ViceroyFizzlebottom
[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
  • 3
  • 2
6 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 18801483
Select V.VehicleID, count(RO.DateOfRO) as NumRepairs
From Vehicle V Inner Join VehicleRepairOrder RO on V.VehicleId  = RO.VehicleId
Where DateOfRO Between '1/1/2006' and '12/31/2006'
Group by V.VehicleID
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 18801501
Above solution gives you the count for each vehicle.

to limit it to those w/ 3+ repairs, add:

HAVING count(RO.DateOfRO) >=3
0
 
LVL 6

Author Comment

by:ViceroyFizzlebottom
ID: 18801790
Thanks for the quick response first of all.
The solution you gave is in fact what I am using, but to clarify a bit; that solution gives the number of vehicles if the date range is explicitly given. I am more interested in something that for the entire history of vehicle repairs (in this case is 1999-Present).
The method I was (am) using and what you listed above gives the vehicles for that year, but if the same vehicle was also in 3 or more times in 2005 and I run the query for 2005, that vehicle will essentially be counted twice.

Sorry if that doesn't help explain my issue a little better.


0
Industry Leaders: 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!

 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 250 total points
ID: 18801867
OK ... how about this?


Select distinct VehicleID From
(Select V.VehicleID, Year(RO.DateOfRO) as RepYear, count(RO.DateOfRO) as NumRepairs
From Vehicle V Inner Join VehicleRepairOrder RO on V.VehicleId  = RO.VehicleId
Group by V.VehicleID, Year(RO.DateOfRO)
HAVING count(RO.DateOfRO) >=3) VCount
0
 
LVL 6

Author Comment

by:ViceroyFizzlebottom
ID: 18802190
Ah beautiful. It's funny how simple and straightforward things are when someone shows you how to do it :)

Great job, I appreciate it DanielWilson
0
 

Expert Comment

by:riveter
ID: 25663767
OK
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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