1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
| use zhengcaioa
|
| if exists (select 1
| from sysobjects
| where id = object_id('t_salary_appeal')
| and type = 'U')
| drop table t_salary_appeal
| go
|
| /*==============================================================*/
| /* Table: t_salary_appeal */
| /*==============================================================*/
| create table t_salary_appeal (
| id int identity,
| salary_id nvarchar(50) null,
| reason nvarchar(1000) null,
| amount decimal(10,2) null,
| basis nvarchar(500) null,
| sub_user nvarchar(50) null,
| sub_time datetime null,
| chk_user nvarchar(50) null,
| chk_time datetime null,
| chk_result nvarchar(1000) null,
| status nvarchar(1) null,
| constraint PK_T_SALARY_APPEAL primary key (id)
| )
| go
|
|
| INSERT [dbo].[plt_page] ([Id], [system_id], [page_name], [display_seq], [page_path], [page_method], [page_type], [page_superior], [open_type], [rec_status], [creater], [createtime]) VALUES (newid(), N'1', N'¹¤×ʺ˶Ô', 10, N'/SalaryCheck/Employee', N'01', N'M', N'6a13cb10-35d9-4176-a231-8887948e059e', 0, N'A', N'e4c93811-b9b1-4998-89f5-c416ebab0c07', getdate())
| go
|
| INSERT [dbo].[plt_page] ([Id], [system_id], [page_name], [display_seq], [page_path], [page_method], [page_type], [page_superior], [open_type], [rec_status], [creater], [createtime]) VALUES (newid(), N'1', N'¹¤×ʲéѯ', 8, N'/SalaryCheck/SearchIndex', N'01', N'M', N'6a13cb10-35d9-4176-a231-8887948e059e', 0, N'A', N'e4c93811-b9b1-4998-89f5-c416ebab0c07', getdate())
| go
|
| INSERT [dbo].[plt_page] ([Id], [system_id], [page_name], [display_seq], [page_path], [page_method], [page_type], [page_superior], [open_type], [rec_status], [creater], [createtime]) VALUES (newid(), N'1', N'¹¤×ʺË×¼', 9, N'/SalaryCheck/Index', N'01', N'M', N'6a13cb10-35d9-4176-a231-8887948e059e', 0, N'A', N'e4c93811-b9b1-4998-89f5-c416ebab0c07', getdate())
| go
|
|
| --2021-6-28 µç»°·Ñ
| SELECT td.sim_id, td.bill_year, td.Jan, td.Feb, td.Mar, td.Apr, td.May, td.Jun, td.Jul, td.Aug, td.Sept, td.Oct, td.Nov, td.Dece,
| b.goods_name AS sim
| FROM (SELECT sim_id, bill_year, SUM(CASE [bill_month] WHEN '1' THEN (bill_amount) ELSE 0 END) AS Jan,
| SUM(CASE [bill_month] WHEN '2' THEN (bill_amount) ELSE 0 END) AS Feb,
| SUM(CASE [bill_month] WHEN '3' THEN (bill_amount) ELSE 0 END) AS Mar,
| SUM(CASE [bill_month] WHEN '4' THEN (bill_amount) ELSE 0 END) AS Apr,
| SUM(CASE [bill_month] WHEN '5' THEN (bill_amount) ELSE 0 END) AS May,
| SUM(CASE [bill_month] WHEN '6' THEN (bill_amount) ELSE 0 END) AS Jun,
| SUM(CASE [bill_month] WHEN '7' THEN (bill_amount) ELSE 0 END) AS Jul,
| SUM(CASE [bill_month] WHEN '8' THEN (bill_amount) ELSE 0 END) AS Aug,
| SUM(CASE [bill_month] WHEN '9' THEN (bill_amount) ELSE 0 END) AS Sept,
| SUM(CASE [bill_month] WHEN '10' THEN (bill_amount) ELSE 0 END) AS Oct,
| SUM(CASE [bill_month] WHEN '11' THEN (bill_amount) ELSE 0 END) AS Nov,
| SUM(CASE [bill_month] WHEN '12' THEN (bill_amount) ELSE 0 END) AS Dece
| FROM dbo.t_sim_cost
| GROUP BY sim_id, bill_year) AS td INNER JOIN
| dbo.adm_goods_manage AS b ON td.sim_id = b.Id
|
|
