Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Please help me FIX my SQL query

Posted on 2005-05-09
25
Medium Priority
?
238 Views
Last Modified: 2010-03-19
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.

0
Comment
Question by:subhanali
  • 11
  • 10
  • 4
25 Comments
 
LVL 9

Expert Comment

by:dbeneit
ID: 13964058
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
0
 

Author Comment

by:subhanali
ID: 13964064
I am sorry, i don't comprehend your answer. Can you please repeat what you are trying to tell me?
0
 
LVL 9

Expert Comment

by:dbeneit
ID: 13964066
excuse me I had click the links and I don't read the end of question... :-)
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 9

Expert Comment

by:dbeneit
ID: 13964076
Ok, that was ok

0
 

Author Comment

by:subhanali
ID: 13964085
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.
0
 
LVL 9

Expert Comment

by:dbeneit
ID: 13964181
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 ......  
0
 

Author Comment

by:subhanali
ID: 13964208
Look at my previous question:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21410245.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.
0
 

Author Comment

by:subhanali
ID: 13964228
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'
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13973295
Step 1:

Select '194781' as FIN,

substring(p.accountnum,1,12) as ARN,

substring(case when len(p.ssn)= 9 then p.ssn else '000000001' end,1,9) as SSN,

substring(case when len(p.zip_code) = 5 then p.zip_code else '99999' end,1,5) as ZIP_Code,
 
substring(case when p.dob is NULL or p.dob = '' then '99999999' else datename(year,p.dob)
+ case when len(Cast(datepart(mm,p.dob)as varchar))<2 then '0'+ Cast(datepart(mm,p.dob)as varchar) else Cast(datepart(mm,p.dob)as varchar) end
+ case when len(datename(dd,p.dob))< 2 then '0' + datename(dd,p.dob) else datename(dd,p.dob)end end,1,8) as DOB,

substring (case when p.sex <> 'M' and p.sex <> 'F' then 'U' else p.sex end,1,1)as SEX,
 
substring('99',1,2) as Race,

substring('99',1,2) as Ethnicity,

substring(case when hcfa.last_dos is NULL or hcfa.last_dos = '' then '99999999' else datename(year,hcfa.last_dos)
+ case when len(Cast(datepart(mm,hcfa.last_dos)as varchar))<2 then '0'+ Cast(datepart(mm,hcfa.last_dos)as varchar) else Cast(datepart(mm,hcfa.last_dos)as varchar) end
+ case when len(datename(dd,hcfa.last_dos))< 2 then '0' + datename(dd,hcfa.last_dos) else datename(dd,hcfa.last_dos)end end,1,8) as DOS,

substring('01',1,2) as Disposition_of_Patient,

Case cr.receiver_type when 'B' then 'WC'
when 'C' then 'MA'
when 'D' then 'MC'
else '00' end as Expected_Source_of_Payment,

case when hcfa.diag1 is NULL or hcfa.diag1 = ' ' then '       '
else substring(hcfa.diag1 + space(5),1,7) end as Principal_Diagnosis,

case when hcfa.diag2 is NULL or hcfa.diag1 = ' ' then '       ' else substring(hcfa.diag1 + space(5),1,7) end as DIAG_CODE_1,
case when hcfa.diag3 is NULL or hcfa.diag2 = ' ' then '       ' else substring(hcfa.diag2 + space(5),1,7) end as DIAG_CODE_2,
case when hcfa.diag4 is NULL or hcfa.diag3 = ' ' then '       ' else substring(hcfa.diag3 + space(5),1,7) end as DIAG_CODE_3,
'       ' as DIAG_CODE_4,
'       ' as DIAG_CODE_5,
'       ' as DIAG_CODE_6,
'       ' as DIAG_CODE_7,
'       ' as DIAG_CODE_8,
'       ' as DIAG_CODE_9,
'       ' as DIAG_CODE_10,
'       ' as DIAG_CODE_11,
'       ' as DIAG_CODE_12,
'       ' as DIAG_CODE_13,
'       ' as DIAG_CODE_14,
'       ' as DIAG_CODE_15,
'       ' as DIAG_CODE_16,
'       ' as DIAG_CODE_17,
'       ' as DIAG_CODE_18,
'       ' as DIAG_CODE_19,
'       ' as DIAG_CODE_20,
'       ' as DIAG_CODE_21,
'       ' as DIAG_CODE_22,
'       ' as DIAG_CODE_23,
'       ' as DIAG_CODE_24,

'       ' as Principal_E_Code,

'       ' as E_Code_1,
'       ' as E_Code_2,
'       ' as E_Code_3,
'       ' as E_Code_4,

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,

'     ' as Procedure_1,
'     ' as Procedure_2,
'     ' as Procedure_3,
'     ' as Procedure_4,
'     ' as Procedure_5,
'     ' as Procedure_6,
'     ' as Procedure_7,
'     ' as Procedure_8,
'     ' as Procedure_9,
'     ' as Procedure_10,
'     ' as Procedure_11,
'     ' as Procedure_12,
'     ' as Procedure_13,
'     ' as Procedure_14,
'     ' as Procedure_15,
'     ' as Procedure_16,
'     ' as Procedure_17,
'     ' as Procedure_18,
'     ' as Procedure_19,
'     ' as Procedure_20,

'          ' as National_Provider_Identifier_Number,

IDENTITY(INT,1,1) AS IdentityColumn

Into TmpTable1
From patient p, claim hcfa, carrier cr, transact tr
Where p.id_pati = hcfa.id_patient
and cr.code = hcfa.code_primary_carrier
and tr.billing_procedure_code <> 'PI'
and hcfa.id_clai = tr.id_claim
and hcfa.last_dos > '2004-12-31'
and hcfa.last_dos < '2005-01-05'
order by DOS

0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13973321
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
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13973347
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
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13973373
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
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13973374
That should do it.
0
 

Author Comment

by:subhanali
ID: 13973447
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'.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13973462
Can you please your SQL statement.
0
 

Author Comment

by:subhanali
ID: 13973465
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
0
 

Author Comment

by:subhanali
ID: 13973472
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
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13973473
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
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13973486
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
0
 

Author Comment

by:subhanali
ID: 13973518
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
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13973522
Yes.
0
 

Author Comment

by:subhanali
ID: 13973623
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?
0
 

Author Comment

by:subhanali
ID: 13973764
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
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13974174
You are correct, it should be a LEFT OUTER JOIN instead of an INNER JOIN.
0
 

Author Comment

by:subhanali
ID: 13974185
Thanks man,

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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question