CODEWITHSUNDEEP
sql-servert-sql
Tom Mitchell
Tom Mitchell

Reputation: 1

Query to create records between two dates

I have a table with the following fields (among others)

TagID
TagType
EventDate
EventType

EventType can be populated with "Passed Inspection", "Failed Inspection" or "Repaired" (there are actually many others, but simplifies to this for my issue)

Tags can go many months between a failed inspection and the ultimate repair... in this state they are deemed to be "awaiting repair". Tags are still inspected each month even after they have been identified as having failed. (and just to be clear, a “failed inspection” doesn’t mean the item being inspected doesn’t work at all… it still works, just not at 100% capacity…which is why we still do inspections on it).

I need to create a query that counts, by TagType, Month and Year the number of Tags that are awaiting repair. The end result table would look like this, for example

 TagType      EventMonth      EventYear     CountofTagID  
 xyz               1            2011               3  
 abc               1            2011               2  
 xyz               2            2011               2>>>>>>>>>>>>indicating a repair had been made since 1/2011  
 abc               2            2011               2  
 and so on

The "awaiting repair" status should be assessed on the last day of the month

This is totally baffling me...

One thought that I had was to develop a query that returned:

 TagID,
 TagType,
 FailedInspectionDate, and
 NextRepairDate,

then try and do something that stepped thru the months in between the two dates, but that seems wildly inefficient.

Any help would be much appreciated.

Update
A little more research, and a break from the problem to think about it differently gave me the following approach. I'm sure its not efficient or elegant, but it works. Comments to improve would be appreciated.

declare @counter int
declare @FirstRepair date
declare @CountMonths as int

set @FirstRepair = (<Select statement to find first repair across all records>)
set @CountMonths = (<select statement to find the number of months between the first repair across all records and today>)
--clear out the scratch table
delete from dbo.tblMonthEndDate
set @counter=0
while @counter <=@CountMonths --fill the scratch table with the date of the last day of every month from the @FirstRepair till today
begin
insert into dbo.tblMonthEndDate(monthenddate) select             dbo.lastofmonth(dateadd(m,@counter, @FirstRepair))
set @counter = @counter+1
end
--set up a CTE to get a cross join between the scratch table and the view that has the associated first Failed Inspection and Repair
;with Drepairs_CTE (FacilityID, TagNumber, CompType, EventDate) 
AS
(
SELECT dbo.vwDelayedRepairWithRepair.FacilityID,     dbo.vwDelayedRepairWithRepair.TagNumber, dbo.vwDelayedRepairWithRepair.CompType, 
           dbo.tblMonthEndDate.MonthEndDate
FROM  dbo.vwDelayedRepairWithRepair INNER JOIN
           dbo.tblMonthEndDate ON dbo.vwDelayedRepairWithRepair.EventDate <= dbo.tblMonthEndDate.MonthEndDate AND 
           dbo.vwDelayedRepairWithRepair.RepairDate >= dbo.tblMonthEndDate.MonthEndDate
)
--use the CTE to build the final table I want
Select FacilityID, CompType, Count(TagNumber), MONTH(EventDate),     YEAR(EventDate), 'zzz' as EventLabel
FROM  Drepairs_CTE
GROUP BY FacilityID, CompType, MONTH(EventDate), YEAR(EventDate)`

Result set ultimately looks like this:

FacilityID  CompType  Count  Month      Year  Label
1        xyz    2   1   2010    zzz
1        xyz    1   2   2010    zzz
1        xyz    1   7   2009    zzz

Upvotes: 0

Views: 1240

Answers (1)

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

Here is a recursive CTE which generates table of last dates of months in interval starting with minimum date in repair table and ending with maximum date.

;with tableOfDates as (
  -- First generation returns last day of month of first date in repair database
  -- and maximum date 
  select dateadd (m, datediff (m, 0, min(eventDate)) + 1, 0) - 1 startDate,
         max(eventDate) endDate
    from vwDelayedRepairWithRepair
  union all
  -- Last day of next month
  select dateadd (m, datediff (m, 0, startDate) + 2, 0) - 1, 
         endDate
  from tableOfDates
  where startDate <= endDate
)
select * 
from tableOfDates
-- If you change the CTE,
-- Set this to reasonable number of months 
-- to prevent recursion problems. 0 means no limit.
option (maxrecursion 0)

EndDate column from tableOfDates is to be ignored, as it serves as upper bound only. If you create UDF which returns all the dates in an interval, omit endDate in select list or remove it from CTE and replace with a parameter.

Sql Fiddle playground is here.

Upvotes: 1

Related Questions