Opening balance for each column

Dear Friends
I need help to get this result
hhhhh

Please provide:

  • table structure in the form of create statement(s)
  • sample data in the form of insert statement(s)
  • your query so far (and error messages, if any)
  • expected output from the sample data you provide

Hi bitsmed and Samir

I have tried to create sample data
Please check if ok

Create Data Script
drop table harTemp
go 

create 	table	harTemp				
(						
Name	varchar	(	100	)	null	,
A	int				null	,
B	int				null	,
C	int				null	
)
go

insert	into 	harTemp	select	'	Opening	'	,	100	,	null	,	null
insert	into 	harTemp	select	'	Add +	'	,	200	,	300	,	50
insert	into 	harTemp	select	'	Exch -	'	,	50	,	200	,	200


select * from harTemp
go

hi

i have posted create data script earlier

Here i tried to do it
Its not over ..

Here is my SQL

my SQL
SELECT NAME, 
       a, 
       b, 
       c 
FROM   hartemp 
UNION ALL 
SELECT 'Balance', 
       Sum(CASE 
             WHEN NAME LIKE '%Exch%' THEN -1 
             ELSE 1 
           END * a), 
       Sum(CASE WHEN NAME LIKE '%Exch%' THEN -1 ELSE 1 END *a) + Sum(CASE WHEN 
       NAME 
       LIKE '%Exch%' THEN -1 ELSE 1 END *b), 
       Sum(CASE WHEN NAME LIKE '%Exch%' THEN -1 ELSE 1 END *a) + Sum(CASE WHEN 
       NAME 
       LIKE '%Exch%' THEN -1 ELSE 1 END *b) + Sum(CASE WHEN NAME LIKE '%Exch%' 
       THEN -1 
       ELSE 1 END *c) 
FROM   hartemp

My Result SET
I am getting Balance correctly
I have to work ON getting the arrows part

My Result

image

Hi bitsmed and Samir

i have got the result

Here is the SQL

SQL
SELECT dd.NAME, 
           dd.a, 
           Isnull(dd.b, crossb), 
           Isnull(dd.c, crossc) 
    FROM   (SELECT NAME, 
                   a, 
                   b, 
                   c 
            FROM   hartemp 
            UNION ALL 
            SELECT 'Balance', 
                   Sum(CASE 
                         WHEN NAME LIKE '%Exch%' THEN -1 
                         ELSE 1 
                       END * a), 
                   Sum(CASE WHEN NAME LIKE '%Exch%' THEN -1 ELSE 1 END *a) + Sum( 
                   CASE WHEN 
                   NAME 
                   LIKE '%Exch%' THEN -1 ELSE 1 END *b), 
                   Sum(CASE WHEN NAME LIKE '%Exch%' THEN -1 ELSE 1 END *a) + Sum( 
                   CASE WHEN 
                   NAME 
                   LIKE '%Exch%' THEN -1 ELSE 1 END *b) + Sum(CASE WHEN NAME LIKE 
                   '%Exch%' 
                   THEN -1 
                   ELSE 1 END *c) 
            FROM   hartemp) dd 
           CROSS JOIN (SELECT 'Balance'                            AS CrossBal, 
                              Sum(CASE 
                                    WHEN NAME LIKE '%Exch%' THEN -1 
                                    ELSE 1 
                                  END * a)                         AS CrossA, 
                              Sum(CASE WHEN NAME LIKE '%Exch%' THEN -1 ELSE 1 END *a 
                              ) + Sum 
                                                  (CASE WHEN 
                                                             NAME 
                              LIKE '%Exch%' THEN -1 ELSE 1 END *b) AS CrossB, 
                              Sum(CASE WHEN NAME LIKE '%Exch%' THEN -1 ELSE 1 END *a 
                              ) + Sum 
                                                  (CASE WHEN 
                                                             NAME 
                              LIKE '%Exch%' THEN -1 ELSE 1 END *b) + Sum(CASE WHEN 
                              NAME 
                                                  LIKE '%Exch%' 
                                                             THEN -1 
                              ELSE 1 END *c)                       AS CrossC 
                       FROM   hartemp) gg

Here is the Output

Output