?
Solved

T-SQL Add Columns to get total

Posted on 2010-01-12
14
Medium Priority
?
1,513 Views
Last Modified: 2012-05-08
Hi,

I been stuck on this issue for awhile now and I have no idea how to resolve it.  Basically what I is a table that has multiple rows that are related to each and I need to group them together to form one row.  

Each row has a creation date and Modified date.  these are the two columns that I have to add together to get a total amount of days.

Each row also has a couple of columns that has the exact information in and at the same time it has a row final row that has a column that will have value of 5

it is hard to exactly explain what I am doing.... below is my code that can sum all of the related rows together but I do not know how to filter to only get the related rows that have the column that has the value of 5 in the squence.  

Attached is an excel spread sheet that may also explain a little more of what I am doing
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, Approval_Status nvarchar(150), Duration int )

 insert into @TempTable (PONUM, tp_WorkflowInstanceID, tp_Created, tp_Modified, Approval_Status, Duration)
    select nvarchar17, tp_WorkflowInstanceID, tp_Created, tp_Modified, nvarchar12,
   (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 [Wss_Content].[dbo].[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 [Wss_Content].[dbo].[AllUserData] as t1
 inner join (Select PONUM, SUM(Duration) as Duration 
 from @TempTable group by PONUM) as t2
 on t1.nvarchar17 = t2.PONUM 
 
 where t1.tp_Created between @startDate and @endDate and nvarchar10 = 'Test Inventory PO Workflow'

Open in new window

Book1.xls
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
  • 7
  • 6
14 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26300055
If you are using SQL Server 2005, this simplified statement should work..
select nvarchar17 as [PO Number], nvarchar16 as [Amount], sum(Duration) over ( partition by nvarchar17) Duration 
FROM (
select nvarchar17, tp_WorkflowInstanceID, tp_Created, tp_Modified, nvarchar12,nvarchar16,nvarchar10,
   (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]
 from [Wss_Content].[dbo].[AllUserData] 
where tp_WorkflowInstanceID is not null ) temp
WHERE tp_Created between @startDate and @endDate 
and nvarchar10 = 'Test Inventory PO Workflow'

Open in new window

0
 

Author Comment

by:thomasm1948
ID: 26300081
The following is my output with the above code

F27985      $4,269.11      1
F27985      $4,269.11      1
F27986      $796.50      1
F27986      $796.50      1

if the value added to 13 then it would be correct.  In my case the end date is not the completion date; it is the end of the date range for when the PO was created
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26300142
>> if the value added to 13 then it would be correct.  In my case the end date is not the completion date; it is the end of the date range for when the PO was created

I am not clear on what you meant over here..

Kindly post the result of this query

select nvarchar17, tp_WorkflowInstanceID, tp_Created, tp_Modified, nvarchar12,nvarchar16,nvarchar10,
   (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]
 from [Wss_Content].[dbo].[AllUserData]
where tp_WorkflowInstanceID is not null

and the expected result set so that I can modify the above to get it work..
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

Author Comment

by:thomasm1948
ID: 26300161
Hi

the following is the results.  I attached it excel  as well to make it a little easier to read

nvarchar17      tp_WorkflowInstanceID      tp_Created      tp_Modified      nvarchar12      nvarchar16      nvarchar10      Duration
F27986      113F22A9-9ABA-43F0-B2A3-057B6A5C1E12      2009-12-19 02:48:08.000      2009-12-19 02:49:16.000      Approved      $796.50      Test Inventory PO Workflow      0
F27986      113F22A9-9ABA-43F0-B2A3-057B6A5C1E12      2009-12-19 02:49:16.000      2009-12-21 16:43:42.000      Approved      $796.50      Test Inventory PO Workflow      1
F27985      04864BE4-3FA6-4623-BEE0-95E6274FCAD6      2009-12-19 03:06:53.000      2009-12-19 03:08:06.000      Approved      $4,269.11      Test Inventory PO Workflow      0
F27985      04864BE4-3FA6-4623-BEE0-95E6274FCAD6      2009-12-19 03:08:06.000      2009-12-21 16:44:08.000      Approved      $4,269.11      Test Inventory PO Workflow      1
F27986      113F22A9-9ABA-43F0-B2A3-057B6A5C1E12      2009-12-21 16:43:42.000      2010-01-04 02:16:35.000      Approved      $796.50      Test Inventory PO Workflow      11
F27985      04864BE4-3FA6-4623-BEE0-95E6274FCAD6      2009-12-21 16:44:08.000      2010-01-04 02:17:28.000      Approved      $4,269.11      Test Inventory PO Workflow      11
F27986      113F22A9-9ABA-43F0-B2A3-057B6A5C1E12      2010-01-04 02:16:35.000      2010-01-04 17:06:09.000      Filed      $796.50      Test Inventory PO Workflow      1
F27985      04864BE4-3FA6-4623-BEE0-95E6274FCAD6      2010-01-04 02:17:28.000      2010-01-04 17:06:39.000      Filed      $4,269.11      Test Inventory PO Workflow      1
Book2.xls
0
 

Author Comment

by:thomasm1948
ID: 26300164
thank you for tling the time to review this issue with me
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26300187
Post the expected result for the sample set in Post# 26300161
0
 

Author Comment

by:thomasm1948
ID: 26300192
I have attached another document with your query along with its results.  I have also included my query that give an example of what I would like, but it has one flaw; it will grab all of the workflows regardless if they are completed or not
Your-query-and-my-expected-resul.doc
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26300214
Thanks And kindly post me the expected result set for the above sample..
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26300228
This should give you the expected result set:
select nvarchar17 as [PO Number], max(nvarchar16) as [Amount], sum(Duration) Duration 
FROM (
select nvarchar17, tp_WorkflowInstanceID, tp_Created, tp_Modified, nvarchar12,nvarchar16,nvarchar10,
   (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]
 from [Wss_Content].[dbo].[AllUserData] 
where tp_WorkflowInstanceID is not null ) temp
WHERE tp_Created between @startDate and @endDate 
and nvarchar10 = 'Test Inventory PO Workflow'
GROUP By nvarchar17

Open in new window

0
 

Author Comment

by:thomasm1948
ID: 26300288
no the duration is 1 day, it still needs to calculated the other rows in the workflow

In my last post I included what I would like in the attachment.  My query successfully grouped all of the related workflows together and calculated the total duration between all of the rows.

My expected result is to make sure that the query will only grab the completed workflows.  the completed workflows will a column with value of 5 or if I was evaluating approval status then it will have a value of filed

So what I would need the query to evaluate would be both the workflowinstanceID (this will ensure that it is the same workflow) and the approval status that will show a value of filed for the last entry.  

While the workflow is in progress then the workflowinstanceID will be the same and approval status approved or if it was rejected it would show rejected
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26300315
Can you kindly verify the output of the query posted earlier..
Posting it again for reference:

And for your information, its similar to your current query expect for the fact that it is tuned out and multiple statements are reduced into a single statement..

Kindly confirm..
And do let me know if you need to obtain output other than the one you obtained earlier..
select nvarchar17 as [PO Number], max(nvarchar16) as [Amount], sum(Duration) Duration 
FROM (
select nvarchar17, tp_WorkflowInstanceID, tp_Created, tp_Modified, nvarchar12,nvarchar16,nvarchar10,
   (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]
 from [Wss_Content].[dbo].[AllUserData] 
where tp_WorkflowInstanceID is not null ) temp
WHERE tp_Created between @startDate and @endDate 
and nvarchar10 = 'Test Inventory PO Workflow'
GROUP By nvarchar17

Open in new window

0
 

Author Comment

by:thomasm1948
ID: 26310225
OK, I figured it out....I think i was thinking to hard about it.  Further 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
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1500 total points
ID: 26313919
Kindly confirm whether this is resolved out now or not..
0
 

Author Closing Comment

by:thomasm1948
ID: 31676482
Yes this was resolved
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

765 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