Complicated sql query - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Complicated sql query

Author

Topic

pizzojm
Starting Member

20 Posts

Posted - 2003-12-18 : 16:37:05

I have a query based on the following table. Basically what I need to do is sum the 50 highest items which have the greatest ci_qty_wgt from today * the cost from yesterday ordered desc. I have the query in a sproc already but I am not sure how I can find the the extended amount without already knowing the item_ser and store_nbr to get yesterday's cost. Any ideas?...

ci_ser -- serial
ci_date -- date
store_nbr -- smallint
item_ser -- integer
ci_qty_wgt -- decimal(11,2)
ci_ext_amt -- decimal(11,4)
pi_qty_wgt -- decimal(11,2)
lchg_dte -- date
cost -- decimal(10,4)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-18 : 16:39:54

Perhaps if you provided the DDL (CREATE TABLE statement) for your table, sample data in the form of INSERT INTO statements plus the expected result set using the sample data, then we'd be able to start working on this problem.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-18 : 16:41:30

You need to self join the table to itself, one with a predicate of today, and one with a predicate of yesterday, then JOIN on the KEY and SELECT TOP 50 Order by your calculated field....

If you post the DDL and Sample Data in a form we can use, I'm sure someone will help you out

Sample data in the form of

INSERT INTO myTable99 (Col1, col2, ect)
SELECT 'a','1',ect UNION ALL
SELECT 'a','1',ect UNION ALL
SELECT 'a','1',ect UNION ALL
SELECT 'a','1',ect UNION ALL
SELECT 'a','1',ect UNION ALL
ect

Brett

8-)

EDIT: Scary Tara, Scary

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources