asked
how to sum datetime in same column
![alt text][1] DateChanged Column = it will log the timestamp every time newvalue column changed. DateIssued Column = is the start time where new DeviceName is log. TotalTime = calculate the datediff from Dateissued to (if newvalue column becomes "Release to Production") per IssueID PendingTime = calculate the time of each IssueID if NewValue column is equal to Pending. DesignTime= TotalTime - PendingTime. To get the actual design hours spent of each issueID . SQL query i used to get TotalTime: isnull(DATEDIFF(DAY, dateissued, (select datechanged where NewValue = 'RELEASE TO PRODUCTION' )),0)*24 as TotalTime My question is how can i get PendingTime and DesignTime, since i'm already done with TotalTime. Below is the Data: CREATE TABLE [dbo].[summaryXLS] ( [IssueId] [int] NOT NULL , [DeviceName] [nvarchar](500) NOT NULL , [CustomerName] [nvarchar](MAX) NOT NULL , [Designer] [nvarchar](100) NOT NULL , [FieldChanged] [nvarchar](50) NOT NULL , [NewValue] [nvarchar](50) NOT NULL , [DateChanged] [datetime] NOT NULL , [DateIssued] [datetime] NOT NULL , [TotalTime] [int] NULL , [DesignTime] [int] NULL ) ON [PRIMARY] GO INSERT [dbo].[summaryXLS] ( [IssueId] , [DeviceName] , [CustomerName] , [Designer] , [FieldChanged] , [NewValue] , [DateChanged] , [DateIssued] , [TotalTime] , [DesignTime] ) VALUES ( 40 , N'01_TEST' , N'STM' , N'User1' , N'Status' , N'RELEASE TO PRODUCTION' , CAST(0x0000A63300C28E89 AS DATETIME) , CAST(0x0000A61E00F8409B AS DATETIME) , 504 , 0 ) INSERT [dbo].[summaryXLS] ( [IssueId] , [DeviceName] , [CustomerName] , [Designer] , [FieldChanged] , [NewValue] , [DateChanged] , [DateIssued] , [TotalTime] , [DesignTime] ) VALUES ( 40 , N'01_TEST' , N'STM' , N'User1' , N'Status' , N'DESIGN ONGOING' , CAST(0x0000A61E0100CDB8 AS DATETIME) , CAST(0x0000A61E00F8409B AS DATETIME) , 0 , 0 ) INSERT [dbo].[summaryXLS] ( [IssueId] , [DeviceName] , [CustomerName] , [Designer] , [FieldChanged] , [NewValue] , [DateChanged] , [DateIssued] , [TotalTime] , [DesignTime] ) VALUES ( 40 , N'01_TEST' , N'STM' , N'User1' , N'Status' , N'PENDING APPROVAL' , CAST(0x0000A61E01003272 AS DATETIME) , CAST(0x0000A61E00F8409B AS DATETIME) , 0 , 0 ) INSERT [dbo].[summaryXLS] ( [IssueId] , [DeviceName] , [CustomerName] , [Designer] , [FieldChanged] , [NewValue] , [DateChanged] , [DateIssued] , [TotalTime] , [DesignTime] ) VALUES ( 40 , N'01_TEST' , N'STM' , N'User1' , N'Status' , N'DESIGN ONGOING' , CAST(0x0000A61E00FF4EE3 AS DATETIME) , CAST(0x0000A61E00F8409B AS DATETIME) , 0 , 0 ) INSERT [dbo].[summaryXLS] ( [IssueId] , [DeviceName] , [CustomerName] , [Designer] , [FieldChanged] , [NewValue] , [DateChanged] , [DateIssued] , [TotalTime] , [DesignTime] ) VALUES ( 40 , N'01_TEST' , N'STM' , N'User1' , N'Status' , N'PENDING DATA' , CAST(0x0000A61E00FE13A5 AS DATETIME) , CAST(0x0000A61E00F8409B AS DATETIME) , 0 , 0 ) INSERT [dbo].[summaryXLS] ( [IssueId] , [DeviceName] , [CustomerName] , [Designer] , [FieldChanged] , [NewValue] , [DateChanged] , [DateIssued] , [TotalTime] , [DesignTime] ) VALUES ( 40 , N'01_TEST' , N'STM' , N'User1' , N'Status' , N'DESIGN ONGOING' , CAST(0x0000A61E00FD364B AS DATETIME) , CAST(0x0000A61E00F8409B AS DATETIME) , 0 , 0 ) INSERT [dbo].[summaryXLS] ( [IssueId] , [DeviceName] , [CustomerName] , [Designer] , [FieldChanged] , [NewValue] , [DateChanged] , [DateIssued] , [TotalTime] , [DesignTime] ) VALUES ( 40 , N'01_TEST' , N'STM' , N'User1' , N'Status' , N'PENDING STRUCTURE' , CAST(0x0000A61E00F9673F AS DATETIME) , CAST(0x0000A61E00F8409B AS DATETIME) , 0 , 0 ) INSERT [dbo].[summaryXLS] ( [IssueId] , [DeviceName] , [CustomerName] , [Designer] , [FieldChanged] , [NewValue] , [DateChanged] , [DateIssued] , [TotalTime] , [DesignTime] ) VALUES ( 40 , N'01_TEST' , N'STM' , N'User1' , N'Status' , N'START DESIGN' , CAST(0x0000A61E00F8DC38 AS DATETIME) , CAST(0x0000A61E00F8409B AS DATETIME) , 0 , 0 ) INSERT [dbo].[summaryXLS] ( [IssueId] , [DeviceName] , [CustomerName] , [Designer] , [FieldChanged] , [NewValue] , [DateChanged] , [DateIssued] , [TotalTime] , [DesignTime] ) VALUES ( 40 , N'01_TEST' , N'STM' , N'User1' , N'Category' , N'FTB' , CAST(0x0000A61E00F86ADE AS DATETIME) , CAST(0x0000A61E00F8409B AS DATETIME) , 0 , 0 ) Thanks. [1]:
https://ask.sqlservercentral.com/storage/attachments/3559-untitled.jpg
Might be better to post as text rather than a Excel spreadsheet. Also include table DDL and an example of the kind of query and/or results you want.
![alt text][1] [1]: /storage/temp/3559-untitled.jpg Sorry if it is confusing, based on the image below i got the **TotalTime** using this query. isnull(DATEDIFF(DAY, dateissued, (select datechanged where NewValue = 'RELEASE TO PRODUCTION' )),0)*24 as TotalTime and my problem is i don't know how to get the **Pending Time** & **DesignTime** of user. If the user has the value of *Pending Data, Pending Structure, Pending Approval,* then it will compute the idle time of user. something like **DesignTime = TotalTime - Pending Time**
You really should be adding these details to your question instead of creating multiple answers. I really can't help here as it is not at all clear what you want. What is TotalTime? What is PendingTime? Nobody can help you find an answer until you figure out the details of the question.
OK, I think I've got the gist of what it is you're asking for - but please correct me if I'm wrong. You're after a process that works on the data in DateChanged order, looking at the NewValue field. Working on your data, you want code that gives the sum of the time differences between each record's DateChanged, but splitting it into several groups - for DESIGN (indicated where NewValue changes from something with design in it to something without design in it), PENDING (similarly, but only with pending), and the TOTAL you've already identified. Yes?
19 People are following this question.
