with B as (
select  OrderCode,CardNum,OrderStatus,GrpOrderCode  from  DD_Order  where grpordercode in (
'7522F11F-4D40-41B4-95B5-07DDBE32126A'

)  ),
T as (
select do.OrderCode,customerName,do.CardNum , CONVERT(varchar,CheckBeginTime,23) CheckDate,SUBSTRING( CONVERT(varchar,CheckBeginTime,23),0,8) CheckDateMonth,
case when dc.Sex =1 then '男' else  '女' end sex ,
FLOOR(datediff(DY,Birthday,getdate())/365.25) Age,
cast (Left(REPLACE( CONVERT(varchar(100), CheckBeginTime, 8),':',''),4)as int ) timeSpan, 
 isnull(datediff( hour, CheckBeginTime, CheckFinishTime ),24) checkSpendTime,CheckBeginTime, CheckFinishTime  --,do.*  
 from DD_Order do 
 inner join B on do.OrderCode =B.orderCode
left join DD_Customer dc on do.CustomerCode =dc.CustomerCode  ),
T1 as (select a.* from FJ_RptSubItemRstNotLis a inner join B on a.OrderCode =B.OrderCode where Findings <>'IsSummary'  /* where a.RptSubItemCode  in ('90002','90003','90004','90005') */),
T2 as (select a.* from FJ_RptSubItemRstLis a inner join B on a.OrderCode =B.orderCode ),
M2 as (select orderCode,

Max(case when RptSubItemCode='90072'then ExamValue else '' end)'白细胞',
Max(case when RptSubItemCode='90073'then ExamValue else '' end)'血红蛋白',
Max(case when RptSubItemCode='90074'then ExamValue else '' end)'红细胞',
Max(case when RptSubItemCode='90075'then ExamValue else '' end)'红细胞压积',
Max(case when RptSubItemCode='90076'then ExamValue else '' end)'平均血红蛋白量（MCH）',
Max(case when RptSubItemCode='90077'then ExamValue else '' end)'平均血红蛋白浓度（MCHC）',
Max(case when RptSubItemCode='90078'then ExamValue else '' end)'平均红细胞体积（MCV）',
Max(case when RptSubItemCode='90079'then ExamValue else '' end)'红细胞分布宽度标准差（RDW-SD）',
Max(case when RptSubItemCode='90080'then ExamValue else '' end)'红细胞体积分布宽度变异系数（RDW-CV）',
Max(case when RptSubItemCode='90081'then ExamValue else '' end)'血小板分布宽度（PDW）',
Max(case when RptSubItemCode='90082'then ExamValue else '' end)'平均血小板体积（MPV）',
Max(case when RptSubItemCode='90083'then ExamValue else '' end)'血小板',
Max(case when RptSubItemCode='90084'then ExamValue else '' end)'中性粒细胞',
Max(case when RptSubItemCode='90085'then ExamValue else '' end)'淋巴细胞',
Max(case when RptSubItemCode='90086'then ExamValue else '' end)'单核细胞',
Max(case when RptSubItemCode='90087'then ExamValue else '' end)'嗜酸性粒细胞',
Max(case when RptSubItemCode='90088'then ExamValue else '' end)'嗜碱性粒细胞',
Max(case when RptSubItemCode='90809'then ExamValue else '' end)'淋巴细胞百分比',
Max(case when RptSubItemCode='90810'then ExamValue else '' end)'中性粒细胞百分比',
Max(case when RptSubItemCode='90811'then ExamValue else '' end)'嗜酸性粒细胞百分比',
Max(case when RptSubItemCode='90812'then ExamValue else '' end)'嗜碱性粒细胞百分比',
Max(case when RptSubItemCode='90813'then ExamValue else '' end)'血小板压积',
Max(case when RptSubItemCode='90814'then ExamValue else '' end)'大型血小板比率',
Max(case when RptSubItemCode='90882'then ExamValue else '' end)'单核细胞百分比',
Max(case when RptSubItemCode='90091'then ExamValue else '' end)'尿胆原',
Max(case when RptSubItemCode='90092'then ExamValue else '' end)'尿胆红素',
Max(case when RptSubItemCode='90093'then ExamValue else '' end)'尿酮体',
Max(case when RptSubItemCode='90094'then ExamValue else '' end)'尿糖',
Max(case when RptSubItemCode='90097'then ExamValue else '' end)'亚硝酸盐',
Max(case when RptSubItemCode='90100'then ExamValue else '' end)'白细胞-尿',
Max(case when RptSubItemCode='90102'then ExamValue else '' end)'PH-尿',
Max(case when RptSubItemCode='90103'then ExamValue else '' end)'尿潜血',
Max(case when RptSubItemCode='90817'then ExamValue else '' end)'维生素C',
Max(case when RptSubItemCode='91428'then ExamValue else '' end)'尿蛋白',
Max(case when RptSubItemCode='98697'then ExamValue else '' end)'尿镜检',
Max(case when RptSubItemCode='G0095'then ExamValue else '' end)'尿比重',
Max(case when RptSubItemCode='90115'then ExamValue else '' end)'丙氨酸氨基转移酶（ALT）',
Max(case when RptSubItemCode='90116'then ExamValue else '' end)'天门冬氨酸氨基转移酶（AST）',
Max(case when RptSubItemCode='90117'then ExamValue else '' end)'总胆红素',
Max(case when RptSubItemCode='90118'then ExamValue else '' end)'结合胆红素',
Max(case when RptSubItemCode='90119'then ExamValue else '' end)'总胆汁酸（TBA）',
Max(case when RptSubItemCode='90122'then ExamValue else '' end)'总蛋白',
Max(case when RptSubItemCode='90123'then ExamValue else '' end)'白蛋白',
Max(case when RptSubItemCode='90801'then ExamValue else '' end)'球蛋白',
Max(case when RptSubItemCode='90802'then ExamValue else '' end)'白球比例',
Max(case when RptSubItemCode='97242'then ExamValue else '' end)'间接胆红素(IBIL)',
Max(case when RptSubItemCode='90125'then ExamValue else '' end)'尿素',
Max(case when RptSubItemCode='90126'then ExamValue else '' end)'肌酐',
Max(case when RptSubItemCode='90127'then ExamValue else '' end)'尿酸',
Max(case when RptSubItemCode='90303'then ExamValue else '' end)'血清胱抑素C',
Max(case when RptSubItemCode='G0184'then ExamValue else '' end)'促甲状腺素（TSH）',
Max(case when RptSubItemCode='G0187'then ExamValue else '' end)'血清游离甲状腺素（FT4）',
Max(case when RptSubItemCode='G0188'then ExamValue else '' end)'血清游离甲状腺素（FT3）',
Max(case when RptSubItemCode='90179'then ExamValue else '' end)'肌酸激酶',
Max(case when RptSubItemCode='90180'then ExamValue else '' end)'肌酸激酶同工酶（CK-MB）',
Max(case when RptSubItemCode='90181'then ExamValue else '' end)'乳酸脱氢酶（LDH）',
Max(case when RptSubItemCode='98654'then ExamValue else '' end)'肌钙蛋白I',
Max(case when RptSubItemCode='90133'then ExamValue else '' end)'空腹葡萄糖',
Max(case when RptSubItemCode='90232'then ExamValue else '' end)'甲胎蛋白(AFP)',
Max(case when RptSubItemCode='90233'then ExamValue else '' end)'鳞状细胞癌抗原(SCC)',
Max(case when RptSubItemCode='90234'then ExamValue else '' end)'癌胚抗原(CEA)',
Max(case when RptSubItemCode='90235'then ExamValue else '' end)'糖类抗原CA-199',
Max(case when RptSubItemCode='90241'then ExamValue else '' end)'总前列腺抗原(TPSA)',
Max(case when RptSubItemCode='90243'then ExamValue else '' end)'游离前列腺特异性抗原(F-PSA)',
Max(case when RptSubItemCode='90128'then ExamValue else '' end)'总胆固醇',
Max(case when RptSubItemCode='90129'then ExamValue else '' end)'甘油三酯',
Max(case when RptSubItemCode='90130'then ExamValue else '' end)'高密度脂蛋白胆固醇',
Max(case when RptSubItemCode='90131'then ExamValue else '' end)'低密度脂蛋白胆固醇',
Max(case when RptSubItemCode='90222'then ExamValue else '' end)'载脂蛋白B',
Max(case when RptSubItemCode='90829'then ExamValue else '' end)'载脂蛋白A-I',
Max(case when RptSubItemCode='98678'then ExamValue else '' end)'非HDL-C',
Max(case when RptSubItemCode='90301'then ExamValue else '' end)'超敏C反应蛋白',
Max(case when RptSubItemCode='97556'then ExamValue else '' end)'EB病毒IgA抗体',
Max(case when RptSubItemCode='90168'then ExamValue else '' end)'同型半胱氨酸',
from T2  group by orderCode ),
M1 as (select orderCode,


Max(case when RptSubItemCode='90002'then Findings else '' end)'收缩压',
Max(case when RptSubItemCode='90003'then Findings else '' end)'舒张压',
Max(case when RptSubItemCode='90004'then Findings else '' end)'身高',
Max(case when RptSubItemCode='90005'then Findings else '' end)'体重',
Max(case when RptSubItemCode='90006'then Findings else '' end)'体重指数',
Max(case when RptSubItemCode='97278'then Findings else '' end)'脉搏',
Max(case when RptSubItemCode='22112212'then Findings else '' end)'杂音',
Max(case when RptSubItemCode='22112214'then Findings else '' end)'神经系统',
Max(case when RptSubItemCode='90010'then Findings else '' end)'肺',
Max(case when RptSubItemCode='90013'then Findings else '' end)'心律',
Max(case when RptSubItemCode='90032'then Findings else '' end)'其他-内科',
Max(case when RptSubItemCode='G0018'then Findings else '' end)'肝',
Max(case when RptSubItemCode='G0019'then Findings else '' end)'脾',
Max(case when RptSubItemCode='G0020'then Findings else '' end)'肾',
Max(case when RptSubItemCode='202307121'then Findings else '' end)'裸眼视力(右)',
Max(case when RptSubItemCode='2023071210'then Findings else '' end)'内眼(左)',
Max(case when RptSubItemCode='2023071211'then Findings else '' end)'内眼(右)',
Max(case when RptSubItemCode='202307122'then Findings else '' end)'裸眼视力（左）',
Max(case when RptSubItemCode='202307124'then Findings else '' end)'眼底检查',
Max(case when RptSubItemCode='202307126'then Findings else '' end)'外眼(右)',
Max(case when RptSubItemCode='202307129'then Findings else '' end)'色觉检查',
Max(case when RptSubItemCode='22112217'then Findings else '' end)'矫正视力（右）',
Max(case when RptSubItemCode='22112219'then Findings else '' end)'矫正视力（左）',
Max(case when RptSubItemCode='90038'then Findings else '' end)'外眼（左）',
Max(case when RptSubItemCode='ZMD0002'then Findings else '' end)'裂隙灯检查',
Max(case when RptSubItemCode='22112202'then Findings else '' end)'扁桃体',
Max(case when RptSubItemCode='90048'then Findings else '' end)'外耳及外耳道',
Max(case when RptSubItemCode='90049'then Findings else '' end)'鼻腔',
Max(case when RptSubItemCode='90050'then Findings else '' end)'外鼻及鼻前庭',
Max(case when RptSubItemCode='90051'then Findings else '' end)'口咽部',
Max(case when RptSubItemCode='CZ019'then Findings else '' end)'鼓膜',
Max(case when RptSubItemCode='97017'then Findings else '' end)'心电图',
Max(case when RptSubItemCode='97013'then Findings else '' end)'超声',
Max(case when RptSubItemCode='97014'then Findings else '' end)'X线成像',
Max(case when RptSubItemCode='22112204'then Findings else '' end)'皮肤',
Max(case when RptSubItemCode='NY012'then Findings else '' end)'脊柱',
Max(case when RptSubItemCode='NY013'then Findings else '' end)'四肢',
Max(case when RptSubItemCode='NY015'then Findings else '' end)'淋巴结',
Max(case when RptSubItemCode='NY033'then Findings else '' end)'甲状腺',
Max(case when RptSubItemCode='NY037'then Findings else '' end)'其他-外科',
from T1  group by orderCode )
select T.CustomerName,T.CardNum,T.sex,T.age,M1.*,M2.* from T  
inner join M1 on T.OrderCode=M1.OrderCode
inner join M2 on T.OrderCode=M2.OrderCode


SELECT * from DD_Order where cardnum='230803000121'


---FJ_RptSubItemRstNotLis
SELECT DISTINCT fj.rptitemcode, fj.RptsubItemcode,dic.subitemname,('Max(case when RptSubItemCode=''' + fj.RptsubItemcode +'' + '''then Findings else '''' end)' + ''''+ dic.subitemname+''''+',' ) as A from FJ_RptSubItemRstNotLis fj  
INNER JOIN DIC_RptsubItem dic on dic.subitemcode = fj.RptsubItemcode
inner join DD_Order ddo on ddo.ordercode=fj.ordercode 
where ddo.grpordercode='80CD4B38-8274-44D6-ADF2-647626953781'
ORDER BY fj.rptitemcode

---FJ_RptSubItemRstLis
SELECT DISTINCT fj.rptitemcode, fj.RptsubItemcode,dic.subitemname,('Max(case when RptSubItemCode=''' + fj.RptsubItemcode +'' + '''then ExamValue else '''' end)' + ''''+ dic.subitemname+''''+',' ) as A from FJ_RptSubItemRstLis fj  
INNER JOIN DIC_RptsubItem dic on dic.subitemcode = fj.RptsubItemcode
inner join DD_Order ddo on ddo.ordercode=fj.ordercode 
where ddo.grpordercode='80CD4B38-8274-44D6-ADF2-647626953781'
ORDER BY fj.rptitemcode