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'
DECLARE @_beginDate Date = @BeginDate
DECLARE @_endDate date = @EndDate
While @_beginDate < DATEADD(d,1,@_endDate)
SELECT (SELECT COUNT(*) FROM dbo.FAC_WorkOrders wo
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)