Group By Formatting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group By Formatting

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2006-05-16 : 16:38:24

What I need to do with the below data is to grab all the data Grouped By intAdmittingPhysicianID WHERE dtmAdmissionDate(Year)= a certain year, so that the data would look like this

Ay thoughts on that Grouping?

PhysicianID (Heading) For 2004
strPatientEpisodeNumber
strHemoCategory
dtmAdmissionDate
dtmDischargeDate
intLengthOfStay
strProcedureType
strPrimaryDiagnosis
strSecondaryDiagnosis
decHGBLevelPreAdmit
decHGBLevelAdmit
decHGBLevelDischarge
decHCTLevelPreAdmit
decHCTLevelAdmit
decHCTLevelDischarge
intBloodTests
intTypeCrossmatch
dtmEPOPreAdmitDate

Ok, here first is all the relevant SQL to create the table I have and to insert some sample data

CREATE TABLE [dbo].[tblPatientEpisode](
[intEpisodeID] [int] IDENTITY(1,1) NOT NULL,
[intHospitalID] [int] NOT NULL,
[strPatientMRNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strInternalNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strPatientEpisodeNumber] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strHemoCategory] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[dtmAdmissionDate] [datetime] NOT NULL,
[dtmDischargeDate] [datetime] NULL,
[intLengthOfStay] [int] NULL,
[strLanguage] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strRace] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strPatientType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strGender] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strMedSurg] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strProcedureType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strPrimaryDiagnosis] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[strSecondaryDiagnosis] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strCoordConsultedBy] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strInsuranceType] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[blnFirstAdmit] [bit] NULL,
[blnTransfer] [bit] NULL,
[blnAdvDirective] [bit] NULL,
[blnDNR] [bit] NULL,
[blnBMSPReferral] [bit] NULL,
[blnJehovahWitness] [bit] NULL,
[intAdmittingPhysicianID] [int] NOT NULL,
[intConsultingPhysician1ID] [int] NULL,
[intConsultingPhysician2ID] [int] NULL,
[blnAllogeneicTransfusedFlag] [bit] NULL,
[intAllogeneicTransfusedAmount] [smallint] NULL,
[intAllogeneicTransfusedUOM] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[decHGBLevelPreAdmit] [decimal](3, 1) NULL,
[decHGBLevelAdmit] [decimal](3, 1) NULL,
[decHGBLevelDischarge] [decimal](3, 1) NULL,
[decHCTLevelPreAdmit] [decimal](3, 0) NULL,
[decHCTLevelAdmit] [decimal](3, 0) NULL,
[decHCTLevelDischarge] [decimal](3, 0) NULL,
[blnMicrosampling] [bit] NULL,
[intBloodTests] [int] NULL,
[intTypeCrossmatch] [int] NULL,
[intTypeScreen] [int] NULL,
[dtmEPOPreAdmitDate] [datetime] NULL,
[intEPOPreAdmitDose] [int] NULL,
[intEPOPreAdmitPhysicianID] [int] NULL,
[dtmEPOInHouseDate] [datetime] NULL,
[intEPOInHouseDose] [int] NULL,
[intEPOInHousePhysicianID] [int] NULL,
[strIronPreAdmit1Type] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[intIronPreAdmit1Dose] [int] NULL,
[intIronPreAdmitPhysician1ID] [int] NULL,
[strIronPreAdmit2Type] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[intIronPreAdmit2Dose] [int] NULL,
[intIronPreAdmitPhysician2ID] [int] NULL,
[strIronInHouse1Type] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[intIronInHouse1Dose] [int] NULL,
[intIronInHousePhysician1ID] [int] NULL,
[strIronInHouse2Type] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[intIronInHouse2Dose] [int] NULL,
[intIronInHousePhysician2ID] [int] NULL,
[strWoundDrain1Type] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[intWoundDrain1Collected] [int] NULL,
[strWoundDrain2Type] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[intWoundDrain2Collected] [int] NULL,
[strWoundDrain3Type] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[intWoundDrain3Collected] [int] NULL,
[intWoundDrainReinfused] [int] NULL,
[intEstimatedBloodLoss] [int] NULL,
[intPADUnitsDrawn] [int] NULL,
[intPADUnitsTransfused] [int] NULL,
[blnATS] [bit] NULL,
[intATSReinfused] [int] NULL,
[blnANH] [bit] NULL,
[blnAPG] [bit] NULL,
[blnComplications] [bit] NULL,
[blnExpired] [bit] NULL,
[strNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dtmCreatedDate] [datetime] NULL,
[dtmModifiedDate] [datetime] NULL,
[strModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dtmDateOfBirth] [datetime] NULL,
CONSTRAINT [tblPatientEpisode_PK] PRIMARY KEY CLUSTERED
(
[intEpisodeID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT INTO [tblPatientEpisode] ([intEpisodeID],[intHospitalID],[strPatientMRNumber],[strInternalNumber],[strPatientEpisodeNumber],[strHemoCategory],[dtmAdmissionDate],[dtmDischargeDate],[intLengthOfStay],[strLanguage],[strRace],[strPatientType],[strGender],[strMedSurg],[strProcedureType],[strPrimaryDiagnosis],[strSecondaryDiagnosis],[strCoordConsultedBy],[strInsuranceType],[blnFirstAdmit],[blnTransfer],[blnAdvDirective],[blnDNR],[blnBMSPReferral],[blnJehovahWitness],[intAdmittingPhysicianID],[intConsultingPhysician1ID],[intConsultingPhysician2ID],[blnAllogeneicTransfusedFlag],[intAllogeneicTransfusedAmount],[intAllogeneicTransfusedUOM],[decHGBLevelPreAdmit],[decHGBLevelAdmit],[decHGBLevelDischarge],[decHCTLevelPreAdmit],[decHCTLevelAdmit],[decHCTLevelDischarge],[blnMicrosampling],[intBloodTests],[intTypeCrossmatch],[intTypeScreen],[dtmEPOPreAdmitDate],[intEPOPreAdmitDose],[intEPOPreAdmitPhysicianID],[dtmEPOInHouseDate],[intEPOInHouseDose],[intEPOInHousePhysicianID],[strIronPreAdmit1Type],[intIronPreAdmit1Dose],[intIronPreAdmitPhysician1ID],[strIronPreAdmit2Type],[intIronPreAdmit2Dose],[intIronPreAdmitPhysician2ID],[strIronInHouse1Type],[intIronInHouse1Dose],[intIronInHousePhysician1ID],[strIronInHouse2Type],[intIronInHouse2Dose],[intIronInHousePhysician2ID],[strWoundDrain1Type],[intWoundDrain1Collected],[strWoundDrain2Type],[intWoundDrain2Collected],[strWoundDrain3Type],[intWoundDrain3Collected],[intWoundDrainReinfused],[intEstimatedBloodLoss],[intPADUnitsDrawn],[intPADUnitsTransfused],[blnATS],[intATSReinfused],[blnANH],[blnAPG],[blnComplications],[blnExpired],[strNotes],[dtmCreatedDate],[dtmModifiedDate],[strModifiedBy],[dtmDateOfBirth])VALUES(23,1,'000308836','OMC23','OMC23','2','Sep 12 2005 12:00:00:000AM','Sep 19 2005 12:00:00:000AM',7,'1','1','1','M','Surgery','8151','V4364','29282','Physician','5',1,0,0,0,0,0,17,NULL,NULL,NULL,NULL,NULL,16.1,12.3,11.1,46,36,32,1,12,1,0,NULL,0,NULL,'Sep 12 2005 12:00:00:000AM',40000,17,NULL,0,NULL,NULL,0,NULL,'3',100,17,'4',1650,17,'2',470,NULL,0,NULL,0,0,300,0,0,1,250,0,0,0,0,'Failed total hip replacement. Altered mental status due to meds.','Oct 7 2005 5:46:08:890PM','Feb 22 2006 1:39:57:327PM','admin','Dec 21 1940 12:00:00:000AM')
INSERT INTO [tblPatientEpisode] ([intEpisodeID],[intHospitalID],[strPatientMRNumber],[strInternalNumber],[strPatientEpisodeNumber],[strHemoCategory],[dtmAdmissionDate],[dtmDischargeDate],[intLengthOfStay],[strLanguage],[strRace],[strPatientType],[strGender],[strMedSurg],[strProcedureType],[strPrimaryDiagnosis],[strSecondaryDiagnosis],[strCoordConsultedBy],[strInsuranceType],[blnFirstAdmit],[blnTransfer],[blnAdvDirective],[blnDNR],[blnBMSPReferral],[blnJehovahWitness],[intAdmittingPhysicianID],[intConsultingPhysician1ID],[intConsultingPhysician2ID],[blnAllogeneicTransfusedFlag],[intAllogeneicTransfusedAmount],[intAllogeneicTransfusedUOM],[decHGBLevelPreAdmit],[decHGBLevelAdmit],[decHGBLevelDischarge],[decHCTLevelPreAdmit],[decHCTLevelAdmit],[decHCTLevelDischarge],[blnMicrosampling],[intBloodTests],[intTypeCrossmatch],[intTypeScreen],[dtmEPOPreAdmitDate],[intEPOPreAdmitDose],[intEPOPreAdmitPhysicianID],[dtmEPOInHouseDate],[intEPOInHouseDose],[intEPOInHousePhysicianID],[strIronPreAdmit1Type],[intIronPreAdmit1Dose],[intIronPreAdmitPhysician1ID],[strIronPreAdmit2Type],[intIronPreAdmit2Dose],[intIronPreAdmitPhysician2ID],[strIronInHouse1Type],[intIronInHouse1Dose],[intIronInHousePhysician1ID],[strIronInHouse2Type],[intIronInHouse2Dose],[intIronInHousePhysician2ID],[strWoundDrain1Type],[intWoundDrain1Collected],[strWoundDrain2Type],[intWoundDrain2Collected],[strWoundDrain3Type],[intWoundDrain3Collected],[intWoundDrainReinfused],[intEstimatedBloodLoss],[intPADUnitsDrawn],[intPADUnitsTransfused],[blnATS],[intATSReinfused],[blnANH],[blnAPG],[blnComplications],[blnExpired],[strNotes],[dtmCreatedDate],[dtmModifiedDate],[strModifiedBy],[dtmDateOfBirth])VALUES(24,1,'000382563','OMC24','OMC24','2','Sep 1 2005 12:00:00:000AM','Sep 7 2005 12:00:00:000AM',6,'1','1','1','M','Surgery','605','2394',NULL,'Physician','1',1,0,0,0,0,0,21,NULL,NULL,NULL,NULL,NULL,13.5,11.3,8.9,39,33,26,0,27,0,0,NULL,0,NULL,'Sep 1 2005 12:00:00:000AM',60000,21,NULL,0,NULL,NULL,0,NULL,'3',400,42,'4',900,21,'3',2270,NULL,0,NULL,0,0,600,0,0,1,300,0,0,0,0,'Radical Cystoprostatectomy with Ileo Conduit','Oct 9 2005 5:57:15:517PM','Feb 22 2006 1:39:57:327PM','admin','Mar 12 1921 12:00:00:000AM')
INSERT INTO [tblPatientEpisode] ([intEpisodeID],[intHospitalID],[strPatientMRNumber],[strInternalNumber],[strPatientEpisodeNumber],[strHemoCategory],[dtmAdmissionDate],[dtmDischargeDate],[intLengthOfStay],[strLanguage],[strRace],[strPatientType],[strGender],[strMedSurg],[strProcedureType],[strPrimaryDiagnosis],[strSecondaryDiagnosis],[strCoordConsultedBy],[strInsuranceType],[blnFirstAdmit],[blnTransfer],[blnAdvDirective],[blnDNR],[blnBMSPReferral],[blnJehovahWitness],[intAdmittingPhysicianID],[intConsultingPhysician1ID],[intConsultingPhysician2ID],[blnAllogeneicTransfusedFlag],[intAllogeneicTransfusedAmount],[intAllogeneicTransfusedUOM],[decHGBLevelPreAdmit],[decHGBLevelAdmit],[decHGBLevelDischarge],[decHCTLevelPreAdmit],[decHCTLevelAdmit],[decHCTLevelDischarge],[blnMicrosampling],[intBloodTests],[intTypeCrossmatch],[intTypeScreen],[dtmEPOPreAdmitDate],[intEPOPreAdmitDose],[intEPOPreAdmitPhysicianID],[dtmEPOInHouseDate],[intEPOInHouseDose],[intEPOInHousePhysicianID],[strIronPreAdmit1Type],[intIronPreAdmit1Dose],[intIronPreAdmitPhysician1ID],[strIronPreAdmit2Type],[intIronPreAdmit2Dose],[intIronPreAdmitPhysician2ID],[strIronInHouse1Type],[intIronInHouse1Dose],[intIronInHousePhysician1ID],[strIronInHouse2Type],[intIronInHouse2Dose],[intIronInHousePhysician2ID],[strWoundDrain1Type],[intWoundDrain1Collected],[strWoundDrain2Type],[intWoundDrain2Collected],[strWoundDrain3Type],[intWoundDrain3Collected],[intWoundDrainReinfused],[intEstimatedBloodLoss],[intPADUnitsDrawn],[intPADUnitsTransfused],[blnATS],[intATSReinfused],[blnANH],[blnAPG],[blnComplications],[blnExpired],[strNotes],[dtmCreatedDate],[dtmModifiedDate],[strModifiedBy],[dtmDateOfBirth])VALUES(26,1,'000096638','OMC26','OMC26','2','Sep 12 2005 12:00:00:000AM','Sep 15 2005 12:00:00:000AM',3,'1','1','1','M','Surgery','8154','7366',NULL,'Physician','5',1,0,0,0,0,0,16,NULL,NULL,NULL,NULL,NULL,16.4,13.5,10.0,48,39,29,1,7,0,0,NULL,0,NULL,NULL,0,NULL,NULL,0,NULL,NULL,0,NULL,'4',600,16,NULL,0,NULL,'2',400,NULL,0,NULL,0,0,400,0,0,1,500,0,0,0,0,' ','Oct 9 2005 6:12:57:970PM','Feb 22 2006 1:39:57:327PM','admin','Jan 4 1955 12:00:00:000AM')
INSERT INTO [tblPatientEpisode] ([intEpisodeID],[intHospitalID],[strPatientMRNumber],[strInternalNumber],[strPatientEpisodeNumber],[strHemoCategory],[dtmAdmissionDate],[dtmDischargeDate],[intLengthOfStay],[strLanguage],[strRace],[strPatientType],[strGender],[strMedSurg],[strProcedureType],[strPrimaryDiagnosis],[strSecondaryDiagnosis],[strCoordConsultedBy],[strInsuranceType],[blnFirstAdmit],[blnTransfer],[blnAdvDirective],[blnDNR],[blnBMSPReferral],[blnJehovahWitness],[intAdmittingPhysicianID],[intConsultingPhysician1ID],[intConsultingPhysician2ID],[blnAllogeneicTransfusedFlag],[intAllogeneicTransfusedAmount],[intAllogeneicTransfusedUOM],[decHGBLevelPreAdmit],[decHGBLevelAdmit],[decHGBLevelDischarge],[decHCTLevelPreAdmit],[decHCTLevelAdmit],[decHCTLevelDischarge],[blnMicrosampling],[intBloodTests],[intTypeCrossmatch],[intTypeScreen],[dtmEPOPreAdmitDate],[intEPOPreAdmitDose],[intEPOPreAdmitPhysicianID],[dtmEPOInHouseDate],[intEPOInHouseDose],[intEPOInHousePhysicianID],[strIronPreAdmit1Type],[intIronPreAdmit1Dose],[intIronPreAdmitPhysician1ID],[strIronPreAdmit2Type],[intIronPreAdmit2Dose],[intIronPreAdmitPhysician2ID],[strIronInHouse1Type],[intIronInHouse1Dose],[intIronInHousePhysician1ID],[strIronInHouse2Type],[intIronInHouse2Dose],[intIronInHousePhysician2ID],[strWoundDrain1Type],[intWoundDrain1Collected],[strWoundDrain2Type],[intWoundDrain2Collected],[strWoundDrain3Type],[intWoundDrain3Collected],[intWoundDrainReinfused],[intEstimatedBloodLoss],[intPADUnitsDrawn],[intPADUnitsTransfused],[blnATS],[intATSReinfused],[blnANH],[blnAPG],[blnComplications],[blnExpired],[strNotes],[dtmCreatedDate],[dtmModifiedDate],[strModifiedBy],[dtmDateOfBirth])VALUES(27,1,'000492136','OMC27','OMC27','2','Sep 12 2005 12:00:00:000AM','Sep 16 2005 12:00:00:000AM',4,'1','1','1','M','Surgery','8154','7366',NULL,'Physician','5',1,0,0,0,0,0,16,NULL,NULL,NULL,NULL,NULL,11.8,12.2,9.5,35,37,28,1,9,0,0,'Aug 26 2005 12:00:00:000AM',80000,16,'Sep 12 2005 12:00:00:000AM',40000,16,'3',100,16,NULL,0,NULL,'3',400,16,'4',300,16,'2',910,NULL,0,NULL,0,0,0,1,1,1,1250,0,0,0,0,'Bilateral osteoarthritis knees','Oct 9 2005 6:18:19:593PM','Feb 22 2006 1:39:57:327PM','admin','Apr 18 1942 12:00:00:000AM')
INSERT INTO [tblPatientEpisode] ([intEpisodeID],[intHospitalID],[strPatientMRNumber],[strInternalNumber],[strPatientEpisodeNumber],[strHemoCategory],[dtmAdmissionDate],[dtmDischargeDate],[intLengthOfStay],[strLanguage],[strRace],[strPatientType],[strGender],[strMedSurg],[strProcedureType],[strPrimaryDiagnosis],[strSecondaryDiagnosis],[strCoordConsultedBy],[strInsuranceType],[blnFirstAdmit],[blnTransfer],[blnAdvDirective],[blnDNR],[blnBMSPReferral],[blnJehovahWitness],[intAdmittingPhysicianID],[intConsultingPhysician1ID],[intConsultingPhysician2ID],[blnAllogeneicTransfusedFlag],[intAllogeneicTransfusedAmount],[intAllogeneicTransfusedUOM],[decHGBLevelPreAdmit],[decHGBLevelAdmit],[decHGBLevelDischarge],[decHCTLevelPreAdmit],[decHCTLevelAdmit],[decHCTLevelDischarge],[blnMicrosampling],[intBloodTests],[intTypeCrossmatch],[intTypeScreen],[dtmEPOPreAdmitDate],[intEPOPreAdmitDose],[intEPOPreAdmitPhysicianID],[dtmEPOInHouseDate],[intEPOInHouseDose],[intEPOInHousePhysicianID],[strIronPreAdmit1Type],[intIronPreAdmit1Dose],[intIronPreAdmitPhysician1ID],[strIronPreAdmit2Type],[intIronPreAdmit2Dose],[intIronPreAdmitPhysician2ID],[strIronInHouse1Type],[intIronInHouse1Dose],[intIronInHousePhysician1ID],[strIronInHouse2Type],[intIronInHouse2Dose],[intIronInHousePhysician2ID],[strWoundDrain1Type],[intWoundDrain1Collected],[strWoundDrain2Type],[intWoundDrain2Collected],[strWoundDrain3Type],[intWoundDrain3Collected],[intWoundDrainReinfused],[intEstimatedBloodLoss],[intPADUnitsDrawn],[intPADUnitsTransfused],[blnATS],[intATSReinfused],[blnANH],[blnAPG],[blnComplications],[blnExpired],[strNotes],[dtmCreatedDate],[dtmModifiedDate],[strModifiedBy],[dtmDateOfBirth])VALUES(28,1,'000435456','OMC28','OMC28','2','Sep 12 2005 12:00:00:000AM','Sep 15 2005 12:00:00:000AM',3,'6','1','1','F','Surgery','8154','7366',NULL,'Physician','1',1,0,0,0,0,0,17,NULL,NULL,NULL,NULL,NULL,13.1,11.2,10.6,39,33,30,1,9,0,0,NULL,0,NULL,'Sep 12 2005 12:00:00:000AM',40000,17,NULL,0,NULL,NULL,0,NULL,'3',100,17,'4',600,17,'2',470,NULL,0,NULL,0,0,25,0,0,1,100,0,0,0,0,'Degenerative Joint Disease','Oct 9 2005 6:25:17:000PM','Feb 22 2006 1:39:57:327PM','admin','Jan 24 1925 12:00:00:000AM')
INSERT INTO [tblPatientEpisode] ([intEpisodeID],[intHospitalID],[strPatientMRNumber],[strInternalNumber],[strPatientEpisodeNumber],[strHemoCategory],[dtmAdmissionDate],[dtmDischargeDate],[intLengthOfStay],[strLanguage],[strRace],[strPatientType],[strGender],[strMedSurg],[strProcedureType],[strPrimaryDiagnosis],[strSecondaryDiagnosis],[strCoordConsultedBy],[strInsuranceType],[blnFirstAdmit],[blnTransfer],[blnAdvDirective],[blnDNR],[blnBMSPReferral],[blnJehovahWitness],[intAdmittingPhysicianID],[intConsultingPhysician1ID],[intConsultingPhysician2ID],[blnAllogeneicTransfusedFlag],[intAllogeneicTransfusedAmount],[intAllogeneicTransfusedUOM],[decHGBLevelPreAdmit],[decHGBLevelAdmit],[decHGBLevelDischarge],[decHCTLevelPreAdmit],[decHCTLevelAdmit],[decHCTLevelDischarge],[blnMicrosampling],[intBloodTests],[intTypeCrossmatch],[intTypeScreen],[dtmEPOPreAdmitDate],[intEPOPreAdmitDose],[intEPOPreAdmitPhysicianID],[dtmEPOInHouseDate],[intEPOInHouseDose],[intEPOInHousePhysicianID],[strIronPreAdmit1Type],[intIronPreAdmit1Dose],[intIronPreAdmitPhysician1ID],[strIronPreAdmit2Type],[intIronPreAdmit2Dose],[intIronPreAdmitPhysician2ID],[strIronInHouse1Type],[intIronInHouse1Dose],[intIronInHousePhysician1ID],[strIronInHouse2Type],[intIronInHouse2Dose],[intIronInHousePhysician2ID],[strWoundDrain1Type],[intWoundDrain1Collected],[strWoundDrain2Type],[intWoundDrain2Collected],[strWoundDrain3Type],[intWoundDrain3Collected],[intWoundDrainReinfused],[intEstimatedBloodLoss],[intPADUnitsDrawn],[intPADUnitsTransfused],[blnATS],[intATSReinfused],[blnANH],[blnAPG],[blnComplications],[blnExpired],[strNotes],[dtmCreatedDate],[dtmModifiedDate],[strModifiedBy],[dtmDateOfBirth])VALUES(29,1,'000498407','OMC29','OMC29','2','Sep 20 2005 12:00:00:000AM','Sep 24 2005 12:00:00:000AM',4,'1','2','1','F','Surgery','8154','7366',NULL,'Physician','6',1,0,0,0,0,0,18,NULL,NULL,NULL,NULL,NULL,12.0,11.2,9.8,35,33,29,1,9,1,0,'Sep 13 2005 12:00:00:000AM',40000,18,NULL,0,NULL,NULL,0,NULL,NULL,0,NULL,'3',400,18,'4',1200,18,'2',520,NULL,0,NULL,0,0,500,0,0,1,300,0,0,0,0,'Pt. experienced SOB after 1st and only dose of Procrit.','Oct 14 2005 5:12:39:903PM','Feb 22 2006 1:39:57:327PM','admin','Oct 11 1951 12:00:00:000AM')
INSERT INTO [tblPatientEpisode] ([intEpisodeID],[intHospitalID],[strPatientMRNumber],[strInternalNumber],[strPatientEpisodeNumber],[strHemoCategory],[dtmAdmissionDate],[dtmDischargeDate],[intLengthOfStay],[strLanguage],[strRace],[strPatientType],[strGender],[strMedSurg],[strProcedureType],[strPrimaryDiagnosis],[strSecondaryDiagnosis],[strCoordConsultedBy],[strInsuranceType],[blnFirstAdmit],[blnTransfer],[blnAdvDirective],[blnDNR],[blnBMSPReferral],[blnJehovahWitness],[intAdmittingPhysicianID],[intConsultingPhysician1ID],[intConsultingPhysician2ID],[blnAllogeneicTransfusedFlag],[intAllogeneicTransfusedAmount],[intAllogeneicTransfusedUOM],[decHGBLevelPreAdmit],[decHGBLevelAdmit],[decHGBLevelDischarge],[decHCTLevelPreAdmit],[decHCTLevelAdmit],[decHCTLevelDischarge],[blnMicrosampling],[intBloodTests],[intTypeCrossmatch],[intTypeScreen],[dtmEPOPreAdmitDate],[intEPOPreAdmitDose],[intEPOPreAdmitPhysicianID],[dtmEPOInHouseDate],[intEPOInHouseDose],[intEPOInHousePhysicianID],[strIronPreAdmit1Type],[intIronPreAdmit1Dose],[intIronPreAdmitPhysician1ID],[strIronPreAdmit2Type],[intIronPreAdmit2Dose],[intIronPreAdmitPhysician2ID],[strIronInHouse1Type],[intIronInHouse1Dose],[intIronInHousePhysician1ID],[strIronInHouse2Type],[intIronInHouse2Dose],[intIronInHousePhysician2ID],[strWoundDrain1Type],[intWoundDrain1Collected],[strWoundDrain2Type],[intWoundDrain2Collected],[strWoundDrain3Type],[intWoundDrain3Collected],[intWoundDrainReinfused],[intEstimatedBloodLoss],[intPADUnitsDrawn],[intPADUnitsTransfused],[blnATS],[intATSReinfused],[blnANH],[blnAPG],[blnComplications],[blnExpired],[strNotes],[dtmCreatedDate],[dtmModifiedDate],[strModifiedBy],[dtmDateOfBirth])VALUES(31,1,'000406204','OMC31','OMC31','2','Sep 19 2005 12:00:00:000AM','Sep 24 2005 12:00:00:000AM',5,'1','1','1','M','Surgery','8154','7366','401','Physician','2',1,0,0,0,0,0,16,NULL,NULL,NULL,NULL,NULL,15.0,13.2,10.9,44,39,32,1,0,0,0,NULL,0,NULL,NULL,0,NULL,NULL,0,NULL,NULL,0,NULL,'3',100,16,'4',1450,16,'2',300,NULL,0,NULL,0,0,100,0,0,1,550,0,0,0,0,' ','Oct 16 2005 1:07:17:750PM','Feb 22 2006 2:05:10:817PM',NULL,'Feb 15 1952 12:00:00:000AM')
INSERT INTO [tblPatientEpisode] ([intEpisodeID],[intHospitalID],[strPatientMRNumber],[strInternalNumber],[strPatientEpisodeNumber],[strHemoCategory],[dtmAdmissionDate],[dtmDischargeDate],[intLengthOfStay],[strLanguage],[strRace],[strPatientType],[strGender],[strMedSurg],[strProcedureType],[strPrimaryDiagnosis],[strSecondaryDiagnosis],[strCoordConsultedBy],[strInsuranceType],[blnFirstAdmit],[blnTransfer],[blnAdvDirective],[blnDNR],[blnBMSPReferral],[blnJehovahWitness],[intAdmittingPhysicianID],[intConsultingPhysician1ID],[intConsultingPhysician2ID],[blnAllogeneicTransfusedFlag],[intAllogeneicTransfusedAmount],[intAllogeneicTransfusedUOM],[decHGBLevelPreAdmit],[decHGBLevelAdmit],[decHGBLevelDischarge],[decHCTLevelPreAdmit],[decHCTLevelAdmit],[decHCTLevelDischarge],[blnMicrosampling],[intBloodTests],[intTypeCrossmatch],[intTypeScreen],[dtmEPOPreAdmitDate],[intEPOPreAdmitDose],[intEPOPreAdmitPhysicianID],[dtmEPOInHouseDate],[intEPOInHouseDose],[intEPOInHousePhysicianID],[strIronPreAdmit1Type],[intIronPreAdmit1Dose],[intIronPreAdmitPhysician1ID],[strIronPreAdmit2Type],[intIronPreAdmit2Dose],[intIronPreAdmitPhysician2ID],[strIronInHouse1Type],[intIronInHouse1Dose],[intIronInHousePhysician1ID],[strIronInHouse2Type],[intIronInHouse2Dose],[intIronInHousePhysician2ID],[strWoundDrain1Type],[intWoundDrain1Collected],[strWoundDrain2Type],[intWoundDrain2Collected],[strWoundDrain3Type],[intWoundDrain3Collected],[intWoundDrainReinfused],[intEstimatedBloodLoss],[intPADUnitsDrawn],[intPADUnitsTransfused],[blnATS],[intATSReinfused],[blnANH],[blnAPG],[blnComplications],[blnExpired],[strNotes],[dtmCreatedDate],[dtmModifiedDate],[strModifiedBy],[dtmDateOfBirth])VALUES(32,1,'000050724','OMC32','OMC32','2','Sep 19 2005 12:00:00:000AM','Sep 22 2005 12:00:00:000AM',3,'1','1','1','F','Surgery','8154','7366',NULL,'Physician','1',1,0,0,0,0,0,16,NULL,NULL,NULL,NULL,NULL,14.1,12.5,9.8,42,37,29,1,0,0,0,NULL,0,NULL,'Sep 19 2005 12:00:00:000AM',40000,16,NULL,0,NULL,NULL,0,NULL,'3',400,16,'4',750,16,'2',460,NULL,0,NULL,0,0,0,0,0,1,300,0,0,0,0,' ','Oct 16 2005 1:12:12:623PM','Feb 22 2006 1:39:57:327PM','admin','Jan 10 1929 12:00:00:000AM')
INSERT INTO [tblPatientEpisode] ([intEpisodeID],[intHospitalID],[strPatientMRNumber],[strInternalNumber],[strPatientEpisodeNumber],[strHemoCategory],[dtmAdmissionDate],[dtmDischargeDate],[intLengthOfStay],[strLanguage],[strRace],[strPatientType],[strGender],[strMedSurg],[strProcedureType],[strPrimaryDiagnosis],[strSecondaryDiagnosis],[strCoordConsultedBy],[strInsuranceType],[blnFirstAdmit],[blnTransfer],[blnAdvDirective],[blnDNR],[blnBMSPReferral],[blnJehovahWitness],[intAdmittingPhysicianID],[intConsultingPhysician1ID],[intConsultingPhysician2ID],[blnAllogeneicTransfusedFlag],[intAllogeneicTransfusedAmount],[intAllogeneicTransfusedUOM],[decHGBLevelPreAdmit],[decHGBLevelAdmit],[decHGBLevelDischarge],[decHCTLevelPreAdmit],[decHCTLevelAdmit],[decHCTLevelDischarge],[blnMicrosampling],[intBloodTests],[intTypeCrossmatch],[intTypeScreen],[dtmEPOPreAdmitDate],[intEPOPreAdmitDose],[intEPOPreAdmitPhysicianID],[dtmEPOInHouseDate],[intEPOInHouseDose],[intEPOInHousePhysicianID],[strIronPreAdmit1Type],[intIronPreAdmit1Dose],[intIronPreAdmitPhysician1ID],[strIronPreAdmit2Type],[intIronPreAdmit2Dose],[intIronPreAdmitPhysician2ID],[strIronInHouse1Type],[intIronInHouse1Dose],[intIronInHousePhysician1ID],[strIronInHouse2Type],[intIronInHouse2Dose],[intIronInHousePhysician2ID],[strWoundDrain1Type],[intWoundDrain1Collected],[strWoundDrain2Type],[intWoundDrain2Collected],[strWoundDrain3Type],[intWoundDrain3Collected],[intWoundDrainReinfused],[intEstimatedBloodLoss],[intPADUnitsDrawn],[intPADUnitsTransfused],[blnATS],[intATSReinfused],[blnANH],[blnAPG],[blnComplications],[blnExpired],[strNotes],[dtmCreatedDate],[dtmModifiedDate],[strModifiedBy],[dtmDateOfBirth])VALUES(33,1,'000497690','OMC33','OMC33','2','Sep 16 2005 12:00:00:000AM','Sep 19 2005 12:00:00:000AM',3,'7','6','1','M','Surgery','8151','73639','7944','Physician','1',1,0,0,0,0,0,30,NULL,NULL,NULL,NULL,NULL,15.5,13.3,10.6,47,40,31,1,0,1,0,NULL,0,NULL,'Sep 16 2005 12:00:00:000AM',60000,30,NULL,0,NULL,NULL,0,NULL,'3',100,30,'4',900,30,NULL,0,NULL,0,NULL,0,0,0,0,0,1,220,0,0,0,0,' ','Oct 16 2005 1:18:10:280PM','Feb 22 2006 1:39:57:327PM','admin','Mar 17 1937 12:00:00:000AM')

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-16 : 20:24:38

I'm not 100% sure if this is what you want:

SELECT
intAdmittingPhysicianID,
strPatientEpisodeNumber,
strHemoCategory,
dtmAdmissionDate,
dtmDischargeDate,
intLengthOfStay,
strProcedureType,
strPrimaryDiagnosis,
strSecondaryDiagnosis,
decHGBLevelPreAdmit,
decHGBLevelAdmit,
decHGBLevelDischarge,
decHCTLevelPreAdmit,
decHCTLevelAdmit,
decHCTLevelDischarge,
intBloodTests,
intTypeCrossmatch,
dtmEPOPreAdmitDate
FROM tblPatientEpisode
WHERE YEAR(dtmAdmissionDate) = 2005
ORDER BY intAdmittingPhysicianID

This just orders the data by physicianID. You can't really group by the physicianID because you didn't have any aggregation functions in the select list, so there's nothing to group by. If you wanted to have headings like a report, SQL can't really do that (at least, not easily).

Harry C
Posting Yak Master

148 Posts

Posted - 2006-05-17 : 09:49:48

Thank you. Yea, I think I was overcomplicating it...seems to be a problem for me. Thank you

HC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources