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)
 Total value need to show row in EMP Basis

Author  Topic 

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-06-20 : 03:16:01
Hi All

I preparing Report In SSRS using SSMS2012. my sql query is like below manner.Multiple employee id's are there i have shown 2 emps in the query.

select distinct ed.EmpID,ed.EmpName,zs.kunnr ,zs.ntot outstanding,zs1.DATE1
from SAP_DWH..kna1 k
inner join SAP_DWH..zcustaging_sum zs on k.kunnr=zs.kunnr
inner join SAP_DWH..knvp kp on kp.kunnr=k.kunnr and kp.parvw='PE'
INNER JOIN SAP_DWH..EMPLOYEE_DETAILS ED ON ED.EmpID=KP.PERNR
inner JOIN(SELECT MAX(RUN_dATE) DATE1,
ZS.KUNNR FROM SAP_DWH..ZCUSTAGING_SUM ZS GROUP BY ZS.KUNNR
)ZS1 ON ZS1.DATE1=ZS.RUN_DATE AND ZS1.KUNNR=zs.KUNNR
where ed.EmpIDin(466,396)
above query output is
466 Jana Siddhartha 0000100179 86593.35 2013-06-20
466 Jana Siddhartha 0000100211 11573.39 2013-06-20
466 Jana Siddhartha 0000100235 0.02 2013-06-20
466 Jana Siddhartha 0000100258 404.58 2013-06-20
396 Bhaskar Naidu 0000100318 3170.41 2013-06-20
396 Bhaskar Naidu 0000100470 50605.63 2013-06-20
396 Bhaskar Naidu 0000100555 -0.31 2013-06-20

i need to show the outstanding amt below manner
466 Jana Siddhartha 0000100179 86593.35 2013-06-20
466 Jana Siddhartha 0000100211 11573.39 2013-06-20
466 Jana Siddhartha 0000100235 0.02 2013-06-20
466 Jana Siddhartha 0000100258 404.58 2013-06-20
-------------------------------------------
total outstanding 98570.6
---------------------------------------------------
396 Bhaskar Naidu 0000100318 3170.41 2013-06-20
396 Bhaskar Naidu 0000100470 50605.63 2013-06-20
396 Bhaskar Naidu 0000100555 -0.31 2013-06-20
----------------------------------------------
total outstanding 53775
-------------------------------------------------------


any one know the logic pls explain

mohan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-20 : 03:26:43
DECLARE @rollup TABLE(Id INt, Name VARCHAR(30), kunnr VARCHAR(30), oamt dec(10,2), dates dATE)
INSERT INTO @rollup
SELECT 466, 'Jana Siddhartha', '0000100179', 86593.35, '2013-06-20' union all
SELECT 466, 'Jana Siddhartha', '0000100211', 11573.39 , '2013-06-20' union all
SELECT 466, 'Jana Siddhartha', '0000100235', 0.02 , '2013-06-20' union all
SELECT 466, 'Jana Siddhartha', '0000100258', 404.58 , '2013-06-20' union all
SELECT 396, 'Bhaskar Naidu', '0000100318', 3170.41 , '2013-06-20' union all
SELECT 396, 'Bhaskar Naidu', '0000100470', 50605.63 , '2013-06-20' union all
SELECT 396, 'Bhaskar Naidu', '0000100555', -0.31 , '2013-06-20'

SELECT *
FROM @rollup
ORDER BY ID
COMPUTE SUM(oamt) BY ID

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 03:39:11
i would have done subtotals at the front end (reporting end) using tools like SSRS where its easier to get the required formatting.
Also COMPUTE clause is deprecated and when used the result will involve multiple resultsets which may not work well if you want to use this result for later manipulations.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -