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

SQL Server 2005 Forums

Transact-SQL (2005)

Query

Author

Topic

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 07:57:25

Hi Guys

Am in little stuck..

I want to calculate on Carrying_Cost column value by each row.

  
I have table data like this
Sl.No	      Demand	      Period	       SKU	Carrying_Cost
1		10		10		A		0
2		20		20		B		0				---
3		30		30		C		0
4		40		40		D		0

Calculation is



Second Row Calculation is

Carrying_Cost	=	

Carrying_cost of first row value*0.5*100(default value)
+
Demand(current Row)*0.5*100(Default Value0
+
Demand(current Row)*100*Sl.No(first row Value which means previous row value)

First Row  Carrying_Cost Values is First row Demand Value

please help on this.....

thanks..

-------------------------
R..

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-24 : 08:14:57

Could you plug some numbers in to your algorithm to make it clearer what you want?

Jim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-24 : 08:16:27

1. can you show us what is the expected carrying cost value for all the rows ?
2.

quote:
Second Row Calculation is
Carrying_Cost =
Carrying_cost of first row value*0.5*100(default value) is this always first row value or prev row value
+ Demand(current Row)*0.5*100(Default Value0
+ Demand(current Row)*100*Sl.No(first row Value which means previous row value)

First Row Carrying_Cost Values is First row Demand Value

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 08:29:40

[code]
First Row Carrying_Cost is Frist row Demand Value so carrying_cost = 10

Now the table is

Sl.No Demand Period SKU Carrying_Cost
1 10 10 A 10
2 20 20 B 0
3 30 30 C 0
4 40 40 D 0

Now Rest of the row calci is

Carrying_Cost = 10*0.5*100 + 20(current row demand)*0.5*100 + 20*100*1(previous row sl.No) [/code]

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 09:12:17

Hi
khtan & jimf is there........

-------------------------
R..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-24 : 09:20:31

[code]
DECLARE @sample TABLE
(
S1No int,
Demand int,
Period int,
SKU CHAR
)
INSERT INTO @sample
SELECT 1, 10, 10, 'A' UNION ALL
SELECT 2, 20, 20, 'B' UNION ALL
SELECT 3, 30, 30, 'C' UNION ALL
SELECT 4, 40, 40, 'D'

;WITH data(S1No, Demand, Period, SKU, row_no)
AS
(
SELECT S1No, Demand, Period, SKU,
row_no = row_number() OVER (ORDER BY S1No)
FROM @sample
)
SELECT d.S1No, d.Demand, d.Period, d.SKU,
Carrying_Cost = SUM(CASE WHEN d.row_no = 1 THEN c.Demand
WHEN c.row_no = 1 THEN c.Demand * 0.5 * 100
WHEN c.row_no = d.row_no THEN c.Demand * 0.5 * 100
WHEN c.row_no = d.row_no - 1 THEN d.Demand * 100 * c.S1No END)
FROM data d
INNER JOIN data c ON c.row_no = 1
OR (c.row_no >= d.row_no - 1
AND c.row_no <= d.row_no)
GROUP BY d.S1No, d.Demand, d.Period, d.SKU

/*
S1No Demand Period SKU Carrying_Cost
----------- ----------- ----------- ---- ----------------------------------------
1 10 10 A 10.0
2 20 20 B 1500.0
3 30 30 C 8000.0
4 40 40 D 14500.0

(4 row(s) affected)

*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 09:23:58

Hi KH

What is that

FROM ledgertable
INNER JOIN ledgertrans ON ledgertable.accountnum = ledgertrans.accountnum
INNER JOIN vendtrans ON ledgertrans.voucher = vendtrans.voucher
INNER JOIN bankchequetable c ON vendtrans.paymreference = bankchequetable.chequenum

-------------------------
R..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-24 : 09:37:01

C&P error. Edited the post.

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 09:46:59

hi KH

I need one more help. what i trying to say is

First time only we can take the Demand data...which i have mentioned in red color value 10

second time onwarda we can take Carrying_Cost instead of demand data which you have find in first..

second time onwards Carrying_Cost(find value in frist row)*0.5*100
third time onwards Carrying_Cost(find value in second row)*0.5*100
fourth time onwards Carrying_Cost(find value in third row)*0.5*100
like that...

-------------------------
R..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-24 : 09:49:48

can you confirm this is correct ? if not can you show how it should be calculated ?


S1No        Demand      Period      SKU  Carrying_Cost
----------- ----------- ----------- ---- -------------
1           10          10          A    10.0
2           20          20          B    1500.0
3           30          30          C    8000.0
4           40          40          D    14500.0

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 01:31:22

Hi Khtan

Sorry my network was down due to maintenance..

I want like this …pls help on this..

  S1No        Demand      Period      SKU  Carrying_Cost
----------- ----------- ----------- ---- -------------
1           10          10                   A    10.0
2           20          20                   B    3500.0
3           30          30                   C    182500.0
4           40          40                   D    9139000.00

First Time calc 
Carrying_Cost	=	First time only we have to take the value of Carrying_Cost is Demand value = 10


Rest
Carrying_Cost	=	(Previous Row Carrying_Cost)*0.5*100   
+    20(current row demand)*0.5*100	
+	20(current row demand)*100*1(previous row sl.No)

Ex

Carrying_Cost	=	

SELECT 10*0.5*100	+	20*0.5*100	+	20*100*1

SELECT 3500*0.5*100	+	30*0.5*100	+	30*100*2

SELECT 182500.0*0.5*100	+	40*0.5*100	+	40*100*3

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 03:27:00

Hi khtan

Are you there we can discuss now....

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 05:27:08

hi

Any one help on this .....

-------------------------
R..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 06:12:25

[code]
DECLARE @sample TABLE
(
S1No int,
Demand int,
Period int,
SKU CHAR,
Carrying_Cost decimal(10,2)
)
INSERT INTO @sample (S1No, Demand, Period, SKU)
SELECT 1, 10, 10, 'A' UNION ALL
SELECT 2, 20, 20, 'B' UNION ALL
SELECT 3, 30, 30, 'C' UNION ALL
SELECT 4, 40, 40, 'D'

DECLARE @S1No int,
@First_S1No int

SELECT @S1No = MIN(S1No)
FROM @sample

SELECT @First_S1No = @S1No

WHILE @S1No IS NOT NULL
BEGIN
;WITH data(S1No, Demand, Period, SKU, Carrying_Cost, row_no)
AS
(
SELECT S1No, Demand, Period, SKU, Carrying_Cost,
row_no = row_number() OVER (ORDER BY S1No)
FROM @sample
)
UPDATE c
SET Carrying_Cost = CASE WHEN @S1No = @First_S1No
THEN c.Demand
ELSE
(p.Carrying_Cost * 0.5 * 100)
+ (c.Demand * 0.5 * 100)
+ (c.Demand * 100 * p.S1No)
END
FROM data c
LEFT JOIN data p ON c.row_no = p.row_no + 1
WHERE c.S1No = @S1No

SELECT @S1No = MIN(S1No)
FROM @sample
WHERE S1No > @S1No
END

SELECT *
FROM @sample

/*
S1No Demand Period SKU Carrying_Cost
----------- ----------- ----------- ---- -------------
1 10 10 A 10.00
2 20 20 B 3500.00
3 30 30 C 182500.00
4 40 40 D 9139000.00

(4 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 06:21:37

the recursive CTE way


DECLARE @sample TABLE
(
	S1No		int,
	Demand		int,
	Period		int,
	SKU		CHAR,
	Carrying_Cost	decimal(10,2)
)
INSERT INTO @sample (S1No, Demand, Period, SKU)
SELECT	1, 10, 10, 'A' UNION ALL
SELECT	2, 20, 20, 'B' UNION ALL
SELECT	3, 30, 30, 'C' UNION ALL
SELECT	4, 40, 40, 'D'

DECLARE @First_S1No	int

SELECT	@First_S1No	= MIN(S1No)
FROM	@sample

;WITH data (S1No, Demand, Period, SKU, Carrying_Cost, row_no)
AS
(
	-- Anchor Member
	SELECT	S1No, Demand, Period, SKU, 
		Carrying_Cost 	= CONVERT(decimal(10,2), Demand), 
		row_no 		= CONVERT(int, 1)
	FROM	@sample
	WHERE	S1No	= @First_S1No

	UNION ALL
	
	-- Recursive Member
	SELECT	c.S1No, c.Demand, c.Period, c.SKU, 
		Carrying_Cost 	= CONVERT(decimal(10,2),
				  (p.Carrying_Cost * 0.5 * 100)
				+ (c.Demand * 0.5 * 100)
				+ (c.Demand * 100 * p.S1No)),
		row_no	= c.row_no
	FROM	data p
		INNER JOIN 
		(
			SELECT	S1No, Demand, Period, SKU, 
				row_no	= CONVERT(int, row_number() OVER (ORDER BY S1No))
			FROM	@sample
		) c	ON	c.row_no	= p.row_no + 1
)
UPDATE	s
SET	Carrying_Cost	= d.Carrying_Cost
FROM	@sample s
	INNER JOIN data d	ON	s.S1No	= d.S1No

SELECT	*
FROM	@sample

/*
S1No        Demand      Period      SKU  Carrying_Cost 
----------- ----------- ----------- ---- ------------- 
1           10          10          A    10.00
2           20          20          B    3500.00
3           30          30          C    182500.00
4           40          40          D    9139000.00

(4 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 06:37:12

Hi khtan

Thanks a lot friend....its working fine..

But i can't able to understand why u used LEFT OUTER JOIN...

can you explain pls what u done...bcoz its shows null value for first row..while track the code row by row.

-------------------------
R..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 06:42:33

the LEFT JOIN is to handle the case of first row. For first row, there isn't any previous row for JOINing.

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 06:51:40

Hi

I got... thanks for your help..

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 07:30:31

Hi

I have little alter your code for my handy...correct me if am in wrong...

 DECLARE @sample TABLE
(
	row_no			INT IDENTITY(1,1),
	S1No			int,
	Demand			int,
	Period			int,
	SKU				CHAR,
	Carrying_Cost	decimal(16,2)
)
INSERT INTO @sample (S1No, Demand, Period, SKU)
SELECT	1, 10, 10, 'A' UNION ALL
SELECT	2, 20, 20, 'B' UNION ALL
SELECT	3, 30, 30, 'C' UNION ALL
SELECT	4, 40, 40, 'D' UNION ALL
SELECT	5, 50, 50, 'E'

DECLARE @S1No		int,
	@First_S1No	int

SELECT	@S1No	= MIN(S1No)
FROM	@sample

SELECT	@First_S1No	= @S1No

WHILE	@S1No <= ISNULL((SELECT COUNT(row_no) FROM @sample),0)
BEGIN

	UPDATE 	c
	SET	Carrying_Cost	= CASE 	WHEN 	@S1No = @First_S1No 
					THEN 	c.Demand
					ELSE
						(p.Carrying_Cost * 0.5 * 100)
					+	(c.Demand * 0.5 * 100)
					+	(c.Demand * 100 * p.S1No)
					END
	FROM	@sample c
		LEFT JOIN @sample p	ON	c.row_no	= p.row_no + 1
	WHERE	c.S1No	= @S1No


	SET @S1No	=	@S1No + 1

END

SELECT	*
FROM	@sample