SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Total value need to show row in EMP Basis
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kond.mohan
Posting Yak Master

India
194 Posts

Posted - 06/20/2013 :  03:16:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/20/2013 :  03:26:43  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/20/2013 :  03:39:11  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000