July 7, 2008 at 1:01 am
Hello,
I want to write query for:
I have a purchase orders table and structure is like:
POID | POQuantity | PO Expect Date.
I want to first sort it based on PO Expect date and then want to
find out PO that will fulfill asked quantity(Sum of all previous records upto sum(PO quantity) >= given quantity).
E.g.,
table contents should be like afer sorting:
1207/11
2107/15
3107/30
and it should return 2nd recordset when asked quantity is 30 and should return 1st recordset
when asked quantity is 20 and should return 3rd recordset when asked quantity is 40.
How do I do that in signle query?
July 7, 2008 at 1:11 am
Please could you post your table definition, sample data (as insert statements) and desired output
see - http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2008 at 1:24 am
As far as I can understand, it is something like calculating totals of an ordered set (ORDER BY date), where the desired result is that row of the original table where total is equal or greater than some input parameter (is this a running total? sorry, I'm not native speaker and I'm not sure... but I think it is).
Basically, the question probably is like "At what date the customer's orders reached XXX", only instead of the date, you return the entire order of the customer from this date.
Anyway, please post your table structure, sample data and sample result as Gila asked, and in case I didn't interpret your requirements correctly, also describe in words what you want to achieve. Don't forget to mention if there can be ties (i.e. several orders with the same date), and if yes, what to do with them (display both or use additional order criterion?).
/*Edit : I missed something in the requirements, but now it should be OK - corrected*/
July 7, 2008 at 2:32 am
e.g., my sample table and values are like:
create table purchase_orders
(order_ID uniqueidentifier,
Basequantity decimal,
Expect_date datetime)
insert into purchase_orders
values
(newid(), 20, '2008-07-31 00:00:00.000')
insert into purchase_orders
values
(newid(), 10, '2008-07-31 00:00:00.000')
insert into purchase_orders
values
(newid(), 10, '2008-07-06 00:00:00.000'),
now first demanded quantity of 10 will be fulfilled from last inserted row, so I should get details of this rowset. total demanded quantity of 20 will be fulfilled from second inserted rowset. that is these data first should be sorted in order of expect date desc, then their Basequantity should be added added run time to find total Basequanitity till that line, if for a particular line, my tcalculated this total matched demanded quantity, that recordset should be returned. I hope my problem is clear. for data with same expect_date, the first that can fulfill asked quanity should be returned...
July 7, 2008 at 2:41 am
Or the problem can be interpreted in words as:
My application creates Purchase orders for items with some quantity and these purchase orders have some Expect date. This date represents when that PO's quantiy would be available in stock. Now i want to know which purchase order will fulfill my quantity demanded at a given point. i.e., for which line previous and this sum(basequanitity) >= asked quantity.
To solve this I'm making query to sort the data in order of expect_date column and making recordset of this. Then running in loop on this recordset to find specific PO that will fulfill the demand.
I want to get rid of this FOR Loop again and somehow want to embedd logic of run time sum of privious and current line Basequantity calculation and checking it against given quantity in same query if possible.
July 7, 2008 at 3:48 am
Based on the sample you gave, what do you want returning?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2008 at 3:52 am
Expect_date of found table row.
July 7, 2008 at 4:06 am
ngarg (7/7/2008)
Expect_date of found table row.
Which row?
What I'm looking for is the required output so that if I write a query for you, I can test it and tell if I have the logic right.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2008 at 4:09 am
I need expect_date column value as output.
July 7, 2008 at 4:32 am
Hi,
If your Expect_date is distinct in every row that this query can be useful:
Select order_ID,Basequantity,Expect_date,
(Select SUM(Basequantity) From purchase_orders
Where Expect_date>=A.Expect_date) CalculatedQty
From purchase_orders A
Group By order_ID,Basequantity,Expect_date
Order By Expect_date Desc
July 7, 2008 at 4:36 am
Now you can apply WHERE condition on CalculatedQty Column like:
Select * From (
Select order_ID,Basequantity,Expect_date,
(Select SUM(Basequantity) From purchase_orders
Where Expect_date>=A.Expect_date) CalculatedQty
From purchase_orders A
Group By order_ID,Basequantity,Expect_date
) AS Temp
WHERE CalculatedQty<=40
Order By Expect_date Desc
July 7, 2008 at 5:49 am
I tried this, it worked, Thanks Hari.
I do have few q now:
1. In real scenarions, I have many where and join clauses. e.g, I also want to filter lines
based on some more conditions. - So should I use same where and join in inner subquery also?
e.g., I change the definition of column to add one more field "statusflag". I don't want to
consider lines when the value is zero:
here are table updations and query updations commands:
alter table purchase_orders add statusFlag int default 0
update purchase_orders set statusflag = 1 where order_id = '30C40D5C-FA24-482F-8CAA-F81AB3B2E354' -- any guid from three lines.
Select * From (
Select order_ID,Basequantity,Expect_date,statusflag,
(Select SUM(Basequantity) From purchase_orders
Where Expect_date <=A.Expect_date and statusflag = 1) CalculatedQty
From purchase_orders A
Group By order_ID,Basequantity,Expect_date,statusflag
) AS Temp
WHERE CalculatedQty<=40
and statusflag = 1
Order By Expect_date
Is it the right way? Will all joins and where clauses be also applied on inner subqyuery for
calculating CalculatedQty?
2. I wanted to do it for performace optimizations. As earlier said: My previous code was to sort
all the lines based on expect_date column and then run a FOR loop programatically to break on line where
Sum(basequantity) > askedqty.
So what is your opinion about performace optimizations? Is it big gain doing suggested way or FOR loop should equally be good?
As in given query way, as I understand inner query will again internally for each line would iterate on all the lines to get sum total of
base quantity till that line based on expect_date. where as in for loop we can avoid this internal iteration for each line?
July 7, 2008 at 5:52 am
In my previous post: "Will all joins and where clauses be also applied on inner subqyuery for calculating CalculatedQty?"
I actually mean to say: all where and join clauses must also be applied to inner query, is the way I have written in last post is right or should do it some other way?
July 7, 2008 at 6:24 am
Yeah, It should work...
Try this
Select * From (
Select order_ID,Basequantity,Expect_date,statusflag,
(Select SUM(Basequantity) From purchase_orders
Where Expect_date >= A.Expect_date and statusflag = 1) CalculatedQty
From purchase_orders A
Group By order_ID,Basequantity,Expect_date,statusflag
) AS Temp
WHERE CalculatedQty<=100 and statusflag = 1
Order By Expect_date Desc
This is the same query as you posted except Condition (>=) and Order By Clause in the last.
July 7, 2008 at 6:28 am
Yes, it is the same query, I changed the condition, 'coz I needed reverse checkes....
My second Q is still unanswered:
Which way is better performace wise??
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply