• sqlserver-sql 个人写过最长sql记录 192行

    1. #获取当前日期前面30天的日期
    2.  
    3. select convert(varchar(10),dateadd(dd,number,dateadd(month,-1,getdate())),120) as cr_date
    4. 	from master..spt_values 
    5. 	where type='P' and 
    6. 	dateadd(dd,number,dateadd(month,-1,getdate()))<=getdate()
    7.  
    8.  
    9.  
    10.  
    11.  
    12. # 1， Total_call 最近30天每日创建工单数量 【已完成】
    13. declare @kehu VARCHAR(20)
    14. set @kehu ='WTC';
    15.  
    16.  
    17. select 
    18. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
    19. 	COUNT(TICKET_ID) Total_call
    20. from 
    21. 	VAPP_ITEM
    22. where 
    23. 	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    24. 	and person1_root_org_name = @kehu
    25. group by 
    26. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);
    27.  
    28.  
    29.  
    30. #2，  Uniclosed 最近30天状态为开着的未关单数量，已创建时间排序 【已完成】
    31. declare @kehu VARCHAR(20)
    32. set @kehu ='WTC';
    33.  
    34. select 
    35. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
    36. 	COUNT(TICKET_ID) Uniclosed
    37. from 
    38. 	VAPP_ITEM
    39. where 
    40. 	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and TICKET_STATUS IN ('Active','New','Queued') and person1_root_org_name = @kehu
    41. group by 
    42. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);
    43.  
    44.  
    45. #3， Scheduled  最近30天每天应到期工单数量且  工单状态为未关 （按照到期时间排序）【已完成】
    46. declare @kehu VARCHAR(20)
    47. set @kehu ='WTC';
    48.  
    49. SELECT
    50. 	a.cr_date,
    51. 	COUNT ( TICKET_ID ) Scheduled 
    52. from
    53. 		(Select CONVERT (VARCHAR ( 10 ),
    54. 		DATEADD(SECOND, (select top 1 sla_due_by from VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), 	'1970/1/1 08:00:00') ,120) as 'cr_date',
    55. 	vi.TICKET_ID
    56. 	from VAPP_ITEM as vi where  person1_root_org_name = @kehu and TICKET_STATUS not in ('closed') and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,vi.CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30) as a
    57. GROUP BY 
    58. 	a.cr_date
    59.  
    60.  
    61.  
    62. #4， P1_call 最近30天工单优先级最高的数量（按照创建时间排序）【已完成】
    63.  
    64. declare @kehu VARCHAR(20)
    65. set @kehu ='WTC';
    66.  
    67.  
    68. select 
    69. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    70. 	count(TICKET_ID) P1_call
    71. from
    72. 	VAPP_ITEM
    73. where 
    74. 	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and Ticket_priority='P2'
    75. 	and person1_root_org_name = @kehu
    76. GROUP BY 
    77. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
    78.  
    79.  
    80.  
    81. #5 ，Over_SLA 最近30天工单状态已超时工单数量 按照创建时间排序 【已完成】
    82. declare @kehu VARCHAR(20)
    83. set @kehu ='WTC';
    84.  
    85.  
    86. select 
    87. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    88. 	count(TICKET_ID) Over_SLA
    89. from
    90. 	VAPP_ITEM
    91. where 
    92. 	sla_compliance_status_indicator='Breached SLA' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and person1_root_org_name = @kehu
    93. GROUP by 
    94. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
    95.  
    96.  
    97. #6，SLA_Met 最近30天达成率 按照创建时间排序 公式（totalcall - uniclosed - oversla）/(totalcall - uniclosed ) 【已完成】
    98.  
    99. #每日sla达成数量
    100. declare @kehu VARCHAR(20)
    101. set @kehu ='WTC';
    102.  
    103. select 
    104. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
    105. 	COUNT(TICKET_ID) SLA_Met
    106. from 
    107. 	VAPP_ITEM
    108. where 
    109. 	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    110.  AND TICKET_STATUS NOT IN ('Active','New','Queued') AND sla_compliance_status_indicator NOT IN ('Breached SLA')
    111.  and person1_root_org_name = @kehu
    112. group by 
    113. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);
    114.  
    115. #sla达成率的分母
    116. declare @kehu VARCHAR(20)
    117. set @kehu ='WTC';
    118.  
    119. select 
    120. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
    121. 	COUNT(TICKET_ID) SLA_Met
    122. from 
    123. 	VAPP_ITEM
    124. where 
    125. 	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    126.  and TICKET_STATUS NOT IN ('Active','New','Queued')
    127.  and person1_root_org_name = @kehu
    128. group by 
    129. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);
    130.  
    131. #SLA_Met sla达成率 
    132. declare @kehu VARCHAR(20)
    133. set @kehu ='WTC';
    134.  
    135. select 
    136. 	a.cr_date,
    137. 	(a.xjsl/b.gdsl) SLA_Met
    138. from
    139. 		(select 
    140. 		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
    141. 		COUNT(TICKET_ID) xjsl
    142. 	from 
    143. 		VAPP_ITEM
    144. 	where 
    145. 		datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    146. 	 AND TICKET_STATUS NOT IN ('Active','New','Queued') AND sla_compliance_status_indicator NOT IN ('Breached SLA')
    147. 	 and person1_root_org_name = @kehu
    148. 	group by 
    149. 		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)) as a 
    150. 	 join 
    151. 	(select 
    152. 		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
    153. 		COUNT(TICKET_ID) gdsl
    154. 	from 
    155. 		VAPP_ITEM
    156. 	where 
    157. 		datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    158. 	 and TICKET_STATUS NOT IN ('Active','New','Queued')
    159. 	 and person1_root_org_name = @kehu
    160. 	group by 
    161. 		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)) as b 
    162. 	 on a.cr_date=b.cr_date;
    163.  
    164.  
    165.  
    166.  
    167.  
    168. #7，Worst_TAT 最近30天每天工单处理花费最大时间 【已完成】
    169. #原理 1，以创建时间排序 2，关单时间- 创建时间 3，工单状态为关闭 4，当天最大值
    170. declare @kehu VARCHAR(20)
    171. set @kehu ='WTC';
    172.  
    173.  
    174.  
    175. select 
    176. 	b.cr_date,
    177. 	max(b.zd) Worst_TAT
    178. from 
    179. 	(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    180. 	convert(varchar(10),(DATEADD(S,CLOSED_DATE,'1970/1/1 08:00:00') -DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),108) zd
    181. 	from
    182. 	VAPP_ITEM
    183. 	where TICKET_STATUS='Closed' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    184. 	and person1_root_org_name = @kehu
    185. 	) b
    186. group by 
    187. 	b.cr_date;
    188.  
    189.  
    190.  
    191. #8，Avg_onsite_time 最近30天平均上门时间  只计算有第一次上门时间的工单 【已完成】
    192. #单位是小时
    193. declare @kehu VARCHAR(20)
    194. set @kehu ='WTC';
    195.  
    196.  
    197.  
    198. select t.cr_date,avg(t.avg_onsitetime) Avg_onsite_time 
    199. from
    200. (
    201. select 
    202. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    203. 	DATEDIFF(hh,DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00'),(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=553)) avg_onsitetime
    204. from
    205. 	VAPP_ITEM as vi
    206. where 
    207. 	(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=553) is not null 
    208. 	and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    209. 	and person1_root_org_name = @kehu
    210. ) as t
    211. group by t.cr_date;
    212.  
    213.  
    214.  
    215. #9， No_onsite_time 最近30天没有上门时间的工单数量且ccti=hw 按照创建时间排序 【已完成】
    216. declare @kehu VARCHAR(20)
    217. set @kehu ='WTC';
    218.  
    219.  
    220.  
    221. select 
    222. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    223. 	COUNT(TICKET_ID) No_onsite_time
    224. from
    225. 	VAPP_ITEM as vi
    226. where 
    227. 	(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=553) is null 
    228. 	and CCTI_CLASS='HW' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    229. 	and person1_root_org_name = @kehu
    230. GROUP BY 
    231. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);
    232.  
    233. #10，Onsite_1 最近30天 重复上门工单数量 统计有第二次和第三次上门时间的工单 按照创建时间分组 【已完成】
    234. declare @kehu VARCHAR(20)
    235. set @kehu ='WTC';
    236.  
    237.  
    238.  
    239. select 
    240. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    241. 	COUNT(TICKET_ID) Onsite_1
    242. from
    243. 	VAPP_ITEM as vi
    244. where 
    245. 	(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=row_id and va.ATTR_ID=555) is not null and (SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=row_id and va.ATTR_ID=558) is not null and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    246. 	and person1_root_org_name = @kehu
    247. GROUP BY 
    248. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);
    249.  
    250.  
    251.  
    252.  
    253.  
    254. ---------------------------------------------------------------------------------------------------------
    255.  
    256.  
    257. #综合报表
    258. 	t1.cr_date 日期,
    259. 	
    260. declare @kehu VARCHAR(20)
    261. set @kehu ='WTC';
    262.  
    263. select 
    264. 	t2.Total_call,
    265. 	t3.Uniclosed,
    266. 	t4.Scheduled,
    267. 	t5.P1_call,
    268. 	t6.Over_SLA,
    269. 	datename(day,t1.cr_date) 日期,
    270. 	t7.SLA_Met,
    271. 	t8.Worst_TAT,
    272. 	t9.Avg_onsite_time,
    273. 	t10.No_onsite_time,
    274. 	t11.Onsite_1
    275. from
    276. 	(select convert(varchar(10),dateadd(dd,number,dateadd(month,-1,getdate())),120) as cr_date
    277. 	from master..spt_values 
    278. 	where type='P' and 
    279. 	dateadd(dd,number,dateadd(month,-1,getdate()))<=getdate()) 
    280. 	as t1 
    281. left join 
    282. 	(select 
    283. 		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
    284. 		COUNT(TICKET_ID) Total_call
    285. 	from 
    286. 		VAPP_ITEM
    287. 	where 
    288. 		datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    289. 		and person1_root_org_name = @kehu
    290. 	group by 
    291. 		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
    292. 	)
    293. 	as t2 
    294. 	on t1.cr_date=t2.cr_date
    295. left join 
    296. 	(
    297. 	select 
    298. 		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
    299. 		COUNT(TICKET_ID) Uniclosed
    300. 	from 
    301. 		VAPP_ITEM
    302. 	where 
    303. 		datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and TICKET_STATUS IN ('Active','New','Queued') and person1_root_org_name = @kehu
    304. 	group by 
    305. 		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
    306. 	)
    307. 	as t3 
    308. 	on t1.cr_date=t3.cr_date 
    309. left join 
    310. 	(
    311. 	SELECT
    312. 		a.cr_date,
    313. 		COUNT ( TICKET_ID ) Scheduled
    314. 	from
    315. 			(Select CONVERT (VARCHAR ( 10 ),
    316. 			DATEADD(SECOND, (select top 1 sla_due_by from VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), 	'1970/1/1 08:00:00') ,120) as 'cr_date',
    317. 		vi.TICKET_ID
    318. 		from VAPP_ITEM as vi where  person1_root_org_name = @kehu and TICKET_STATUS not in ('closed') and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,vi.CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30) as a
    319. 	GROUP BY 
    320. 		a.cr_date
    321. 	)
    322. 	as t4  
    323. 	on t1.cr_date=t4.cr_date
    324. left join 
    325. 	(select 
    326. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    327. 	count(TICKET_ID) P1_call
    328. from
    329. 	VAPP_ITEM
    330. where 
    331. 	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and Ticket_priority='P2'
    332. 	and person1_root_org_name = @kehu
    333. GROUP BY 
    334. 	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
    335. 	)
    336. 	as t5
    337. 	on t1.cr_date=t5.cr_date
    338. left join
    339. 	(
    340. 	select 
    341. 		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    342. 		count(TICKET_ID) Over_SLA
    343. 	from
    344. 		VAPP_ITEM
    345. 	where 
    346. 		sla_compliance_status_indicator='Breached SLA' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and person1_root_org_name = @kehu
    347. 	GROUP by 
    348. 		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
    349. 	)
    350.  as t6
    351.  on t1.cr_date=t6.cr_date
    352. left join
    353. 		(
    354. 		select 
    355. 		a.cr_date,
    356. 		(a.xjsl/b.gdsl) SLA_Met
    357. 	from
    358. 			(select 
    359. 			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
    360. 			COUNT(TICKET_ID) xjsl
    361. 		from 
    362. 			VAPP_ITEM
    363. 		where 
    364. 			datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    365. 		 AND TICKET_STATUS NOT IN ('Active','New','Queued') AND sla_compliance_status_indicator NOT IN ('Breached SLA')
    366. 		 and person1_root_org_name = @kehu
    367. 		group by 
    368. 			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)) as a 
    369. 		 join 
    370. 		(select 
    371. 			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
    372. 			COUNT(TICKET_ID) gdsl
    373. 		from 
    374. 			VAPP_ITEM
    375. 		where 
    376. 			datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    377. 		 and TICKET_STATUS NOT IN ('Active','New','Queued')
    378. 		 and person1_root_org_name = @kehu
    379. 		group by 
    380. 			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)) as b 
    381. 		 on a.cr_date=b.cr_date
    382. 		)
    383. 	as t7
    384. 	on t1.cr_date=t7.cr_date
    385. left join
    386. 		(
    387. 		select 
    388. 		b.cr_date,
    389. 		max(b.zd) Worst_TAT
    390. 	from 
    391. 		(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    392. 		convert(varchar(10),(DATEADD(S,CLOSED_DATE,'1970/1/1 08:00:00') -DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),108) zd
    393. 		from
    394. 		VAPP_ITEM
    395. 		where TICKET_STATUS='Closed' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    396. 		and person1_root_org_name = @kehu
    397. 		) b
    398. 	group by 
    399. 		b.cr_date
    400. 		)
    401. 	as t8
    402. 	on t1.cr_date=t8.cr_date
    403. left join
    404. 			(
    405. 			select t.cr_date,avg(t.avg_onsitetime) Avg_onsite_time
    406. 		from
    407. 		(
    408. 		select 
    409. 			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    410. 			DATEDIFF(hh,DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00'),(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=553)) avg_onsitetime
    411. 		from
    412. 			VAPP_ITEM as vi
    413. 		where 
    414. 			(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=553) is not null 
    415. 			and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    416. 			and person1_root_org_name = @kehu
    417. 		) as t
    418. 		group by t.cr_date
    419. 			)
    420. 	as t9
    421. 	on t1.cr_date=t9.cr_date
    422. left join 
    423. 		(
    424. 		select 
    425. 			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    426. 			COUNT(TICKET_ID) No_onsite_time
    427. 		from
    428. 			VAPP_ITEM as vi
    429. 		where 
    430. 			(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.		ROW_ID and va.ATTR_ID=553) is null 
    431. 			and CCTI_CLASS='HW' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    432. 			and person1_root_org_name = @kehu
    433. 		GROUP BY 
    434. 			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
    435. 		)
    436. 	as t10
    437. 	on t1.cr_date=t10.cr_date
    438. left join
    439. 		(
    440. 		select 
    441. 			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
    442. 			COUNT(TICKET_ID) Onsite_1
    443. 		from
    444. 			VAPP_ITEM as vi
    445. 		where 
    446. 			(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=row_id and va.ATTR_ID=555) is not null and (SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=row_id and va.ATTR_ID=558) is not null and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
    447. 			and person1_root_org_name = @kehu
    448. 		GROUP BY 
    449. 			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
    450. 		)
    451. 	as t11
    452. 	on t1.cr_date=t11.cr_date;
    453.  
    454.  
    455.  
    456.  
    457.  
    458.  
    459.  
    460.  
    461.  
    462.  
    463.  
    464.  
    465.  
    466.  
    467.  
    468.  
    469.  
    470.  
    471.  
    472.  
    473.

  • 相关阅读:
    app一键加固加签名脚本 百度加固 window版本
    山形三元组
    EXTJS 中grid 动态增加列的方法
    Jtti：新加坡云服务器怎么部署javaweb
    [echarts] 折线图+柱状图，多 Y 轴
    Kubernetes技术与架构-14
    Educational Codeforces Round 155 (Rated for Div. 2)
    三种引流方法&案例分析
    Springboot+美妆网站的设计与实现 毕业设计-附源码211539
    神经网络的图像识别技术,神经网络识别物体形状
  • 原文地址：https://blog.csdn.net/weixin_45774059/article/details/126480559