allocation issue. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

allocation issue.

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-10-17 : 01:59:52

tbl_charge

customerid chargeid amount
1 11 10
1 12 12
1 13 16
1 14 13
1 15 60
2 11 15
2 12 18
2 13 22
2 14 24
2 15 85

i have a table tbl_charge that contains charges that are applied to diffrent customer.
for each customer there is row where chargeid 15 , that is the amount that each customer paid against
his charges 11 ,12 ,13 ,14 .
i need to allocate the amount in 11 ,12,13,14 that means the amount in diffrent charge should be decrease and tends to 0
and also after satisfying the charge the 15 is also should be updated.

challenge everything

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-17 : 02:05:49

I am not getting your question.. Can you provide expected output?

--
Chandu

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-10-17 : 02:12:39

customerid chargeid amount
1 11 0
1 12 0
1 13 0
1 14 0
1 15 9
2 11 0
2 12 0
2 13 0
2 14 0
2 15 6

challenge everything

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-17 : 02:41:28

DECLARE @tbl_charge TABLE( customerid int, chargeid int, amount int)
INSERT INTO @tbl_charge
SELECT 1, 11, 10 UNION ALL
SELECT 1, 12, 12 UNION ALL
SELECT 1, 13, 16 UNION ALL
SELECT 1, 14, 13 UNION ALL
SELECT 1, 15, 60 UNION ALL
SELECT 2, 11, 15 UNION ALL
SELECT 2, 12, 18 UNION ALL
SELECT 2, 13, 22 UNION ALL
SELECT 2, 14, 24 UNION ALL
SELECT 2, 15, 85
--SELECT * FROM @tbl_charge
;with cte
AS(SELECT customerid, chargeid, SUM(CASE when chargeid IN (11,12,13,14) THEN amount else 0 end) over (partition by customerid) as sumOfAmount FROM @tbl_charge)
SELECT distinct t.customerid, t.chargeid, case when t.chargeid = 15 then t.amount - sumOfAmount else 0 end as [Amount]
FROM cte c JOIN @tbl_charge t ON c.customerid = t.customerid AND c.chargeid = t.chargeid

--
Chandu

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-10-17 : 06:40:40

i want to update the charges also

challenge everything

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-17 : 06:46:43

quote:
Originally posted by pnpsql

i want to update the charges also

challenge everything

To update amount field as per your requirement,


;with cte 
AS
(SELECT customerid, chargeid, SUM(CASE when chargeid IN (11,12,13,14) THEN amount else 0 end) over (partition by customerid) as sumOfAmount 
 FROM @tbl_charge
)
UPDATE t 
SET t.amount = case when t.chargeid = 15 then t.amount - sumOfAmount else 0 end
FROM cte c JOIN @tbl_charge t ON c.customerid = t.customerid AND c.chargeid = t.chargeid

--
Chandu

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-10-17 : 07:23:36

i have an another table where i need to track the detail like

tblcharge_dtl

customerid chargeid amount
1 11 10
1 12 12

Please help

challenge everything

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-17 : 07:44:29

Before updating tbl_charges,
First you track the details into table tblcharge_dtl

;with cte
AS(SELECT customerid, chargeid, SUM(CASE when chargeid IN (11,12,13,14) THEN amount else 0 end) over (partition by customerid) as sumOfAmount FROM @tbl_charge)
INSERT INTO tbl_charge_dtl
SELECT distinct t.customerid, t.chargeid, case when t.chargeid = 15 then t.amount - sumOfAmount else t.amount end as [Amount]
FROM cte c JOIN @tbl_charge t ON c.customerid = t.customerid AND c.chargeid = t.chargeid

Here am tracking all existing records of charge_ids (11,12,13,14) as it is
And also updated value for chargeid =15
i.e.


cusid	chargeid	Amount
1	11	10
1	12	12
1	13	16
1	14	13
1	15	9
2	11	15
2	12	18
2	13	22
2	14	24
2	15	6

If you want amount of charge id as 60, 85......,
then simply write
INSERT INTO tbl_charge_dtl
SELECT *
FROM tbl_charge

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources