how to select last few entries from orders? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to select last few entries from orders?

Author

Topic

gibsosmat
Starting Member

7 Posts

Posted - 2008-10-23 : 13:38:31

my order table is like (order_id, user_id, amount, order_ts).
I want to get the order/order timestamp from where the user made orders of worth $100 amount till now.

is there any elegant solution for this (standard SQL)? some kind of SUM() FEW

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 14:09:34

[code]SELECT t.*
FROM Table t
INNER JOIN (SELECT user_id,SUM(amount) AS Total
FROM Table
GROUP BY user_id) t1
ON t.user_id=t1.user_Id
WHERE t1.Total =100
[/code]

gibsosmat
Starting Member

7 Posts

Posted - 2008-10-24 : 01:21:23

thanks for the reply,
but that will give me the users who have made $100 till now.

but my problem is little different.

let me explain.

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, timestamp);
I want to give discount of 10% for the customer who made some transactions of worth 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 01:24:56

what will deterime your time for consideration? how many days, weeks,months to be considered? based on what?

gibsosmat
Starting Member

7 Posts

Posted - 2008-10-24 : 03:06:09

there is no time constraint.
it could be like any time.

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

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

got it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 05:48:37

Are you using sql 2005?

gibsosmat
Starting Member

7 Posts

Posted - 2008-10-24 : 07:57:45

not exactly. the original data is on postgres.
so I want some standard sql like solution.
no prob even if it is sql 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 10:21:30

This is a solution which works in sql 2005

SELECT o.user_id,MAX(timestamp)
FROM Orders o
CROSS APPLY (SELECT SUM(amount_paid) AS Total
             FROM Orders
             WHERE timestamp > t.timestamp)r
WHERE o.amount_paid+ r.Total >=500
GROUP BY user_id

gibsosmat
Starting Member

7 Posts

Posted - 2008-10-25 : 10:05:08

sounds good. but I need to get some workaround for this to make it standard SQL.

can anyone help me on this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 10:19:04

quote:
Originally posted by gibsosmat

sounds good. but I need to get some workaround for this to make it standard SQL.

can anyone help me on this?

what do you mean by standard SQL?

gibsosmat
Starting Member

7 Posts

Posted - 2008-10-26 : 12:14:45

I mean something that works on all sql platforms. not just some that have extensions to the sql like cross apply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 13:49:08

quote:
Originally posted by gibsosmat

I mean something that works on all sql platforms. not just some that have extensions to the sql like cross apply.

what's keeping you away from posting this on relevant forums? you would got accurate solutions long before if you had posted this in some postgres forums i guess.

gibsosmat
Starting Member

7 Posts

Posted - 2008-10-26 : 14:00:12

I agree.
But when I googled for sql forum this site showed up.
I didnt recognize that it is only for MSSQL.
the site name sounded as if its for general sql.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 14:02:19

quote:
Originally posted by gibsosmat

I agree.
But when I googled for sql forum this site showed up.
I didnt recognize that it is only for MSSQL.
the site name sounded as if its for general sql.

ok. no worries

you can find most of sql forums at www.dbforums.com
you may try your luck there

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources