Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert into new table based on conditions Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 6:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
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
Post #1438299
Posted Wednesday, April 3, 2013 7:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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
Post #1438332
Posted Wednesday, April 3, 2013 7:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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
Post #1438349
Posted Wednesday, April 3, 2013 8:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, Visits: 141
Hi ChrisM@Work,

This works very well. Thank you very much!

Greetz,

Geert
Post #1438361
Posted Wednesday, April 3, 2013 8:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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
Post #1438366
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse