Loop query over a date range

I have a database for tracking work orders and I need to run a report that shows how many work orders were in an open status for every day of a date range period. That is to say there is a DateOpened but no DateClosed for every date in the range. I have a sproc that gives me my numbers but I would like to include the DateOpened in the results. I can't figure out how to do that.
Thanks for any help
ALTER  PROCEDURE [dbo].[spSelectFAC_WorkOrdersOpen] 
@BeginDate Date ='1/1/2011',
@EndDate Date = '4/30/2011'

AS
BEGIN
DECLARE @_beginDate Date = @BeginDate
DECLARE @_endDate date = @EndDate
	
	While @_beginDate < DATEADD(d,1,@_endDate)
	begin
	SELECT  (SELECT COUNT(*)	FROM	dbo.FAC_WorkOrders	wo
	JOIN
	dbo.FAC_Equipment e ON e.EquipmentID = wo.EquipmentID 
	WHERE (wo.DateOpened <= @_beginDate AND (wo.DateCompleted IS NULL OR wo.DateCompleted > @_beginDate)
	 )) as TotalWoOpened
	SET @_beginDate = DATEADD(d,1,@_beginDate)
  END
END

Open in new window

LVL 6
AkAlanAsked:
Who is Participating?
 
David ToddSenior DBACommented:
Hi,

This is a perfect problem for a tally table or numbers table solution, instead of looping through a record set.

http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://www.simple-talk.com/sql/database-administration/creative-solutions-by-using-a-number-table/

its fairly easy in your case as you only want numbers up to 120 or so.

HTH
  David


use tempdb
go

IF OBJECT_ID('Number') IS NULL
BEGIN
 
       CREATE TABLE Number (
              N INT CONSTRAINT Number_PK PRIMARY KEY CLUSTERED(N)
              ); 
 
       WITH
              L0   AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
              L1   AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
              L2   AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
              L3   AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
              L4   AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
              L5   AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
 
              Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)
 
       INSERT INTO Number SELECT TOP 10000 N FROM Nums ORDER BY N;
 
END 
go

select top 10 *
from dbo.Number
;

ALTER PROCEDURE [dbo].[spSelectFAC_WorkOrdersOpen] 
	@BeginDate Date ='1/1/2011'
	, @EndDate Date = '4/30/2011'

AS BEGIN
	SELECT 
		dateadd( day, n.N - 1, @BeginDate ) as ReportDate
		, COUNT(*)	
	FROM dbo.FAC_WorkOrders	wo
	inner JOIN dbo.FAC_Equipment e 
		ON e.EquipmentID = wo.EquipmentID 
	cross join dbo.Number n
	
	WHERE 
		dateadd( day, n.N - 1, @BeginDate ) <= @EndDate
		(
		wo.DateOpened <= dateadd( day, n.N - 1, @BeginDate ) AND (wo.DateCompleted IS NULL OR wo.DateCompleted > dateadd( day, n.N - 1, @BeginDate ))
	 group by
		dateadd( day, n.N - 1, @BeginDate )

end

Open in new window

0
 
AkAlanAuthor Commented:
Thanks David, I had not seen the Tally table used before, what an eye opener. Great links. I got my sproc to work form your example (had to add an "AND" in the where clause after the first statement) and it works perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.