IS Anything wrong in this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

IS Anything wrong in this query?

Author

Topic

alisag
Starting Member

5 Posts

Posted - 2010-01-26 : 04:52:18

I have 3 Tables- Transfer Line, Item Ledger Entry(Alias ILE) and Production Order Line(Alias POLine). I want all the records from transfer line table. The other 2 tables are related to Transfer line with Transfer Line.[Item No_] = ILE.[Item No_] Transfer Line.[Item No_] = POline.[Item No_].
Now how to join the three tables. I wrote the following query but I m not getting the desired result for POLIne.Quantity. I have 30 records in transfer line table.

SELECT [Transfer Line].[Document No_], [Transfer Line].[Transfer-from Code], [Transfer Line].[Item No_], [Transfer Line].Description, [Transfer Line].Quantity,
SUM(ILE.Quantity) AS Inventory, SUM([Prod Order Lines].[Remaining Qty_ (Base)]) AS [Qty on Prod(Released)],
SUM([Prod Order Lines2].[Remaining Quantity]) AS [Qty On Prod(Firm Planned)], [Transfer Line].[Transfer-to Code]
FROM [Company$Item Ledger Entry] AS ILE RIGHT OUTER JOIN
[Company$Transfer Line] AS [Transfer Line] LEFT OUTER JOIN
[Company$Prod_ Order Line] AS [Prod Order Lines] ON [Transfer Line].[Item No_] = [Prod Order Lines].[Item No_] AND
[Prod Order Lines].Status = 3 LEFT OUTER JOIN
[Company$Prod_ Order Line] AS [Prod Order Lines2] ON [Transfer Line].[Item No_] = [Prod Order Lines2].[Item No_] AND
[Prod Order Lines2].Status = 2 ON ILE.[Location Code] = [Transfer Line].[Transfer-from Code] AND ILE.[Item No_] = [Transfer Line].[Item No_]
GROUP BY [Transfer Line].[Document No_], [Transfer Line].[Transfer-from Code], [Transfer Line].[Item No_], [Transfer Line].Description, [Transfer Line].Quantity,
[Transfer Line].[Transfer-to Code]

Transfer Line
Document No.
No. Item
Item Description
XYZ Item 1 First Item
XYZ Item 2 Second Item
ABC Item 3 Third Item
ABC Item 2 Second Item
PQR Item 1 First Item

Item Ledger Entry As ILE
Entry No. Item No. Quantity
1 Item 1 4
2 Item 1 3
3 Item 2 5
4 Item 3 2
5 Item 2 2
6 Item 1 1
7 Item 3 10
8 Item 3 4

Prod. Order Line
Prod Order No. Status item No. Quantity
WO-111234 2 Item 1 100
WO-111113 3 Item 3 50
WO-111009 1 Item 2 100
WO-111678 2 Item 1 195
WO-111990 3 Item 1 200
WO-187645 3 Item 1 120

The ILE, Prod Order Line are related to Transfer Line table with Item No_.

Query should work like this.
Get the first record from Transfer Line Table. Take the Item_No.(In this case it is Item 1)

Go to ILE table with ILE.Item No_ = Transfer Line. Item No_
sum of the quantity fields in ILE table based on this condition

o Result Till here

Document No. Item No. Inventory(sum of Quantity field in ILE)
XYZ Item 1 8

now go to Prod. Order Line Sum up the quantities where Prod. Order Line.Item No. = Transfer Line.Item No. AND Prod. Order Line.Status = 3

o Result Till here
Document No. Item No. Inventory(sum of Quantity field in ILE) Qty on Released Prod Order
XYZ Item 1 8 320

Again go to Prod. Order Line Table Sum of the quantities Prod. Order Line.Item No. = Transfer Line.Item No. AND Prod. Order Line.Status = 2
o Result Till here
Doc No. Item No. Qty(Sum of Qty filed in ILE) Qty on Prod Order Line(Status 3) Qty on Prod Order Line (Status 2)
XYZ Item 1 8 320 295

So the final result should come like

Doc No. Item No. Qty(Sum of Qty filed in ILE) Qty on Prod Order Line(Status 3) Qty on Prod Order Line (Status 2)
XYZ Item 1 8 320 295
XYZ Item 2 7 0 0

ABC Item 3 16 50 0
ABC Item 2 7 0 0
PQR Item 1 8 320 295

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 05:03:17

the query should be


select t1.[Document No.],
t1.[Item No.],
ile.TotalQty,
pol.[TotalQtySt3],
pol.[TotalQtySt2]
from [Transfer Line] tl
inner join (select [Item No.], SUM(Quantity) AS TotalQty
            from  [Item Ledger Entry]
            group by [Item No.]) ile
ON ile.[Item No.] = t1.[No.Item]
inner join (select [Item No.],
            SUM(case when Status=3 then Quantity else 0 end) AS [TotalQtySt3],
            SUM(case when Status=2 then Quantity else 0 end) AS [TotalQtySt2]
            from [Prod. Order Line]
            group by [Item No.])pol
on pol.[Item No.] = t1.[No.Item]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources