We help IT Professionals succeed at work.

sql

hrvica5
hrvica5 used Ask the Experts™
on
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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pls. explain followings

What is count?
What is Position?
What is exp?

- Deepak Lakkad

Author

Commented:
Count is RowCount, 1,2,3,4,5
Position is fixed 751200, for everything except for sum of calculation 280110 (count 004)
Exp. is it has to be written - B and date(today)
what is the equation to get value  131.05   and  -700.00?

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


Author

Commented:
131, 05 is mispelled it should be 131,9, sorry

1001 >250    (250*(1-100/123)) = 46,75
1002 >400    (400*(1-100/123)) = 74,80
1003 >  50    (50*(1-100/123)) =      9,35
----------------------------------------------------------
                                                          131,90

280110 and 5030 are fixed values and should be just written.

Thank you very much.
                                                             
Pls. try following code, you have to add fields for your fixed values into query

-- 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

Open in new window


- Deepak Lakkad

Author

Commented:
Amounts are not fixed, but they are something like this 4454,86
i put like this because it is easier to follow.


Thx
In code, pls. ignore line no 1 to 30 because, i have added it to create sample data and table for testing purpose only.

- Deepak Lakkad

Author

Commented:
This is great thank you, everything is ok, but how do i get only recordcount for row.
1
2
3
4
???

Thank you this was very helpful
modified query

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

Open in new window


- Deepak Lakkad
Top Expert 2011

Commented:
like this
;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

Open in new window

Author

Commented:
deepaklakkad:
last two don't have rownumber but only first,
these alsho should have rownumbers