显示所有月份的员工工资 [英] show employee salary of all months

问题描述

你好朋友，

i希望为员工工资显示这样的数据。

如果我在nov2012到2013年2月之间搜索数据

名称nov2012 dec2012 jan2013 
 pawan 31110 22020 2000 
 pawan 31110 22020 2000 
 pawan 31110 22020 2000 
 pawan 31110 22020 2000 

这是我的查询

选择id ，姓名，父亲姓名，公寓，[1月]，[2月]，[3月]，[4月]，[5月]，[6月]，[7月]，[8月]，[9月]，[10月]，[11月] ，[Dec] 
来自
（
选择e.cemployeecode作为id，e.cfirstname + space（1）+ e.clastname作为名称，e.cfatherfirstname + space（1）+ e .cfatherlastname as fathername，
 
 CAST（DateName（MONTH，cast（cast（i.lmonthid as varchar（200））+'/ 01 /'+ cast（i.lyearid as varchar（200）） as datetime））as varchar（3））
 as Mon，round（i.HWF，2）as HWF，ed.cdepartment 
 from invoice i inner join employee e on e.cem ployeecode = i.cemployeeid 
内部联接employeedepartment ed ed.ldepartmentid = e.ldepartmentid 
 
其中i.lyearid = @ yeidid和i.lcompanyid = @ companyid 
 
）作为SourceTable 
 
 PIVOT 
（
总和（HWF）
对于（[1]，[2]，[3月]，[4月]的周一， [May]，[Jun]，[Jul]，[Aug]，[Sep]，[Oct]，[Nov]，[Dec]）
）as pivotTable 

Plz帮我根据我的要求纠正它。

解决方案

http://social .msdn.microsoft.com /论坛/ SQLSERVER / EN-US / f588fbcb-f523-4189-aef6-526b4eee10c1 /存储过程对获得-monthwise-收入和费用？论坛= TRANSACTSQL [< a href =http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f588fbcb-f523-4189-aef6-526b4eee10c1/stored-procedure-to-get-monthwise-income-and -expense？forum = transactsqltarget =_ blanktitle =New Window> ^ ]





i think它可以帮助你...........

请参考这里，

http://stackoverflow.com/questions/12790511/show-salary-history-in-a-table -format-from-a-listing-format [ ^ ]

您好，



请参考以下链接，它将帮助您解决问题（使用PIVOT工资）



http://indiandotnet.wordpress.com/2009/03/12/pivot-in-sql-server/ [ ^ ]

hello friends ,
i want to display data like this for employee salary.
if i search data between nov2012 to feb 2013

name      nov2012    dec2012     jan2013
pawan     31110      22020         2000
pawan     31110      22020         2000
pawan     31110      22020         2000
pawan     31110      22020         2000

this is my query

Select  id,name,fathername,cdepartment, [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
From
(
    Select e.cemployeecode as id,e.cfirstname+space(1)+e.clastname as name,e.cfatherfirstname+space(1)+e.cfatherlastname as fathername,    

    CAST(DateName(MONTH,cast(cast (i.lmonthid as varchar(200))+'/01/'+cast(i.lyearid as varchar(200)) as datetime)) as varchar(3))
     as Mon, round(i.HWF,2) as HWF,ed.cdepartment
        From invoice i inner join employee e on e.cemployeecode=i.cemployeeid
        inner join employeedepartment ed on ed.ldepartmentid=e.ldepartmentid
        
          where i.lyearid=@yearid and i.lcompanyid =@companyid

) as SourceTable

PIVOT
(
    Sum(HWF)
    For Mon in ( [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec] )
) as PivotTable

Plz help me rectifying it according to my requirement.

解决方案

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f588fbcb-f523-4189-aef6-526b4eee10c1/stored-procedure-to-get-monthwise-income-and-expense?forum=transactsql[^]


i think it may help you...........

Refer here,
http://stackoverflow.com/questions/12790511/show-salary-history-in-a-table-format-from-a-listing-format[^]

Hi,

Please refer below Link it will help you in your solution (Salary using PIVOT)

http://indiandotnet.wordpress.com/2009/03/12/pivot-in-sql-server/[^]

这篇关于显示所有月份的员工工资的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！