March 25, 2011 at 5:10 am
Hi
I have a query that i need to produce that looks at codes and their positions.
This is the my sample data.
DATA SAMPLE TABLE
drop table PS_TestForOnline
CREATE TABLE PS_TestForOnline
(
rowkey int,
crn varchar (10),
admission VARCHAR(5),
diag VARCHAR(5) ,
position int,
);
INSERT INTO PS_TestForOnline
VALUES('1','12345','1','A12.3','1' );
INSERT INTO PS_TestForOnline
VALUES('2','12345','1','B12.3','2' );
INSERT INTO PS_TestForOnline
VALUES('3','12345','1','C12.3','3' );
INSERT INTO PS_TestForOnline
VALUES('4','12345','1','D12.3','4' );
INSERT INTO PS_TestForOnline
VALUES('5','54321','2','A12.3','1' );
INSERT INTO PS_TestForOnline
VALUES('6','54321','2','D12.3','2' );
INSERT INTO PS_TestForOnline
VALUES('7','55555','1','A12.3','1' );
INSERT INTO PS_TestForOnline
VALUES('8','55555','1','E12.3','2' );
select * from PS_TestForOnline
from this table i need to identify any crn and admission where the diag code = 'A12.3' but in position '1' AND diag code D12.3 must exist in any other row where position != '1'.
I also need to display the results in a single row as per expect_results table below.
I would only expect to see results from the smaple data above for rowkey 1 and 6 .
The results need to be displayed as per PS_TestForOnline_Answer Table below.
EXPECTED RESULTS
CREATE TABLE PS_TestForOnline_Answer
(
rowkey int,
crn varchar (10),
admission VARCHAR(5),
diag VARCHAR(5) ,
position int,
diag2 VARCHAR(5) ,
position2 int,
);
INSERT INTO PS_TestForOnline_Answer
VALUES('1','12345','1','A12.3','1','D12.3','4' );
INSERT INTO PS_TestForOnline_Answer
VALUES('6','12345','1','A12.3','1','D12.3','2' );
select * from PS_TestForOnline_Answer
Thanks in advance for any help and/or guidance.
March 25, 2011 at 7:16 am
How's this?
WITH cte AS
(
SELECT *
FROM PS_TestForOnline
WHERE diag = 'D12.3'
AND position > 1
)
SELECT t1.*,
diag2 = cte.diag,
position2 = cte.position
FROM PS_TestForOnline t1
JOIN cte
ON t1.crn = cte.crn
AND t1.position = 1
AND t1.diag = 'A12.3';
Edit: added A12.3 diag
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 25, 2011 at 7:24 am
The CTE version will work. My first instinct was a simple Inner Join between two iterations of the table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 25, 2011 at 7:34 am
GSquared (3/25/2011)
The CTE version will work. My first instinct was a simple Inner Join between two iterations of the table.
Like so...
SELECT t1.*,
diag2 = t2.diag,
position2 = t2.position
FROM PS_TestForOnline t1
JOIN PS_TestForOnline t2
ON t1.crn = t2.crn
AND t1.position = 1
AND t1.diag = 'A12.3'
AND t2.diag = 'D12.3'
AND t2.position > 1;
Which gives the same execution plan as the first.
Adding a unique index on "diag, POSITION, crn" that includes the admission and rowkey columns will provide nice index seeks.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 25, 2011 at 7:58 am
Thanks a lot for that. great help. Starting to get my head round all this with the help of this site.:-)
Just 1 more quick question .....
How would i go about adding in more than 1 clause as you dont have a WHERE clause in your script.
for example if i wanted to show all records....
where
(t1.diag1 = 'x' and t2.daig = 'y')
or
(t1.diag1 = 'a' and t2.daig = 'b')
or
(t1.diag1 = 'c' and t2.daig = 'd')
as a single results table.
Thanks in advance
or
March 25, 2011 at 8:35 am
The simplest solution would to perform a union on three separate queries so long as you only have a handful of criteria.
If criteria are likely to expand then this wouldt be scalable nless you wanted loads of unions
Alternatively a reference table containing allowable combinations of what must come first and what can follow.
Along lines of
{A12.3, D23.4}
{a,b}
{x,y}
{c,d}
and so on.
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
March 25, 2011 at 8:47 am
The codes selection will remain pretty static, i may need to add in a date parameter though.
My main prioirty is to be able to get the 4 parameter groups in one script to then be able to produce a report through Reporting Services.
Thanks
March 25, 2011 at 8:49 am
Put the combos in a table, join the table to itself via that, you'll have a solid, extensible solution that can easily accommodate more combos if/when business rules change, without having to rewrite a single line of code.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 25, 2011 at 9:24 am
Hi
I've tried the union route which should now return an extra row of data from my sample table but I've never tried doing a union on queries before and am slightly confused as there is no WHERE clause in these queries.
Could someone point out the error of my ways please . Many Thanks in advance
This is what i'm trying.
SELECT
*
FROM
(
SELECT t1.*,
diag2 = t2.diag,
position2 = t2.position
FROM (PS_TestForOnline t1
JOIN PS_TestForOnline t2
ON t1.crn = t2.crn
and t1.admission = t2.admission
AND t1.position = 1
AND t1.diag = 'A12.3'
AND t2.diag = 'D12.3'
AND t2.position > 1
)
union
(
SELECT t1.*,
diag2 = t2.diag,
position2 = t2.position
FROM (PS_TestForOnline t1
JOIN PS_TestForOnline t2
ON t1.crn = t2.crn
and t1.admission = t2.admission
AND t1.position = 1
AND t1.diag = 'A12.3'
AND t2.diag = 'E12.3'
AND t2.position > 1
)
March 25, 2011 at 9:38 am
Don't do the unions thing. It's a pain to modify/extend/maintain.
create table dbo.Combos (
Diag1 char(10) not null,
Diag2 char(10) not null,
primary key (Diag1, Diag2));
GO
insert into dbo.Combos... -- put the valid Diag combos in here
GO
SELECT t1.*,
diag2 = t2.diag,
position2 = t2.position
FROM PS_TestForOnline t1
JOIN Combos
ON t1.diag = Combos.Diag1
JOIN PS_TestForOnline t2
ON t1.crn = t2.crn
AND t2.diag = Combos.Diag2
AND t1.position = 1
AND t2.position > 1;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 25, 2011 at 9:44 am
Further to the reference table see if this meets your needs.
Note: The AllowableCombinations would require maintaining, however no more code would have to be written.
Note: I'd also suggest making Diag char(5), UNLESS they do vary in length or indeed it's too late to change the model 🙂
CREATE TABLE dbo.AllowableDiagCombinations
(
Diag1 varchar(5)
, Diag2 varchar(5)
)
INSERT INTO dbo.AllowableDiagCombinations VALUES ('A12.3', 'D12.3')
-- Then the query itself
SELECT t1.rowkey, t1.crn, t1.admission, t1.diag, t1.position
, t2.diag AS diag2, t2.position AS position2
FROM PS_TestForOnline AS t1
INNER JOIN PS_TestForOnline AS t2
ON t1.crn = t2.crn
INNER JOIN AllowableDiagCombinations AS adc
ON t1.diag = adc.diag1
AND t2.diag = adc.diag2
WHERE t1.position = 1
AND t2.position > 1
edit. I seem to be repeating GSquared today, I'll hang up now 🙂
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
March 25, 2011 at 9:46 am
p.stevens76 (3/25/2011)
HiI've tried the union route which should now return an extra row of data from my sample table but I've never tried doing a union on queries before and am slightly confused as there is no WHERE clause in these queries.
Could someone point out the error of my ways please . Many Thanks in advance
This is what i'm trying.
SELECT
*
FROM
(
SELECT t1.*,
diag2 = t2.diag,
position2 = t2.position
FROM (PS_TestForOnline t1
JOIN PS_TestForOnline t2
ON t1.crn = t2.crn
and t1.admission = t2.admission
AND t1.position = 1
AND t1.diag = 'A12.3'
AND t2.diag = 'D12.3'
AND t2.position > 1
)
union
(
SELECT t1.*,
diag2 = t2.diag,
position2 = t2.position
FROM (PS_TestForOnline t1
JOIN PS_TestForOnline t2
ON t1.crn = t2.crn
and t1.admission = t2.admission
AND t1.position = 1
AND t1.diag = 'A12.3'
AND t2.diag = 'E12.3'
AND t2.position > 1
)
No need for the brackets all over the place, nor placing each query as a subquery.
Union combines the results of multiple queries into a single output.
SELECT t1.*,
diag2 = t2.diag,
position2 = t2.position
FROM PS_TestForOnline t1
JOIN PS_TestForOnline t2
ON t1.crn = t2.crn
and t1.admission = t2.admission
AND t1.position = 1
AND t1.diag = 'A12.3'
AND t2.diag = 'D12.3'
AND t2.position > 1
union all
SELECT t1.*,
diag2 = t2.diag,
position2 = t2.position
FROM PS_TestForOnline t1
JOIN PS_TestForOnline t2
ON t1.crn = t2.crn
and t1.admission = t2.admission
AND t1.position = 1
AND t1.diag = 'A12.3'
AND t2.diag = 'E12.3'
AND t2.position > 1
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
March 25, 2011 at 9:54 am
Old Hand has it, just the job.
Thanks a million. :-):-):-)
I can see why you would maybe use a reftable of codes shoudldthe job require it, but as the codes will remain static then I will use the UNION route.
Once again many thanks to all that assisted.
I can assure you I will be back, probably sooner rather that later.;-)
March 25, 2011 at 10:03 am
I'd still stick the join table method, even if you assume the codes will be static for the rest of eternity. It's a better design practice, and easier to document. If you look back at it a year from now, or if you move on and the next guy has to look at it, it'll make more sense and be easier to deal with. It'll also almost certainly have a simpler execution plan, which means less server resources to resolve it, which is a good thing for future-proofing performance as well as maintainability.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply