Обсуждение: need help in building a query

hi,
I need some help in creating a sql.
the problem is as below.

assume that:
I am a store keeper
and I have the list of customer(user_id) transactions in my order table.
schema: Orders(order_id, user_id, amount_paid, create_timestamp)

I want to give discount of 10% for the customer who made orders of worth (sum) at least $500 in the least time from now (i.e last few days, but NOT ALL TIME LEAST TIME TO BUY $500 WORTH GOODS).
and 9% discount for the customer who made $500 in second least time from now.
and so on

there is no time constraint.
it customer could take any amount of time to make $500.
the customer might not even made $500 bill till now in that case I should get the first date.

the result can take upto the first entry time of the table itself.

in short its like sum up entries of each user in order table backwards (i.e from last entry to the first) and find the entry that has sum > $500.
If there is some user who didnt even make 500 till now in my shop return the first date of transaction/order.

can anyone help me on this?

none can help me?

On Tue, Nov 4, 2008 at 9:08 PM, Devil™ Dhuvader <gibsosmat@gmail.com> wrote:

hi,
I need some help in creating a sql.
the problem is as below.

assume that:
I am a store keeper
and I have the list of customer(user_id) transactions in my order table.
schema: Orders(order_id, user_id, amount_paid, create_timestamp)

I want to give discount of 10% for the customer who made orders of worth (sum) at least $500 in the least time from now (i.e last few days, but NOT ALL TIME LEAST TIME TO BUY $500 WORTH GOODS).
and 9% discount for the customer who made $500 in second least time from now.
and so on

there is no time constraint.
it customer could take any amount of time to make $500.
the customer might not even made $500 bill till now in that case I should get the first date.

the result can take upto the first entry time of the table itself.

in short its like sum up entries of each user in order table backwards (i.e from last entry to the first) and find the entry that has sum > $500.
If there is some user who didnt even make 500 till now in my shop return the first date of transaction/order.

can anyone help me on this?

I personally would help if I understood what you need. I'm sure others feel the same way. Provide DDL, sample data, and expected result of the query. Maybe you'll have better luck...

On Thu, Nov 6, 2008 at 11:15 AM, Devil™ Dhuvader <gibsosmat@gmail.com> wrote:

none can help me?

On Tue, Nov 4, 2008 at 9:08 PM, Devil™ Dhuvader <gibsosmat@gmail.com> wrote:

hi,
I need some help in creating a sql.
the problem is as below.

assume that:
I am a store keeper
and I have the list of customer(user_id) transactions in my order table.
schema: Orders(order_id, user_id, amount_paid, create_timestamp)

I want to give discount of 10% for the customer who made orders of worth (sum) at least $500 in the least time from now (i.e last few days, but NOT ALL TIME LEAST TIME TO BUY $500 WORTH GOODS).
and 9% discount for the customer who made $500 in second least time from now.
and so on

there is no time constraint.
it customer could take any amount of time to make $500.
the customer might not even made $500 bill till now in that case I should get the first date.

the result can take upto the first entry time of the table itself.

in short its like sum up entries of each user in order table backwards (i.e from last entry to the first) and find the entry that has sum > $500.
If there is some user who didnt even make 500 till now in my shop return the first date of transaction/order.

can anyone help me on this?

Devil™ Dhuvader wrote:

 its like sum up entries of each user in order table backwards (i.e from last entry to the first) and find the entry that has sum > $500.
If there is some user who didnt even make 500 till now in my shop return the first date of transaction/order .

ex:
Orders(order_id, user_id, amount_paid, create_timestamp)
values:
(1, 1, 100, 1)
(2, 1, 300, 2)
(3, 2, 100, 2)
(4, 2, 100, 3)
(5, 1, 100, 4)
(6, 1, 200, 5)
(7, 2, 150, 5)

for user 1: the order_id = 2
for user 2: the order_id = 3 (coz he couldnt make 500)

I might also take a crack at the SQL if I understood the question; but I still don't know what you want.  You're initial question indicated you were trying to calculate the proper discount for each customer.  This example mentions neither customers nor discounts at all and appears to be looking for a specific transaction instead.

Frank

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Try:

bdteste=# SELECT o1.user_id, o1.order_id, '>= 500' AS cond FROM Orders o1
bdteste-#  WHERE (SELECT sum(o2.amount_paid) FROM Orders o2 WHERE
o2.user_id = o1.user_id AND o2.order_id > o1.order_id) < 500 AND
bdteste-#        (SELECT sum(o2.amount_paid) FROM Orders o2 WHERE
o2.user_id = o1.user_id AND o2.order_id >= o1.order_id) >=500
bdteste-# UNION
bdteste-# SELECT user_id, min(order_id) AS "min order id", '< 500' AS
cond FROM Orders
bdteste-#  WHERE user_id IN (SELECT user_id FROM Orders GROUP BY
user_id HAVING sum(amount_paid) < 500)
bdteste-#  GROUP BY user_id;
 user_id | order_id |  cond
---------+----------+--------
      1 |        2 | >= 500
      2 |        3 | < 500

Osvaldo