T-SQL Select Date Range and Calculate Time Duration

Hi,

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
use TEST2_SP_ERP
declare @startDate datetime
declare @endDate datetime
set @startDate = '12/19/2009'
set @endDate = '12/19/2009'

if @startDate = @endDate
begin
set @endDate = @endDate + 1

end

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

Open in new window

thomasm1948Asked:
Who is Participating?
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.

Eugene ZCommented:
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'
0
thomasm1948Author Commented:
hi,

Attached is a ZIP file with some examples

Thank you for all of your help
Examples.zip
0
thomasm1948Author Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SharathData EngineerCommented:
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
0
thomasm1948Author Commented:
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

0
thomasm1948Author Commented:
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)
0
SharathData EngineerCommented:
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.
0
Eugene ZCommented:
you posted:
"
workflow name
Calculated Duration
Amount
PO Number"

Amount is  count records per day  of stages per day?

please tell more about stages?
0
thomasm1948Author Commented:
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
0
Eugene ZCommented:
let say from your doc example
what a result you wish to get?
0
thomasm1948Author Commented:
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
0
Eugene ZCommented:
do you have any key column to identify stage for a workflow ?

0
thomasm1948Author Commented:
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
0
thomasm1948Author Commented:
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
0
thomasm1948Author Commented:
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
0
Eugene ZCommented:
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?
0
thomasm1948Author Commented:
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
0
thomasm1948Author Commented:
sorry, please diregart the last post

attached an excel file the shows a better picture
Book1.xls
0
thomasm1948Author Commented:
I forgot to format the cells for date
Book1.xls
0
thomasm1948Author Commented:
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

Open in new window

0
thomasm1948Author Commented:
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
0
Eugene ZCommented:
it is fine
if you think it is running long then we can review and optimize
good job!
0

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
thomasm1948Author Commented:
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'

Open in new window

0
thomasm1948Author Commented:
I resolved the issue thank you for all of your help
0
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 SharePoint

From novice to tech pro — start learning today.

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.