subhanali
asked on
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
substring(cast(datepart(mm
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
select patient.accountnum as Account,
transact.billing_procedure
substring(cast(datepart(mm
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
>> 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.
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.
ASKER
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?
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?
ASKER
OH WAIT,
I figured it out......
I figured it out......
ASKER
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.
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.
ASKER
Ok,
I have posted the other question as:
'Please help me FIX my SQL query'
I have posted the other question as:
'Please help me FIX my SQL query'
ASKER
Here is a link to my new question
https://www.experts-exchange.com/questions/21418207/Please-help-me-FIX-my-SQL-query.html
https://www.experts-exchange.com/questions/21418207/Please-help-me-FIX-my-SQL-query.html
subhanali, I will help you with your new question tomorrow.
ASKER
Oh thank you Dude.
ASKER
rafrancisco, any luck on that query?
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.