February 19, 2015 at 4:19 am
Hello,
I am trying to subtract data in a table. But how do I do this if the entries are on different rows.
I want to subtract the assigned time with the new time so I can see how long it takes to assign something
Basically what I want at the end is something like this
numberstatassignedtime
17061assigned1900-1-1 00:01:00.043
The query I have is (don't mind the joins etc, it is just my query to get to the raw data)
---------
Select S.number,
SStatus.name stat,
Sshis.StartDate
From supportcall S
JOIN dbo.Party AS Pa
ON Pa.PartyID = S.PartyID
JOIN Organization AS O
ON O.OrganizationID = PA.OrganizationID
JOIN SupportCallStatusHistory AS SSHis
ON Sshis.SupportCallID = S.SupportCallID
JOIN SupportCallStatus AS SStatus
ON sshis.StatusID = sstatus.SupportCallStatusID
Where O.name = Organisation'
AND S.opendate > '1-1-2015'
AND (sstatus.name = 'assigned' or sstatus.name = 'new')
order by S.number, Sshis.StartDate
and the result is like this (So I need to subtract to entries, Assigned - New)
--------
numberstatStartDate
17061New2015-01-06 09:01:48.843
17061Assigned2015-01-06 09:02:42.657
17105New2015-01-07 15:16:19.290
17105Assigned2015-01-07 15:16:59.840
17131New2015-01-08 14:25:22.057
17131Assigned2015-01-08 14:26:49.937
17143New2015-01-09 08:40:47.670
17143Assigned2015-01-09 08:42:04.317
17183New2015-01-12 10:31:12.830
17183Assigned2015-01-12 10:31:30.627
February 19, 2015 at 8:32 am
Try this:
--== TEST DATA ==--
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP;
CREATE TABLE #TEMP (Number Int, Stat Varchar(10), StartDate DateTime);
INSERT #TEMP VALUES (17061, 'New', '2015-01-06 09:01:48.843');
INSERT #TEMP VALUES (17061, 'Assigned', '2015-01-06 09:02:42.657');
INSERT #TEMP VALUES (17105, 'New', '2015-01-07 15:16:19.290');
INSERT #TEMP VALUES (17105, 'Assigned', '2015-01-07 15:16:59.840');
INSERT #TEMP VALUES (17131, 'New', '2015-01-08 14:25:22.057');
INSERT #TEMP VALUES (17131, 'Assigned', '2015-01-08 14:26:49.937');
INSERT #TEMP VALUES (17143, 'New', '2015-01-09 08:40:47.670');
INSERT #TEMP VALUES (17143, 'Assigned', '2015-01-09 08:42:04.317');
INSERT #TEMP VALUES (17183, 'New', '2015-01-12 10:31:12.830');
INSERT #TEMP VALUES (17183, 'Assigned', '2015-01-12 10:31:30.627 ');
--== TRY THIS ==--
SELECT T1.Number, T1.StartDate as [New], T2.StartDate as [Assigned], (T2.StartDate - T1.StartDate) as [Subtracted]
FROM #TEMP T1
CROSS APPLY (SELECT * FROM #TEMP WHERE Stat = 'Assigned'AND Number = T1.Number) T2
WHERE T1.Stat = 'New'
February 19, 2015 at 9:22 pm
Using Laurie's set up data, this is probably also an option:
SELECT Number
,NewDate = MAX(CASE Stat WHEN 'New' THEN StartDate END)
,AssignedDate = MAX(CASE Stat WHEN 'Assigned' THEN StartDate END)
,AssignedMinusNew = MAX(CASE Stat WHEN 'Assigned' THEN StartDate END) -
MAX(CASE Stat WHEN 'New' THEN StartDate END)
FROM #TEMP
GROUP BY Number;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 20, 2015 at 9:35 am
Thanks,
I needed to play a bit around to get the data the same way you had. Using a table variable now, but the last line with the cross apply did work
February 20, 2015 at 9:43 am
Extra info, I made it like this to see the exact days hours etc
SELECT T1.Number, T1.StartDate as [New], T2.StartDate as [Assigned],
CAST ((datediff (ss, T1.StartDate, T2.StartDate)/3600/24) AS varchar (2)) + ' days ' + CAST((datediff(ss, T1.StartDate, T2.StartDate)/3600%24) AS varchar(4)) + ' hours, ' + CAST((datediff(ss, T1.StartDate, T2.StartDate)%3600)/60 AS Varchar(2)) + ' minutes, ' + CAST((datediff(ss, T1.StartDate, T2.StartDate)%60) AS VARCHAR(2)) + ' seconds' as [Subtracted]
FROM @data T1
CROSS APPLY (SELECT * FROM @data WHERE Stat = 'Assigned' AND Number = T1.Number) T2
WHERE T1.Stat = 'New'
February 20, 2015 at 10:29 am
Good. Glad you got it sorted.
I meant to suggest using a CTE like this to get the base data using your original query:
;WITH CTE_Base_Data AS
(
Select S.number,
SStatus.name stat,
Sshis.StartDate
From supportcall S
JOIN dbo.Party AS Pa
ON Pa.PartyID = S.PartyID
JOIN Organization AS O
ON O.OrganizationID = PA.OrganizationID
JOIN SupportCallStatusHistory AS SSHis
ON Sshis.SupportCallID = S.SupportCallID
JOIN SupportCallStatus AS SStatus
ON sshis.StatusID = sstatus.SupportCallStatusID
Where O.name = 'Organisation'
AND S.opendate > '1-1-2015'
AND (sstatus.name = 'assigned' or sstatus.name = 'new')
order by S.number, Sshis.StartDate
)
SELECT T1.Number, T1.StartDate as [New], T2.StartDate as [Assigned],
CAST ((datediff (ss, T1.StartDate, T2.StartDate)/3600/24) AS varchar (2)) + ' days ' + CAST((datediff(ss, T1.StartDate, T2.StartDate)/3600%24) AS varchar(4)) + ' hours, ' + CAST((datediff(ss, T1.StartDate, T2.StartDate)%3600)/60 AS Varchar(2)) + ' minutes, ' + CAST((datediff(ss, T1.StartDate, T2.StartDate)%60) AS VARCHAR(2)) + ' seconds' as [Subtracted]
FROM CTE_Base_Data T1
CROSS APPLY (SELECT * FROM CTE_Base_Data WHERE Stat = 'Assigned' AND Number = T1.Number) T2
WHERE T1.Stat = 'New';
You can avoid using a table variable with a CTE.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply