Query Help - 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)

Query Help

Author

Topic

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-11 : 20:17:27

Hi,

I'm writing a query where I need it to add up certain records from a table based upon a value from another table.

Example:
If the current date is 12/06/2008 and the value returned is 13/09/2008, I want to add up records in another table that represent July and August.

Basically the report is going to tell me the date a delivery of a certain item is due into our warehouse, and whether we will have enough stock of that item until the delivery arrives, based on a sales forecast.

So I have the delivery due date and a forecast for each month. I want to add up the forecast records that come before the delivery due date, then subtract this from our current on hand figure. My problem is getting SQL to know which records it has to add up based on the delivery due date.

Hope I have explained well enough.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 01:01:19

Nope not quite. You would more sense when you've some sample data with which you illustrate what you're looking for.

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-12 : 01:36:17

OK...
Assume all the info below is stored in fields in various tables:
Current date June 12th, 2008
Item Number 123456
Currently hold 20 in stock
Item is on Purchase Order 100
Purchase Order due for delivery on October 1st, 2008
Purchase Order is for 40 of this item.
Forecast for sales of item 123456 for the month of July = 15
Forecast for sales of item 123456 for the month of August = 10
Forecast for sales of item 123456 for the month of September = 10
Forecast for sales of item 123456 for the month of October = 10
Forecast for sales of item 123456 for the month of November = 15
Forecast for sales of item 123456 for the month of December = 20

What I need to do is have the query do the following calculation:
Qty On Hand less Sales Qty Forecast for the months up to the date it is due for delivery. So in this example, I would want 20 - 5 - 10 - 10. On hand less July Forecast less August Forecast less September Forecast. I don't need October to December because the Purchase Order is due for delivery before these periods.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 01:45:48

quote:
Originally posted by michaelb

OK...
Assume all the info below is stored in fields in various tables:
Current date June 12th, 2008
Item Number 123456
Currently hold 20 in stock
Item is on Purchase Order 100
Purchase Order due for delivery on October 1st, 2008
Purchase Order is for 40 of this item.
Forecast for sales of item 123456 for the month of July = 15
Forecast for sales of item 123456 for the month of August = 10
Forecast for sales of item 123456 for the month of September = 10
Forecast for sales of item 123456 for the month of October = 10
Forecast for sales of item 123456 for the month of November = 15
Forecast for sales of item 123456 for the month of December = 20

What I need to do is have the query do the following calculation:
Qty On Hand less Sales Qty Forecast for the months up to the date it is due for delivery. So in this example, I would want 20 - 5 - 10 - 10. On hand less July Forecast less August Forecast less September Forecast. I don't need October to December because the Purchase Order is due for delivery before these periods.

somethink like this:-

SELECT m.ItemNumber,m.QtyInStock - b.ForecastTotal
FROM MainTable m
CROSS APPLY(SELECT SUM(ForecastQty) AS ForecastTotal
            FROM ForecastTable
            WHERE ItemNumber=m.ItemNumber
            AND ForecastMonthNo<MONTH(m.DeliveryDueDate))b

i dont know ur column names and table structures so this provide you with generic solution.If you need more accurate solution, please provide your table structures with some sample data

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-12 : 02:08:13

Current date June 12th, 2008 - GETDATE()

Item Number 123456 - ITEMMST.ITEM_NUMBER

Currently hold 20 in stock - ITEMMST.ON_HAND_QTY

Item is on Purchase Order 100 - SUPPLIER_ORDS.PO_NUMBER (Joined on SUPPLIER_ORDS.ITEM_NUMBER = ITEMMST.ITEM_NUMBER)

Purchase Order due for delivery on October 1st, 2008 - SUPPLIER_ORDS.PO_DUE_DATE

Purchase Order is for 40 of this item - SUPPLIER_ORDS.PO_QTY

Forecast for sales of item 123456 for the month of July = 15 - FORECST1.MTH1YEARF (Joined on FORECST1.ITEM_NUMBER = ITEMMST.ITEM_NUMBER)
Forecast for sales of item 123456 for the month of August = 10 - FORECST1.MTH2YEARF
Forecast for sales of item 123456 for the month of September = 10 - FORECST1.MTH3YEARF
Forecast for sales of item 123456 for the month of October = 10 - FORECST1.MTH4YEARF
Forecast for sales of item 123456 for the month of November = 15 - FORECST1.MTH5YEARF
Forecast for sales of item 123456 for the month of December = 20 - FORECST1.MTH6YEARF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 02:20:37

Why have months started with July? Your posted column MTH1YEARF contains value for July and continues . Does your year always start with July and ends with June?

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-12 : 02:25:13

Yes it does, it follows our Financial Year

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 02:56:05

it will be something on lines of

;
With MonthValues_CTE (ID,Months,MonthValue)AS
(
SELECT p.ID,p.Months,p.MonthValue FROM
(
SELECT p.ITEM_NUMBER,p.Months,p.MonthValue FROM FORECST1)m
UNPIVOT (MonthValue FOR Months IN ([MTH1YEARF],[MTH2YEARF],[MTH3YEARF],.. ,[MTH12YEARF] ))p
)


SELECT i.ITEM_NUMBER,i.ON_HAND_QTY-b.ForecastSum
FROM ITEMMST i
INNER JOIN SUPPLIER_ORDS s
ON s.ITEM_NUMBER = i.ITEM_NUMBER
CROSS APPLY (SELECT SUM(c.MonthValue) AS ForecastSum
             FROM MonthValues_CTE 
             WHERE ItemNumber=i.ITEM_NUMBER
             AND REPLACE(REPLACE(Months,'MTH',''),'YEARF','')<MONTH(s.PO_DUE_DATE)-6 + CASE WHEN (MONTH(s.PO_DUE_DATE)-6) <=0 THEN 12 ELSE 0 END
)b

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-12 : 18:20:19

I'm getting all this when I use it:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "p.ITEM_NUMBER" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "p.Months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "p.MonthValue" could not be bound.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'MTH1YEARF'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'MTH2YEARF'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'MTH3YEARF'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'MTH4YEARF'.
Msg 265, Level 16, State 1, Line 6
The column name "MonthValue" specified in the UNPIVOT operator conflicts with the existing column name in the UNPIVOT argument.
Msg 265, Level 16, State 1, Line 6
The column name "Months" specified in the UNPIVOT operator conflicts with the existing column name in the UNPIVOT argument.
Msg 8156, Level 16, State 1, Line 6
The column 'MonthValue' was specified multiple times for 'p'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'ID'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 01:07:58

[code]DECLARE @FORECST1 table
(
ITEM_NUMBER int,
MTH1YEARF int,--july
MTH2YEARF int,
MTH3YEARF int,
MTH4YEARF int,
MTH5YEARF int,
MTH6YEARF int,
MTH7YEARF int,
MTH8YEARF int,
MTH9YEARF int,
MTH10YEARF int,
MTH11YEARF int,
MTH12YEARF int
)

INSERT INTO @FORECST1
SELECT 101,21,32,44,10,5,0,0,0,0,6,12,11
UNION ALL
SELECT 105,22,44,38,2,5,5,0,3,8,12,11,22

DECLARE @ITEMMST table
(
ITEM_NUMBER int,
ON_HAND_QTY int
)

INSERT INTO @ITEMMST
SELECT 101,400
UNION ALL
SELECT 105,310

DECLARE @SUPPLIER_ORDS table
(
ITEM_NUMBER int,
PO_DUE_DATE datetime
)
INSERT INTO @SUPPLIER_ORDS
SELECT 101,'1 Oct 2008'
UNION ALL
SELECT 105,'1 Sep 2008'
;
With MonthValues_CTE (ItemNumber,Months,MonthValue)AS
(
SELECT p.ITEM_NUMBER,p.Months,p.MonthValue FROM
(SELECT * FROM @FORECST1)m
UNPIVOT (MonthValue FOR Months IN ([MTH1YEARF],[MTH2YEARF],[MTH3YEARF],[MTH4YEARF],[MTH5YEARF],[MTH6YEARF],[MTH7YEARF],
[MTH8YEARF],[MTH9YEARF],[MTH10YEARF],[MTH11YEARF],[MTH12YEARF]))p
)

SELECT i.ITEM_NUMBER,i.ON_HAND_QTY-ISNULL(b.ForecastSum,0)
FROM @ITEMMST i
INNER JOIN @SUPPLIER_ORDS s
ON s.ITEM_NUMBER = i.ITEM_NUMBER
CROSS APPLY (SELECT SUM(MonthValue) AS ForecastSum
FROM MonthValues_CTE
WHERE ItemNumber=i.ITEM_NUMBER
AND REPLACE(REPLACE(Months,'MTH',''),'YEARF','')<MONTH(s.PO_DUE_DATE)-6 + CASE WHEN (MONTH(s.PO_DUE_DATE)-6) <=0 THEN 12 ELSE 0 END
)b
output
-------------------------------------------------
ITEM_NUMBER
----------- -----------
101 303
105 244
[/code]

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-15 : 20:07:28

Thanks for that.

Could you please explain what these parts are and what statement I would need to get my data from the tables?

SELECT 101,21,32,44,10,5,0,0,0,0,6,12,11
UNION ALL
SELECT 105,22,44,38,2,5,5,0,3,8,12,11,22

SELECT 101,400
UNION ALL
SELECT 105,310

SELECT 101,'1 Oct 2008'
UNION ALL
SELECT 105,'1 Sep 2008'

Thanks again.

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-15 : 23:39:31

I've worked them out.

Only issue I'm having is it returns a record per PO where an item has more than one PO. How can I include only the PO with the earliest due date?

----------------------------------------------------
--GET FORECAST DETAILS--
----------------------------------------------------

DECLARE @FORECST1 table
(
ITEM_NUMBER varchar(30),
MTH1YEARF int,--july
MTH2YEARF int,
MTH3YEARF int,
MTH4YEARF int,
MTH5YEARF int,
MTH6YEARF int,
MTH7YEARF int,
MTH8YEARF int,
MTH9YEARF int,
MTH10YEARF int,
MTH11YEARF int,
MTH12YEARF int
)

INSERT INTO @FORECST1
SELECT t0.item_number, sum(t0.MTH1YEARF),sum(t0.MTH2YEARF),sum(t0.MTH3YEARF),sum(t0.MTH4YEARF),sum(t0.MTH5YEARF),sum(t0.MTH6YEARF),
sum(t0.MTH7YEARF),sum(t0.MTH8YEARF),sum(t0.MTH9YEARF),sum(t0.MTH10YEARF),sum(t0.MTH11YEARF),sum(t0.MTH12YEARF)
from vpp_2006sbo.dbo.forecst1 t0
group by t0.item_number

----------------------------------------------------
--GET ON HAND QTY--
----------------------------------------------------

DECLARE @ITEMMST table
(
ITEM_NUMBER varchar(30),
ON_HAND_QTY int
)

INSERT INTO @ITEMMST
SELECT t1.item_number, sum(t1.on_hand_qty)
from vpp_2006sbo.dbo.itemmst t1
group by t1.item_number

----------------------------------------------------
--GET PO DETAILS--
----------------------------------------------------

DECLARE @SUPPLIER_ORDS table
(
ITEM_NUMBER varchar(30),
PO_DUE_DATE datetime
)
INSERT INTO @SUPPLIER_ORDS
SELECT t2.item_number,t2.po_due_date
from vpp_2006sbo.dbo.supplier_ords t2
where t2.location <> 'Z'

;
With MonthValues_CTE (ItemNumber,Months,MonthValue)AS
(
SELECT p.ITEM_NUMBER,p.Months,p.MonthValue FROM
(SELECT * FROM @FORECST1)m
UNPIVOT (MonthValue FOR Months IN ([MTH1YEARF],[MTH2YEARF],[MTH3YEARF],[MTH4YEARF],[MTH5YEARF],[MTH6YEARF],[MTH7YEARF],
[MTH8YEARF],[MTH9YEARF],[MTH10YEARF],[MTH11YEARF],[MTH12YEARF]))p
)

----------------------------------------------------
--FINAL CALCULATION--
----------------------------------------------------

SELECT i.ITEM_NUMBER,i.ON_HAND_QTY-ISNULL(b.ForecastSum,0)
FROM @ITEMMST i
INNER JOIN @SUPPLIER_ORDS s
ON s.ITEM_NUMBER = i.ITEM_NUMBER
CROSS APPLY (SELECT SUM(MonthValue) AS ForecastSum
FROM MonthValues_CTE
WHERE ItemNumber=i.ITEM_NUMBER
AND REPLACE(REPLACE(Months,'MTH',''),'YEARF','')<=MONTH(s.PO_DUE_DATE)-6 + CASE WHEN (MONTH(s.PO_DUE_DATE)-6) <=0 THEN 12 ELSE 0 END
)b

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 01:11:30

Change lst SELECT like this and see:-


SELECT m.ITEM_NUMBER,m.MinPODate,m.ON_HAND_QTY-ISNULL(b.ForecastSum,0)
FROM (SELECT i.ITEM_NUMBER,i.ON_HAND_QTY,mindate.MinPODate
FROM @ITEMMST i
INNER JOIN @SUPPLIER_ORDS s
ON s.ITEM_NUMBER = i.ITEM_NUMBER
INNER JOIN (SELECT ITEM_NUMBER,MIN(PO_DUE_DATE) AS MinPODate
            FROM @SUPPLIER_ORDS
            GROUP BY ITEM_NUMBER)mindate
ON mindate.ITEM_NUMBER=i.ITEM_NUMBER
AND mindate.MinPODate=s.PO_DUE_DATE)m
CROSS APPLY (SELECT SUM(MonthValue) AS ForecastSum
             FROM MonthValues_CTE 
             WHERE ItemNumber=m.ITEM_NUMBER
             AND REPLACE(REPLACE(Months,'MTH',''),'YEARF','')<MONTH(m.MinPODate)-6 + CASE WHEN (MONTH(m.MinPODate)-6) <=0 THEN 12 ELSE 0 END
)b

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-16 : 01:56:54

Great!

But how can I join in the other details I require such as PO_QTY, PO_ORDER_DATE, ON_HAND_QTY etc

They're all in the same tables used in the query already, just not required in the calculation but I need the info for my report.

Cheers.

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-16 : 02:02:48

If I add them in like this I end up with duplicates again

SELECT m.ITEM_NUMBER,m.MinPODate,m.ON_HAND_QTY-ISNULL(b.ForecastSum,0), m.po_qty, m.po_number
FROM (SELECT i.ITEM_NUMBER,i.ON_HAND_QTY,mindate.MinPODate, mindate.po_qty, mindate.po_number
FROM @ITEMMST i
INNER JOIN @SUPPLIER_ORDS s
ON s.ITEM_NUMBER = i.ITEM_NUMBER
INNER JOIN (SELECT ITEM_NUMBER,MIN(PO_DUE_DATE) AS MinPODate, PO_QTY, PO_NUMBER
FROM @SUPPLIER_ORDS
GROUP BY ITEM_NUMBER, PO_QTY, PO_NUMBER)mindate
ON mindate.ITEM_NUMBER=i.ITEM_NUMBER
AND mindate.MinPODate=s.PO_DUE_DATE)m
CROSS APPLY (SELECT SUM(MonthValue) AS ForecastSum
FROM MonthValues_CTE
WHERE ItemNumber=m.ITEM_NUMBER
AND REPLACE(REPLACE(Months,'MTH',''),'YEARF','')<MONTH(m.MinPODate)-6 + CASE WHEN (MONTH(m.MinPODate)-6) <=0 THEN 12 ELSE 0 END
)b

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

[code]SELECT m.ITEM_NUMBER,m.MinPODate,m.PO_QTY,m.PO_NUMBER,m.ON_HAND_QTY-ISNULL(b.ForecastSum,0)
FROM (SELECT i.ITEM_NUMBER,i.ON_HAND_QTY,mindate.MinPODate,s.PO_QTY,s.PO_NUMBER
FROM @ITEMMST i
INNER JOIN @SUPPLIER_ORDS s
ON s.ITEM_NUMBER = i.ITEM_NUMBER
INNER JOIN (SELECT ITEM_NUMBER,MIN(PO_DUE_DATE) AS MinPODate
FROM @SUPPLIER_ORDS
GROUP BY ITEM_NUMBER)mindate
ON mindate.ITEM_NUMBER=i.ITEM_NUMBER
AND mindate.MinPODate=s.PO_DUE_DATE)m
CROSS APPLY (SELECT SUM(MonthValue) AS ForecastSum
FROM MonthValues_CTE
WHERE ItemNumber=m.ITEM_NUMBER
AND REPLACE(REPLACE(Months,'MTH',''),'YEARF','')<MONTH(m.MinPODate)-6 + CASE WHEN (MONTH(m.MinPODate)-6) <=0 THEN 12 ELSE 0 END
)b[/code]

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-16 : 02:29:55

Thanks!

I now want to say only return items where calculation result is less than 5.

having m.ON_HAND_QTY-ISNULL(b.ForecastSum,0) < 5

returns:
Msg 8121, Level 16, State 1, Line 63
Column 'm.ON_HAND_QTY' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Line 63
Column 'b.ForecastSum' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 02:34:33

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-16 : 02:51:08

Thanks heaps for all that!

I'm just trying to verify the data the query has returned and I'm finding some odd results.

One is where the PO due date is in the past. What would the calculation be doing with the forecast figures where this is the case?

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 02:58:56

quote:
Originally posted by michaelb

Thanks heaps for all that!

I'm just trying to verify the data the query has returned and I'm finding some odd results.

One is where the PO due date is in the past. What would the calculation be doing with the forecast figures where this is the case?

Cheers

Didnt get that. Can you explain how data will be in this case?

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-06-16 : 03:08:12

Well for example where a PO has a due date that has already passed, it seems to be doing on hand less the forecast total of the entire year. I guess what I want it to do is return on hand less the next month's forecast.

It also seems to be doing this where the po due date is in the current month.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources