I have a quick question...I have a basic t-sql query that enables to evaluate several rows and groups them togther and calculates the total duration
If I and and tp_Created between @startDate and @endDate then the time duration is calculated wrong. This makes since being that I am only selecting those dates.
So my question is how can get the correct time duration and at the same filter by the date range
Below is my code for your review
thank you for all of your help in advance
declare @startDate datetime
declare @endDate datetime
set @startDate = '12/19/2009'
set @endDate = '12/19/2009'
if @startDate = @endDate
set @endDate = @endDate + 1
select nvarchar10 as [Workflow Name],
sum((DATEDIFF(dd, tp_Created, tp_Modified) + 1)
-(DATEDIFF(wk, tp_Created, tp_Modified) * 2)
-(CASE WHEN DATENAME(dw, tp_Created) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, tp_Modified) = 'Saturday' THEN 1 ELSE 0 END)) as [Duration],
nvarchar16 as [Amount], nvarchar17 as [PO Number]
from [Wss_Content].[dbo].[AllUserData] where nvarchar10 = 'Test Inventory PO Workflow'
--and tp_Created between @startDate and @endDate
Group By nvarchar10, nvarchar16, nvarchar17