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.
Author |
Topic |
kond.mohan
Posting Yak Master
213 Posts |
Posted - 2013-06-20 : 03:16:01
|
Hi AllI 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.DATE1from 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.KUNNRwhere ed.EmpIDin(466,396)above query output is466 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-20396 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-20i 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 explainmohan |
|
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 @rollupSELECT 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 allSELECT 396, 'Bhaskar Naidu', '0000100318', 3170.41 , '2013-06-20' union all SELECT 396, 'Bhaskar Naidu', '0000100470', 50605.63 , '2013-06-20' union allSELECT 396, 'Bhaskar Naidu', '0000100555', -0.31 , '2013-06-20'SELECT *FROM @rollupORDER BY IDCOMPUTE SUM(oamt) BY ID--Chandu |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|