SQL 2000 Query question

Thank you, Erland.

"Erland Sommarskog" wrote:

> TimXox ( ) writes:
> > CREATE TABLE Baskets (BasketID INT, ItemID INT, AsOf DATETIME , ItemValue
> > FLOAT, X FLOAT)
> >
> > I need to create groups ( AsOf, BasketID ) and order items (ItemID ) in
> > each group by ItemValue. And calculate X like this:
> > x= 1 if the Item located in first half particular group (COUNT(*) /2)
> > ESLE X= 0
>
> In SQL 2005 this should be doable in one query with help of the
> row_number and ntile functions.
>
> Now we are on SQL 2000, and while it maybe could be one in one query,
> it would not be very efficient.
>
> So:
>
> CREATE TABLE #numbered (ident int IDENTITY,
> no_in_group int NULL,
> <columns from Baskets follow here>)
>
> INSERT #numbered(<columns from Baskets>)
> SELECT BasketID, etc
> FROM Baskets
> ORDER BY AsOf, BasketID, ItemValue
>
>
> UPDATE #numbered
> SET no_in_group = n.ident - m.mindent + 1,
> X = CASE WHEN n.ident - m.mindent + 1 >
> (m.maxident - m.minident + 1 / 2)
> THEN 1
> ELSE 0
> FROM #numbered n
> JOIN (SELECT AsOf, BasketID, minident = MIN(ident), maxident = MAX(ident)
> FROM #numbered
> GROUP BY AsOf, BasketID) AS m ON n.AsOf = m.AsOf
> AND n.BasketID = m.BasketID
>
> UPDATE Baskets
> SET X = n.x
> FROM Baskets B
> JOIN #numbered n ON B.BasketID = n.BasketID
> AND B.AsOf = n.AsOf
> AND B.ItemValue = n.ItemValue
>
>
> Obviously, this solution is untested. If you want a tested solution, please
> post your sample data as INSERT statement, as well the desired result
> given the sample.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>
 >> Stay informed about: SQL 2000 Query question 

I am glad that you tried to post DDL, but you have a lot of problems.

I will guess that basket_id and the date are the key. What the heck
is "item_id"? We usually have an industry standard like UPC, EAN,
ISBN, CUSIP, etc. The "asOf" date is not a data element name at all
-- where are the noun? What does this record for us? If it was
supposed to be the value of an item, then you would use (start_date,
end_date) to show the duration. Why did you use FLOAT for a
value? I assume it is money and not some other scale, but that might
be wrong. What the hell is X? Why is it a FLOAT when you assign it
integer values? Why do you want to store a calculation? It will
change every time you add an new row! Put it in a query or a VIEW
omstead.

If you will fix the DDL, the DML is much easier, faster, better,.
etc. Here are my guesses that I would not have to make if you had
done your job:

CREATE TABLE Baskets
(basket_id INTEGER NOT NULL,
item_id INTEGER NOT NULL, -- no indusry standards!
basket_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (basket_id, basket_date) -- total guess!
item_value DECIMAL (9,2) NOT NULL -- avoid FP numbers
CHECK (item_value >= 0.00)); --wild guess
--drop computed columns

>> I need to create groups (basket_date, basket_id) and order items (item_id) in each group by item_value and calculate x like this: x= 1 if the Item located in first half particular group (COUNT(*) /2)
ELSE x = 0 <<

Your specs do not work! {1,2,3,4,5} would break down to up = {1,2}
and {4,5} with {3] going to either or neither set. Let's use the
tricotomy law for the groups:

SELECT basket_date, basket_id,
CASE WHEN (x_up < x_down) THEN (+1)
WHEN (x_up > x_down) THEN (-1)
ELSE 0 END AS grp_nbr
FROM
(SELECT basket_date, basket_id,
ROW_NUMBER() OVER (PARTITION BY basket_id ORDER BY item_value
ASC) AS x_up,
ROW_NUMBER() OVER (PARTITION BY basket_id ORDER BY item_value
DESC) AS x_down
FROM Baskets) AS X(basket_date, basket_id, x_up., x_down)

You need the parens on (+1) and (-1) because SQL Server 2008 is
screwed up on unary operators.
 >> Stay informed about: SQL 2000 Query question 

Thank you, --CELKO--,
It works perfect.
The key is basket_id , item_id and the basket_date.
Probably I have to use this "......PARTITION BY basket_date,basket_id
ORDER BY item_value....."
For simplicity insted of item_id we may say currency_id and for item_value
=> currency_return. I am trying to define for the particular basket/date
"bad" currency (X = -1) or "good" currency (X=1) and X is just one of params
for a function which return a FLOAT estimation.
Tim.

"--CELKO--" wrote:

> I am glad that you tried to post DDL, but you have a lot of problems.
>
> I will guess that basket_id and the date are the key. What the heck
> is "item_id"? We usually have an industry standard like UPC, EAN,
> ISBN, CUSIP, etc. The "asOf" date is not a data element name at all
> -- where are the noun? What does this record for us? If it was
> supposed to be the value of an item, then you would use (start_date,
> end_date) to show the duration. Why did you use FLOAT for a
> value? I assume it is money and not some other scale, but that might
> be wrong. What the hell is X? Why is it a FLOAT when you assign it
> integer values? Why do you want to store a calculation? It will
> change every time you add an new row! Put it in a query or a VIEW
> omstead.
>
> If you will fix the DDL, the DML is much easier, faster, better,.
> etc. Here are my guesses that I would not have to make if you had
> done your job:
>
> CREATE TABLE Baskets
> (basket_id INTEGER NOT NULL,
> item_id INTEGER NOT NULL, -- no indusry standards!
> basket_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> PRIMARY KEY (basket_id, basket_date) -- total guess!
> item_value DECIMAL (9,2) NOT NULL -- avoid FP numbers
> CHECK (item_value >= 0.00)); --wild guess
> --drop computed columns
>
> >> I need to create groups (basket_date, basket_id) and order items (item_id) in each group by item_value and calculate x like this: x= 1 if the Item located in first half particular group (COUNT(*) /2)
> ELSE x = 0 <<
>
> Your specs do not work! {1,2,3,4,5} would break down to up = {1,2}
> and {4,5} with {3] going to either or neither set. Let's use the
> tricotomy law for the groups:
>
> SELECT basket_date, basket_id,
> CASE WHEN (x_up < x_down) THEN (+1)
> WHEN (x_up > x_down) THEN (-1)
> ELSE 0 END AS grp_nbr
> FROM
> (SELECT basket_date, basket_id,
> ROW_NUMBER() OVER (PARTITION BY basket_id ORDER BY item_value
> ASC) AS x_up,
> ROW_NUMBER() OVER (PARTITION BY basket_id ORDER BY item_value
> DESC) AS x_down
> FROM Baskets) AS X(basket_date, basket_id, x_up., x_down)
>
> You need the parens on (+1) and (-1) because SQL Server 2008 is
> screwed up on unary operators.
>
 >> Stay informed about: SQL 2000 Query question