Employee will have to get only that information

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Employee will have to get only that information

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-06-04 : 02:51:34

Hi all,
i have Prepared one query in SSMS 2008 for all employees information for My reporting purpose on product series basis.(total 270 employees).my query and out put is below mentioned
my requirement is i Need to send the information Respective to that specified employee(Instead of Creating 270 objects).on MAIL.i have to send the only employee information that employee through mail

Example .
Monojkumar will have to get only monojkumar information(columns) only.

with cte_test
as
(
SELECT EMP_NO,[EMPNAME] ,CASE WHEN sum( [TOTALVALUE])=0 THEN NULL ELSE sum( [TOTALVALUE]) END as tv, [PRODUCT SERIES],
ROW_NUMBER() OVER (PARTITION BY [EMPname] ORDER BY empname) AS RN --,sum( FKLMG) as fk
FROM ZSSR_REPORT_TABLE
group by EMP_NO,EMPNAME,[PRODUCT SERIES]-- ,FKLMG
)
select EMP_NO,
case when cte_test.RN=1 then cte_test.EMPNAME else '' end AS EMP_NAME,
[PRODUCT SERIES],
tv as TOTALVALUE--,fk as fklmg
,ED.Branch,ED.Area_Covered,ED.Division,ED.Budget_PA,ED.Budget_PM,ED.Place_of_Posting,ED.Reporting_to_1st_Level,ED.EmpName,
ED.Segment
from cte_test
inner JOIN EMPLOYEE_DETAILS ED on cte_test.EMP_NO=ed.EmpID
group by cte_test.EMP_NO,cte_test.EMPNAME,cte_test.RN,cte_test.[PRODUCT SERIES],cte_test.tv,ed.Segment,ed.Branch,
ed.Area_Covered,ed.Reporting_to_1st_Level,ed.Place_of_Posting,ed.Budget_PA,ed.Budget_PM,ed.Division,ed.EmpName

175 RobinDominic 1776 400024.41 Cochin Trirssur 10 156 13 Tirssur Sumesh Dominic Robin Retail
175 252 1582080.00 Cochin Trirssur 10 156 13 Tirssur Sumesh Dominic Robin Retail
ManojkumarT 1776 319885.87 Cochin Kannur,Kasargode 10 168 14 Kannur Sumesh Manojkumar T Retail
252 99034.52 Cochin Kannur,Kasargode 10 168 14 Kannur Sumesh Manojkumar T Retail
254 28483.26 Cochin Kannur,Kasargode 10 168 14 Kannur Sumesh Manojkumar T Retail

if anybody knows pls guide me on this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 03:00:40

pass either EmpName or Emp_NO as a parameter and add a filter on them

like


create proc get employeeinfo
@EmpName varchar(100)
AS
;with cte_test
as
(
SELECT EMP_NO,[EMPNAME] ,CASE WHEN sum( [TOTALVALUE])=0 THEN NULL ELSE sum( [TOTALVALUE]) END as tv, [PRODUCT SERIES], 
ROW_NUMBER() OVER (PARTITION BY [EMPname] ORDER BY empname) AS RN --,sum( FKLMG) as fk
FROM ZSSR_REPORT_TABLE
group by EMP_NO,EMPNAME,[PRODUCT SERIES]-- ,FKLMG
)
select EMP_NO,
case when cte_test.RN=1 then cte_test.EMPNAME else '' end AS EMP_NAME,
[PRODUCT SERIES],
tv as TOTALVALUE--,fk as fklmg 
,ED.Branch,ED.Area_Covered,ED.Division,ED.Budget_PA,ED.Budget_PM,ED.Place_of_Posting,ED.Reporting_to_1st_Level,ED.EmpName,
ED.Segment
from cte_test
inner JOIN EMPLOYEE_DETAILS ED on cte_test.EMP_NO=ed.EmpID
WHERE cte_test.EMPNAME = @EmpName
group by cte_test.EMP_NO,cte_test.EMPNAME,cte_test.RN,cte_test.[PRODUCT SERIES],cte_test.tv,ed.Segment,ed.Branch,
ed.Area_Covered,ed.Reporting_to_1st_Level,ed.Place_of_Posting,ed.Budget_PA,ed.Budget_PM,ed.Division,ed.EmpName

go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources