asked on

Please help me FIX my SQL query

I am placing LINKS to the Query, the current result and the required result, since the width of the result is wide

This is the full query that I have written:

http://sale.compsx.com/Query/query.txt

When I run this, it returns:

http://sale.compsx.com/Query/queryresult.txt

But you will see under the column for "Principal_Procedure", that for the same "DOS" and "ARN", there are different "Principal_Procedure".
I need the result to look like this:

http://sale.compsx.com/Query/resultrequired.txt

where, if the ARN and DOS is the same, the second, third, fourth, fifth and sixth entry for "Principal_Procedure" moves on to the column "Procedure_1" and "Procedure_2" and so forth till procedure_5

NOTE: In the Database, currently no one has more that 6 Procedure Codes, therefore, max it will go to "Procedure_5" since Principal_Procedure is Procedure_0. In the query, for this particular report purpose, I have to go UPTO 20 Procedures, and am just keeping them blank.

dbeneit

there was because you have 2 procedure codes distincs than 'PI'.

and tr.billing_procedure_code <> 'PI'

you should test if you can restrict billing_procedure_code to the desire value

subhanali

ASKER

I am sorry, i don't comprehend your answer. Can you please repeat what you are trying to tell me?

dbeneit

excuse me I had click the links and I don't read the end of question... :-)

dbeneit

Ok, that was ok

subhanali

ASKER

Got it :)

Basically the query result is CORRECT. I want the result to look like the third link on the premise explained above.

I belive temporary tables will be involved here.

dbeneit

It 's more dificult,
You can join 20 tables or
the only way that i see you can do it is a very :-(((
I don't test it but you can test with 2 values..............

max(case when tr.billing_procedure_code is NULL or tr.billing_procedure_code = '' then ' '
else substring(tr.billing_procedure_code + space(5),1,5)) end as Principal_Procedure , ---> level 1
max(max (case when tr.billing_procedure_code is NULL or tr.billing_procedure_code = '' then ' '
else
case
when substring(tr.billing_procedure_code + space(5),1,5) =max(case when tr.billing_procedure_code is NULL or tr.billing_procedure_code = '' then ' ' else substring(tr.billing_procedure_code + space(5),1,5)) end then ' '

else substring(tr.billing_procedure_code + space(5),1,5)
end
end as Procedure_1, ............................. --- level 2
then you must to include group by all the fields including procedure_X

more and more code ......

subhanali

ASKER

Look at my previous question:

https://www.experts-exchange.com/questions/21410245/Need-Immediate-help-for-a-specific-QUERY-to-find-and-join-records.html

rafrancisco had answered it, and I had asked a scaled down version of my question. It is the same question with the FULL query, cause for some reason [I JUST STARTED WRITING IN SQL], I cannot integrate his solution to my full answer.

I HOPE he is currently also looking into helping me out.

subhanali

ASKER

Also please keep in mind, that there is nothing wrong with the actual QUERY. it is supposed to return the results the way I am getting them.

I need to have it manipulated, so it looks the way it is in 'resultrequired.txt'

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rafrancisco

Step #2:

SELECT A.*, (SELECT COUNT(*) FROM TmpTable1 B WHERE A.ARN = B.ARN AND A.IdentityColumn <= B.IdentityColumn) AS IdColumn
INTO TmpTable1
FROM TmpTable2 A

rafrancisco

Step #2 (Again):

SELECT A.*, (SELECT COUNT(*) FROM TmpTable1 B WHERE A.ARN = B.ARN AND A.IdentityColumn <= B.IdentityColumn) AS IdColumn
INTO TmpTable2
FROM TmpTable1 A

rafrancisco

Step #3:

SELECT Tbl1.FIN, ... <type all your other columns here>,
Tbl1.Principal_Procedure AS Principal_Procedure,
Tbl2.Principal_Procedure AS Procedure_1,
Tbl3.Principal_Procedure AS Procedure_2,
Tbl4.Principal_Procedure AS Procedure_3,
Tbl5.Principal_Procedure AS Procedure_4,
Tbl6.Principal_Procedure AS Procedure_5
(SELECT * FROM TmpTable2 WHERE IdColumn = 1) Tbl1
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 2) Tbl2
ON Tbl1.ARN = Tbl2.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 3) Tbl3
ON Tbl2.ARN = Tbl3.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 4) Tbl4
ON Tbl3.ARN = Tbl4.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 5) Tbl5
ON Tbl4.ARN = Tbl5.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 6) Tbl6
ON Tbl5.ARN = Tbl6.ARN

rafrancisco

That should do it.

subhanali

ASKER

In step3, I am getting the following error:

Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'Tbl1'.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'Tbl2'.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'Tbl3'.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near 'Tbl4'.
Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 'Tbl5'.
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near 'Tbl6'.

rafrancisco

Can you please your SQL statement.

subhanali

ASKER

Also in step 2, shouldn't there be some condition for DOS.

SInce we are trying to get the result, stating that if ARN and DOS is the same, then all the PROCEDURES should be in the same row

subhanali

ASKER

I tried it both ways, by adding all the columns and without adding all my columns

SELECT Tbl1.FIN,

Tbl1.Principal_Procedure AS Principal_Procedure,
Tbl2.Principal_Procedure AS Procedure_1,
Tbl3.Principal_Procedure AS Procedure_2,
Tbl4.Principal_Procedure AS Procedure_3,
Tbl5.Principal_Procedure AS Procedure_4,
Tbl6.Principal_Procedure AS Procedure_5
(SELECT * FROM TmpTable2 WHERE IdColumn = 1) Tbl1
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 2) Tbl2
ON Tbl1.ARN = Tbl2.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 3) Tbl3
ON Tbl2.ARN = Tbl3.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 4) Tbl4
ON Tbl3.ARN = Tbl4.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 5) Tbl5
ON Tbl4.ARN = Tbl5.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 6) Tbl6
ON Tbl5.ARN = Tbl6.ARN

rafrancisco

SELECT Tbl1.FIN,

Tbl1.Principal_Procedure AS Principal_Procedure,
Tbl2.Principal_Procedure AS Procedure_1,
Tbl3.Principal_Procedure AS Procedure_2,
Tbl4.Principal_Procedure AS Procedure_3,
Tbl5.Principal_Procedure AS Procedure_4,
Tbl6.Principal_Procedure AS Procedure_5
FROM
(SELECT * FROM TmpTable2 WHERE IdColumn = 1) Tbl1
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 2) Tbl2
ON Tbl1.ARN = Tbl2.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 3) Tbl3
ON Tbl2.ARN = Tbl3.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 4) Tbl4
ON Tbl3.ARN = Tbl4.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 5) Tbl5
ON Tbl4.ARN = Tbl5.ARN
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 6) Tbl6
ON Tbl5.ARN = Tbl6.ARN

rafrancisco

Step #2:

SELECT A.*, (SELECT COUNT(*) FROM TmpTable1 B WHERE A.ARN = B.ARN AND A.DOS = B.DOS AND A.IdentityColumn <= B.IdentityColumn) AS IdColumn
INTO TmpTable2
FROM TmpTable1 A

subhanali

ASKER

Ok, so in step 3, should I add DOS like this:

SELECT Tbl1.FIN,

Tbl1.Principal_Procedure AS Principal_Procedure,
Tbl2.Principal_Procedure AS Procedure_1,
Tbl3.Principal_Procedure AS Procedure_2,
Tbl4.Principal_Procedure AS Procedure_3,
Tbl5.Principal_Procedure AS Procedure_4,
Tbl6.Principal_Procedure AS Procedure_5
FROM
(SELECT * FROM TmpTable2 WHERE IdColumn = 1) Tbl1
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 2) Tbl2
ON Tbl1.ARN = Tbl2.ARN and Tbl1.DOS = Tbl2.DOS
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 3) Tbl3
ON Tbl2.ARN = Tbl3.ARN and Tbl2.DOS = Tbl3.DOS
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 4) Tbl4
ON Tbl3.ARN = Tbl4.ARN and Tbl3.DOS = Tbl4.DOS
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 5) Tbl5
ON Tbl4.ARN = Tbl5.ARN and Tbl4.DOS = Tbl5.DOS
INNER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 6) Tbl6
ON Tbl5.ARN = Tbl6.ARN and Tbl5.DOS = Tbl6.DOS

rafrancisco

Yes.

subhanali

ASKER

Ok, I am running that query, but it is returning ZERO rows

FIN Principal_Procedure Procedure_1 Procedure_2 Procedure_3 Procedure_4 Procedure_5
------ ------------------- ----------- ----------- ----------- ----------- -----------

(0 row(s) affected)

The table TmpTable2 is populated.
And If I add my columns back into step3, I get a bunch of erros on missing table references etc. but shouldn't just this query return the actual rows?

subhanali

ASKER

Tell me if this is right, cause after doing this, it works. The result set looks ok:

FROM
(SELECT * FROM TmpTable2 WHERE IdColumn = 1) Tbl1
LEFT OUTER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 2) Tbl2
ON Tbl1.ARN = Tbl2.ARN and Tbl1.DOS = Tbl2.DOS
LEFT OUTER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 3) Tbl3
ON Tbl2.ARN = Tbl3.ARN and Tbl2.DOS = Tbl3.DOS
LEFT OUTER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 4) Tbl4
ON Tbl3.ARN = Tbl4.ARN and Tbl3.DOS = Tbl4.DOS
LEFT OUTER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 5) Tbl5
ON Tbl4.ARN = Tbl5.ARN and Tbl4.DOS = Tbl5.DOS
LEFT OUTER JOIN
(SELECT * FROM TmpTable2 WHERE IdColumn = 6) Tbl6
ON Tbl5.ARN = Tbl6.ARN and Tbl5.DOS = Tbl6.DOS

rafrancisco

You are correct, it should be a LEFT OUTER JOIN instead of an INNER JOIN.

subhanali

ASKER

Thanks man,

You are a life saver. If I have any other questions, I will ring you again here.