Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to calculate total sum of these table Expand / Collapse
Author
Message
Posted Friday, July 11, 2014 12:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
Hi Friends


i ve the table like


create table accutn_det
(
fs_locn char(50),
fs_accno varchar(100),
fs_cost_center varchar(100),
fs_tran_type char(50)
fs_amount numeric(50),
fs_trans_date datetime,

)


insert into accutn_det values('CHN','E-Sw-2100','205produ','Cr','5000','2014-05-01')

values('CHN','E-Sw-2100','205produ','Dr','15000','2014-05-06')
values('HYD','E-Sw-2100','206produ','Dr','8000','2014-05-03')
values('BANG','E-Sw-2100','208produ','Dr','25000','2014-05-01')
values('BANG','E-Sw-2100','208produ','Cr','5000','2014-05-06')

like all loctaion details stored from all months in these table

here Dr=debit,Cr=Credit Formula= 'Dr-Cr' to find the salary wavges of amount


so i made the query to find the amount for may

select
fs_locn,
fs_accno,
amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount
when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1
end
)
from
accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014'
groupby fs_locn,fs_accno



now i need the sum values of all costcenter for the particular account


how to do that?
Post #1591438
Posted Friday, July 11, 2014 12:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 13,570, Visits: 10,447
Add the cost center in the GROUP BY?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1591447
Posted Friday, July 11, 2014 12:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 1,094, Visits: 1,353
Add Cost center (fs_cost_center) in Group by clause.

Thanks
Post #1591459
Posted Friday, July 11, 2014 3:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
Hi Friends

i need out put like

for exmaple :

account no 205prod 206prod total

E-SW-100 10000 3000 13000




how make code?
Post #1591493
Posted Friday, July 11, 2014 3:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 13,570, Visits: 10,447
raghuldrag (7/11/2014)
Hi Friends

i need out put like



account no 205prod 206prod total

E-SW-100 10000 3000 13000




how make code?


How is the 3000 calculated?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1591500
Posted Friday, July 11, 2014 3:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
Hi friends,



i need the output like



Accno 205produ 206produ 208produ total

E-SW-2100 15000 8000 20000 42000



after find the sum of account how to calculate the total value of each costcenter for that account
Post #1591503
Posted Friday, July 11, 2014 3:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 13,570, Visits: 10,447
Is it always 205, 206 and 208, or are there more columns possible?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1591531
Posted Friday, July 11, 2014 4:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
Hi Friends,


The costcneter account wont be changed always same account and unique
Post #1591537
Posted Friday, July 11, 2014 4:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
Hi Friends ,

i just added my code with rollup



select
fs_locn,
fs_accno,
fs_cost_center,
amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount
when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1
end
)
from
accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014'
groupby fs_locn,fs_accno,fs_cost_center with rollup



its giving the sum total value with NULL
how to avoid "Null" replace on TOTAL In That???
Post #1591551
Posted Friday, July 11, 2014 5:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 1,094, Visits: 1,353
Give it a try:
; WITH AccountCTE AS (
SELECT fs_accno, fs_cost_center, fs_amount from accutn_det
)
SELECT fs_accno,[205produ],[206produ],[208produ], COALESCE([205produ], 0)+ COALESCE([206produ], 0)+ COALESCE([208produ],0) AS Total FROM AccountCTE
PIVOT (SUM(fs_amount) FOR fs_cost_center IN ([205produ],[206produ],[208produ])) p



Thanks
Post #1591558
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse