SQL Code Question - 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 2005 Forums

Transact-SQL (2005)

SQL Code Question

Author

Topic

Minco
Starting Member

2 Posts

Posted - 2009-02-03 : 12:57:48

I am trying to add a calculated field to the Backlog Report that will be:

(omdDeliveryQuantity - omdQuantityShipped) * omlFullUnitBasePrice

but I can't seem to get it to accept it. Below is the current SQL statement that I is there and i would just like to know where to put my calculation for this to work. Unfortunately, the calculation has to be after the From for this to work with my software.

SELECT OMDSALESORDERID,OMDSALESORDERLINEID,OMDSALESORDERDELIVERYID,OMDDELIVERYDATE,OMPCUSTOMERORGANIZATIONID,OMDDELIVERYQUANTITY,OMDQUANTITYSHIPPED,REMAININGQUANTITY:C='Remaining Qty',OMLUNITPRICEBASE,OMLPARTID,OMLPARTREVISIONID,OMLPARTSHORTDESCRIPTION FROM SalesOrderDeliveries a Inner Join (Select omdSalesOrderID As Key1,omdSalesOrderLineID As Key2,omdSalesOrderDeliveryID As Key3,omdDeliveryQuantity-omdQuantityShipped As RemainingQuantity From SalesOrderDeliveries) As b On a.omdSalesOrderID = Key1 And a.omdSalesOrderLineID = Key2 And a.omdSalesOrderDeliveryID = Key3 inner join SalesOrderLines on omdSalesOrderID=omlSalesOrderID and omdSalesOrderLineID=omlSalesOrderLineID inner join SalesOrders on omdSalesOrderID=ompSalesOrderID WHERE omdClosed = 0 and omdShippedComplete = 0 and omdDeliveryQuantity > omdQuantityShipped

Thanks,

Rex Miller

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 13:04:03

may be this


SELECT OMDSALESORDERID,
OMDSALESORDERLINEID,
OMDSALESORDERDELIVERYID,
OMDDELIVERYDATE,
OMPCUSTOMERORGANIZATIONID,
OMDDELIVERYQUANTITY,
OMDQUANTITYSHIPPED,
REMAININGQUANTITY AS Remaining Qty,
REMAININGQUANTITY * omlFullUnitBasePrice AS YourQty,
OMLUNITPRICEBASE,OMLPARTID,
OMLPARTREVISIONID,
OMLPARTSHORTDESCRIPTION 
FROM SalesOrderDeliveries a 
Inner Join 
     (Select omdSalesOrderID As Key1,
      omdSalesOrderLineID As Key2,
      omdSalesOrderDeliveryID As Key3,
      omdDeliveryQuantity-     omdQuantityShipped As RemainingQuantity 
       From SalesOrderDeliveries
     ) As b 
On a.omdSalesOrderID = Key1 
And a.omdSalesOrderLineID = Key2 
And a.omdSalesOrderDeliveryID = Key3 
inner join SalesOrderLines 
on omdSalesOrderID=omlSalesOrderID 
and omdSalesOrderLineID=omlSalesOrderLineID 
inner join SalesOrders 
on omdSalesOrderID=ompSalesOrderID 
WHERE omdClosed = 0 
and omdShippedComplete = 0 
and omdDeliveryQuantity > omdQuantityShipped

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-03 : 13:04:46

, (coalesce(omdDeliveryQuantity, 0) - coalesce(omdQuantityShipped, 0)) * coalesce(omlFullUnitBasePrice, 0)

E 12°55'05.63"
N 56°04'39.26"

Minco
Starting Member

2 Posts

Posted - 2009-02-03 : 13:45:02

This doesn't work as I need the calculation below the From clause as in the first inner join expression.

quote:

Originally posted by visakh16

may be this


SELECT OMDSALESORDERID,
OMDSALESORDERLINEID,
OMDSALESORDERDELIVERYID,
OMDDELIVERYDATE,
OMPCUSTOMERORGANIZATIONID,
OMDDELIVERYQUANTITY,
OMDQUANTITYSHIPPED,
REMAININGQUANTITY AS Remaining Qty,
REMAININGQUANTITY * omlFullUnitBasePrice AS YourQty,
OMLUNITPRICEBASE,OMLPARTID,
OMLPARTREVISIONID,
OMLPARTSHORTDESCRIPTION 
FROM SalesOrderDeliveries a 
Inner Join 
     (Select omdSalesOrderID As Key1,
      omdSalesOrderLineID As Key2,
      omdSalesOrderDeliveryID As Key3,
      omdDeliveryQuantity-     omdQuantityShipped As RemainingQuantity 
       From SalesOrderDeliveries
     ) As b 
On a.omdSalesOrderID = Key1 
And a.omdSalesOrderLineID = Key2 
And a.omdSalesOrderDeliveryID = Key3 
inner join SalesOrderLines 
on omdSalesOrderID=omlSalesOrderID 
and omdSalesOrderLineID=omlSalesOrderLineID 
inner join SalesOrders 
on omdSalesOrderID=ompSalesOrderID 
WHERE omdClosed = 0 
and omdShippedComplete = 0 
and omdDeliveryQuantity > omdQuantityShipped

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 13:54:44

did you mean this?


SELECT OMDSALESORDERID,
OMDSALESORDERLINEID,
OMDSALESORDERDELIVERYID,
OMDDELIVERYDATE,
OMPCUSTOMERORGANIZATIONID,
OMDDELIVERYQUANTITY,
OMDQUANTITYSHIPPED,
REMAININGQUANTITY AS Remaining Qty,
OMLUNITPRICEBASE,OMLPARTID,
OMLPARTREVISIONID,
OMLPARTSHORTDESCRIPTION 
FROM SalesOrderDeliveries a 
Inner Join 
     (Select omdSalesOrderID As Key1,
      omdSalesOrderLineID As Key2,
      omdSalesOrderDeliveryID As Key3,
      omdDeliveryQuantity-     omdQuantityShipped As RemainingQuantity,
       (omdDeliveryQuantity-     omdQuantityShipped) * omlFullUnitBasePrice AS YourQty, 
       From SalesOrderDeliveries
     ) As b 
On a.omdSalesOrderID = Key1 
And a.omdSalesOrderLineID = Key2 
And a.omdSalesOrderDeliveryID = Key3 
inner join SalesOrderLines 
on omdSalesOrderID=omlSalesOrderID 
and omdSalesOrderLineID=omlSalesOrderLineID 
inner join SalesOrders 
on omdSalesOrderID=ompSalesOrderID 
WHERE omdClosed = 0 
and omdShippedComplete = 0 
and omdDeliveryQuantity > omdQuantityShipped

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources