SELECT statement

SELECT statement

Post by Simo » Tue, 05 Aug 2003 10:47:24



I have 2 tables: orders and orderProduct:

Orders:

orderId    status

orderProduct:

orderId    productId    quantity    date

Now I must select the latest quantity of product, if status of order is 2
or the second latest quantity of product if the status of order is 3.

For example:

orderId    status
    1            2
    2            3

orderId    productId    quantity        date
    1                1                3        18/07/2003
    1                1                2        17/07/2003
    2                2                4        18/07/2003
    2                2                1        17/07/2003
    2                2                5        16/07/2003
................
...............

So my result should be:

productId    quantity
    1                3
    2                1
........
........

Thank you for your answer,
Simon

 
 
 

SELECT statement

Post by John Gilso » Tue, 05 Aug 2003 14:02:52



> I have 2 tables: orders and orderProduct:

> Orders:

> orderId    status

> orderProduct:

> orderId    productId    quantity    date

> Now I must select the latest quantity of product, if status of order is 2
> or the second latest quantity of product if the status of order is 3.

> For example:

> orderId    status
>     1            2
>     2            3

> orderId    productId    quantity        date
>     1                1                3        18/07/2003
>     1                1                2        17/07/2003
>     2                2                4        18/07/2003
>     2                2                1        17/07/2003
>     2                2                5        16/07/2003
> ................
> ...............

> So my result should be:

> productId    quantity
>     1                3
>     2                1
> ........
> ........

> Thank you for your answer,
> Simon

CREATE TABLE Orders
(
orderid INT NOT NULL PRIMARY KEY,
status INT NOT NULL
)

INSERT INTO Orders (orderid, status)
VALUES (1, 2)
INSERT INTO Orders (orderid, status)
VALUES (2, 3)

CREATE TABLE OrderProduct
(
orderid INT NOT NULL REFERENCES Orders (orderid),
productid INT NOT NULL,
quantity INT NOT NULL,
date SMALLDATETIME NOT NULL,
PRIMARY KEY (orderid, date)
)

INSERT INTO OrderProduct (orderid, productid, quantity, date)
VALUES (1, 1, 3, '20030718')
INSERT INTO OrderProduct (orderid, productid, quantity, date)
VALUES (1, 1, 2, '20030717')
INSERT INTO OrderProduct (orderid, productid, quantity, date)
VALUES (2, 2, 4, '20030718')
INSERT INTO OrderProduct (orderid, productid, quantity, date)
VALUES (2, 2, 1, '20030717')
INSERT INTO OrderProduct (orderid, productid, quantity, date)
VALUES (2, 2, 5, '20030716')

SELECT OP1.*
FROM OrderProduct AS OP1
            INNER JOIN
            Orders AS O
            ON OP1.orderid = O.orderid AND
                   ((O.status = 2 AND
                      1 = (SELECT COUNT(*)
                              FROM OrderProduct AS OP2
                              WHERE OP1.orderid = OP2.orderid AND
                                             OP2.date >= OP1.date)) OR
                    (O.status = 3 AND
                     2 = (SELECT COUNT(*)
                            FROM OrderProduct AS OP2
                            WHERE OP1.orderid = OP2.orderid AND
                                           OP2.date >= OP1.date)))
ORDER BY OP1.orderid

orderid productid quantity date
1 1 3 2003-07-18 00:00:00
2 2 1 2003-07-17 00:00:00

Regards,
jag

 
 
 

SELECT statement

Post by Simo » Tue, 05 Aug 2003 18:16:23


Thank you for your answer.

Can you explain me the code:
2 = (SELECT COUNT(*)
                            FROM OrderProduct AS OP2
                            WHERE OP1.orderid = OP2.orderid AND
                                           OP2.date >= OP1.date
It works also if I wrote 3 or 1, how you know that the select will be 2?

I tried to get for example the third row from table, where quantity>1

SELECT * from orderProduct
WHERE 3=
(SELECT COUNT(*) FROM orderProduct WHERE quantity>=1)
but it doesnt work that way.
Of course, count(*)>3, so, thats way I don't understand your query.

Thank you,
Simon



Quote:> > I have 2 tables: orders and orderProduct:

> > Orders:

> > orderId    status

> > orderProduct:

> > orderId    productId    quantity    date

> > Now I must select the latest quantity of product, if status of order is
2
> > or the second latest quantity of product if the status of order is 3.

> > For example:

> > orderId    status
> >     1            2
> >     2            3

> > orderId    productId    quantity        date
> >     1                1                3        18/07/2003
> >     1                1                2        17/07/2003
> >     2                2                4        18/07/2003
> >     2                2                1        17/07/2003
> >     2                2                5        16/07/2003
> > ................
> > ...............

> > So my result should be:

> > productId    quantity
> >     1                3
> >     2                1
> > ........
> > ........

> > Thank you for your answer,
> > Simon

> CREATE TABLE Orders
> (
> orderid INT NOT NULL PRIMARY KEY,
> status INT NOT NULL
> )

> INSERT INTO Orders (orderid, status)
> VALUES (1, 2)
> INSERT INTO Orders (orderid, status)
> VALUES (2, 3)

> CREATE TABLE OrderProduct
> (
> orderid INT NOT NULL REFERENCES Orders (orderid),
> productid INT NOT NULL,
> quantity INT NOT NULL,
> date SMALLDATETIME NOT NULL,
> PRIMARY KEY (orderid, date)
> )

> INSERT INTO OrderProduct (orderid, productid, quantity, date)
> VALUES (1, 1, 3, '20030718')
> INSERT INTO OrderProduct (orderid, productid, quantity, date)
> VALUES (1, 1, 2, '20030717')
> INSERT INTO OrderProduct (orderid, productid, quantity, date)
> VALUES (2, 2, 4, '20030718')
> INSERT INTO OrderProduct (orderid, productid, quantity, date)
> VALUES (2, 2, 1, '20030717')
> INSERT INTO OrderProduct (orderid, productid, quantity, date)
> VALUES (2, 2, 5, '20030716')

> SELECT OP1.*
> FROM OrderProduct AS OP1
>             INNER JOIN
>             Orders AS O
>             ON OP1.orderid = O.orderid AND
>                    ((O.status = 2 AND
>                       1 = (SELECT COUNT(*)
>                               FROM OrderProduct AS OP2
>                               WHERE OP1.orderid = OP2.orderid AND
>                                              OP2.date >= OP1.date)) OR
>                     (O.status = 3 AND
>                      2 = (SELECT COUNT(*)
>                             FROM OrderProduct AS OP2
>                             WHERE OP1.orderid = OP2.orderid AND
>                                            OP2.date >= OP1.date)))
> ORDER BY OP1.orderid

> orderid productid quantity date
> 1 1 3 2003-07-18 00:00:00
> 2 2 1 2003-07-17 00:00:00

> Regards,
> jag

 
 
 

SELECT statement

Post by John Gilso » Tue, 05 Aug 2003 23:46:11



> Thank you for your answer.

> Can you explain me the code:
> 2 = (SELECT COUNT(*)
>                             FROM OrderProduct AS OP2
>                             WHERE OP1.orderid = OP2.orderid AND
>                                            OP2.date >= OP1.date
> It works also if I wrote 3 or 1, how you know that the select will be 2?

It selects the row, for a given orderid, whose date is ranked
2nd when placed in decreasing date order.  If you change the
value to 1 or to 3, you'll get the 1st or 3rd row, respectively.

Quote:> I tried to get for example the third row from table, where quantity>1

> SELECT * from orderProduct
> WHERE 3=
> (SELECT COUNT(*) FROM orderProduct WHERE quantity>=1)
> but it doesnt work that way.
> Of course, count(*)>3, so, thats way I don't understand your query.

If one ranks all rows based on decreasing quantity, then the 3rd row can
be found by (DISTINCT is used here since there will probably be ties
with quantity)

SELECT *
FROM OrderProduct AS OP1
WHERE 3 = (SELECT COUNT(DISTINCT OP2.quantity)
                      FROM OrderProduct AS OP2
                      WHERE OP2.quantity >= OP1.quantity)

Regards,
jag

> Thank you,
> Simon





> > > I have 2 tables: orders and orderProduct:

> > > Orders:

> > > orderId    status

> > > orderProduct:

> > > orderId    productId    quantity    date

> > > Now I must select the latest quantity of product, if status of order is
> 2
> > > or the second latest quantity of product if the status of order is 3.

> > > For example:

> > > orderId    status
> > >     1            2
> > >     2            3

> > > orderId    productId    quantity        date
> > >     1                1                3        18/07/2003
> > >     1                1                2        17/07/2003
> > >     2                2                4        18/07/2003
> > >     2                2                1        17/07/2003
> > >     2                2                5        16/07/2003
> > > ................
> > > ...............

> > > So my result should be:

> > > productId    quantity
> > >     1                3
> > >     2                1
> > > ........
> > > ........

> > > Thank you for your answer,
> > > Simon

> > CREATE TABLE Orders
> > (
> > orderid INT NOT NULL PRIMARY KEY,
> > status INT NOT NULL
> > )

> > INSERT INTO Orders (orderid, status)
> > VALUES (1, 2)
> > INSERT INTO Orders (orderid, status)
> > VALUES (2, 3)

> > CREATE TABLE OrderProduct
> > (
> > orderid INT NOT NULL REFERENCES Orders (orderid),
> > productid INT NOT NULL,
> > quantity INT NOT NULL,
> > date SMALLDATETIME NOT NULL,
> > PRIMARY KEY (orderid, date)
> > )

> > INSERT INTO OrderProduct (orderid, productid, quantity, date)
> > VALUES (1, 1, 3, '20030718')
> > INSERT INTO OrderProduct (orderid, productid, quantity, date)
> > VALUES (1, 1, 2, '20030717')
> > INSERT INTO OrderProduct (orderid, productid, quantity, date)
> > VALUES (2, 2, 4, '20030718')
> > INSERT INTO OrderProduct (orderid, productid, quantity, date)
> > VALUES (2, 2, 1, '20030717')
> > INSERT INTO OrderProduct (orderid, productid, quantity, date)
> > VALUES (2, 2, 5, '20030716')

> > SELECT OP1.*
> > FROM OrderProduct AS OP1
> >             INNER JOIN
> >             Orders AS O
> >             ON OP1.orderid = O.orderid AND
> >                    ((O.status = 2 AND
> >                       1 = (SELECT COUNT(*)
> >                               FROM OrderProduct AS OP2
> >                               WHERE OP1.orderid = OP2.orderid AND
> >                                              OP2.date >= OP1.date)) OR
> >                     (O.status = 3 AND
> >                      2 = (SELECT COUNT(*)
> >                             FROM OrderProduct AS OP2
> >                             WHERE OP1.orderid = OP2.orderid AND
> >                                            OP2.date >= OP1.date)))
> > ORDER BY OP1.orderid

> > orderid productid quantity date
> > 1 1 3 2003-07-18 00:00:00
> > 2 2 1 2003-07-17 00:00:00

> > Regards,
> > jag

 
 
 

SELECT statement

Post by Simo » Wed, 06 Aug 2003 15:09:09


I have 2 tables:

CREATE TABLE [dbo].[orderTable] (
 [orderId] [varchar] (10) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
 [date] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[orderTable] WITH NOCHECK ADD
 CONSTRAINT [PK_orderTable] PRIMARY KEY  CLUSTERED
 (
  [orderId]
 )  ON [PRIMARY]
GO

INSERT [dbo].[orderTable] VALUES (1,'20030717')
INSERT [dbo].[orderTable] VALUES (2,'20030718')
INSERT [dbo].[orderTable] VALUES (3,'20030718')
INSERT [dbo].[orderTable] VALUES (4,'20030719')
INSERT [dbo].[orderTable] VALUES (5,'20030720')

CREATE TABLE [dbo].[orderProducts] (
 [orderId] [varchar] (10) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
 [subOrderId] [int] NOT NULL ,
 [productId] [varchar] (10) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
 [crossSellID] [varchar] (10) COLLATE SQL_Slovenian_CP1250_CI_AS NULL ,
 [name] [varchar] (500) COLLATE SQL_Slovenian_CP1250_CI_AS NULL ,
 [quantity] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[orderProducts] WITH NOCHECK ADD
 CONSTRAINT [PK_orderProducts] PRIMARY KEY  CLUSTERED
 (
  [orderId],
  [subOrderId],
  [productId]
 )  ON [PRIMARY]
GO
INSERT [dbo].[orderProducts] VALUES(1,1,1,null,'product1',2)
INSERT [dbo].[orderProducts] VALUES(1,2,1,1,'product1',1)
INSERT [dbo].[orderProducts] VALUES(1,2,2,1,'product1',1)
INSERT [dbo].[orderProducts] VALUES(1,2,3,1,'product1',1)
INSERT [dbo].[orderProducts] VALUES(2,1,1,null,'product1',1)
INSERT [dbo].[orderProducts] VALUES(2,2,1,1,'product1',1)
INSERT [dbo].[orderProducts] VALUES(2,2,3,1,'product3',1)
INSERT [dbo].[orderProducts] VALUES(2,3,3,null,'product3',2)
INSERT [dbo].[orderProducts] VALUES(2,4,2,2,'product2',1)
INSERT [dbo].[orderProducts] VALUES(2,4,3,2,'product3',1)
INSERT [dbo].[orderProducts] VALUES(3,1,1,null,'product1',2)
INSERT [dbo].[orderProducts] VALUES(4,1,1,1,'product1',1)
INSERT [dbo].[orderProducts] VALUES(4,1,2,1,'product2',2)
INSERT [dbo].[orderProducts] VALUES(4,2,1,1,'product1',1)
INSERT [dbo].[orderProducts] VALUES(4,2,3,1,'product3',1)
INSERT [dbo].[orderProducts] VALUES(5,1,1,null,'product1',3)
INSERT [dbo].[orderProducts] VALUES(5,2,1,1,'product1',1)
INSERT [dbo].[orderProducts] VALUES(5,2,2,1,'product2',1)
INSERT [dbo].[orderProducts] VALUES(5,3,1,1,'product1',1)
INSERT [dbo].[orderProducts] VALUES(5,3,3,1,'product3',1)
INSERT [dbo].[orderProducts] VALUES(5,4,2,null,'product2',3)

now, I would like to get the query, which returns:

refName        crossName        quantity    crossQuantity
crossRefQuantity    %crossRef       %efficency    %all
product1                                    8
14                                            175%        87.5%
                        product1                                6
42.9%                75%        37.5%
                        product2                                4
28.6%                50%        25%
                        product3                                4
28.6%                50%        25%
product2                                    3
2                                                66.7%     12.5%
                        product2                                1
50%                   33.3%     6.25%
                        product3                                1
50%                   33.3%     6.25%
product3                                   2
0                                                    0%        0%

quantity - means quantity of product which was sold without cross( WHERE
crossSellID=null)
crossQuantity - means quantity of product WHERE crossSellID is not null and
if we look for product1 then crosssellID=1 and so on...
crossRefQuantity - is sum of all cross quantity, where crosSellId=productId
of the product which we look for
%crossRef - (crossQuantity/crossRefQuantity)*100
%efficency - (crossRefQuantity/quantity)*100 OR when we look for
cross:(crossQuantity/quantity)*100
%all -      (crossRefQuantity /sum(crossQuantity))*100   or when we look for
cross(WHERE crossSellID is not null ):(crossQuantity
/sum(crossQuantity))*100

Does anybody know the select query?

Thank you,
Simon