Hi people i have problem and can somebody help me:

I have table like this:

Place Amount

1001 100

1001 150

1002 200

1003 50

1002 200

I have to get tabel like this

Count Position Place Exp. Amount

001 751200 1001 B 19.07.11 203,25

002 751200 1002 B 19.07.11 325,20

003 751200 1003 B 19.07.11 40,5

004 280110 B 19.07.11 131,05

005 751200 5030 B 19.07.11 -700,00

Explanation:

I need to group place for example 1001 and sum amount and I get 250,

but i have number with wich i have to multiply (0,1869918699186992)

and in count 001 it has to be calculation of this 250-(250*(0,1869918699186992))

and difference i had to sum and put in count 004

and in count 005 it has to be sum of everething but in minus.

I hope it is undestandable, thx a lot.

I have table like this:

Place Amount

1001 100

1001 150

1002 200

1003 50

1002 200

I have to get tabel like this

Count Position Place Exp. Amount

001 751200 1001 B 19.07.11 203,25

002 751200 1002 B 19.07.11 325,20

003 751200 1003 B 19.07.11 40,5

004 280110 B 19.07.11 131,05

005 751200 5030 B 19.07.11 -700,00

Explanation:

I need to group place for example 1001 and sum amount and I get 250,

but i have number with wich i have to multiply (0,1869918699186992)

and in count 001 it has to be calculation of this 250-(250*(0,18699186991869

and difference i had to sum and put in count 004

and in count 005 it has to be sum of everething but in minus.

I hope it is undestandable, thx a lot.

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

How do you get value 280110 for Position and 5030 for Place?

```
-- Create Table
CREATE TABLE TableA
(
Place int,
Amount decimal
)
-- Insert Records into Table
INSERT INTO TableA (Place, Amount)
SELECT
1001 AS Place,
100 AS Amount
UNION ALL
SELECT
1001 AS Place,
150 AS Amount
UNION ALL
SELECT
1002 AS Place,
200 AS Amount
UNION ALL
SELECT
1003 AS Place,
50 AS Amount
UNION ALL
SELECT
1002 AS Place,
200 AS Amount
-- Get Desired Result
SELECT
Place,
Sum(Amount) as Column1,
Sum(Amount) * 0.1869918699186992 AS Column2,
Sum(Amount) - (Sum(Amount) * 0.1869918699186992) as Column3
FROM
TableA
GROUP BY
Place
UNION ALL
SELECT
0 AS Place,
0 AS Column1,
Sum(Column2) as Column2,
0 AS Column3
FROM
(
SELECT
Place,
Sum(Amount) as Column1,
Sum(Amount) * 0.1869918699186992 AS Column2,
Sum(Amount) - (Sum(Amount) * 0.1869918699186992) as Column3
FROM
TableA
GROUP BY
Place
) as NewTable
```

- Deepak Lakkad

- Deepak Lakkad

```
SELECT
row_number() OVER (ORDER BY Place) AS SrNo,
Place,
Sum(Amount) as Column1,
Sum(Amount) * 0.1869918699186992 AS Column2,
Sum(Amount) - (Sum(Amount) * 0.1869918699186992) as Column3
FROM
TableA
GROUP BY
Place
UNION ALL
SELECT
0 AS SrNo,
0 AS Place,
0 AS Column1,
Sum(Column2) as Column2,
0 AS Column3
FROM
(
SELECT
Place,
Sum(Amount) as Column1,
Sum(Amount) * 0.1869918699186992 AS Column2,
Sum(Amount) - (Sum(Amount) * 0.1869918699186992) as Column3
FROM
TableA
GROUP BY
Place
) as b
```

- Deepak Lakkad

```
;with cte as
(select place,sum(amount) as amttot
from yourtable
group by place)
,cte1 as
(select place,amttot
,convert(decimal(10,2),round(amttot*0.1869918699186992,2)) as nett
,row_number() over (order by place) as rn
from cte)
,cte2 as
(select sum(amttot)-sum(nett) as nett
,max(rn)+1 as rn
from cte1)
,cte3 as
(select sum(nett) nett
,max(rn)+2 as rn
from cte1)
,cte4 as
(select rn,-(nett+(select nett from cte2)) as full
from cte3
)
select rn as [count]
,'751200' as position
,convert(char(4),place) as place
,'B' as [Exp.]
,convert(char(8),getdate(),4)
,nett as amount
from cte1
union all
select rn,'280110',space(4),'B',convert(char(8),getdate(),4)
,nett
from cte2
union all
select rn,'751200','5030','B',convert(char(8),getdate(),4)
,full
from cte4
order by 1
select rn,'751200','5030','B',convert(char(8),getdate(),4)
,nett
```

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial