July 1, 2015 at 6:47 am
Hi
I am trying to increase performance of a query that joins a table 19 times.
The current example works similarly to below...
CREATE TABLE #Data(PersonID INT, Activity nvarchar(200), ActivityTime DATETIME)
INSERT INTO #Data
VALUES
(1,'Woke Up','2015-07-01 07:00:00.000'),
(2,'Woke Up','2015-07-01 09:00:00.000'),
(2,'Had Breakfast','2015-07-01 09:30:00.000'),
(2,'Got Dressed','2015-07-01 10:00:00.000'),
(3,'Woke Up','2015-07-01 06:00:00.000'),
(3,'Had Breakfast','2015-07-01 06:45:00.000'),
(4,'Woke Up','2015-07-01 04:00:00.000'),
(4,'Had Breakfast','2015-07-01 05:00:00.000'),
(4,'Got Dressed','2015-07-01 06:00:00.000')
SELECT D.PersonID, D.ActivityTime AS WakeUpTime, D2.ActivityTime AS BreakfastTime, D3.ActivityTime AS DressTime
FROM #Data D
LEFT JOIN #Data D2
ON d.PersonID = D2.PersonID
AND D2.Activity = 'Had Breakfast'
LEFT JOIN #Data D3
ON d.PersonID = D3.PersonID
AND D3.Activity = 'Got Dressed'
WHERE D.Activity = 'Woke Up'
Is there a better way to do this? The query joins a table 19 times to get dates for certain values.
Regards
Sam
July 1, 2015 at 7:00 am
Hi,
Try a test using Pivot. Ex:
select
PersonID,
[Woke Up] as WakeUpTime,
[Had Breakfast] as BreakFastTime,
[Got Dressed] as DressTime
from #Data
pivot
(
max(ActivityTime) for Activity in ([Woke Up], [Had Breakfast], [Got Dressed])
) as p
Hope this helps.
July 1, 2015 at 7:31 am
Sam Garth (7/1/2015)
HiI am trying to increase performance of a query that joins a table 19 times.
The current example works similarly to below...
CREATE TABLE #Data(PersonID INT, Activity nvarchar(200), ActivityTime DATETIME)INSERT INTO #Data
VALUES
(1,'Woke Up','2015-07-01 07:00:00.000'),
(2,'Woke Up','2015-07-01 09:00:00.000'),
(2,'Had Breakfast','2015-07-01 09:30:00.000'),
(2,'Got Dressed','2015-07-01 10:00:00.000'),
(3,'Woke Up','2015-07-01 06:00:00.000'),
(3,'Had Breakfast','2015-07-01 06:45:00.000'),
(4,'Woke Up','2015-07-01 04:00:00.000'),
(4,'Had Breakfast','2015-07-01 05:00:00.000'),
(4,'Got Dressed','2015-07-01 06:00:00.000')
SELECT D.PersonID, D.ActivityTime AS WakeUpTime, D2.ActivityTime AS BreakfastTime, D3.ActivityTime AS DressTime
FROM #Data D
LEFT JOIN #Data D2
ON d.PersonID = D2.PersonID
AND D2.Activity = 'Had Breakfast'
LEFT JOIN #Data D3
ON d.PersonID = D3.PersonID
AND D3.Activity = 'Got Dressed'
WHERE D.Activity = 'Woke Up'
Is there a better way to do this? The query joins a table 19 times to get dates for certain values.
Regards
Sam
A standard crosstab query should sort this out for you. An excellent article here [/url]will get you started, post back if you get stuck.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 1, 2015 at 2:52 pm
Here's a quick cross-tab example specific to your situation:
SELECT D.PersonID,
MAX(CASE WHEN D.Activity = 'Woke Up' THEN D.ActivityTime END) AS WakeUpTime,
MAX(CASE WHEN D.Activity = 'Had Breakfast' THEN D.ActivityTime END) AS BreakfastTime,
MAX(CASE WHEN D.Activity = 'Got Dressed' THEN D.ActivityTime END) AS DressTime
--,...
FROM #data D
GROUP BY D.PersonID
ORDER BY D.PersonID
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 2, 2015 at 6:43 am
Thank you all for the suggestion, however If I was to run the following
INSERT INTO #Data
VALUES
(4,'Got Dressed','2015-07-02 06:00:00.000')
And then run my query, It would show me two entries for PersonID 4 with the different dates for Got Dressed.
Using a pivot, I will only give me 1 entry for Person ID.
Is there a way that I can use a pivot but to return both?
Regards
Sam
July 2, 2015 at 6:46 am
Sam Garth (7/2/2015)
Thank you all for the suggestion, however If I was to run the following
INSERT INTO #Data
VALUES
(4,'Got Dressed','2015-07-02 06:00:00.000')
And then run my query, It would show me two entries for PersonID 4 with the different dates for Got Dressed.
Using a pivot, I will only give me 1 entry for Person ID.
Is there a way that I can use a pivot but to return both?
Regards
Sam
Try grouping by both PersonID and date. You're using datetime, so cast your date as DATE to remove the time component.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 2, 2015 at 10:31 am
I would need it to show as below
12015-07-01 07:00:00.000NULLNULL
22015-07-01 09:00:00.0002015-07-01 09:30:00.0002015-07-01 10:00:00.000
32015-07-01 06:00:00.0002015-07-01 06:45:00.000NULL
42015-07-01 04:00:00.0002015-07-01 05:00:00.0002015-07-01 06:00:00.000
42015-07-01 04:00:00.0002015-07-01 05:00:00.0002015-07-02 06:00:00.000
Does anyone have any ideas?
Thanks
Sam
July 2, 2015 at 11:15 am
Yeah, you could do that, but it's rather bizarre. What would the day before's activities have to do with today?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 3, 2015 at 3:22 am
I agree it is bizarre but it is the way the script currently works and they do not wish to change it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply