Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert into new table based on conditions


Insert into new table based on conditions

Author
Message
geert.de.vylder
geert.de.vylder
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 141
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8962 Visits: 19020
Does the SELECT without the INSERT output the correct result set?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8962 Visits: 19020
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
geert.de.vylder
geert.de.vylder
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 141
Hi ChrisM@Work,

This works very well. Thank you very much!

Greetz,

Geert
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8962 Visits: 19020
Anytime, thanks for the feedback.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search