İ have problem about sql query.There are two person and 8 different accountsuffix.I want to share 50.000$ for InsurenceFund column which is equal to Balance column.When money achieves the 50.000$ i write 0 remain of InsurunceFund columns
This is before the query
PersonID Name AccounNumber AccountSuffix Balance InsurunceFund
1 Eren 1245 1 20000 ?
1 Eren 1245 2 10000 ?
1 Eren 1245 4 20000 ?
1 Eren 1245 1001 30000 ?
1 Eren 1245 5001 5000 ?
2 Jack 1245 2 35000 ?
2 JACk 1245 23 15000 ?
2 Jack 1245 42 20000 ?
The Result will be
PersonID Name AccounNumber AccountSuffix Balance InsurunceFund
DECLARE @TABLE TABLE
(
PersonID INT,Name VARCHAR(100),
AccounNumber INT,AccountSuffix INT,
Balance INT
)
INSERT INTO @TABLE
SELECT 1,'Eren',1245,1,20000 UNION ALL
SELECT 1,'Eren',1245,2,10000 UNION ALL
SELECT 1,'Eren',1245,4,20000 UNION ALL
SELECT 1,'Eren',1245,1001,30000 UNION ALL
SELECT 1,'Eren',1245,5001,5000 UNION ALL
SELECT 2,'Jack',1245,2,35000 UNION ALL
SELECT 2,'JACk',1245,23,15000 UNION ALL
SELECT 2,'Jack',1245,42,20000
;WITH CTE AS
(
SELECT PersonID,Name,AccounNumber,AccountSuffix,Balance,Balance AS InsurunceFund,
ROW_NUMBER()OVER(ORDER BY PersonID) AS RN
FROM @TABLE
),CTE1 AS
(
SELECT PersonID,Name,AccounNumber,AccountSuffix,Balance,InsurunceFund,Balance AS Total,
RN
FROM CTE
WHERE RN=1
UNION ALL
SELECT t2.PersonID,t2.Name,t2.AccounNumber,t2.AccountSuffix,t2.Balance,
CASE WHEN t2.PersonID=A.PersonID
THEN
CASE WHEN (a.Total)>=50000 THEN 0 ELSE t2.InsurunceFund END
ELSE t2.Balance END AS InsurunceFund,
CASE WHEN t2.PersonID=A.PersonID THEN a.Total+t2.Balance ELSE t2.Balance END AS Total,
t2.RN
FROM CTE1 AS A
INNER Join CTE t2 ON a.RN+1=t2.RN
)
SELECT PersonID,Name,AccounNumber,AccountSuffix,Balance,InsurunceFund
FROM CTE1
--output
--PersonID Name AccounNumber AccountSuffix Balance InsurunceFund
--1 Eren 1245 1 20000 20000
--1 Eren 1245 2 10000 10000
--1 Eren 1245 4 20000 20000
--1 Eren 1245 1001 30000 0
--1 Eren 1245 5001 5000 0
--2 Jack 1245 2 35000 35000
--2 JACk 1245 23 15000 15000
--2 Jack 1245 42 20000 0
Try changing the value of Balance Column to 25000 in 3rd row, then output is generated as
PersonID,Name,AccounNumber,AccountSuffix,Balance,InsurunceFund
1,Eren,1245,1,20000,20000
1,Eren,1245,2,10000,10000
1,Eren,1245,4,25000,25000 --i believe as per Eren requirement, it should be 20000
1,Eren,1245,1001,30000,0
1,Eren,1245,5001,5000,0
2,Jack,1245,2,35000,35000
2,JACk,1245,23,15000,15000
2,Jack,1245,42,20000,0
Try changing the value of Balance Column to 25000 in 3rd row, then output is generated as
You are right. But according to me, balance should be in accordance with 50000. thats why posted above solution. you can also check OP qoute below
eren23
here are two person and 8 different accountsuffix.I want to share 50.000$ for InsurenceFund column which is equal to Balance column.When money achieves the 50.000$ i write 0 remain of InsurunceFund columns
Try changing the value of Balance Column to 25000 in 3rd row, then output is generated as
You can check below query, where only 50000 value is considered.
DECLARE @TABLE TABLE
(
PersonID INT,Name VARCHAR(100),
AccounNumber INT,AccountSuffix INT,
Balance INT
)
INSERT INTO @TABLE
SELECT 1,'Eren',1245,1,20000 UNION ALL
SELECT 1,'Eren',1245,2,35000 UNION ALL
SELECT 1,'Eren',1245,4,25000 UNION ALL
SELECT 1,'Eren',1245,1001,30000 UNION ALL
SELECT 1,'Eren',1245,5001,5000 UNION ALL
SELECT 2,'Jack',1245,2,35000 UNION ALL
SELECT 2,'JACk',1245,23,15000 UNION ALL
SELECT 2,'Jack',1245,42,20000
;WITH CTE AS
(
SELECT PersonID,Name,AccounNumber,AccountSuffix,Balance,Balance AS InsurunceFund,
ROW_NUMBER()OVER(ORDER BY PersonID) AS RN
FROM @TABLE
),CTE1 AS
(
SELECT PersonID,Name,AccounNumber,AccountSuffix,Balance,InsurunceFund,Balance AS Total,
RN,Balance AS BalanceData
FROM CTE
WHERE RN=1
UNION ALL
SELECT t2.PersonID,t2.Name,t2.AccounNumber,t2.AccountSuffix,t2.Balance,
CASE WHEN t2.PersonID=A.PersonID
THEN
CASE WHEN (a.Total)>=50000 THEN 0 ELSE
CASE WHEN BalanceData>50000
THEN t2.InsurunceFund ELSE ABS(BalanceData-50000) END
END
ELSE t2.Balance END AS InsurunceFund,
CASE WHEN t2.PersonID=A.PersonID
THEN
CASE WHEN (a.Total+t2.Balance)>50000
THEN a.Total+t2.Balance else a.Total+t2.Balance end
ELSE t2.Balance END AS Total,T2.RN,
CASE WHEN t2.PersonID=A.PersonID THEN a.Total+t2.Balance ELSE t2.Balance END
FROM CTE1 AS A
INNER Join CTE t2 ON a.RN+1=t2.RN
)
SELECT PersonID,Name,AccounNumber,AccountSuffix,Balance,InsurunceFund
FROM CTE1
--output
--PersonID Name AccounNumber AccountSuffix Balance InsurunceFund
--1 Eren 1245 1 20000 20000
--1 Eren 1245 2 10000 10000
--1 Eren 1245 4 20000 20000
--1 Eren 1245 1001 30000 0
--1 Eren 1245 5001 5000 0
--2 Jack 1245 2 35000 35000
--2 JACk 1245 23 15000 15000
--2 Jack 1245 42 20000 0
DECLARE @TABLE TABLE(
PersonID INT,Name VARCHAR(100),AccounNumber INT,AccountSuffix INT,Balance INT
)
INSERT INTO @TABLE
SELECT 1,'Eren',1245,1,20000 UNION ALL
SELECT 1,'Eren',1245,2,10000 UNION ALL
SELECT 1,'Eren',1245,4,20000 UNION ALL
SELECT 1,'Eren',1245,1001,30000 UNION ALL
SELECT 1,'Eren',1245,5001,5000 UNION ALL
SELECT 2,'Jack',1245,2,35000 UNION ALL
SELECT 2,'JACk',1245,23,15000 UNION ALL
SELECT 2,'Jack',1245,42,20000
DECLARE @Ins_Fund INT
SET @Ins_Fund = 50000
;WITH CTE AS(
SELECT PersonID,Name,AccounNumber,AccountSuffix,Balance
, ROW_NUMBER()OVER (PARTITION BY PersonID ORDER BY AccountSuffix) AS RN
FROM @TABLE
)
SELECT T1.PersonID,T1.Name,T1.AccounNumber,T1.AccountSuffix, T1.Balance
, CASE WHEN TOT<= 50000 THEN T1.Balance WHEN @Ins_Fund-TOT+Balance > 0 THEN @Ins_Fund-TOT+Balance ELSE 0 END as InsurunceFund
FROM CTE T1
INNER JOIN (
SELECT A.PersonID, A.AccountSuffix, SUM(B.Balance) AS TOT
FROM CTE A
LEFT JOIN CTE B ON A.PersonID = B.PersonID AND A.RN >=B.RN
GROUP BY A.PersonID, A.AccountSuffix
) T2 ON T1.PersonID = T2.PersonID AND T1.AccountSuffix = T2.AccountSuffix
eren23
Member
16 Points
54 Posts
Sql query problem
Dec 06, 2012 12:57 PM|LINK
Hi friends,
İ have problem about sql query.There are two person and 8 different accountsuffix.I want to share 50.000$ for InsurenceFund column which is equal to Balance column.When money achieves the 50.000$ i write 0 remain of InsurunceFund columns
This is before the query
PersonID Name AccounNumber AccountSuffix Balance InsurunceFund
1 Eren 1245 1 20000 ?
1 Eren 1245 2 10000 ?
1 Eren 1245 4 20000 ?
1 Eren 1245 1001 30000 ?
1 Eren 1245 5001 5000 ?
2 Jack 1245 2 35000 ?
2 JACk 1245 23 15000 ?
2 Jack 1245 42 20000 ?
The Result will be
PersonID Name AccounNumber AccountSuffix Balance InsurunceFund
1 Eren 1245 1 20000 20000
1 Eren 1245 2 10000 10000
1 Eren 1245 4 20000 20000
1 Eren 1245 1001 30000 0
1 Eren 1245 5001 5000 0
2 Jack 1245 2 35000 35000
2 JACk 1245 23 15000 15000
2 Jack 1245 42 20000 0
swapna.anu
Contributor
2658 Points
745 Posts
Re: Sql query problem
Dec 06, 2012 01:43 PM|LINK
Hi
You can write a procedure taking each customer into cursor and allocate amount in a loop till it becomes zero. Again do the same for next customer.
Hope this helps.
eren23
Member
16 Points
54 Posts
Re: Sql query problem
Dec 06, 2012 02:59 PM|LINK
Yes I do this.but i have 1.000.000 records so it is so slow when i used the while or cursor.Is there any solution for this?
yrb.yogi
Star
14460 Points
2402 Posts
Re: Sql query problem
Dec 07, 2012 07:10 AM|LINK
.Net All About
sandeepmitta...
Contributor
6789 Points
1058 Posts
Re: Sql query problem
Dec 07, 2012 09:48 AM|LINK
Dear Yogi
Try changing the value of Balance Column to 25000 in 3rd row, then output is generated as
Sandeep Mittal | My Blog - IT Developer Zone
yrb.yogi
Star
14460 Points
2402 Posts
Re: Sql query problem
Dec 07, 2012 09:57 AM|LINK
You are right. But according to me, balance should be in accordance with 50000. thats why posted above solution. you can also check OP qoute below
.Net All About
yrb.yogi
Star
14460 Points
2402 Posts
Re: Sql query problem
Dec 07, 2012 10:04 AM|LINK
You can check below query, where only 50000 value is considered.
.Net All About
sandeepmitta...
Contributor
6789 Points
1058 Posts
Re: Sql query problem
Dec 07, 2012 10:20 AM|LINK
DECLARE @TABLE TABLE( PersonID INT,Name VARCHAR(100),AccounNumber INT,AccountSuffix INT,Balance INT ) INSERT INTO @TABLE SELECT 1,'Eren',1245,1,20000 UNION ALL SELECT 1,'Eren',1245,2,10000 UNION ALL SELECT 1,'Eren',1245,4,20000 UNION ALL SELECT 1,'Eren',1245,1001,30000 UNION ALL SELECT 1,'Eren',1245,5001,5000 UNION ALL SELECT 2,'Jack',1245,2,35000 UNION ALL SELECT 2,'JACk',1245,23,15000 UNION ALL SELECT 2,'Jack',1245,42,20000 DECLARE @Ins_Fund INT SET @Ins_Fund = 50000 ;WITH CTE AS( SELECT PersonID,Name,AccounNumber,AccountSuffix,Balance , ROW_NUMBER()OVER (PARTITION BY PersonID ORDER BY AccountSuffix) AS RN FROM @TABLE ) SELECT T1.PersonID,T1.Name,T1.AccounNumber,T1.AccountSuffix, T1.Balance , CASE WHEN TOT<= 50000 THEN T1.Balance WHEN @Ins_Fund-TOT+Balance > 0 THEN @Ins_Fund-TOT+Balance ELSE 0 END as InsurunceFund FROM CTE T1 INNER JOIN ( SELECT A.PersonID, A.AccountSuffix, SUM(B.Balance) AS TOT FROM CTE A LEFT JOIN CTE B ON A.PersonID = B.PersonID AND A.RN >=B.RN GROUP BY A.PersonID, A.AccountSuffix ) T2 ON T1.PersonID = T2.PersonID AND T1.AccountSuffix = T2.AccountSuffixSandeep Mittal | My Blog - IT Developer Zone
eren23
Member
16 Points
54 Posts
Re: Sql query problem
Dec 07, 2012 10:54 AM|LINK
It works excellent!!!Thank you!!