I'm Stuck - 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)

I'm Stuck

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-15 : 18:33:24

Hi

I am completely stuck trying to make a query

Heres is a shorten downed example of what I am trying to achieve. I have four tables. The first being OrderHeader, the second OrderLines, the third StockCategory. The OrderHeader table contains basic order details, which in turn is linked to the OrderLines table which show if the order in the OrderHeader has a single or multiple order lines. The StockCategory table shows what stock group the item in each order line is associated to.

Here is an example of the tables in a shorten downed version (in both data and fields):

OrderHeader:
Sales Order Ref, Order Date,
1, 01/05/2008
2, 01/05/2008
3, 02/05/2008
4, 02/05/2008

OrderLines:
Sales Order Ref, Part Number
1, 222
1, 234
1, 333
2, 222
2, 555
2, 444
3, 333
3, 111
4, 222

StockCategory

Stock Category, Part Number,
A, 222
B, 234
C, 333
D, 444
E, 111

Stock Group:
Stock Group, Stock Category, Priority
Berr, A, 1
Berr, B, 1
OGD, C, 2
OGD, D, 2
DFID , E, 3

The thing I am trying to do is assign each of the orders to a Stock Group which I can do. The thing that has baffled me is if an two of the order lines in one order are assigned to different stock groups. If this occurs I want to assign the order to the order to the stock group with the highest priority (1 being highest, 3 being lowest) for example if one order line in the order was assigned to Berr (priority 1) and the other to DFID (priority 3) the order would be assigned to Berr.

I am using the following query:

DECLARE @Period DATETIME
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME

SET @Period =’2008-05-01’

SELECT @BeginDate = [BeginDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT @EndDate = [EndDate] FROM DatePeriods AS dp WHERE dp.ID = @Period

SELECT
COUNT(oh.[Sales Order Reference])
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockCategories AS sc ON ol.[Part Number] = sc.[Part Number]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Stock Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'Berr')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])

GROUP BY oh.[Sales Order Reference]

Is this possible to do?

Thanking you in advance!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 00:36:17

Is this what you're looking for?

DECLARE @OrderHeader table
(
Sales_Order_Ref int, Order_Date datetime)
INSERT INTO  @OrderHeader
SELECT 1, '01/05/2008'
UNION ALL
SELECT 2, '01/05/2008'
UNION ALL
SELECT 3, '02/05/2008'
UNION ALL
SELECT 4, '02/05/2008'

DECLARE @OrderLines table
(Sales_Order_Ref int, Part_Number int)
INSERT INTO @OrderLines
SELECT 1, 222
UNION ALL
SELECT 1, 234
UNION ALL
SELECT 1, 333
UNION ALL
SELECT 2, 222
UNION ALL
SELECT 2, 555
UNION ALL
SELECT 2, 444
UNION ALL
SELECT 3, 333
UNION ALL
SELECT 3, 111
UNION ALL
SELECT 4, 222

DECLARE @StockCategory table
(Stock_Category char(1), Part_Number int)
INSERT INTO  @StockCategory
SELECT 'A', 222
UNION ALL
SELECT 'B', 234
UNION ALL
SELECT 'C', 333
UNION ALL
SELECT 'D', 444
UNION ALL
SELECT 'E', 111

DECLARE @StockGroup table
(
Stock_Group varchar(5), Stock_Category char(1), Priority int)
INSERT INTO @StockGroup
SELECT  'Berr', 'A', 1
UNION ALL
SELECT 'Berr', 'B', 1
UNION ALL
SELECT 'OGD', 'C', 2
UNION ALL
SELECT 'OGD', 'D', 2
UNION ALL
SELECT 'DFID' , 'E', 3

SELECT MAX(Stock_Group) AS StockGroup,COUNT(DISTINCT Sales_Order_Ref) AS OrderLineCount
FROM @StockGroup sg1
LEFT JOIN
(
SELECT ol.Sales_Order_Ref,MIN(Priority) AS HighestPriority
FROM @OrderHeader oh
INNER JOIN @OrderLines ol
ON ol.Sales_Order_Ref=oh.Sales_Order_Ref
INNER JOIN @StockCategory sc
ON sc.Part_Number=ol.Part_Number
INNER JOIN @StockGroup sg
ON sg.Stock_Category=sc.Stock_Category
GROUP BY ol.Sales_Order_Ref) t
ON t.HighestPriority=sg1.Priority
GROUP BY HighestPriority

StockGroup OrderLineCount
---------- --------------
DFID       0
Berr       3
OGD        1

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-16 : 04:46:30

Hi

Thanks for that, is it possible to put in a similar syntax that I have used above for consistency. Sorry to be a pain.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 05:02:38

quote:
Originally posted by rcr69er

Hi

Thanks for that, is it possible to put in a similar syntax that I have used above for consistency. Sorry to be a pain.

Thanks

Similar syntax? didnt understand that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 05:20:00

Did you mean this?

SELECT MAX(Stock_Group) AS StockGroup,COUNT(DISTINCT Sales_Order_Ref) AS OrderLineCount
FROM @StockGroup sg1
LEFT JOIN
(
SELECT ol.Sales_Order_Ref,MIN(Priority) AS HighestPriority
FROM @OrderHeader oh
LEFT JOIN @OrderLines ol
ON ol.Sales_Order_Ref=oh.Sales_Order_Ref
LEFT JOIN @StockCategory sc
ON sc.Part_Number=ol.Part_Number
LEFT JOIN @StockGroup sg
ON sg.Stock_Category=sc.Stock_Category
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
GROUP BY ol.Sales_Order_Ref) t
ON t.HighestPriority=sg1.Priority
GROUP BY HighestPriority

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-16 : 06:01:06

Sorry I meant apply it to the query that I had already created.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 06:09:33

quote:
Originally posted by rcr69er

Sorry I meant apply it to the query that I had already created.

Thanks

But that wont give you desired result. that was the reason why i cahnged it. ANy reason why you cant use mine?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources