Prev: "parallel nested xacts outstanding" error reported from my custome
Next: Locks
From: Aaron Bertrand [SQL Server MVP] on 21 Jul 2008 17:40
Start with something simple.

SELECT PortID, AllocQty
FROM MoxyAllocation
WHERE Omnibus <> 3
UNION
SELECT PortID = 8080, SUM(AllocQty)
FROM MoxyAllocation
WHERE Omnibus = 3;




"pvong" <phillip*at*yahoo*dot*com> wrote in message
news:eCHiDh36IHA.3816(a)TK2MSFTNGP03.phx.gbl...
> This is on SQL2005 and I'm sort of a newbie at this.
>
> This is a really hard one that I can not come even close. I was wondering
> if someone could help me with this one. I'm sure it's going to be
> complicated. The xls file shows the whole scenarios with all the tables
> listed in the tabs.
>
> I really need to solve this and I am so stuck I don't even know how to
> continue.
> Basically, if the Ominbus =3, I need it to sum up all the Qty to one line
> item and change the PortID to equal "8080". If it's not, just leave the
> PortID alone.
>
> Can someone help me?
>
> Thanks!
> Phil
>
>


From: Roy Harvey (SQL Server MVP) on 21 Jul 2008 17:56
This is along the same lines as what Aaron posted, but I tried to
include some of the confusing parts I wasn't really sure about. If
you like what Aaron suggested you might look at this next.

SELECT A.OrderID, A.Symbol,
C.AllocQty,
TotalFillCost = (B.TotalFillCost / B.TotalFillQty)
* C.AllocQty,
C.PortID
FROM MoxyOrder as A
JOIN MoxyTradeSummary as B
ON A.OrderID = B.OrderID
JOIN MoxyAllocation as C
ON A.OrderID = C.OrderID
WHERE OmnibusID <> 3
UNION ALL
SELECT A.OrderID, A.Symbol,
SUM(C.AllocQty) as AllocQty,
TotalFillCost = (B.TotalFillCost / B.TotalFillQty)
* SUM(C.AllocQty),
'8080' as PortID
FROM MoxyOrder as A
JOIN MoxyTradeSummary as B
ON A.OrderID = B.OrderID
JOIN MoxyAllocation as C
ON A.OrderID = C.OrderID
WHERE OmnibusID = 3
GROUP BY A.OrderID, A.Symbol,
B.TotalFillCost, B.TotalFillQty

Roy Harvey
Beacon Falls, CT

On Mon, 21 Jul 2008 17:29:34 -0400, "pvong" <phillip*at*yahoo*dot*com>
wrote:

>This is on SQL2005 and I'm sort of a newbie at this.
>
>This is a really hard one that I can not come even close. I was wondering
>if someone could help me with this one. I'm sure it's going to be
>complicated. The xls file shows the whole scenarios with all the tables
>listed in the tabs.
>
>I really need to solve this and I am so stuck I don't even know how to
>continue.
>Basically, if the Ominbus =3, I need it to sum up all the Qty to one line
>item and change the PortID to equal "8080". If it's not, just leave the
>PortID alone.
>
>Can someone help me?
>
>Thanks!
>Phil
>
From: pvong on 21 Jul 2008 18:12
Thanks guys! This will give me a good start.

Phil


"Roy Harvey (SQL Server MVP)" <roy_harvey(a)snet.net> wrote in message
news:id1a84deug2s5vldljr9j90kr560ffiros(a)4ax.com...
> This is along the same lines as what Aaron posted, but I tried to
> include some of the confusing parts I wasn't really sure about. If
> you like what Aaron suggested you might look at this next.
>
> SELECT A.OrderID, A.Symbol,
> C.AllocQty,
> TotalFillCost = (B.TotalFillCost / B.TotalFillQty)
> * C.AllocQty,
> C.PortID
> FROM MoxyOrder as A
> JOIN MoxyTradeSummary as B
> ON A.OrderID = B.OrderID
> JOIN MoxyAllocation as C
> ON A.OrderID = C.OrderID
> WHERE OmnibusID <> 3
> UNION ALL
> SELECT A.OrderID, A.Symbol,
> SUM(C.AllocQty) as AllocQty,
> TotalFillCost = (B.TotalFillCost / B.TotalFillQty)
> * SUM(C.AllocQty),
> '8080' as PortID
> FROM MoxyOrder as A
> JOIN MoxyTradeSummary as B
> ON A.OrderID = B.OrderID
> JOIN MoxyAllocation as C
> ON A.OrderID = C.OrderID
> WHERE OmnibusID = 3
> GROUP BY A.OrderID, A.Symbol,
> B.TotalFillCost, B.TotalFillQty
>
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 21 Jul 2008 17:29:34 -0400, "pvong" <phillip*at*yahoo*dot*com>
> wrote:
>
>>This is on SQL2005 and I'm sort of a newbie at this.
>>
>>This is a really hard one that I can not come even close. I was wondering
>>if someone could help me with this one. I'm sure it's going to be
>>complicated. The xls file shows the whole scenarios with all the tables
>>listed in the tabs.
>>
>>I really need to solve this and I am so stuck I don't even know how to
>>continue.
>>Basically, if the Ominbus =3, I need it to sum up all the Qty to one line
>>item and change the PortID to equal "8080". If it's not, just leave the
>>PortID alone.
>>
>>Can someone help me?
>>
>>Thanks!
>>Phil
>>


 | 
Pages: 1
Prev: "parallel nested xacts outstanding" error reported from my custome
Next: Locks