Complex query

I've a table where there are 4 columns for Qty(Q1, Q2, Q3, Q4) and
another column as Virtual_Pack. I've to write a query to update
Virtual_Pack column. The logic is as follows

1 virtual pack consists of Q1 = 10, Q2 = 2, Q3 = 3.5, Q4 = 6

Lets say Q1 = 20, Q2 = 2, Q3 = 20, Q4 = 6 then the Virtual_Pack will
be = 1 because Q2 = 2

Example Q1 = 30, Q2 = 5, Q3 = 8, Q4 = 15 then the Virtual_Pack will be
= 2

If any of the Q1, Q2, Q3 or Q4 = 0 then Virtual_Pack will be = 0

I don't want to write a cursor or do it in a loop. Any thoughts how
virtual_pack column can be updated in a single query.

Thanks in advance.

SubodhI don't understand the logic of how you calculate Virtual_Pack from
Q1,Q2,Q3,Q4. If you want a complete solution maybe you could explain by
showing us a formula or a pseudo-code algorithm. "Virtual_Pack will be = 1
because Q2 = 2" doesn't explain much to me but maybe I'm just particularly
dense today!

Lookup the CASE statement in Books Online. I think that may help you. For
example:

Virtual_Pack =
CASE
WHEN Q2 = 2 THEN 1
WHEN Q2 = x THEN y
END

Hope this helps.

--
David Portas
SQL Server MVP
--|||Okay, it SOUNDS LIKE what you want is the largest number (Virtual_Pack)
such that no element of (10, 2, 3.5, 6)*(Virtual_Pack)
is greater than (Q1, Q2, Q3, Q4). Am I right?

In that case, what you're asking for is the smallest (integer)
from among Q1/10, Q2/2, Q3/3.5, Q4/6

Unfortunately, MSSQL has no built-in function such as SmallestOf(A,B,...)
You can do it with a gawdawful CASE statement.

Old SQL coders kludge SmallerOf(A,B) as: (A+B-abs(A-B))/2
This does NOT extend to 3,4,... arguments gracefully :-)
But assuming your Qty table has columns (Q1, Q2, Q3, Q4, F1,...)
you can write a query (preferrably for burial in a view):

select (Q1+VP-abs(Q1-VP))/2 as Virtual_Pack, *
from(
select (Q2+VP-abs(Q2-VP))/2 as VP, *
from(
select (Q3+Q4-abs(Q3-Q4))/2 as VP, *
from Qty
) X
) X

The 'X' tags are just a syntactic requirement for nested queries.

"Subodh" <sgoyal@.agline.on.ca> wrote in message
news:90104bf0.0407131244.28097221@.posting.google.c om...
> I've a table where there are 4 columns for Qty(Q1, Q2, Q3, Q4) and
> another column as Virtual_Pack. I've to write a query to update
> Virtual_Pack column. The logic is as follows
> 1 virtual pack consists of Q1 = 10, Q2 = 2, Q3 = 3.5, Q4 = 6
> Lets say Q1 = 20, Q2 = 2, Q3 = 20, Q4 = 6 then the Virtual_Pack will
> be = 1 because Q2 = 2
> Example Q1 = 30, Q2 = 5, Q3 = 8, Q4 = 15 then the Virtual_Pack will be
> = 2
> If any of the Q1, Q2, Q3 or Q4 = 0 then Virtual_Pack will be = 0
> I don't want to write a cursor or do it in a loop. Any thoughts how
> virtual_pack column can be updated in a single query.
> Thanks in advance.
> Subodh|||> In that case, what you're asking for is the smallest (integer)
> from among Q1/10, Q2/2, Q3/3.5, Q4/6

If that's correct then here's one method that gives the desired result:

CREATE TABLE Qty (Q1 INTEGER NOT NULL, Q2 INTEGER NOT NULL, Q3 INTEGER NOT
NULL, Q4 INTEGER NOT NULL /* , PRIMARY KEY ? */)

INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (20, 2, 20, 6)
INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (30, 5, 8, 15)

SELECT Q1,Q2,Q3,Q4,
(SELECT CAST(MIN(x) AS INTEGER)
FROM
(SELECT Q1/10.0 AS x UNION ALL
SELECT Q2/2.0 UNION ALL
SELECT Q3/3.5 UNION ALL
SELECT Q4/6.0) AS X)
AS virtual_pack
FROM Qty

Result:

Q1 Q2 Q3 Q4 virtual_pack
---- ---- ---- ---- ----
20 2 20 6 1
30 5 8 15 2

Mischa, I couldn't get your query to produce the answer that the OP wanted.
Maybe you could test it again against my sample data. I'm interested to see
if it can be extended in the way you suggest.

Hope this helps.

--
David Portas
SQL Server MVP
--|||Ummm ... what was I smoking. This one passes the smoke test:

select (Q1/10+V234-abs(Q1/10-V234))/2 as Virtual_Pack, *
from(
select (Q2/2+V34-abs(Q2/2-V34))/2 as V234, *
from(
select (Q3*2/7+Q4/6-abs(Q3*2/7-Q4/6))/2 as V34, *
from Qty
) X
) X

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:CuydncOuNJqcQmnd4p2dnA@.giganews.com...
> > In that case, what you're asking for is the smallest (integer)
> > from among Q1/10, Q2/2, Q3/3.5, Q4/6
> If that's correct then here's one method that gives the desired result:
> CREATE TABLE Qty (Q1 INTEGER NOT NULL, Q2 INTEGER NOT NULL, Q3 INTEGER NOT
> NULL, Q4 INTEGER NOT NULL /* , PRIMARY KEY ? */)
> INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (20, 2, 20, 6)
> INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (30, 5, 8, 15)
> SELECT Q1,Q2,Q3,Q4,
> (SELECT CAST(MIN(x) AS INTEGER)
> FROM
> (SELECT Q1/10.0 AS x UNION ALL
> SELECT Q2/2.0 UNION ALL
> SELECT Q3/3.5 UNION ALL
> SELECT Q4/6.0) AS X)
> AS virtual_pack
> FROM Qty
> Result:
> Q1 Q2 Q3 Q4 virtual_pack
> ---- ---- ---- ---- ----
> 20 2 20 6 1
> 30 5 8 15 2
> Mischa, I couldn't get your query to produce the answer that the OP
wanted.
> Maybe you could test it again against my sample data. I'm interested to
see
> if it can be extended in the way you suggest.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --