T-SQL Select Date Range and Calculate Time Duration

please clarify:

can you please post some data example and what do you expect ?
---
check http://msdn.microsoft.com/en-us/library/ms189794.aspx
datediff - time datepart
for example for minutes

DATEDIFF(mi, tp_Created, tp_Modified
--
try to set for the same day:

set @startDate = ' '2009-12-19 00:00:00.000000'
set @endDate = ''2009-12-19 23:59:59.9999999'

hi,

Attached is a ZIP file with some examples

Thank you for all of your help
Examples.zip

Here is one more example that shows the query without the group by or date range

So basically what I would like to do is group the rows together by the:

workflow name
Calculated Duration
Amount
PO Number

how the workflow works is by adding a new row to a table for each stage in a workflow process.  This is why I use other factors to make sure that I getting the correct time duration for the workflow life cycle.

The main flaw in my query is that if I calculate the time duration correctly then I will pull all of the PO Numbers for the workflow process.  This is why I need the date range.  the date range should enable the user get all of the workflows that were finalized within a range


Revised-query-to-show-relative-f.doc

In your Example - calculates the Duration correctly.doc and Example - calculates the Duration incorrectly.doc, i did not find any difference in the output. i.e. with and without the WHERE clause on tp_created between @startdate and @enddate

Please look at the my last example without the group by.  The duration for each PO Number is calculated correctly.  

In my example there is only two PO Numbers which represent the workflow stages.  The group by is calculating the worflow life cyle

if we add the date range then the duration column will show 1.  If we do not add the date range then the duration column will show 13 (this should be correct being that I am adding that column is the group by)

I checked Example - query without the group by or data range.doc and Revised-query-to-show-relative-f.doc and did n ot find any difference in the output.

Trying to understand, what exactly, you are looking for.

you posted:
"
workflow name
Calculated Duration
Amount
PO Number"

Amount is  count records per day  of stages per day?

please tell more about stages?

the reviced doc shows each stage in a workflow life cycle  and calculates the duration for each workflow stage.

what I am trying to do is calculate the duration for each workflow life cycle.  to do this I have to group the workflow stages together and add the duration.  In the reviced doc there should only be two actual PO Numbers, but it shows each stage and the duration for each stage

the documents with the Group By is taking the PO Numbers and grouping them together.  This will add the total duration for the workflow life cycle.  Both PO numbers happen have taken 13 days for completion

let say from your doc example
what a result you wish to get?

I would like to sum the durations columns for a workflow life cycle.  A workflow life cycle can have several rows  (stages) in  the table.  By utlizing the group by then I will get the total duration for each workflow life cycle

but when I add the date range in the query then the query will filter out the stages that were created on a date outside the date range.  This is why the duration is correct for the query without the date range

do you have any key column to identify stage for a workflow ?

for example

Stage 1 was created 12/19/2009 and modified (wich is completed) 12/19/2009  (duration for this stage is 0)
Stage 2 was created 12/19/2009 and modified (wich is completed) 12/20/2009  (duration for this stage is 1)
Stage 3 was created 12/20/2009  and modified (wich is completed) 12/22/2009  (duration for this stage is 2)

If we do a query based on the utiling the group by then we should get a duration total of 3

if we add a date range of that only includes 1 full day then we will get a total duration of 1 this is because the date range is based on the stage creation

there are mutiple keys in the table that I am querying.  unfortunately I cannot change anything in the database schema for the wss_content (this is sharepoint). This why I am utilizing another database to create the queries for sql reporting services

the main columns that can identify a workflow life cycle is a PO Number, Workflow Name and the Amount.  These should always be the same for row (stage) in a workflow life cycle and be identified for grouping purposes

hmm I did not get where from there is 3rd stage

how do you know it is belong to this stages chain?
"
Stage 1 was created 12/19/2009 and modified (wich is completed) 12/19/2009  (duration for this stage is 0)
Stage 2 was created 12/19/2009 and modified (wich is completed) 12/20/2009  (duration for this stage is 1)
Stage 3 was created 12/20/2009  and modified (wich is completed) 12/22/2009  (duration for this stage is 2)
"
can you use your doc example to create manually an expected  output?

Hi,

attached is another example that should explain everything a little better.  You may have to adjust the page settings to land scape
Example-Output.doc

sorry, please diregart the last post

attached an excel file the shows a better picture
Book1.xls

I forgot to format the cells for date
Book1.xls

OK, I think i found a way to solve but I diffidently feel that I am doing it the long

will you please review and see if there is a better way

thank you for all of your help

use WSS_Content
 declare @startDate datetime
 declare @endDate datetime
 set @startDate = '12/19/2009'
 set @endDate = '12/19/2009'
 set @endDate = @endDate + 1

 DECLARE @TempTable TABLE(PONUM nvarchar(150), tp_WorkflowInstanceID uniqueidentifier, tp_Created datetime, tp_Modified datetime, Duration int )

 insert into @TempTable (PONUM, tp_WorkflowInstanceID, tp_Created, tp_Modified, Duration)
    select nvarchar17, tp_WorkflowInstanceID, tp_Created, tp_Modified,
   (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] --into #tmp 
 from AllUserData where tp_WorkflowInstanceID is not null  
 order by nvarchar17, nvarchar14
 

 
 select distinct t1.nvarchar17 as [PO Number], t1.nvarchar16 as [Amount], t2.Duration
 from AllUserData as t1
 inner join (Select PONUM, SUM(Duration) as Duration 
 from @TempTable group by PONUM, tp_WorkflowInstanceID) as t2
 on t1.nvarchar17 = t2.PONUM
 where t1.tp_Created between @startDate and @endDate

Select allOpen in new window

OK, I just realized that I also should grad only the groups that have filed in them......

Please see the excel file.  I am kind of stuck

OK, I figured it out....I think i was thinking to hard about it.  Further more based on my past code the number of days would be incorrect because it was adding each persons days versus actual days.

To resolve the issue I created a join within the same table and grabbed the creation date for stage 1 and then joined it to stage 5 modified date and added them together

below is my code

use WSS_Content 
 declare @startDate datetime
 declare @endDate datetime
 set @startDate = '12/19/2009'
 set @endDate = '12/19/2009'
 set @endDate = @endDate + 1
 select distinct t1.nvarchar10 as [Workflow Name], t1.nvarchar17 as [PO Number], t1.tp_Created, t2.tp_modified, 
 t2.nvarchar17, (DATEDIFF(dd, tp_Created, t2.tp_Modified) + 1) 
  -(DATEDIFF(wk, tp_Created, t2.tp_Modified) * 2) 
  -(CASE WHEN DATENAME(dw, tp_Created) = 'Sunday' THEN 1 ELSE 0 END) 
  -(CASE WHEN DATENAME(dw, t2.tp_Modified) = 'Saturday' THEN 1 ELSE 0 END) as [Duration]
from AllUserData t1
 inner join (select tp_modified, nvarchar17, tp_WorkflowInstanceID 
                from AllUserData where nvarchar14 = '5') as t2
   on t1.tp_WorkflowInstanceID = t2.tp_WorkflowInstanceID 

where nvarchar10 = 'Test Inventory PO Workflow' and nvarchar14 = '1'

Select allOpen in new window

I resolved the issue thank you for all of your help