September 18, 2006 at 9:02 am
hey Guys hope you can help with this one its really pushing the old grey cells today.
I have a Table ORDER_HISTORY
BranchNo,
Flow,
Units,
Date,
SequenceNumber,
ItemCode.
The Branch Number has values in the format of
AC30004
AC30005
Etc
FLOW has two Values I / O (In and out)
Units is the number of units moved in / out as a positive integer.
Date is a decimal (I know awful but thats the way its been done) YYYYMMDD
SequenceNumber is the sequence of the transaction,
1 being first transaction
ItemCode is a string eg
A110-02
A111-01
B232-04
I need to be able to show two things
1)To be able to do is show the highest number of units at any point in the history of the Branch
In the form of
BranchNo, Date, Item ,Units
2) To be able to show in a historical format the number of units by branch , by unit the Total number of units
BranchNo, Date , Item, Units, Culumativeunits
In the results above it should take into consideration any transactions out as a negative figure
Hope someone can help with this
Big Thanks in advance
September 18, 2006 at 10:05 am
Hope this is what u are looking for:
Select ORDER_HISTORY.BranchNo,ORDER_HISTORY.ItemCode,ORDER_HISTORY.Units,ORDER_HISTORY.[Date]
from ORDER_HISTORY INNER JOIN (Select BranchNo,ItemCode,max(Units) as MaxUnits
from ORDER_HISTORY
group by BranchNo,ItemCode) Max_ORDER_HISTORY
on ORDER_HISTORY.ItemCode =Max_ORDER_HISTORY.ItemCode
and ORDER_HISTORY.BranchNo = Max_ORDER_HISTORY.BranchNo
and ORDER_HISTORY.Units = Max_ORDER_HISTORY.MaxUnits)
Select ORDER_HISTORY.BranchNo, ORDER_HISTORY.[Date] , ORDER_HISTORY.Item,ORDER_HISTORY.Units
(Select Sum(Case when Flow = 1 Then -1 *Units Else 1*Units) from ORDER_HISTORY SummaryHist
where SummaryHist.BranchNo = ORDER_HISTORY.BranchNo
and SummaryHist.[Item] = ORDER_HISTORY.[Item]
and SummaryHist.[Date] <= ORDER_HISTORY.[Date]) as Culumativeunits
from ORDER_HISTORY
Thanks
Sreejith
September 19, 2006 at 4:28 am
Brilliant Sreejith
Thank a lot for that
for those who are doing the same thing though
Make sure you include 'end' in the Case function
And it will work fine
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply