How to get order total?

Question

Post reply

How to get order total?

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110209 More actions · Answer 1

Create table "OrderDetails", populate it with right data and then query it.

Actually your example does not have any relation neither to TSQL nor to relational databases at all.

_____________
Code for TallyGenerator

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 2

SwePeso

SSC-Dedicated

Points: 39758

August 31, 2006 at 12:50 am

#657732

Normalization?

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 3

It is not pretty, but it works.

declare @test table (NoOfItems varchar(100), Quantities varchar(100))

insert @test

select '1,2,10,15,1,1',

'50,75,20,100,20,25'

DECLARE @Items TABLE (i INT IDENTITY(0,1), Value INT)

INSERT @Items

(

Value

&nbsp

SELECT SUBSTRING(',' + t.NoOfItems + ',', w.i + 1, CHARINDEX(',', ',' + t.NoOfItems + ',', w.i + 1) - w.i - 1)

FROM @Test t

CROSS JOIN (

SELECT b4.n + b3.n + b2.n + b1.n + b0.n i

FROM (SELECT 0 n UNION ALL SELECT 1) b0

CROSS JOIN (SELECT 0 n UNION ALL SELECT 2) b1

CROSS JOIN (SELECT 0 n UNION ALL SELECT 4) b2

CROSS JOIN (SELECT 0 n UNION ALL SELECT 8) b3

CROSS JOIN (SELECT 0 n UNION ALL SELECT 16) b4

&nbsp w

WHERE w.i = CHARINDEX(',', ',' + t.NoOfItems + ',', w.i)

AND w.i < LEN(',' + t.NoOfItems)

ORDER BY w.i

DECLARE @Quantities TABLE (i INT IDENTITY(0,1), Value INT)

INSERT @Quantities

(

Value

&nbsp

SELECT SUBSTRING(',' + t.Quantities + ',', w.i + 1, CHARINDEX(',', ',' + t.Quantities + ',', w.i + 1) - w.i - 1) v

FROM @Test t

CROSS JOIN (

SELECT b4.n + b3.n + b2.n + b1.n + b0.n i

FROM (SELECT 0 n UNION ALL SELECT 1) b0

CROSS JOIN (SELECT 0 n UNION ALL SELECT 2) b1

CROSS JOIN (SELECT 0 n UNION ALL SELECT 4) b2

CROSS JOIN (SELECT 0 n UNION ALL SELECT 8) b3

CROSS JOIN (SELECT 0 n UNION ALL SELECT 16) b4

&nbsp w

WHERE w.i = CHARINDEX(',', ',' + t.Quantities + ',', w.i)

AND w.i < LEN(',' + t.Quantities)

ORDER BY w.i

SELECT SUM(i.Value * q.Value)

FROM @Items i

INNER JOIN @Quantities q ON q.i = i.i

N 56°04'39.16"
E 12°55'05.25"

Soma Majumdar SSC Enthusiast Points: 186 More actions · Answer 4

Thanks for your reply Peter. I have done exactly the same except putting the logic in a UDF which returns the Sum total. But I was wondering if there exist any logic that will be faster.

Moreover many many thanks to Sergiy for you very valuable suggestion. Except that in the practical world we the developers don't get the opportunity to create tables as we wish and we are bound to work with the table designs of some esteemed DBAs.

So my suggestion is you can pretty well ignore our non-TSQL and non-RDMS questions.