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.
Author |
Topic |
hayashiryo
Starting Member
46 Posts |
Posted - 2013-09-29 : 10:40:18
|
Hi. I need some help with formulating a SQL Statement.I have a table that logs down transfer of inventory record.In 1 row of record, it stores the item code, from location, to location and the quantity transferred.I want to formulate a statement that returns 2 records for each transfer record. I know it sounds confusing. Perhaps the example below will clarify what I'm trying to achieve.dbo.Transfer tableTxId Item Name FromLocationId ToLocationId Qty1 9001 Pen 10 11 100 Result I've trying to achieveNote: I need the qty to indicate +ve and -ve as indicated belowTxId Item Name FromLocation Qty1 9001 Pen 10 -1001 9001 Pen 11 +100 Any suggestions?Thanks very much in advance. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-29 : 17:09:45
|
[code]select TxId, Item, Name, FromLocationId as FromLocation, -Qty as Qtyfrom YourTableunion allselect TxId, Item, Name, ToLocationId as FromLocation, Qty as Qtyfrom YourTable;[/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-30 : 06:31:13
|
[code]select t.TxId, t.Item, t.Name, CASE WHEN mult=-1 THEN FromLocationId ELSE ToLocationId END as FromLocation, Qty * mult as Qtyfrom YourTable t cross join (select -1 as mult union all select 1)m[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
hayashiryo
Starting Member
46 Posts |
Posted - 2013-10-02 : 22:38:40
|
Thanks all. Works nicely now |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-04 : 01:31:03
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|