Dear Friends
I need help to get this result
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
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