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.
I 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. [quoted text, click to view] "Subodh" <sgoyal@agline.on.ca> wrote in message
news:90104bf0.0407131244.28097221@posting.google.com...
> 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
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 [quoted text, click to view] "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
> --
>
>
[quoted text, click to view] > 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 --
Don't see what you're looking for? Try a search.
|
