?
Solved

T-SQL Select Date Range and Calculate Time Duration

Posted on 2010-01-11
24
Medium Priority
?
1,403 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:thomasm1948
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 6
  • 2
24 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 26289101
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
 

Author Comment

by:thomasm1948
ID: 26289161
hi,

Attached is a ZIP file with some examples

Thank you for all of your help
Examples.zip
0
 

Author Comment

by:thomasm1948
ID: 26289220
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 41

Expert Comment

by:Sharath
ID: 26289316
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
 

Author Comment

by:thomasm1948
ID: 26289385
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
 

Author Comment

by:thomasm1948
ID: 26289399
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
 
LVL 41

Expert Comment

by:Sharath
ID: 26289512
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 26289581
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
 

Author Comment

by:thomasm1948
ID: 26289610
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 26289630
let say from your doc example
what a result you wish to get?
0
 

Author Comment

by:thomasm1948
ID: 26289667
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 26289707
do you have any key column to identify stage for a workflow ?

0
 

Author Comment

by:thomasm1948
ID: 26289717
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
 

Author Comment

by:thomasm1948
ID: 26289750
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
 

Author Comment

by:thomasm1948
ID: 26289763
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 26289954
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
 

Author Comment

by:thomasm1948
ID: 26294519
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
 

Author Comment

by:thomasm1948
ID: 26294739
sorry, please diregart the last post

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

Author Comment

by:thomasm1948
ID: 26294762
I forgot to format the cells for date
Book1.xls
0
 

Author Comment

by:thomasm1948
ID: 26299029
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
 

Author Comment

by:thomasm1948
ID: 26299110
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
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1500 total points
ID: 26305454
it is fine
if you think it is running long then we can review and optimize
good job!
0
 

Author Comment

by:thomasm1948
ID: 26310235
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
 

Author Closing Comment

by:thomasm1948
ID: 31675848
I resolved the issue thank you for all of your help
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question