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 |
|
geraldr
Starting Member
5 Posts |
 Posted - 2010-08-09 : 08:52:41
|
| I'm having a problem starting with this rather complex report that's been requested. I guess this is technically an SSRS issue, but I think starting at the beginning with the statement would be better.I'm trying to create an inventory report with multiple quantities (Available Qty, Committed, Ordered, Purchased, etc...) and many other fields. All of the inventory is divided up by location. I need to pull one field from one location and another from a second location. For example, Committed Qty comes from one location while Ordered comes from a second. At times, calculations will need to be done between quantities in different locations, and all brought in to one table. What is the best way to approach this? I though of doing combined queries, but I'm not using the same SELECT statements so that wouldn't seem to work.Any help is greatly appreciated. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-09 : 09:20:21
|
Please give us table structure, sample data and wanted output.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
geraldr
Starting Member
5 Posts |
Posted - 2010-08-09 : 13:44:24
|
| I'm not sure how to best do this since these tables are gigantic, let me see if this helps out:Table structure:Table 1 ("Inventory") - Base inventory tableID, GroupID, CodeSample Data:1856, 19, V110-HDLTable 2 ("ItemLocations") - Location specific inventory informationInventoryID, BranchID, OnHandQuantity, OrderedQuantity, CommittedQuantity, AvailableQuantitySample Data:For location "1"1856, 50, 500, 499, 0, 500For Location "2"1856, 54, 0, 0, 0, 0Table 3 ("Branches")BranchID, Name,Sample data: 50, WarehouseThe wanted output would look something like this:GroupID, Code, Available Qty (Location 2), OnHandQuantity (Location 2), Committed Quantity (Location 1), Ordered Quantity (Location 2)I'm afraid this is getting terribly complicated, but that's some of the basics. There would be other fields that calculate some quantities between the locations, but I'm first trying to figure out the best way to pull this all in to a SQL statement. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-08-09 : 13:53:40
|
| Are there always two (and only two) locations for every InventoryID?How do you differentiate between Location 1 and 2? |
|
|
|
geraldr
Starting Member
5 Posts |
Posted - 2010-08-09 : 15:58:53
|
| For the purposes of this SQL Statement, yes, there are only two locations. They are differentiated either by their branch ID or by their name under the Branch table. |
|
|
|
geraldr
Starting Member
5 Posts |
Posted - 2010-08-11 : 08:54:58
|
| Has anyone had a chance to take a look at this? I'm really looking for some help on this. Is there any more information that's needed? |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-08-11 : 09:38:27
|
quote:
Originally posted by geraldr
Has anyone had a chance to take a look at this? I'm really looking for some help on this. Is there any more information that's needed?
You still haven't answered a few questions. From this data..For location "1"1856, 50, 500, 499, 0, 500For Location "2"1856, 54, 0, 0, 0, 0 How do we know 50 is Location 1 and 54 is Location 2? |
|
|
|
geraldr
Starting Member
5 Posts |
Posted - 2010-08-11 : 09:50:39
|
quote:
Originally posted by vijayisonly
You still haven't answered a few questions. From this data..For location "1"1856, 50, 500, 499, 0, 500For Location "2"1856, 54, 0, 0, 0, 0 How do we know 50 is Location 1 and 54 is Location 2?[/quote]50 and 54 are just the IDs for the location. I'm not entirely sure how to answer the question. |
|
|
|
|
|
|
|
|
Pulling multiple tables in a query