I need to create a report that does not output to table view but list view and have ID, physician, procedure, location and then group by category of the items in lists. There are multiple ID's for same physician. Example:
Card ID: 2096
Physician Name: DOE, JOHN
Procedure: SHOULDER ARTHROSCOPY MINI OPEN ROTATOR CUFF
Facility: EMH MAIN OR
BASIC SUPPLIES:
12680 GLOVE BIOGEL 8 1/2 1 1 0
10846 GLOVE TRIFLEX SIZE 8.5 1 1 0
10007 DURA-PREP 1 1 0
INSTRUMENTS:
TR10304 ARTHROSCOPE SHOULDER INSTRUMENT TRAY 1 1 0
PK10319 ARTHROSCOPIC RASP 1 1 0
PK02100 NEEDLEHOLDER LONG 1 1 0
PK10031 RASP CURVED NASAL 1 0 1
TR10636 TRAY VERSALOCK 1 0 1
TR10378 DEPUY SHOULDER RETRACTOR (HAWKINS-BELL) 1 0 1
SPECIALTY SUPPLIES:
CT10574 BASKET SHOULDER ARTHROSCOPY HOLD 1 0 1
19637 CANNULA CAPS LOCK 55MM 1 0 1
27947 FACE MASK-DISP #7429 (TENET BEACH CHAIR) 1 1 0
12683 LINVATEC ARTHROSCOPY TUBING 10K150 1 1 0
27001 ANCHOR DUET SUTURE 1 0 1
MISC. SUPPLIES:
10181 STERI STRIP 1/2" 1 1 0
28840 COVEROLL 6" X 2 YDS-MEDIPORE 3M 1 1 0
IRRIGATIONS/SOLUTIONS:
25636 SALINE 3000CC IRRIGATION BAG 3 3 0
SUTURE:
14537 SUTURE VICRYL 4-0 27" FS-2 1 1 0
Card ID: 2555
Physician Name: SMITH, JOHN
Procedure: ANKLE OPEN REDUCTION INTERNAL FIXATION
Facility: EMH MAIN OR
BASIC SUPPLIES:
10007 DURA-PREP 1 1 0
10060 BLADE SURGICAL #15 2 2 0
44982 PACK ARTHROSCOPY W\OUT POUCH SOP63ARSTB 1 1 0
10846 GLOVE TRIFLEX SIZE 8.5 1 1 0
12680 GLOVE BIOGEL 8 1/2 1 1 0
INSTRUMENTS:
TR10231 SYNTHES 4.0 CANNULATED SCREW SET * 1 0 1
TR10065 SYNTHES SMALL FRAGMENT TRAY * 1 1 0
TR10050 ORTHO TRAY-MINOR 1 1 0
SPECIALTY SUPPLIES:
32394 TOURNIQUET CUFF 34" STERILE SINGLE PORT 1 1 0
21005 DRAPE PREMIER MINI-C-ARM 1 1 0
MISC. SUPPLIES:
11639 BANDAGE COBAN 4IN STERILE 1 1 0
20606 WEBRIL 4" UNSTERILE 1 1 0
please provide your data as follows for example
create table #data(CardId int , PhysicianName varchar(150), Procedure varchar(150), Facility varchar(150))
insert into #data
select 2096, 'DOE, JOHN', 'SHOULDER ARTHROSCOPY MINI OPEN ROTATOR CUFF', 'EMH MAIN OR' union
select
etc
Here is a sample file of data.
I guess it was stripped out by firewall.
CardId PhysicianName ProcedureName Facility
1 MOUSE JR MD, CLINTON W Lap Cholecystectomy : ORLAPCHOLE 1
2 MOUSE JR MD, CLINTON W Subcutaneous intravenous device insertio : SQID INSER 1
3 MOUSE JR MD, CLINTON W Pacemaker Placement : PACEMAKER 1
4 MOUSE JR MD, CLINTON W Sigmoid Colon Resection : SIGCOLRE 1
5 MOUSE JR MD, CLINTON W Appendectomy : APPENDEC 1
6 MOUSE JR MD, CLINTON W Cholecystectomy : CHOLECYS 1
7 MOUSE JR MD, CLINTON W Hemorrhoidectomy : HEMORRHO 1
8 GOOFY, REYNALDO D Lap Cholecystectomy : ORLAPCHOLE 1
9 MINI MD, DAVID ALAN ACL Reconstruction : ACLRECON 1
10 MOUSE JR MD, CLINTON W Inguinal Hernia : INGHERNI 1
ok now do the following please?
--DDL
create table #data(CardId int , PhysicianName varchar(150), Procedure varchar(150), Facility varchar(150))
--DML
insert into #data
Same as before, except with real data?
CardId PhysicianName ProcedureName Facility
1 MOUSE JR MD, CLINTON W Lap Cholecystectomy : ORLAPCHOLE 1
2 MOUSE JR MD, CLINTON W Subcutaneous intravenous device insertio : SQID INSER 1
3 MOUSE JR MD, CLINTON W Pacemaker Placement : PACEMAKER 1
4 MOUSE JR MD, CLINTON W Sigmoid Colon Resection : SIGCOLRE 1
5 MOUSE JR MD, CLINTON W Appendectomy : APPENDEC 1
6 MOUSE JR MD, CLINTON W Cholecystectomy : CHOLECYS 1
7 MOUSE JR MD, CLINTON W Hemorrhoidectomy : HEMORRHO 1
8 GOOFY, REYNALDO D Lap Cholecystectomy : ORLAPCHOLE 1
9 MINI MD, DAVID ALAN ACL Reconstruction : ACLRECON 1
10 MOUSE JR MD, CLINTON W Inguinal Hernia : INGHERNI 1
no the sample data you posted but you want to do the hard work for us so we can answer your question.
--DDL
create table #data(CardId int , PhysicianName varchar(150), Procedure varchar(150), Facility varchar(150))
--DML
insert into #data
--here do the select of your sample data to insert into #data table
create table #data1(CardId int , PhysicianName varchar(150), ProcedureName varchar(150), Facility varchar(150));
insert into #data1
SELECT DISTINCT
CardID = TOR600.pc_int_id
,PhysicianName = (SELECT sgn_name = dbo.ufn_get_person_full_name(TOR600.car_gvr_int_id))
,ProcedureName = (select rtrim(TRS500_PROCEDURE.pro_nm) + ' : ' + rtrim(TRS500_PROCEDURE.pro_cd)
from TRS500_PROCEDURE WITH (NOLOCK)
where TRS500_PROCEDURE.pro_int_id = TOR600.pro_int_id )
,Facility = '1'
--,TOR605.[seq_no]
--,pc_ty_ds
FROM [paragon].[dbo].[TOR600_PREF_CARD_HDR] TOR600
LEFT OUTER JOIN [paragon].[dbo].[TOR610_PREF_CARD_DTL] TOR610 ON TOR600.pc_int_id = TOR610.pc_int_id
LEFT OUTER JOIN [paragon].[dbo].[TOR605_PREF_CARD_TYPE] TOR605 ON TOR610.pc_ty = TOR605.pc_ty
SELECT *
FROM #data1
Drop table #data1
sorry but we do not have access to your [paragon] database. use sample data you provided to insert data into your sample table #data1 as follows, you fill in the rest
create table #data(CardId int , PhysicianName varchar(150), Procedure varchar(150),
Facility varchar(150))
insert into #data
select 2096, 'DOE, JOHN', 'SHOULDER ARTHROSCOPY MINI OPEN ROTATOR CUFF',
'EMH MAIN OR' union
--now you yourself fill in the rest of the sample data here below
select
SELECT 1,'MOUSE JR MD, CLINTON W','Cholecystectomy: ORLAPCHOLE','1'
UNION
SELECT 2,'MOUSE JR MD, CLINTON W','Subcutaneous intravenous device insertio: SQID INSER','1'
UNION
SELECT 3,'MOUSE JR MD, CLINTON W','Pacemaker Placement: PACEMAKER','1'
UNION
SELECT 4,'MOUSE JR MD, CLINTON W','Sigmoid Colon Resection: SIGCOLRE','1'
UNION
SELECT 5,'MOUSE JR MD, CLINTON W','Appendectomy: APPENDEC','1'
UNION
SELECT 6,'MOUSE JR MD, CLINTON W','Cholecystectomy: CHOLECYS','1'
UNION
SELECT 7,'MOUSE JR MD, CLINTON W','Hemorrhoidectomy: HEMORRHO','1'
UNION
SELECT 8,'GOOFY, REYNALDO D','Lap Cholecystectomy: ORLAPCHOLE','1
UNION
SELECT 9,'MINI MD, DAVID ALAN','ACL Reconstruction: ACLRECON','1
UNION
SELECT 10,'MOUSE JR MD, CLINTON W','Inguinal Hernia: INGHERNI','1
