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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.