Inspection area cost table structure

 CREATE TABLE [dbo] [T_BODYPART] (
[BodyPartIdentity] [bigint] NOT NULL, 
[BodyPartName] [nvarchar] (20) NOT NULL, 
[BodyPartMemo] [nvarchar] (50) NULL, 
[DepartmentIdentity] [bigint] NULL, 
[PriceIdentity] [int] NULL, 
[DisplayOrder] [int] NULL, 
[ReportNode] [nvarchar] (255), NULL, 
[ModalityTypeIdentity] [int] NULL, 
[Protocolcode] [nvarchar] (256), NULL, 
[BodyPartCount] [int] NULL, 
[EXPOSALCOUNT] [int] NULL, 
[DisplayOrder2] [int] NULL, 
[Price] [numeric] (18, 2) NULL, 
[BodyPartStoped] [smallint] NULL, 
[BodyPartCode] [nvarchar] (20) NULL, 
PRIMARY KEY CONSTRAINT [PK_T_BODYPART] CLUSTERED 
(
[BodyPartIdentity] ASC 
) WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON, FILLFACTOR=90) ON (PRIMARY) 
) ON the (PRIMARY) 

This is the patient report structure

 SELECT TOP 1000 [F_STU_GUID] 
, [F_STU_NUM] 
, [F_STU_NAME] 
, [F_STU_NAME_EN] 
, [F_STU_SEX] 
, [F_STU_AGE] 
, [F_STU_BRITH] 
, [F_STU_TELEPHONE] 
, [F_STU_ADDRESS] 
, [F_STU_PROFESSION] 
, [F_STU_COMPANY] 
, [F_STU_ID_CARD] 
, [F_STU_NATION] 
, [F_STU_MARRY] 
, [F_STU_HEIGHT] 
, [F_STU_WEIGHT] 
, [F_STU_FEE_TYPE] 
, [F_STU_IN_NUM] 
, [F_STU_OUT_NUM] 
, [F_STU_BUNK_NUM] 
, [F_STU_SICKROOM_NUM] 
, [F_STU_SICKROOM_AREA] 
, [F_STU_FROM] 
, [F_STU_MODEL] 
, [F_STU_BODY_PART] 
, [F_STU_METHOD] 
, [F_STU_RESULT] 
, [F_STU_SUSPICION] 
, [F_STU_DISEASE] 
, [F_STU_REFER_DOCTOR] 
, [F_STU_REFER_OFFICE] 
, [F_STU_REFER_DATE] 
, [F_STU_PERFORM_DOCTOR] 
, [F_STU_PERFORM_OFFICE] 
, [F_STU_PERFORM_DATE] 
, [F_STU_OPT_DOCTOR] 
, [F_STU_OPT_DATE] 
, [F_STU_WRITER] 
, [F_STU_WRITE_DATE] 
, [F_STU_OBSERVATION] 
, [F_STU_DIAGNOSE] 
, [F_STU_ADVICE] 
, [F_STU_DETAIL] 
The FROM [PACS]. [dbo]. [T_STUDY_REPORT] 

Data table (PACS). [dbo] [T_STUDY_REPORT] [F_STU_BODY_PART] check parts of the field may be more parts,
For example: abdominal + small organ, the heart,
Then I want to make a statistics, statistics every day each inspection area and the corresponding operating times and cost , how can I achieve, please give a way of thinking, as the code is better,

CodePudding user response:

For example, table 1 body_part price
Small organs 100
The heart of 150
Head 200
Limbs 60
Carotid artery 30
Radius of 20
Knee joint 50

Table 2 F_stu_body_part
Small organ + heart
Limbs
Head + limbs
Results:
On September 24, 2020 statistical data:
Small organ 1 100
The heart once 150
Two limbs 120
Head 1 200
requirements: do not need to function, only a simple SQL statement, (interface)