如何通过商店程序在sql中获取当前月份和当前年份的工资？ [英] how to fetch salary by current month and current year in sql by store procedure?

问题描述

我的表名 - ：

tbl_Salary和字段是 - ：



ID，EmpID，年份，1月，2月，3月， 4月，5月，6月，7月，8月，9月，10月，11月，12月





我已经按当前年份取得以下这个商店程序 - ：

My Table Name-:
tbl_Salary and fields are-:

ID, EmpID, Year, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sept, Oct, Nov, Dec


I have already fetch by current Year to below this store procedure-:

Select t.ID,t.EmpID,t.Year ,(isnull(t.Jan,''))as Jan,(isnull(t.Feb,''))as Feb,(isnull(t.Mar,''))as Mar,(isnull(t.Apr,''))as Apr,
(isnull(t.May,''))as May,(isnull(t.Jun,''))as Jun,(isnull(t.Jul,''))as Jul,(isnull(t.Aug,''))as Aug,
(isnull(t.Sept,''))as Sept,(isnull(t.Oct,''))as Oct,(isnull(t.Nov,''))as Nov,(isnull(t.Dec,''))as Dec,
studentId,name from tbl_Student s left outer join dbo.tbl_Salary t on s.studentId = t.EmpID and t.[Year]=2014
order by studentId desc

所以，请帮助我按当前月份和年份获取工资。

So, please help me to fetch salary by current month and year.

推荐答案

你需要生成如下的动态SQL

you need to generate dynamic SQL like below

DECLARE @sql NVARCHAR(2000)

SET @sql = 'Select t.ID,t.EmpID,t.Year,t.' +DATENAME(month, GETDATE())+' from tbl_Student s 
left outer join dbo.tbl_Salary t on s.studentId = t.EmpID and t.[Year]='+DATENAME(year, GETDATE()) +
' order by studentId desc';

EXECUTE (@sql) 

如果你正在做薪水或雇佣管理人员，表结构不好。

Table structure is not good if you are doing salary or employing management stuff.

CREATE TABLE Salary(
ID INT IDENTITY(2,2), --primary key?
EmpId INT --foregin key?
SalDate DATETIME<br>
)

上表是正确的方式



您可以在以下条件下找到结果

the above table is right way

you can find result with following where condition

Select *
from Salary
where DATEPART(M,SalDate)=DATEPART(M,GETDATE()) and DATEPART(YEAR,SalDate)=DATEPART(YEAR,GETDATE())
order by studentId desc

你可以做必要的连接来获得你想要的结果集。

you can do neccessary join to get your desired result set.

这篇关于如何通过商店程序在sql中获取当前月份和当前年份的工资？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！