There are two tables,
Payroll gongzi
Select emp_code, fenqi, GZ form gongzi. Store all employee salary information.
Emp_code fenqi GZ
001 200905 2500
001 200906 2600
001 200907 2600
002 200905 2000
002 200906 2100
002 200907 2300
Empcode = employee ID primary key
Fenqi = primary key of monthly installment
Gz = total monthly salary
Bonus table
Select emp_code, date, money, Rem form Jiangjin
Emp_code date money Rem
001 200 May 1 bonus
001 400 Prize
001 100 Prize
001 150 Prize
001 100 Prize
002 300 May 1 bonus
002 500 prize
002 200 Prize
002 250 Prize
002 300 Prize
Emp_code = employee ID, associated with the payroll ..
Date = specifies the date on which the bonus adjustment is recorded. It must also be associated with the installment of the payroll. For example, 2009-05-08,2009-05-31 should correspond to 200905 installments of the payroll.
Money = bonus, which is subject to the last adjustment and is included in the total salary at the end of the month.
Rem = remarks: record the reason for adjusting the bonus.
Now I have written a statement
Select top 1G. emp_code, fenqi, GZ, date, money, Rem
From gongzi g, Jiangjin J
Where G. emp_code = J. emp_code and G. fenqi = '000000'
And (datename (YY, date) + datename (mm, date) = g. fenqi
And G. emp_code = '001'
Order by date DESC
Query the information of a person in a specified period, including the bonus items adjusted after the group. You do not need to accumulate the total salary, because the bonus items are included in the adjustment.
The query result is
Emp_code fenqi GZ date money Rem
001 20090525002009-05-31 00:00:00. 000 400 Prize
Write another line to query the July information of the 001 employee.
Select top 1G. emp_code, fenqi, GZ, date, money, Rem
From gongzi g, Jiangjin J
Where G. emp_code = J. emp_code and G. fenqi = '000000'
And (datename (YY, date) + datename (mm, date) = g. fenqi
And G. emp_code = '001'
Order by date DESC
The query result is
Emp_code fenqi GZ date money Rem
001 20090626002009-06-30 00:00:00. 000 100 Prize
But now there is a problem. It's okay if one employee checks it separately.
The problem is that I select multiple employees for query. The result I want is that an employee has only one data record within the specified period, which is consistent with the individual query.
I tried to change the SQL statement
Select top 1G. emp_code, fenqi, GZ, date, money, Rem
From gongzi g, Jiangjin J
Where G. emp_code = J. emp_code and G. fenqi = '000000'
And (datename (YY, date) + datename (mm, date) = g. fenqi
And G. emp_code in ('001', '002 ')
Order by date DESC
The query result is
Emp_code fenqi GZ date money Rem
001 20090525002009-05-31 00:00:00. 000 400 Prize
The result I want is
The query result is
Emp_code fenqi GZ date money Rem
001 20090525002009-05-31 00:00:00. 000 400 Prize
002 200905 2000 00:00:00. 000 500 prize
Do not know how to write? Thank you for helping me.
The scripts for the two tables are as follows:
If exists (select * From sysobjects where id = object_id ('[gongzi]') and objectproperty (ID, 'isusertable') = 1)
Drop table [gongzi]
Create Table [gongzi] (
[Emp_code] [varchar] (50) not null,
[Fenqi] [varchar] (50) not null,
[GZ] [int] Null)
Alter table [gongzi] With nocheck add constraint [pk_gongzi] primary key nonclustered ([emp_code])
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('001', '123', 200905)
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('001', '123', 200906)
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('001', '123', 200907)
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('002 ', '123', 200905)
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('002 ', '123', 200906)
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('002 ', '123', 200907)
If exists (select * From sysobjects where id = object_id ('[Jiangjin]') and objectproperty (ID, 'isusertable') = 1)
Drop table [Jiangjin]
Create Table [Jiangjin] (
[Emp_code] [varchar] (50) null,
[Date] [datetime] Null,
[Money] [int] Null,
[REM] [varchar] (50) null)
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('001', '2017-05-08 0:00:00 ', 2009, 'May 1 ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('001', '2017-05-31 0:00:00 ', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('001', '2017-06-15 0:00:00 ', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('001', '2017-06-19 0:00:00 ', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('001', '2017-06-30 0:00:00 ', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('002 ', '2017-05-08 0:00:00', 2009, 'May 1 ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('002 ', '2017-05-31 0:00:00', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('002 ', '2017-06-15 0:00:00', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('002 ', '2017-06-19 0:00:00', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('002 ', '2017-06-30 0:00:00', 2009, 'bonus ')