April 3, 2013 at 6:42 am
Hi all,
I'm struggling with a query which runs forever and I can't figure out what the problem is.
In attachment you will find a file to create and fill 3 tables to test.
I will try to explain what I have to accomplish.
I have to select data from 2 tables (Application_Useraccounts and Application_Useraccounts_Status) and insert it into a 3rd table (Tussen_Statussen_Adviseur). The column Useraccount_Status_Duration from the table Application_Useraccounts_Status has to be inserted into the correct column in the table Tussen_Statussen_Adviseur based on the ID in the column Useraccount_Status_ID from the table Application_Useraccounts_Status.
For example:
If the ID is 1, then the Useraccount_Status_Duration should go into column Duration_UserLogin.
If the ID is 2, then the Useraccount_Status_Duration should go into column Duration_PauzeKort.
If the ID is 3, then the Useraccount_Status_Duration should go into column Duration_PauzeLang.
etc...
An example as result can be found in the table Tussen_Statussen_Adviseur that will be created in the test file.
At this moment I have the following query, but it runs forever and does not end.
--===Insert the result from the select into the new table.===
INSERT INTO Tussen_Statussen_Adviseur
(Useraccount_Name,
Status_DateTime,
Duration_UserLogin,
Duration_PauzeKort,
Duration_PauzeLang,
Duration_Beschikbaar,
Duration_Storing,
Duration_Werkoverleg,
Duration_NietBeschikbaar,
Duration_Overdracht,
Duration_Beheer)
SELECT
(COALESCE(dtSums.Useraccount_First_Name, '') + ' ' + COALESCE(dtSums.Useraccount_Last_Name, '')) AS Useraccount_Name,
dtSums.Useraccount_Status_DateTime,
dtSums.Duration_UserLogin,
dtSums.Duration_PauzeKort,
dtSums.Duration_PauzeLang,
dtSums.Duration_Beschikbaar,
dtSums.Duration_Storing,
dtSums.Duration_Werkoverleg,
dtSums.Duration_NietBeschikbaar,
dtSums.Duration_Overdracht,
dtSums.Duration_Beheer
FROM ( --===Derived table finds the duration in the column Useraccount_Status_Duration and puts it in the desired column based on the ID in the column Useraccount_Status_ID.===
SELECT UA.Useraccount_First_Name,
UA.Useraccount_Last_Name,
US.Useraccount_Status_DateTime,
US1.Useraccount_Status_Duration AS Duration_UserLogin,
US2.Useraccount_Status_Duration AS Duration_PauzeKort,
US3.Useraccount_Status_Duration AS Duration_PauzeLang,
US4.Useraccount_Status_Duration AS Duration_Beschikbaar,
US5.Useraccount_Status_Duration AS Duration_Storing,
US6.Useraccount_Status_Duration AS Duration_Werkoverleg,
US7.Useraccount_Status_Duration AS Duration_NietBeschikbaar,
US8.Useraccount_Status_Duration AS Duration_Overdracht,
US9.Useraccount_Status_Duration AS Duration_Beheer
FROM Application_Useraccounts_Status AS US LEFT OUTER JOIN Application_Useraccounts AS UA
ON US.Useraccount_ID = UA.Useraccount_ID
LEFT OUTER JOIN Application_Useraccounts_Status AS US1
ON US1.Useraccount_ID = UA.Useraccount_ID AND US1.Useraccount_Status_ID = 1
LEFT OUTER JOIN Application_Useraccounts_Status AS US2
ON US1.Useraccount_ID = UA.Useraccount_ID AND US2.Useraccount_Status_ID = 2
LEFT OUTER JOIN Application_Useraccounts_Status AS US3
ON US1.Useraccount_ID = UA.Useraccount_ID AND US3.Useraccount_Status_ID = 3
LEFT OUTER JOIN Application_Useraccounts_Status AS US4
ON US1.Useraccount_ID = UA.Useraccount_ID AND US4.Useraccount_Status_ID = 4
LEFT OUTER JOIN Application_Useraccounts_Status AS US5
ON US1.Useraccount_ID = UA.Useraccount_ID AND US5.Useraccount_Status_ID = 9
LEFT OUTER JOIN Application_Useraccounts_Status AS US6
ON US1.Useraccount_ID = UA.Useraccount_ID AND US6.Useraccount_Status_ID = 10
LEFT OUTER JOIN Application_Useraccounts_Status AS US7
ON US1.Useraccount_ID = UA.Useraccount_ID AND US7.Useraccount_Status_ID = 11
LEFT OUTER JOIN Application_Useraccounts_Status AS US8
ON US1.Useraccount_ID = UA.Useraccount_ID AND US8.Useraccount_Status_ID = 12
LEFT OUTER JOIN Application_Useraccounts_Status AS US9
ON US1.Useraccount_ID = UA.Useraccount_ID AND US9.Useraccount_Status_ID = 13
) AS dtSums
Can someone tell what I'm doing wrong and where I have to correct the query to run it in a good way and that will give me the desired result?
Thank you very much for your help and assistance.
Greetz,
Geert
April 3, 2013 at 7:42 am
Does the SELECT without the INSERT output the correct result set?
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
April 3, 2013 at 7:55 am
The joins are all messed up. It runs forever because it's generating a partial cartesian product. Try this instead:
SELECT
u.Useraccount_First_Name,
u.Useraccount_Last_Name,
x.*
FROM Application_Useraccounts u -- "driving" table is now correct
CROSS APPLY (
SELECT
Useraccount_Status_DateTime = MAX(Useraccount_Status_DateTime),
Duration_UserLogin = MAX(CASE WHEN Useraccount_Status_ID = 1 THEN Useraccount_Status_Duration END),
Duration_PauzeKort = MAX(CASE WHEN Useraccount_Status_ID = 2 THEN Useraccount_Status_Duration END),
Duration_PauzeLang = MAX(CASE WHEN Useraccount_Status_ID = 3 THEN Useraccount_Status_Duration END),
Duration_Beschikbaar = MAX(CASE WHEN Useraccount_Status_ID = 4 THEN Useraccount_Status_Duration END),
Duration_Storing = MAX(CASE WHEN Useraccount_Status_ID = 9 THEN Useraccount_Status_Duration END),
Duration_Werkoverleg = MAX(CASE WHEN Useraccount_Status_ID = 10 THEN Useraccount_Status_Duration END),
Duration_NietBeschikbaar = MAX(CASE WHEN Useraccount_Status_ID = 11 THEN Useraccount_Status_Duration END),
Duration_Overdracht = MAX(CASE WHEN Useraccount_Status_ID = 12 THEN Useraccount_Status_Duration END),
Duration_Beheer = MAX(CASE WHEN Useraccount_Status_ID = 13 THEN Useraccount_Status_Duration END)
FROM Application_Useraccounts_Status s -- "child" table
WHERE s.Useraccount_ID = u.Useraccount_ID -- outer reference
AND Useraccount_Status_ID IN (1,2,3,4,9,10,11,12,13)
) x
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
April 3, 2013 at 8:14 am
Hi ChrisM@Work,
This works very well. Thank you very much!
Greetz,
Geert
April 3, 2013 at 8:19 am
Anytime, thanks for the feedback.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply