LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » learning T SQL

learning T SQL

Options

Previous Topic · Next Topic

harleydavidson1968@live.com

Posted: Saturday, July 17, 2010 10:22:48 PM

Rank: Newbie

Joined: 7/17/2010
Posts: 2
Points: 6
Where do you live?: Bowling Green

I am working on this T SQL question. Can you guys help me with the answers? And no I am not cheating on a test...I've been out of school for quit awhile. LOL

The database contains a simplified healthcare claim data model consisting of three tables:
1. MedicalClaimHeader: Contains 1 record (claim) per medical encounter
2. MedicalProcedure: Contains 0 or more records for each procedure performed during a medical encounter
3. MedicalDiagnosis: Contains 0 or more records for each diagnosis made during a medial encounter.
The MedicalClaimheader table is related to the MedicalProcedure and MedicalDiagnosis tables by the ClaimNumber field.
Write a T-SQL query that finds every claim where a procedure 8694 was performed, without a diagnosis of 4019.
Answer:

SELECT medicalclaimheader.claimnumber,
medicaldiagnosis
FROM medicalclaimheaders
JOIN Claimnumber
ON ( medicalclaimheader.claimnumber = medicalprocedure.claimnumber =
medicaldiagnosis.claimnumber )
WHERE medicalprocedure = 8694
AND companyname NOT LIKE 4019

GO

Question 2 (SQL)
The database contains a simplified healthcare claim data model consisting of two tables:
1. MedicalClaimHeader: Contains 1 record (claim) per medical encounter. The table contains the following columns;
a. MemberNumber
b. ClaimNumber

2. MedicalClaimDetail: Contains 0 or more records for each procedure made during a medial encounter. The table contains the following columns;
a. ClaimNumber
b. Procedure
c. ProcedureDate
d. LineNumber
The MedicalClaimDetail table is related to the MedicalClaimHeader table by the ClaimNumber field.

Write a T-SQL query that finds every MedicalClaimDetail row where a procedure J1234 was performed multiple times on the same day for the same member.
Answer:
SELECT medicalclaimheader.claimnumber,

FROM suppliers
JOIN products
ON ( medicalclaimheader.ClaimNumber = medicaldetail.ClaimNumber )
WHERE MedicalClaimDetail = J1234
AND ProcedureDate = ProcedureDate

COUNT(ProcedureDate) AS NumOccurrences
GROUP BY ProcedureDate
HAVING ( COUNT(NumOccurrences) > 1 )

GO

Scott Whigham

Posted: Sunday, July 18, 2010 1:56:30 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

I'm confused - are those your proposed answers and you just want us to tell you "Good job!" or are you having trouble? I'm not quite clear of what you want but, if you'll clarify a bit, I think we can help.

harleydavidson1968@live.com

Posted: Sunday, July 18, 2010 3:17:44 PM

Rank: Newbie

Joined: 7/17/2010
Posts: 2
Points: 6
Where do you live?: Bowling Green

Scott Whigham wrote:

Sorry I wasn't clear...I wanted someone that had experience with T SQL tell me if my code was right or at least close. I am still learning and my book only has exercises but doesn't have answers. Frown

I am just using a SQL Formatter so I can't run it.

Mark

Scott Whigham

Posted: Sunday, July 18, 2010 5:27:21 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

harleydavidson1968@live.com wrote:

SELECT medicalclaimheader.claimnumber,
medicaldiagnosis
FROM medicalclaimheaders
JOIN Claimnumber
ON ( medicalclaimheader.claimnumber = medicalprocedure.claimnumber =
medicaldiagnosis.claimnumber )
WHERE medicalprocedure = 8694
AND companyname NOT LIKE 4019

This is all messed up - the JOIN condition is off (don't daisy-chain conditions; use AND or OR instead) and the "companyname NOT LIKE 4019" doesn't make any sense given the query.

harleydavidson1968@live.com wrote:

SELECT medicalclaimheader.claimnumber,
FROM suppliers
JOIN products
ON ( medicalclaimheader.ClaimNumber = medicaldetail.ClaimNumber )
WHERE MedicalClaimDetail = J1234
AND ProcedureDate = ProcedureDate

COUNT(ProcedureDate) AS NumOccurrences
GROUP BY ProcedureDate
HAVING ( COUNT(NumOccurrences) > 1 )

The JOIN condition is correct in this but the WHERE clause is odd. "ProcedureDate = ProcedureDate" might as well say "1 = 1". And this did not pass a SQL parser - the "COUNT(ProcedureDate) AS NumOccurrences " is misplaced. Other than that, the query seems okay.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.