May 11, 2011 at 5:39 am
Hi guys,
i need to calculate time from string. i need calculation column at end
like data team + crm + im team +.......... but here problem is its in char datatype
if i will convert into datetime then it will give me correct result if its below 24 hours , if its more than 24 hours then it will change date , i need result in hh:rr format.
CREATE TABLE [dbo].[tmp](
[PROJECT] [nvarchar](70) NULL,
[Defect_ID] [int] NULL,
[Summary] [nvarchar](255) NULL,
[Priority] [nvarchar](70) NULL,
[Data Team] [char](5) NOT NULL,
[Env Delivery Team] [char](5) NOT NULL,
[CRM] [char](5) NOT NULL,
[IM Team] [char](5) NOT NULL,
[TIL ICC] [char](5) NOT NULL,
[Gemini] [char](5) NOT NULL,
[SVAP] [char](5) NOT NULL,
[MWS] [char](5) NOT NULL,
[MTS] [char](5) NOT NULL,
[ACTUALTIME] [char](5) NULL,
[SLA] [varchar](5) NOT NULL
) ON [PRIMARY]
INSERT INTO tmp ([PROJECT],[Defect_ID],[Summary],[Priority],[Data Team],[Env Delivery Team],[CRM],[IM Team],[TIL ICC],[Gemini],[SVAP],[MWS],[MTS],[ACTUALTIME],[SLA],[TOTAL RESOLUTION]) VALUES ('LEBIS','4','LEBIS Phase 2: Smoke Test: Unable to raise a Blacklisting request via CRM6.Error message f','P1-Critical','P1-Critical','P1-Critical','00:57','00:57','00:57','00:00','00:00','00:00','00:00','11:47','00:30')
INSERT INTO tmp ([PROJECT],[Defect_ID],[Summary],[Priority],[Data Team],[Env Delivery Team],[CRM],[IM Team],[TIL ICC],[Gemini],[SVAP],[MWS],[MTS],[ACTUALTIME],[SLA],[TOTAL RESOLUTION]) VALUES ('LEBIS','7','LEBIS Phase 2: Cycle1 with Live IMEI's: Unable to login to CRM6 SCM SIT env','P1-Critical','P1-Critical','P1-Critical','02:32','02:32','02:32','00:00','00:00','00:00','00:00','16:20','00:30')
thanks in advance
May 11, 2011 at 8:11 am
You know all the people who answer question on these forums are volunteers. You provided little of the information needed for someone to be able to help you. You have a create table script, then you have inserts that don't work because the columns don't match the table definition, you have invalid string handling. Given the extremely vague and partial description of what you are trying to accomplish it is impossible because even your samples don't work. Please read the link in my signature about how to post your question in a format that is most likely to generate help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2011 at 9:49 am
This will give you hours and minutes which you can then format into HH:MM or how you wish.
WITH cte (HH,MM)
AS (SELECT
CAST(LEFT([Data Team],2) as int)+CAST(LEFT([CRM],2) as int)+CAST(LEFT([IM Team],2) as int) AS [HH],
CAST(RIGHT([Data Team],2) as int)+CAST(RIGHT([CRM],2) as int)+CAST(RIGHT([IM Team],2) as int) AS [MM]
FROM dbo.tmp)
SELECT HH+(MM/60) AS [HH],MM%60 AS [MM]
FROM cte
BTW What format will you use if the hours are greater than 99?
Far away is close at hand in the images of elsewhere.
Anon.
May 12, 2011 at 1:55 am
Or without the cte
SELECT
CAST(LEFT([Data Team],2) as int)+CAST(LEFT([CRM],2) as int)+CAST(LEFT([IM Team],2) as int)
+(CAST(RIGHT([Data Team],2) as int)+CAST(RIGHT([CRM],2) as int)+CAST(RIGHT([IM Team],2) as int))/60 AS [HH],
(CAST(RIGHT([Data Team],2) as int)+CAST(RIGHT([CRM],2) as int)+CAST(RIGHT([IM Team],2) as int))%60 AS [MM]
FROM dbo.tmp
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply