ASP.NET

Sql query problem RSS

8 replies

Last post Dec 07, 2012 10:54 AM by eren23

‹ Previous Thread|Next Thread ›

- Reply
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
- Edited by eren23 on Dec 06, 2012 12:57 PM
- Edited by eren23 on Dec 06, 2012 01:01 PM
- Edited by eren23 on Dec 06, 2012 01:03 PM
- Edited by eren23 on Dec 06, 2012 01:12 PM
- Reply
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.
- Reply
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

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

Yogesh Bhadauriya
.Net All About
MCC

- Reply
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
```
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
```
Sandeep Mittal | My Blog - IT Developer Zone
- Edited by sandeepmittal11 on Dec 07, 2012 09:48 AM
- Edited by sandeepmittal11 on Dec 07, 2012 09:49 AM
- Reply
yrb.yogi

Star

14460 Points

2402 Posts

Re: Sql query problem

Dec 07, 2012 09:57 AM|LINK

sandeepmittal11

Dear Yogi

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

Yogesh Bhadauriya
.Net All About

yrb.yogi

Star

14460 Points

2402 Posts

Re: Sql query problem

Dec 07, 2012 10:04 AM|LINK

sandeepmittal11

Dear Yogi

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

Yogesh Bhadauriya
.Net All About
MCC

Marked as answer by eren23 on Dec 07, 2012 10:53 AM

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

Sandeep Mittal | My Blog - IT Developer Zone

Edited by sandeepmittal11 on Dec 07, 2012 10:20 AM
Edited by sandeepmittal11 on Dec 07, 2012 10:21 AM
Marked as answer by eren23 on Dec 07, 2012 10:53 AM

- Reply
eren23

Member

16 Points

54 Posts

Re: Sql query problem

Dec 07, 2012 10:54 AM|LINK

It works excellent!!!Thank you!!

‹ Previous Thread|Next Thread ›

Sql query problem RSS

8 replies

eren23

Sql query problem

swapna.anu

Re: Sql query problem

eren23

Re: Sql query problem

yrb.yogi

Re: Sql query problem

sandeepmitta...

Re: Sql query problem

yrb.yogi

Re: Sql query problem

sandeepmittal11

eren23

yrb.yogi

Re: Sql query problem

sandeepmittal11

sandeepmitta...

Re: Sql query problem

eren23

Re: Sql query problem

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft