Complicated SQL Query for me - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complicated SQL Query for me

Author

Topic

ZITZAT
Starting Member

6 Posts

Posted - 2012-05-01 : 15:24:54

I've made some snapshots by puttin the data on excel for the tables. Below is my problem statement:

Trying to write an SQL query to obtain result in the below format:

Not even close to the solution. Need Expert advise.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 15:44:33

[code]
SELECT t1.File_ID,t1.Emp_Name,t1.Shift_Date,t1.Status AS REWORK,
STUFF(t2.val,1,1,'') AS Phase_Summary
FROM Tbl1 t1
CROSS APPLY (SELECT ',' + ERROR_IN_PHASE + '('+ CAST(COUNT(*) AS varchar(10)) + ')' AS [text()]
FROM Tbl2
WHERE File_ID = t1.File_ID
GROUP BY ERROR_IN_PHASE
FOR XML PATH('')
)t2(val)
[/code]

can you explain rules for getting other field values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ZITZAT
Starting Member

6 Posts

Posted - 2012-05-01 : 16:20:17

Thanks visakh16 for responding.

Please do consider that I am very new to SQL. I've not been able to relate to query you provided.

Issue Description: This is a business that receives various files. Each File is Processes in Phases. File can start at any Phase, New phases can be added later to the entire exercise. A Phase can repeat several times and can also repeat immediately after itself.

Each File is then judged for correctness in Tbl2 and accordingly status is updated in Tbl1 as Correct or Error. In Case of Error rework is called out.

During the file audit, data for Tbl2 is prepared in the same manner as shown. If a comment is marked under Error_1 to Error_8 the status is appened as Error on Tbl1. These errors are types of errors and can be of maximum 8 types.

In the output Tbl,
1) File_ID : File_ID of Tbl1 & Tbl2: used as unique key
2) emp_name: emp_name of Tbl1
3) Shift_Date: Shift_Date of Tbl1
4) REWORK: REWORK required Y/N or even Status Correct/Error will do.
5) Phase_Summary: Unique Phases for the file along with the Occurence seperate by comma. Phase Name [Occerance]. example in Output Tbl
6) FIRST_PHASE_REWORK: PHASE Where an error was marked in either of the columns Error 1 - Error 8 in Tbl2.
7) FIRST_PHASE_Error: Associated Error/(s) for 6 seperated by comma in case of multiple. these may occur in different rows.

8) 9) 10) 11) repeats of 6 & 7. Only upto 3 Errors needs to be considered.

I know the feeling you have after reading this. I wish I could kill the person who designed the process in this fashion in the first place, that should be a good start. :) Thanks for all the help/guidance.

Please see, I also need some help to understand the query that you wrote. Like I said, I am new however do need to understand what i am doing.

ZITZAT
Starting Member

6 Posts

Posted - 2012-05-02 : 13:42:02

Any Solutions anyone..Please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-02 : 20:21:10

[code]
SELECT ROW_NUMBER() OVER (PARTITION BY File_ID ORDER BY ERROR_IN_PHASE ASC) AS Rn,File_ID,ERROR_IN_PHASE ,COUNT(*) AS Cnt
INTO #Temp1
FROM Tbl2
GROUP BY File_ID,ERROR_IN_PHASE

SELECT t2.*,ROW_NUMBER() OVER (PARTITION BY File_ID,ERROR_IN_PHASE ORDER BY Error_Desc) AS Rn
INTO #temp2
FROM @Tbl2 t2
UNPIVOT (Err_Comment FOR Error_Desc IN ([ERROR_1],[ERROR_2],[ERROR_3],[ERROR_4]....[ERROR_9]))u

SELECT t1.File_ID,t1.Emp_Name,t1.Shift_Date,t1.Status AS REWORK,
STUFF((SELECT ',' + ERROR_IN_PHASE + '(' + CAST(Cnt AS Varchar(10)) + ')'
FROM #Temp1 WHERE File_ID = t1.File_ID
ORDER BY ERROR_IN_PHASE
FOR XML PATH('')),1,1,'') AS Phase_Summary,
tmp.FIRST_PHASE_REWORK,
STUFF((SELECT TOP 3 ',' + Err_Comment
FROM #Temp2
WHERE File_ID = t1.File_ID
AND ERROR_IN_PHASE = tmp.FIRST_PHASE_REWORK
ORDER BY Rn ASC
FOR XML PATH('')),1,1,'') AS Related_Error_Phase1,
tmp.SECOND_PHASE_REWORK,
STUFF((SELECT TOP 3 ',' + Err_Comment
FROM #Temp2
WHERE File_ID = t1.File_ID
AND ERROR_IN_PHASE = tmp.SECOND_PHASE_REWORK
ORDER BY Rn ASC
FOR XML PATH('')),1,1,'') AS Related_Error_Phase2,
tmp.THIRD_PHASE_REWORK,
STUFF((SELECT TOP 3 ',' + Err_Comment
FROM #Temp2
WHERE File_ID = t1.File_ID
AND ERROR_IN_PHASE = tmp.THIRD_PHASE_REWORK
ORDER BY Rn ASC
FOR XML PATH('')),1,1,'') AS RELATED_ERROR_PHASE3
FROM Tbl1 t1
INNER JOIN (SELECT File_ID,
MAX(CASE WHEN Rn=1 THEN ERROR_IN_PHASE END) AS FIRST_PHASE_REWORK,
MAX(CASE WHEN Rn=2 THEN ERROR_IN_PHASE END) AS SECOND_PHASE_REWORK,
MAX(CASE WHEN Rn=3 THEN ERROR_IN_PHASE END) AS THIRD_PHASE_REWORK
FROM #Temp1
GROUP BY File_ID
)tmp
ON tmp.File_ID = t1.File_ID

DROP TABLE #Temp1
DROP TABLE #Temp2
[/code]

to understand the concepts see

http://pratchev.blogspot.com/2008/03/row-concatenation-with-for-xml-path.html

http://weblogs.sqlteam.com/jeffs/archive/2008/04/23/unpivot.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources