• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

Need Immediate help for a specific QUERY to find and join records

Here is the QUERY that I am running:

select patient.accountnum as Account,
transact.billing_procedure_code as CPT,
substring(cast(datepart(mm,claim.last_dos)as varchar)+'-'+datename(dd,claim.last_dos)+'-'+datename(year,claim.last_dos),1,10) as Date

from transact, claim, patient

where transact.id_claim = claim.id_clai
and claim.id_patient = patient.id_pati
and transact.dos > '2004-12-31'
and transact.dos < '2005-01-07'


Here is the result set of the query which I am throwing in a TXT file:

Account       CPT      Date      
------------- -------- ----------
74            94760    1-4-2005
74            00630    1-4-2005
155           64476    1-4-2005
155           64476    1-4-2005
155           64476    1-4-2005
155           64476    1-4-2005
158           94760    1-4-2005
158           00630    1-4-2005
152           94760    1-4-2005
152           00600    1-4-2005
149           94760    1-4-2005
149           00630    1-4-2005

I would like to add something in the query which allows me to throw this result in a TEMP table, then do a specific query on it which checks for the following:

If Account and Date are the same, then find all the CPT's and list them as CPT1 CPT2 CPT3 respectively. So in tihs case, this result should look like this. REMEBER the result below does not exist, i need to have my query throw this result, which I will send to a TEXT file, so then I can run this report for the WHOLE year. Right now you will notice it is on a very short time period

Account       CPT1    CPT2     CPT3       CPT4          Date      
------------- --------  --------   --------    ---------     ----------
74            94760     00630                                  1-4-2005
155           64476    64476    64476      64476       1-4-2005
158           94760    00630                                  1-4-2005
152           94760    00600                                  1-4-2005
149           94760    00630                                  1-4-2005

I would really appreciate someone who can write this query specifically for this particular example
0
subhanali
Asked:
subhanali
  • 9
  • 3
1 Solution
 
Anthony PerkinsCommented:
Since the number of CPT's is dynamic it is not trivial.  I suggest you take a look at these links:
http://www.sqlteam.com/item.asp?ItemID=2955
http://www.itrain.de/knowhow/sql/tsql/pivot/pivotsample.asp

And see if you can modify them for your requirements.
0
 
rafranciscoCommented:
Here's what you can do.

Step 1: Insert into a temp table the result of your query above adding an identity column

select patient.accountnum as Account,
transact.billing_procedure_code as CPT,
substring(cast(datepart(mm,claim.last_dos)as varchar)+'-'+datename(dd,claim.last_dos)+'-'+datename(year,claim.last_dos),1,10) as Date,
IDENTITY(INT,1,1) AS IdentityColumn

INTO YourNewTable1
from transact, claim, patient

where transact.id_claim = claim.id_clai
and claim.id_patient = patient.id_pati
and transact.dos > '2004-12-31'
and transact.dos < '2005-01-07'

Step 2: Create another temp table to assign a number to Account/Date group starting from 1.

SELECT A.*, (SELECT COUNT(*) FROM YourNewTable1 B WHERE A.Account = B.Account AND A.Date = B.Date AND A.IdentityColumn <= B.IdentityColumn) AS IdColumn
INTO YourNewTable2
FROM YourNewTable1 A

Step 3: Produce your output table:

SELECT CPT1.Account, CPT1.CPT AS CPT1, CPT2.CPT AS CPT2, CPT3.CPT AS CPT3, CPT4.CPT AS CPT4, CPT1.Date
FROM
(SELECT Account, CPT, Date FROM YourNewTable2 WHERE IdColumn = 1) AS CPT1
LEFT OUTER JOIN
(SELECT Account, CPT, Date FROM YourNewTable2 WHERE IdColumn = 2) AS CPT2
ON CPT1.Account = CPT2.Account AND CPT1.Date = CPT2.Date
LEFT OUTER JOIN
(SELECT Account, CPT, Date FROM YourNewTable2 WHERE IdColumn = 3) AS CPT3
ON CPT1.Account = CPT3.Account AND CPT1.Date = CPT3.Date
LEFT OUTER JOIN
(SELECT Account, CPT, Date FROM YourNewTable2 WHERE IdColumn = 4) AS CPT4
ON CPT1.Account = CPT4.Account AND CPT1.Date = CPT4.Date

Hope this helps.
0
 
subhanaliAuthor Commented:
rafransico,

your query works for the first time, but now how do i make sure that these temp table get deleted after this query is run. Since I have to run it again and again several times for different date spans.

Also, in my actual query there are 20 CPT codes, this was an example, so I guess if I follow your logic, I can do this.

I have just started writing SQL queries 2 weeks ago. Therefore, please excuse my lack of knowledge.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
subhanaliAuthor Commented:
Also i have to make sure that none of the columns for CPT have NULL in them. If nothing is found, then I need them to return Exactly 5 spaces.
0
 
rafranciscoCommented:
>>  but now how do i make sure that these temp table get deleted after this query is run <<

After your query is run, just execute a drop table statement for the temp table:

DROP TABLE YourNewTable1
DROP TABLE YourNewTable2

>> Also i have to make sure that none of the columns for CPT have NULL in them. If nothing is found, then I need them to return Exactly 5 spaces <<

SELECT CPT1.Account, ISNULL(CPT1.CPT, '     ') AS CPT1, ISNULL(CPT2.CPT, '     ') AS CPT2, ISNULL(CPT3.CPT, '     ') AS CPT3, ISNULL(CPT4.CPT, '     ') AS CPT4, CPT1.Date

>> Also, in my actual query there are 20 CPT codes, this was an example, so I guess if I follow your logic, I can do this. <<

Yes you can do this.  Just follow the logic.
0
 
subhanaliAuthor Commented:
Ok thats great,

Now I want to add all this to my BIG QUERY, so the result I am getting in the end, I want to place that into a table. Here is what I was trying to do.

Near your step 3, in the FIRST LINE:

SELECT INTO MIRCAL_HCFA_TEMP CPT1.Account, CPT1.CPT AS CPT1, CPT2.CPT AS CPT2, CPT3.CPT AS CPT3, CPT4.CPT AS CPT4, CPT1.Date

So I am adding "INTO MIRCAL_HCFA_TEMP"

I don't think that is the right place to add that. Where can I do this?
0
 
subhanaliAuthor Commented:
OH WAIT,

I figured it out......
0
 
subhanaliAuthor Commented:
rafrancisco,

This is really helpful, but I cannot seem to integrate it into my actual query. For this question's purpose you have answered it, therefore let me select accept here.

I am going to repost this question with the FULL SCALE actual query. Please take a look at that, and re-modify it to help me out. Will really appreciate it.
0
 
subhanaliAuthor Commented:
Ok,

I have posted the other question as:

'Please help me FIX my SQL query'

0
 
subhanaliAuthor Commented:
0
 
rafranciscoCommented:
subhanali, I will help you with your new question tomorrow.
0
 
subhanaliAuthor Commented:
Oh thank you Dude.
0
 
subhanaliAuthor Commented:
rafrancisco, any luck on that query?
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now